Alter TBS read only waits for end of all sessions 1

[anuktac]

We have a daily batch that alters a tablespace to read only, then takes backups and puts it back to read-write mode again.

The problem is, as long as there are users logged on to this database (even though they are simple select statements and accessing objects from other TBS), the alter tablespace waits. The weird thing is this that even if there are idle sessions in the DB, the "ALTER TBS read only" still waits.

Is there a way around this? Like, ending the sessions on the DB and making the alter TBS run immediately.

-Anukta

 

[Scunningham99]

QUESTION: ARE YOU TAKING BACKUPS OF THE TABLESPACES?

IF SO INSTEAD OF PUTTING THEM INTO READ ONLY (WHICH WILL WAIT FOR TRANSACTIONS TO FINISH) YOU CAN PUT THE TABLESPACES INTO BACKUP MODE (FOR HOT BACKUPS, ASSUMING THIS IS WHAT YOU WANT TO DO!)

SYNTAX: ALTER TABLESPACE <TABLESPACE NAME> BEGIN BACKUP;

ONCE YOU HAVE FINISHED BACKING UP THE TABLESPACE YOU MUST TAKE IT OUT OF BACKUP MODE

ALTER TABLESPACE <TABLESPACE NAME> END BACKUP.

USERS WILL NOT NOTICE ANY DIFFERENCE IN THIS MODE AND CAN CARRY ON AS NORMAL, DML DDL ETC.

THIS WORKS BY APPLYING ALL TRANSACTIONS TO THE REDOLOGS WHICH ARE THEN APPLIED TO DATA FILES AFTER BACKUP. YOU NEED TO BE IN ARCHIVELOG MODE FOR THIS TO WORK

SY

 

[anuktac]

Thanks I will suggest this to our DBA-s.

-Anukta