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

advertisements

_____________________________________________________________________________________________________________________

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

2. 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

3. Drop the tablespace

SQL> drop tablespace newtab including contents;

Tablespace dropped.

4. Create the new tablespace with new name

SQL> create tablespace renametab datafile '/data/oracle/oradata/prod9/renametab_01.dbf' size 50m;

Tablespace created.

5. 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.

_____________________________________________________________________________________________________________________

Website Stats