How to Rename Tablespaces to a New name in Oracle 9i 10g 11g ?

In Oracle 9i we need to use exp imp utility to achive this goal. Please see the example below
  1. Create a new test tablespace
SQL>  create tablespace Newtab datafile '/data/oracle/oradata/prod9/newtab_01.dbf' size 50m;

Tablespace created.

SQL> connect scott/tiger
Connected.
SQL> create table testtable (name varchar2(10)) tablespace Newtab;

Table created.

SQL>  select table_name, tablespace_name from dba_tables where table_name ='TESTTABLE';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TESTTABLE                      NEWTAB

  1. Take the export of the tablespace.
$ exp file=newtab.dmp log=newtab.log tablespaces=newtab

Export: Release 11.1.0.7.0 - Production on Thu Sep 15 08:53:09 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Username: / as sysdba

Connected to: Oracle Database 9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export selected tablespaces ...
For tablespace NEWTAB ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                      TESTTABLE          0 rows exported
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
prod9 /home/oracle/scott

  1. Drop the tablespace
SQL> drop tablespace newtab including contents;

Tablespace dropped.


  1. Create the new tablespace with new name
SQL> create tablespace renametab datafile '/data/oracle/oradata/prod9/renametab_01.dbf' size 50m;

Tablespace created.
  1. Import the content to the new tablespace.

$ imp file=newtab.dmp log=imp_newtab.log full=y tablespaces=renametab

Import: Release 9.2.0.7.0 - Production on Thu Sep 15 08:57:34 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.01.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table                    "TESTTABLE"          0 rows imported
Import terminated successfully without warnings.
oracle@bsdevecdb03b(4105) prod9 /home/oracle/scott


For Oracle 10g and 11g you can use alter tablespace command with rename option.

SQL> create tablespace Newtab datafile '/data/oracle/oradata/prod9/newtab_01.dbf' size 50m reuse;

Tablespace created.


SQL> alter tablespace Newtab rename to renametab;

Tablespace altered.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google