SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMPX
USERDATA
ROOM
6 rows selected.
SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_OCM USERDATA
DBSNMP SYSAUX
DIP USERDATA
APPQOSSYS SYSAUX
SYSTEM SYSTEM
SYS SYSTEM
OUTLN SYSTEM
ABC USERDATA
8 rows selected.
SQL> alter user abc default tablespace room;
User altered.
SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_OCM USERDATA
DBSNMP SYSAUX
DIP USERDATA
APPQOSSYS SYSAUX
SYSTEM SYSTEM
SYS SYSTEM
OUTLN SYSTEM
ABC ROOM
8 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 rolling hyderabad
60 ebus ananthapur
6 rows selected.
[oracle@appsdb exp]$ exp file=tablespace.dmp log=tablespace.log tablespaces=room
Export: Release 11.2.0.1.0 - Production on Tue Aug 30 18:27:16 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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 UTF8 character set (possible charset conversion)
About to export selected tablespaces ...
For tablespace ROOM ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMPX
USERDATA
ROOM
6 rows selected.
SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_OCM USERDATA
DBSNMP SYSAUX
DIP USERDATA
APPQOSSYS SYSAUX
SYSTEM SYSTEM
SYS SYSTEM
OUTLN SYSTEM
ABC USERDATA
8 rows selected.
SQL> alter user abc default tablespace room;
User altered.
SQL> select username,default_tablespace from dba_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_OCM USERDATA
DBSNMP SYSAUX
DIP USERDATA
APPQOSSYS SYSAUX
SYSTEM SYSTEM
SYS SYSTEM
OUTLN SYSTEM
ABC ROOM
8 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 rolling hyderabad
60 ebus ananthapur
6 rows selected.
[oracle@appsdb exp]$ exp file=tablespace.dmp log=tablespace.log tablespaces=room
Export: Release 11.2.0.1.0 - Production on Tue Aug 30 18:27:16 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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 UTF8 character set (possible charset conversion)
About to export selected tablespaces ...
For tablespace ROOM ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMPX
USERDATA
ROOM
6 rows selected.
SQL> drop tablespace room including contents and datafiles;
Tablespace dropped.
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMPX
USERDATA
here the tablespace room is dropped including contents and datafiles.
[oracle@appsdb exp]$ imp file=tablespace.dmp full=y
Import: Release 11.2.0.1.0 - Production on Tue Aug 30 18:37:58 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
Import terminated successfully without warnings.
SQL> conn abc/abc
Connected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 rolling hyderabad
60 ebus ananthapur
6 rows selected.
No comments:
Post a Comment