<<Back to DB Administration Main Page
How to rename a tablespace in oracle
A tablespace in oracle can be renamed using alter tablespace rename command. To rename a tablespace TS1 to TS2 you run below command.SQL> alter tablespace TS1 rename to TS2;
Tablespace altered.
NOTE: SYSTEM and SYSAUX tablespace cannot be renamed.
Lets rename the TEMP tablespace in oracle
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
SQL> select CON_ID,NAME from v$tablespace where name='TEMP';
CON_ID NAME
---------- ------------------------------
1 TEMP
2 TEMP
3 TEMP
SQL> alter session set container=PDB01;
Session altered.
SQL> select * from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
----------------------- -------------- ------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
SQL> col USERNAME for a40
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
---------- -------------------- ----------
USER1 DATA_TS01 TEMP
USER2 DATA_TS01 TEMP
USER3 DATA_TS01 TEMP
USER4 DATA_TS01 TEMP
SQL> alter session set container=PDB01;
Session altered.
SQL>
SQL> alter tablespace temp rename to PDB_TEMP;
Tablespace altered.
SQL> select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
---------- -------------------- ----------
USER1 DATA_TS01 PDB_TEMP
USER2 DATA_TS01 PDB_TEMP
USER3 DATA_TS01 PDB_TEMP
USER4 DATA_TS01 PDB_TEMP
SQL>select * from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
----------------------- -------------- -----------------------------------
DEFAULT_TEMP_TABLESPACE PDB_TEMP Name of default temporary tablespace
Comments
Post a Comment