Solved

ALTER TABLESPACE READ WRITE and ORA-01256

Posted on 2003-12-12
7
1,182 Views
Last Modified: 2007-12-19
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


0
Comment
Question by:Xav2
7 Comments
 
LVL 10

Expert Comment

by:SDutta
ID: 9929303
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.
0
 

Author Comment

by:Xav2
ID: 9929407

 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.

 
0
 
LVL 10

Expert Comment

by:SDutta
ID: 9929638
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.

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
ID: 9935556
Try to shutdown the database in normal mode.

then log in as sys, start the database in the restricted mode:

SQL> start restricted
0
 

Author Comment

by:Xav2
ID: 10450287

 Here's the solution :

 Increase the Sun-Solaris parameter rlim_fd_max (max number of open files per process) then reboot.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 12176163
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Maintaining Oracle Managed Accounts 2 30
Migrate Oracle Database from ASM to Non-ASM on a Windows server. 1 33
Get the parent node - XMLTYPE 9 56
SQL Query 34 82
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now