snapshot too old error during drop tablespace

237 Views

Hi Experts

When we are doing BW reorg and steps followed are

1. created a newtablespace with source tablespace TABART class reference.

2. Export the source tablespace to the filesystem level.

3. DROP the source tablespace now.

4. Rename the new tablespace to source tablespace name.

5. Import

Here in the third step i have received snapshot too old error.

BR0301E SQL error -604 at location BrSqlExecute-1, SQL statement:

'/* BRSPACE */ drop tablespace PSAPADSOLD including contents and datafiles cascade constraints'

ORA-00604: error occurred at recursive SQL level 1

ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too small

BR1017E Execution of SQL statement 'drop tablespace PSAPADSOLD including contents and datafiles cascade constraints' failed

so i tried to rename the tablespace and set to offline and tried to import only 240 tables were imported compared to 24057 tables.Still the PSAPADS - Source tablespace shows 65000 elements.

my queries:

1. After Export of the tablespace how come the Source tablespace retain the tables.

2. why i could not able to drop the tablespace

I had increased the UNDO_RETENTION to 86400 my oracle version is 10.2.04.

Source table space PSAPADS is 80 GB and has only 30 GB data and the remaining are free.

PSAPUNDO was 17GB in size.

Kinldy suggest

Regards

Bala