3 USER ADMINISTRATION AND SECURITY
USING IMPDP
$ impdp userid=text/text123 tables=emp_pp1 directory=dir_priya1 dumpfile= priya1.dmp logfile=priya1.log content=data_only
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
Master table "TEXT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEXT"."SYS_IMPORT_TABLE_01": userid=text/******** tables=emp_pp1 directory=dir_priya1 dumpfile= priya1.dmp logfile=priya1.log content=data_only
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "TEXT"."EMP_PP1" 6.914 KB 30 rows Job "TEXT"."SYS_IMPORT_TABLE_01" successfully completed at 02:01:18 USING IMP
$ imp userid=text/text123 tables=emp_pp2 file= priya2_exp.dmp full=n rows=y
Import: Release 10.2.0.3.0 - Production on Thu Jul 16 02:16:00 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and UTF8 NCHAR character set . importing TEXT's objects into TEXT
. importing TEXT's objects into TEXT
. . importing table "EMP_PP2" 90 rows imported Import terminated successfully without warnings.
N:B OTHER VALUES FOR CONTENT ARE ‘METADATA ONLY’ AND ‘ALL’.
6) DO EXPORT ONLY THE STRUCTURE OF THE TABLE EMP1.
ANS:
EXPORT ( USING EXPDP)
$ expdp text/text123 tables=emp_pp1 directory= dir_priya_meta dumpfile= priya_meta.dmp logfile=priya_meta.log content=metadata_only
Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 16 July, 2009 2:23:21 Copyright (c) 2003, 2005, Oracle. All rights reserved.
With the Partitioning, OLAP and Data Mining options
Starting "TEXT"."SYS_EXPORT_TABLE_01": text/******** tables=emp_pp1 directory= dir_priya_meta dumpfile= priya_meta.dmp logfile=priya_meta.log content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Master table "TEXT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
****************************************************************************** Dump file set for TEXT.SYS_EXPORT_TABLE_01 is:
/usr/app/oracle/product/10.2.0/rdbms/log/dir_priya_meta/priya_meta.dmp Job "TEXT"."SYS_EXPORT_TABLE_01" successfully completed at 02:23:40 IMPORT ( USING IMPDP)
$ impdp userid=text/text123 tables=emp_pp1 directory=dir_priya_meta dumpfile= priya_meta.dmp logfile=priya_meta.log content=metadata_only
Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 16 July, 2009 2:29:40 Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
Master table "TEXT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEXT"."SYS_IMPORT_TABLE_01": userid=text/******** tables=emp_pp1 directory=dir_priya_meta dumpfile= priya_meta.dmp logfile=priya_meta.log content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "TEXT"."SYS_IMPORT_TABLE_01" successfully completed at 02:29:45
7. CREATE A USER CALLED TEXT1 WITH DEFAULT TABLESPACE TBS1 AND IMPORT THE EMP TABLE FROM TEXT TO THE NEW USER TEXT1 – USING IMP/IMPDP.
ANS:
SQL> create user text1 identified by password default tablespace tbs1;
User created. USING EXPDP
$ expdp text/text123 tables=emp_pp2 directory= dir_remap dumpfile= priya_remap.dmp logfile=priya_remap.log
Export: Release 10.2.0.3.0 - 64bit Production on Friday, 17 July, 2009 0:12:01 Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
Starting "TEXT"."SYS_EXPORT_TABLE_01": text/******** tables=emp_pp2 directory= dir_remap dumpfile= priya_remap.dmp logfile=priya_remap.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "TEXT"."EMP_PP2" 8.304 KB 90 rows
Master table "TEXT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
****************************************************************************** Dump file set for TEXT.SYS_EXPORT_TABLE_01 is:
/usr/app/oracle/product/10.2.0/rdbms/log/dir_remap/priya_remap.dmp
Job "TEXT"."SYS_EXPORT_TABLE_01" successfully completed at 00:12:23
USING IMPDP
$ impdp userid=text/text123 tables=emp_pp2 directory=dir_remap dumpfile= priya_remap.dmp logfile=priya_remap.log remap_schema=text:priya
Import: Release 10.2.0.3.0 - 64bit Production on Friday, 17 July, 2009 0:22:38 Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
Master table "TEXT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEXT"."SYS_IMPORT_TABLE_01": userid=text/******** tables=emp_pp2 directory=dir_remap dumpfile= priya_remap.dmp logfile=priya_remap.log remap_schema=text:priya
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "PRIYA"."EMP_PP2" 8.304 KB 90 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "TEXT"."SYS_IMPORT_TABLE_01" successfully completed at 00:22:51
USING EXP
Export: Release 10.2.0.3.0 - Production on Fri Jul 17 00:19:33 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and UTF8 NCHAR character set About to export specified tables via Conventional Path ...
. . exporting table EMP_PP2 90 rows exported Export terminated successfully without warnings.