Xav2
asked on
ALTER TABLESPACE READ WRITE and ORA-01256
Hello,
Somteimes after this command : ALTER TABLESPACE READ WRITE
I receive : ORA-01256 error in locking database file xxxxx
The messages manual says :
Cause: The file is in use by another database instance.
Action: Determine which database instance legitimately owns the file.
But there's only ONE instance on my server.
How can I see who is locking the .dbf ?
Thanks
Somteimes after this command : ALTER TABLESPACE READ WRITE
I receive : ORA-01256 error in locking database file xxxxx
The messages manual says :
Cause: The file is in use by another database instance.
Action: Determine which database instance legitimately owns the file.
But there's only ONE instance on my server.
How can I see who is locking the .dbf ?
Thanks
Do you have any other transportable tablespaces ? Does the tablespace have any partitioned table that is partitioned over to another transportable tablespace ? If you do, then you may face this error after exporting the other transportable tablespace.
ASKER
The tablespace is not a transportable TS .
I put it RO to prevent backup of static data and now Iwant to drop it.
The tablespace contains one partition of a partitioned table.
Say you have tablespaces T1, T2 which contain the partitioned table Table1.
Tablespace T1 is read-only and T2 is read-write.
Create another table with the same columns as the partitioned table in tablespace T2.
SQL> create table mytable tablespace T2
as select * from table1 where 1 = 2;
Exchange the partition with the newly created table.
SQL> alter table table1 exchange partition p1 with table mytable;
Mytable now has partions in T1 and T2.
Drop the newly created table mytable.
Now your tablespace T1 does not have any overlapping partitioned tables.
You should now be able to make it read-write or even drop it.
Tablespace T1 is read-only and T2 is read-write.
Create another table with the same columns as the partitioned table in tablespace T2.
SQL> create table mytable tablespace T2
as select * from table1 where 1 = 2;
Exchange the partition with the newly created table.
SQL> alter table table1 exchange partition p1 with table mytable;
Mytable now has partions in T1 and T2.
Drop the newly created table mytable.
Now your tablespace T1 does not have any overlapping partitioned tables.
You should now be able to make it read-write or even drop it.
Try to shutdown the database in normal mode.
then log in as sys, start the database in the restricted mode:
SQL> start restricted
then log in as sys, start the database in the restricted mode:
SQL> start restricted
ASKER
Here's the solution :
Increase the Sun-Solaris parameter rlim_fd_max (max number of open files per process) then reboot.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.