- Renaming redolog members;
- Renaming tablespaces;
- Renaming datafiles of a single offline tablespace;
- Renaming constraints;
- Renaming schema objects (tables, views, sequences, private synonyms, indexes, triggers);
- Renaming table columns;
- Renaming table and index partitions (subpartitions);
- Restoring and Renaming table from the Recycle Bin;
- Changing the domain in a global database name for a CDB;
- Renaming a PDB;
- Renaming redolog members:
[oracle@localhost admin]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 12 16:16:04 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> set lines 180 SQL> col member format a50 SQL> select GROUP#, MEMBER, CON_ID from V$LOGFILE; GROUP# MEMBER CON_ID ---------- -------------------------------------------------- ---------- 1 /app/oracle/oradata/CDB001/redo01.log 0 2 /app/oracle/oradata/CDB001/redo02.log 0 3 /app/oracle/oradata/CDB001/redo03.log 0 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> host mv /app/oracle/oradata/CDB001/redo01.log /app/oracle/oradata/CDB001/redo01_renamed.log SQL> host mv /app/oracle/oradata/CDB001/redo02.log /app/oracle/oradata/CDB001/redo02_renamed.log SQL> host mv /app/oracle/oradata/CDB001/redo03.log /app/oracle/oradata/CDB001/redo03_renamed.log SQL> host ls -l /app/oracle/oradata/CDB001/*log -rw-r-----. 1 oracle oinstall 52429312 Feb 7 15:59 /app/oracle/oradata/CDB001/redo01_renamed.log -rw-r-----. 1 oracle oinstall 52429312 Mar 3 13:00 /app/oracle/oradata/CDB001/redo02_renamed.log -rw-r-----. 1 oracle oinstall 52429312 Mar 12 16:17 /app/oracle/oradata/CDB001/redo03_renamed.log SQL> startup mount; ORACLE instance started. Total System Global Area 626327552 bytes Fixed Size 2291472 bytes Variable Size 473958640 bytes Database Buffers 146800640 bytes Redo Buffers 3276800 bytes Database mounted. SQL> alter database rename file '/app/oracle/oradata/CDB001/redo01.log','/app/oracle/oradata/CDB001/redo02.log','/app/oracle/oradata/CDB001/redo03.log' 2 to '/app/oracle/oradata/CDB001/redo01_renamed.log','/app/oracle/oradata/CDB001/redo02_renamed.log','/app/oracle/oradata/CDB001/redo03_renamed.log'; Database altered. SQL> alter database open; Database altered. SQL> select GROUP#, MEMBER, CON_ID from V$LOGFILE; GROUP# MEMBER CON_ID ---------- -------------------------------------------------- ---------- 1 /app/oracle/oradata/CDB001/redo01_renamed.log 0 2 /app/oracle/oradata/CDB001/redo02_renamed.log 0 3 /app/oracle/oradata/CDB001/redo03_renamed.log 0
- Renaming tablespaces:
SQL> select a.con_id, a.name, b.name from v$containers a, v$tablespace b where a.con_id = b.con_id order by 1,3; CON_ID NAME NAME ---------- ------------------------------ ------------------------------ 1 CDB$ROOT SYSAUX 1 CDB$ROOT SYSTEM 1 CDB$ROOT TEMP 1 CDB$ROOT UNDOTBS1 1 CDB$ROOT USERS 2 PDB$SEED SYSAUX 2 PDB$SEED SYSTEM 2 PDB$SEED TEMP 3 PDB001 SYSAUX 3 PDB001 SYSTEM 3 PDB001 TEMP 3 PDB001 USERS 4 PDB002 SYSAUX 4 PDB002 SYSTEM 4 PDB002 TEMP 4 PDB002 USERS 5 PDB003 SYSAUX 5 PDB003 SYSTEM 5 PDB003 TEMP 5 PDB003 USERS 20 rows selected. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter tablespace USERS rename to USERS_CDBROOT; Tablespace altered. SQL> select a.con_id, a.name, b.name from v$containers a, v$tablespace b where a.con_id = b.con_id order by 1,3; CON_ID NAME NAME ---------- ------------------------------ ------------------------------ 1 CDB$ROOT SYSAUX 1 CDB$ROOT SYSTEM 1 CDB$ROOT TEMP 1 CDB$ROOT UNDOTBS1 1 CDB$ROOT USERS_CDBROOT 2 PDB$SEED SYSAUX 2 PDB$SEED SYSTEM 2 PDB$SEED TEMP 3 PDB001 SYSAUX 3 PDB001 SYSTEM 3 PDB001 TEMP 3 PDB001 USERS 4 PDB002 SYSAUX 4 PDB002 SYSTEM 4 PDB002 TEMP 4 PDB002 USERS 5 PDB003 SYSAUX 5 PDB003 SYSTEM 5 PDB003 TEMP 5 PDB003 USERS 20 rows selected. SQL> alter session set container=PDB001; Session altered. SQL> alter pluggable database PDB001 open; Pluggable database altered. SQL> alter tablespace USERS rename to USERS_PDB001; Tablespace altered. SQL> select a.con_id, a.name, b.name from v$containers a, v$tablespace b where a.con_id = b.con_id order by 1,3; CON_ID NAME NAME ---------- ------------------------------ ------------------------------ 3 PDB001 SYSAUX 3 PDB001 SYSTEM 3 PDB001 TEMP 3 PDB001 USERS_PDB001
- Renaming datafiles of a single offline tablespace:
SQL> col file_name format a50 SQL> select file_name from dba_data_files where tablespace_name = 'USERS'; FILE_NAME -------------------------------------------------- /app/oracle/oradata/CDB001/users01.dbf SQL> alter tablespace USERS offline; Tablespace altered. SQL> host mv /app/oracle/oradata/CDB001/users01.dbf /app/oracle/oradata/CDB001/users01_renamed.dbf SQL> host ls -l /app/oracle/oradata/CDB001/users01* -rw-r-----. 1 oracle oinstall 5251072 Mar 12 16:36 /app/oracle/oradata/CDB001/users01_renamed.dbf SQL> alter tablespace USERS rename datafile '/app/oracle/oradata/CDB001/users01.dbf' 2 to '/app/oracle/oradata/CDB001/users01_renamed.dbf'; Tablespace altered. SQL> alter tablespace USERS online; Tablespace altered. SQL> select file_name from dba_data_files where tablespace_name = 'USERS'; FILE_NAME -------------------------------------------------- /app/oracle/oradata/CDB001/users01_renamed.dbfTo rename datafiles included in multiple tablespaces follow the redo log file renaming procedure described above (alter database rename file ...).
- Renaming constraints:
SQL> show user; USER is "MARCOV" SQL> select dbms_metadata.get_ddl('TABLE', 'T1') from dual; DBMS_METADATA.GET_DDL('TABLE','T1') --------------------------------------------------------- CREATE TABLE "MARCOV"."T1" ( "A" NUMBER ) SEGMENT CREATION IMMEDIATE SQL> alter table T1 add constraint t1_mypk primary key (a); Table altered. SQL> select dbms_metadata.get_ddl('TABLE', 'T1') from dual; DBMS_METADATA.GET_DDL('TABLE','T1') --------------------------------------------------------- CREATE TABLE "MARCOV"."T1" ( "A" NUMBER, CONSTRAINT "T1_MYPK" PRIMARY KEY ("A") SQL> alter table T1 rename constraint T1_MYPK to T1_PK; Table altered. SQL> select dbms_metadata.get_ddl('TABLE', 'T1') from dual; DBMS_METADATA.GET_DDL('TABLE','T1') --------------------------------------------------------- CREATE TABLE "MARCOV"."T1" ( "A" NUMBER, CONSTRAINT "T1_PK" PRIMARY KEY ("A")
- Renaming schema objects (tables, views, sequences, private synonyms, indexes, triggers):
SQL> show user; USER is "MARCOV" SQL> create sequence T1_MYSEQ; Sequence created. SQL> rename T1_MYSEQ to T1_S001; Table renamed. SQL> create table mysecondtable (a number); Table created. SQL> rename mysecondtable to T2; Table renamed. SQL> select dbms_metadata.get_ddl('TABLE', 'T2') from dual; DBMS_METADATA.GET_DDL('TABLE','T2') --------------------------------------------------------- CREATE TABLE "MARCOV"."T2" ( "A" NUMBER ) SEGMENT CREATION DEFERRED SQL> create or replace view T1_MYVIEW as select * from T1 where a <= 10; View created. SQL> rename T1_MYVIEW to T1_VIEW; Table renamed. SQL> select dbms_metadata.get_ddl('VIEW', 'T1_VIEW') from dual; DBMS_METADATA.GET_DDL('VIEW','T1_VIEW') --------------------------------------------------------- CREATE OR REPLACE FORCE EDITIONABLE VIEW "MARCOV"."T1_VIEW" ("A") AS select "A" from T1 where a <= 10 SQL> create public synonym pub_t1 for t1; Synonym created. SQL> create synonym priv_t1 for t1; Synonym created.
As you can see it is not possible to rename public synonymns, just the privates.
SQL> rename pub_t1 to public_t1; rename pub_t1 to public_t1 * ERROR at line 1: ORA-04043: object PUB_T1 does not exist SQL> rename priv_t1 to private_t1; Table renamed.
Synonym of a renamed object returns instead an error when used:
SQL> select count(*) from private_t1; COUNT(*) ---------- 1 SQL> rename t1 to t1_renamed; Table renamed. SQL> select count(*) from private_t1; select count(*) from private_t1 * ERROR at line 1: ORA-00980: synonym translation is no longer valid SQL> rename t1_renamed to t1; Table renamed. SQL> select count(*) from private_t1; COUNT(*) ---------- 1To rename schema objects such as indexes and triggers you can use the ALTER ... RENAME statement
SQL> show con_name; CON_NAME ------------------------------ PDB001 SQL> show user USER is "SYS" SQL> select index_name from dba_indexes where table_name = 'T1'; INDEX_NAME ---------------------------------------- T1_MYPK SQL> alter index MARCOV.T1_MYPK rename to T1_INDEX_PK; Index altered. SQL> create or replace trigger marcov.t1_mytrigger 2 before insert 3 on marcov.t1 4 for each row 5 declare 6 i number; 7 begin 8 i := 0; 9 end; 10 / Trigger created. SQL> alter trigger marcov.t1_mytrigger rename to t1_trigger; Trigger altered. SQL> select owner, trigger_name from dba_triggers where trigger_name = 'T1_TRIGGER'; OWNER TRIGGER_NAME -------------------- ---------------------------------------- MARCOV T1_TRIGGER
- Renaming table columns:
SQL> alter table t1 rename column a to b; Table altered.
- Renaming table and index partitions (subpartitions):
SQL> alter table t1 add (a number); Table altered. SQL> create index T1_index_partitioned on T1 (a) 2 global partition by range (a) 3 (partition p1 values less than (10), 4 partition p2 values less than (100), 5 partition p3 values less than (maxvalue)); Index created. SQL> alter index T1_index_partitioned rename partition p3 to pmax; Index altered. SQL> drop table t2 purge; Table dropped. SQL> create table T2 (a number, quarter date) partition by range (quarter) 2 (partition Q1_2012 values less than (to_date('01/04/2012','DD/MM/YYYY')), 3 partition Q2_2012 values less than (to_date('01/07/2012','DD/MM/YYYY')), 4 partition Q3_2012 values less than (to_date('01/10/2012','DD/MM/YYYY')), 5 partition Q4_2012 values less than (to_date('01/01/2013','DD/MM/YYYY')), 6 partition Q1_2013 values less than (to_date('01/04/2013','DD/MM/YYYY')), 7 partition Q2_2013 values less than (to_date('01/07/2013','DD/MM/YYYY')), 8 partition Q3_2013 values less than (to_date('01/10/2013','DD/MM/YYYY')), 9 partition Q4_2013 values less than (to_date('01/01/2014','DD/MM/YYYY')), 10 partition Q1_2014 values less than (to_date('01/04/2014','DD/MM/YYYY')), 11 partition Q2_2014 values less than (to_date('01/07/2014','DD/MM/YYYY')), 12 partition Q3_2014 values less than (to_date('01/10/2014','DD/MM/YYYY')), 13 partition Q4_2014 values less than (maxvalue)); Table created. SQL> alter table t2 rename partition Q4_2014 to Q_MAX; Table altered.
- Restoring and Renaming table from the Recycle Bin:
SQL> show user USER is "MARCOV" SQL> select * from tab; no rows selected SQL> create table T1 (a number); Table created. SQL> drop table t1; Table dropped. SQL> create table T1 (a number); Table created. SQL> select * from tab; TNAME TABTYPE CLUSTERID ---------------------------------------- ------- ---------- BIN$9IBy6OCMQ0zgRQAAAAAAAQ==$0 TABLE T1 TABLE SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- T1 BIN$9IBy6OCMQ0zgRQAAAAAAAQ==$0 TABLE 2014-03-13:17:32:48 SQL> flashback table "BIN$9IBy6OCMQ0zgRQAAAAAAAQ==$0" to before drop rename to T2; Flashback complete. SQL> select * from tab; TNAME TABTYPE CLUSTERID ---------------------------------------- ------- ---------- T1 TABLE T2 TABLE SQL> show recyclebin SQL>An equivalent statement to recover and rename the same table could be: flashback table T1 to before drop rename to T2;
Don't forget that double quotes are required when dealing with system generated names such as BIN$9IBy6OCMQ0zgRQAAAAAAAQ==$0.
Dependent objects of a restored table from the recycle bin such as indexes mantains the system generated names, but you can rename them using the ALTER INDEX ... RENAME TO statement described above in the "Renaming Schema Objects" section.
- Changing the domain in a global database name for a CDB:
SQL> select * from global_name; GLOBAL_NAME ------------------------------------------------------ CDB001.MARCOV.COM SQL> alter database rename global_name to CDB001.IT.MARCOV.COM; Database altered. SQL> select * from global_name; GLOBAL_NAME ------------------------------------------------------ CDB001.IT.MARCOV.COMAlso the domain of each PDBs is affected when the previous statement is applied to the domain name of a CDB.
- Renaming a PDB:
SQL> alter session set container=PDB001; Session altered. SQL> select * from global_name; select * from global_name * ERROR at line 1: ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only SQL> alter pluggable database PDB001 open; Pluggable database altered. SQL> select * from global_name; GLOBAL_NAME ------------------------------------------------------ PDB001.IT.MARCOV.COM SQL> alter pluggable database rename global_name to PDB001.ROME.IT.MARCOV.COM; alter pluggable database rename global_name to PDB001.ROME.IT.MARCOV.COM * ERROR at line 1: ORA-65045: pluggable database not in a restricted mode SQL> alter pluggable database PDB001 close; Pluggable database altered. SQL> alter pluggable database PDB001 open restricted; Pluggable database altered. SQL> select * from global_name; GLOBAL_NAME ------------------------------------------------------ PDB001.IT.MARCOV.COM SQL> alter pluggable database rename global_name to PDB001.ROME.IT.MARCOV.COM; alter pluggable database rename global_name to PDB001.ROME.IT.MARCOV.COM * ERROR at line 1: ORA-65042: name is already used by an existing container SQL> alter pluggable database rename global_name to PDB001_ROME.IT.MARCOV.COM; Pluggable database altered. SQL> select name, open_mode from V$PDBS; NAME OPEN_MODE ------------------------------ ---------- PDB001_ROME READ WRITE SQL> select * from global_name; GLOBAL_NAME ------------------------------------------------------ PDB001_ROME.IT.MARCOV.COM
That's all.
192 comments:
Post a Comment