Document Type | Technical Information
Category | Administration
Applicable Product Version | 7FS02PS03
Document Number | TADTI141
Overview
While operating a database, there may be cases where you need to delete the USR tablespace that is automatically created during the database installation process.
In such cases, you can delete the tablespace after changing the default tablespace settings for the schema and for the database.
Method
1. Change Default Tablespace for Each Schema
SQL > select username, default_tablespace from dba_users; USERNAME-------------------- ------------------------------ SYS SYSTEM SYSCAT SYSSUB SYSGIS SYSSUB OUTLN SYSSUB SYSBACKUP SYSSUB TIBERO USR LBACSYS SYSSUB FITNESS USR SQL > alter user TIBERO default tablespace TEST; SQL > alter user FITNESS default tablespace TEST; SQL> select username, default_tablespace from dba_users; USERNAME DEFAULT_TABLESPACE -------------------- ------------------------------ SYS SYSTEM SYSCAT SYSSUB SYSGIS SYSSUB OUTLN SYSSUB SYSBACKUP SYSSUB TIBERO TEST LBACSYS SYSSUB FITNESS TEST
2.pace
You can check the database's permanent tablespace by checking the database property view.
SQL> select name, value from database_properties where name = 'DFLT_PERM_TS'; NAME VALUE ------------------------------ ---------- DFLT_PERM_TS USR 1 row selected. SQL > alter database default tablespace TEST; Database altered. SQL> select name, value from database_properties where name = 'DFLT_PERM_TS'; NAME VALUE ------------------------------ ---------- DFLT_PERM_TS TEST 1 row selected.
3. Drop USR Tablespace
Before performing the drop operation, it is strongly recommended to back up any necessary data in the USR tablespace to another tablespace before proceeding.
SQL > drop tablespace USR including contents and datafiles; Tablespace 'USR' dropped. SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------- SYSTEM UNDO TEMP SYSSUB TEST 5 rows selected.