DATAPUMP?

Diasadvantages with normal export and import:-

1)we cannot monitor export/import because specifying job_name is not possible.

2)we cant pause and resume export/import operations.In order to overcome this disadvantages datapump comes.

Datapump will perform export and import operations much faster than traditional ones.
whenever we go for datapump 2 background process will be done:-
                                              1)master background process.[DM(datapump master)]
                                              2)worker background process.[DW(datapump worker)]
1)master background process:-
It will create a master table with job_name in that particular users schema.
It will monitor entire status of that export/import operation of particular job.
After export/import done table will be dropped automatically.

2)worker background process:-
It will be created by master background process.
The entire export/import operation will be done by worker background process.

In order to perform datapump export and import we need to create the directory structure both at the physical level and logical level.Datapump jobs are served at the server side and these jobs will occupy some space in SGA. streams_pool_size is the view to know the occupied space in SGA.

if a job takes 8 hours to complete and it finished in one hour and then stopped.using traditional export we cant go with the continuation of the job but using expdp we can run the job from the particular point by using unique jobid ('job_id=----')

we will create a directory to keep the datapump jobs and logfiles & dumpfiles information.we can create the dumpfiles in this directory.

Datapump jobs can be parallely executed.

SQL> sho parameter streams_pool;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0 (we need to  assign memory to this.)
SQL> exit  
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@appsdb exp]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
[oracle@appsdb exp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 31 16:04:20 2016

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


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

SQL> startup force
ORACLE instance started.

Total System Global Area  283967488 bytes
Fixed Size                  2212576 bytes
Variable Size             226495776 bytes
Database Buffers           50331648 bytes
Redo Buffers                4927488 bytes
Database mounted.
Database opened.

SQL> show parameter streams_pool;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 72M
SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/ccr/state

SYS                            DATA_PUMP_DIR
/u01/app/oracle/product/11.2.0/rdbms/log/


SQL> create directory dpump as '/u01/app/oracle/dpump';

Directory created.

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/ccr/state

SYS                            DPUMP
/u01/app/oracle/dpump

SYS                            DATA_PUMP_DIR
/u01/app/oracle/product/11.2.0/rdbms/log/


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@appsdb exp]$ cd
[oracle@appsdb ~]$ cd /u01/app/oracle
[oracle@appsdb oracle]$ ls
arch  control.ctl  diag  exp  oraInventory  product  redo1a.log  redo2a.log  roomone.dbf  sysaux.dbf  system1.dbf  system.dbf  tempx.dbf  undotbs.dbf  userdata.dbf
[oracle@appsdb oracle]$ mkdir dpump
[oracle@appsdb oracle]$ ls
arch         diag   exp           product     redo2a.log   sysaux.dbf   system.dbf  undotbs.dbf
control.ctl  dpump  oraInventory  redo1a.log  roomone.dbf  system1.dbf  tempx.dbf   userdata.dbf
[oracle@appsdb oracle]$ cd dpump

[oracle@appsdb dpump]$ expdp directory=dpump dumpfile=datapump.dmp logfile=dp.log job_name=dp_full_1 full=y

here expdp refers to export datapump.
        impdp refers to import datapump.
Export: Release 11.2.0.1.0 - Production on Wed Aug 31 16:34:24 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
Starting "SYS"."DP_FULL_1":  /******** AS SYSDBA directory=dpump dumpfile=datapump.dmp logfile=dp.log job_name=dp_full_1 full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 592 KB
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
. . exported "ABC"."DEPT"                                6.007 KB       7 rows
. . exported "ABC"."DUMMY"                                   5 KB       1 rows
. . exported "ABC"."EMP"                                 8.562 KB      14 rows
. . exported "ABC"."SALGRADE"                            5.859 KB       5 rows
. . exported "XYZ"."DEPT"                                6.007 KB       7 rows
. . exported "XYZ"."DUMMY"                                   5 KB       1 rows
. . exported "XYZ"."EMP"                                 8.562 KB      14 rows
. . exported "XYZ"."SALGRADE"                            5.859 KB       5 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE"          6.328 KB       2 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES"             6.882 KB      28 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD"        5.835 KB      19 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS"          5.484 KB       3 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES"           6.289 KB       2 rows
. . exported "OUTLN"."OL$"                                   0 KB       0 rows
. . exported "OUTLN"."OL$HINTS"                              0 KB       0 rows
. . exported "OUTLN"."OL$NODES"                              0 KB       0 rows
. . exported "SYSTEM"."DEF$_AQCALL"                          0 KB       0 rows
. . exported "SYSTEM"."DEF$_AQERROR"                         0 KB       0 rows
. . exported "SYSTEM"."DEF$_CALLDEST"                        0 KB       0 rows
. . exported "SYSTEM"."DEF$_DEFAULTDEST"                     0 KB       0 rows
. . exported "SYSTEM"."DEF$_DESTINATION"                     0 KB       0 rows
. . exported "SYSTEM"."DEF$_ERROR"                           0 KB       0 rows
. . exported "SYSTEM"."DEF$_LOB"                             0 KB       0 rows
. . exported "SYSTEM"."DEF$_ORIGIN"                          0 KB       0 rows
. . exported "SYSTEM"."DEF$_PROPAGATOR"                      0 KB       0 rows
. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN"                 0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP"                 0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_CONFLICT"                     0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_DDL"                          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_EXCEPTIONS"                   0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_EXTENSION"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_FLAVORS"                      0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_GENERATED"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL"            0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS"                  0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS"                 0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY"                     0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES"            0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCAT"                       0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCATLOG"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPCOLUMN"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPOBJECT"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPPROP"                      0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_REPSCHEMA"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION"                   0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS"        0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS"                0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SITES_NEW"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS"                 0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_SNAPGROUP"                    0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "SYS"."DP_FULL_1" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.DP_FULL_1 is:
  /u01/app/oracle/dpump/datapump.dmp
Job "SYS"."DP_FULL_1" successfully completed at 16:35:52

[oracle@appsdb dpump]$ ls
datapump.dmp  dp.log
[oracle@appsdb dpump]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 31 16:36:41 2016

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


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

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
XYZ                            USERDATA
ABC                            ROOM

9 rows selected.

SQL> conn xyz/xyz
Connected.
SQL> sho user;
USER is "XYZ"
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
DUMMY                          TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL> conn / as sysdba
Connected.
SQL> drop user xyz cascade;

User dropped.

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> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@appsdb dpump]$ impdp directory=dpump dumpfile=datapump.dmp schemas=xyz job_name=dp_full_1

Import: Release 11.2.0.1.0 - Production on Wed Aug 31 16:39:56 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
Master table "SYS"."DP_FULL_1" successfully loaded/unloaded
Starting "SYS"."DP_FULL_1":  /******** AS SYSDBA directory=dpump dumpfile=datapump.dmp schemas=xyz job_name=dp_full_1
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "XYZ"."DEPT"                                6.007 KB       7 rows
. . imported "XYZ"."DUMMY"                                   5 KB       1 rows
. . imported "XYZ"."EMP"                                 8.562 KB      14 rows
. . imported "XYZ"."SALGRADE"                            5.859 KB       5 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYS"."DP_FULL_1" successfully completed at 16:40:08

[oracle@appsdb dpump]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 31 16:40:17 2016

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


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

SQL> conn xyz/xyz
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
DUMMY                          TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL> conn / as sysdba
Connected.
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
XYZ                            USERDATA
ABC                            ROOM

9 rows selected.

[oracle@appsdb dpump]$ expdp directory=dpump dumpfile=dump_file_2 job_name=job_full_2 full=y

Export: Release 11.2.0.1.0 - Production on Wed Aug 31 17:58:08 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
Starting "SYS"."JOB_FULL_2":  /******** AS SYSDBA directory=dpump dumpfile=dump_file_2 job_name=job_full_2 full=y
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 592 KB
^C
Export> stop_job=immediate
Are you sure you wish to stop this job ([yes]/no): y

[oracle@appsdb dpump]$ expdp directory=dpump dumpfile=dump_file_3 job_name=job_full_3 full=y

Export: Release 11.2.0.1.0 - Production on Wed Aug 31 17:59:27 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

Starting "SYS"."JOB_FULL_3":  /******** AS SYSDBA directory=dpump dumpfile=dump_file_3 job_name=job_full_3 full=y 

Estimate in progress using BLOCKS method...

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 592 KB

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/ROLE

^C

Export> stop_job=immediate

Are you sure you wish to stop this job ([yes]/no): n

Export> continue_client

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/RESOURCE_COST

Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE

Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY

Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/CONTEXT

Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM

Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE

Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/AUDIT

. . exported "ABC"."DEPT"                                6.007 KB       7 rows

. . exported "ABC"."DUMMY"                                   5 KB       1 rows

. . exported "ABC"."EMP"                                 8.562 KB      14 rows

. . exported "ABC"."SALGRADE"                            5.859 KB       5 rows

. . exported "XYZ"."DEPT"                                6.007 KB       7 rows

. . exported "XYZ"."DUMMY"                                   5 KB       1 rows

. . exported "XYZ"."EMP"                                 8.562 KB      14 rows

. . exported "XYZ"."SALGRADE"                            5.859 KB       5 rows

. . exported "SYSTEM"."REPCAT$_AUDIT_ATTRIBUTE"          6.328 KB       2 rows

. . exported "SYSTEM"."REPCAT$_OBJECT_TYPES"             6.882 KB      28 rows

. . exported "SYSTEM"."REPCAT$_RESOLUTION_METHOD"        5.835 KB      19 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_STATUS"          5.484 KB       3 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_TYPES"           6.289 KB       2 rows

. . exported "OUTLN"."OL$"                                   0 KB       0 rows

. . exported "OUTLN"."OL$HINTS"                              0 KB       0 rows

. . exported "OUTLN"."OL$NODES"                              0 KB       0 rows

. . exported "SYSTEM"."DEF$_AQCALL"                          0 KB       0 rows

. . exported "SYSTEM"."DEF$_AQERROR"                         0 KB       0 rows

. . exported "SYSTEM"."DEF$_CALLDEST"                        0 KB       0 rows

. . exported "SYSTEM"."DEF$_DEFAULTDEST"                     0 KB       0 rows

. . exported "SYSTEM"."DEF$_DESTINATION"                     0 KB       0 rows

. . exported "SYSTEM"."DEF$_ERROR"                           0 KB       0 rows

. . exported "SYSTEM"."DEF$_LOB"                             0 KB       0 rows

. . exported "SYSTEM"."DEF$_ORIGIN"                          0 KB       0 rows

. . exported "SYSTEM"."DEF$_PROPAGATOR"                      0 KB       0 rows

. . exported "SYSTEM"."DEF$_PUSHED_TRANSACTIONS"             0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_AUDIT_COLUMN"                 0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_COLUMN_GROUP"                 0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_CONFLICT"                     0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_DDL"                          0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_EXCEPTIONS"                   0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_EXTENSION"                    0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_FLAVORS"                      0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_FLAVOR_OBJECTS"               0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_GENERATED"                    0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_GROUPED_COLUMN"               0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_INSTANTIATION_DDL"            0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_KEY_COLUMNS"                  0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_OBJECT_PARMS"                 0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_PARAMETER_COLUMN"             0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_PRIORITY"                     0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_PRIORITY_GROUP"               0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_REFRESH_TEMPLATES"            0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_REPCAT"                       0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_REPCATLOG"                    0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_REPCOLUMN"                    0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_REPGROUP_PRIVS"               0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_REPOBJECT"                    0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_REPPROP"                      0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_REPSCHEMA"                    0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_RESOLUTION"                   0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_RESOLUTION_STATISTICS"        0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_RESOL_STATS_CONTROL"          0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_RUNTIME_PARMS"                0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_SITES_NEW"                    0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_SITE_OBJECTS"                 0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_SNAPGROUP"                    0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_OBJECTS"             0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_PARMS"               0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows

. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows

. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows

Master table "SYS"."JOB_FULL_3" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.JOB_FULL_3 is:

  /u01/app/oracle/dpump/dump_file_3.dmp

Job "SYS"."JOB_FULL_3" successfully completed at 18:00:33

How to take the backup at schema level?

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
        70 subbu                      rajahmundry

7 rows selected.

[oracle@appsdb exp]$ exp file=schema.dmp log=schema.log user=abc
LRM-00101: unknown parameter name 'user'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
[oracle@appsdb exp]$ exp file=schema.dmp log=schema.log owner=abc

Export: Release 11.2.0.1.0 - Production on Tue Aug 30 18:46:00 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Username: abc
Password:

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)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ABC
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ABC
About to export ABC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ABC's tables via Conventional Path ...
. . exporting table                           DEPT          7 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                          DUMMY          1 rows exported
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                       SALGRADE          5 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

here we had exported the data of abc user in schema.dmp

now we are going to import the data from schema.dmp to a new user xyz.

SQL> conn xyz/xyz 

Connected.

SQL> select * from tab;

no rows selected

here there is no data.

[oracle@appsdb exp]$ imp file=schema.dmp log=schema1.log fromuser=abc touser=xyz

Import: Release 11.2.0.1.0 - Production on Tue Aug 30 18:51:00 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

Warning: the objects were exported by ABC, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses UTF8 character set (possible charset conversion)

. importing ABC's objects into XYZ

. . importing table                         "DEPT"          7 rows imported

. . importing table                        "DUMMY"          1 rows imported

. . importing table                          "EMP"         14 rows imported

. . importing table                     "SALGRADE"          5 rows imported

About to enable constraints...

Import terminated successfully without warnings.

[oracle@appsdb exp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 30 18:52:12 2016

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

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

SQL> conn xyz/xyz

Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

DEPT                           TABLE

DUMMY                          TABLE

EMP                            TABLE

SALGRADE                       TABLE

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

        70 subbu          rajahmundry

7 rows selected.

How to take the backup at tablespaces level using export/import utility?

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.

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.

How to take the full database backup using export and import utility?

[oracle@appsdb oracle]$ mkdir exp
[oracle@appsdb oracle]$ cd exp
Here export and import utilities are working from the OS level.for taking the backup the database should be open.
[oracle@appsdb exp]$ exp file=full.dmp log=full.log full=y
Export: Release 11.2.0.1.0 - Production on Tue Aug 30 17:47:11 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 the entire database ...
. exporting tablespace definitions
EXP-00068: tablespace ROOM is offline
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions
. exporting system procedural objects and actions
. exporting pre-schema procedural objects and actions
. exporting cluster definitions
. about to export SYSTEM's tables via Conventional Path ...
. . exporting table                    DEF$_AQCALL          0 rows exported
. . exporting table                   DEF$_AQERROR          0 rows exported
. . exporting table                  DEF$_CALLDEST          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table               DEF$_DEFAULTDEST          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table               DEF$_DESTINATION          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                     DEF$_ERROR          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                       DEF$_LOB          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                    DEF$_ORIGIN          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                DEF$_PROPAGATOR          0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table       DEF$_PUSHED_TRANSACTIONS          0 rows exported
EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

. about to export ABC's tables via Conventional Path ...

. . exporting table                           DEPT          4 rows exported

. . exporting table                          DUMMY          1 rows exported

. . exporting table                            EMP         14 rows exported

. . exporting table                       SALGRADE          5 rows exported

. exporting synonyms

. exporting views

. exporting referential integrity constraints

. exporting stored procedures

. exporting operators

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting triggers

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting user history table

. exporting default and system auditing options

. exporting statistics

Export terminated successfully with warnings.

[oracle@appsdb exp]$ ls

full.dmp  full.log

Export and Import operations are nothing but export is used to export the data to a dump file.dump files are 8 to 10 times smaller than the datafile. where as import is nothing but importing the data from dump file and import the objects into the database.

[oracle@appsdb exp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 30 17:53:01 2016

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

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

SQL> select username from dba_users;

USERNAME

------------------------------

OUTLN

SYS

SYSTEM

ABC

APPQOSSYS

DBSNMP

DIP

ORACLE_OCM

8 rows selected.

SQL> drop user abc;       

drop user abc

*

ERROR at line 1:

ORA-01922: CASCADE must be specified to drop 'ABC'

Here user abc contains any objects we need to go with cascade option otherwise drop user username.

SQL> drop user abc cascade;

User dropped.

SQL> select username from dba_users;

USERNAME

------------------------------

OUTLN

SYS

SYSTEM

APPQOSSYS

DBSNMP

DIP

ORACLE_OCM

7 rows selected.

here abc user is dropped.so now we have the full database backup we had taken using export utility.

let us see how to import using import utility.

[oracle@appsdb exp]$ imp file=full.dmp log=full.log full=y
Export: Release 11.2.0.1.0 - Production on Tue Aug 30 17:47:11 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 import the entire database ...
importing OUTLN's objects into OUTLN
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing SYS's objects into SYS
. importing SYSTEM's objects into SYSTEM
. importing ABC's objects into ABC
About to enable constraints...
Import terminated successfully with warnings.

[oracle@appsdb exp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Aug 30 17:59:57 2016

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

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

SQL> select username from dba_users;

USERNAME

------------------------------

OUTLN

SYS

SYSTEM

ABC

APPQOSSYS

DBSNMP

DIP

ORACLE_OCM

8 rows selected.

SQL> conn abc/abc

Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

DEPT                               TABLE

DUMMY                          TABLE

EMP                                  TABLE

SALGRADE                     TABLE

using this logical backups only simple restore  is possible recovery is not possible.

How to get demonstration tables pupbld.sql?

SQL> create user abc identified by abc;

User created.

SQL> select username from dba_users;

USERNAME
------------------------------
OUTLN
SYS
SYSTEM
ABC
APPQOSSYS
DBSNMP
DIP
ORACLE_OCM

8 rows selected.

SQL> conn abc/abc
ERROR:
ORA-01045: user ABC lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

when connecting to user abc we gets above error since we didnt given privileges to the user abc.
SQL> conn / as sysdba
Connected.
SQL> grant connect,resource to abc identified by abc;

Grant succeeded.

SQL> conn abc/abc
Connected.
SQL> show user;
USER is "ABC"
SQL> select * from tab;

no rows selected

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@appsdb ~]$ cd $ORACLE_HOME/rdbms/admin

[oracle@appsdb admin]$ ls -ltr utlsampl.sql

-rw-r--r-- 1 oracle oinstall 3676 Jun  2  2006 utlsampl.sql

[oracle@appsdb admin]$ cd $ORACLE_HOME/sqlplus

[oracle@appsdb sqlplus]$ ls

admin  bin  doc  lib  mesg

[oracle@appsdb sqlplus]$ mkdir demo

[oracle@appsdb sqlplus]$ ls

admin  bin  demo  doc  lib  mesg

[oracle@appsdb admin]$ ls -ltr utlsampl.sql

-rw-r--r-- 1 oracle oinstall 3676 Jun  2  2006 utlsampl.sql

[oracle@appsdb admin]$ cp utlsampl.sql $ORACLE_HOME/sqlplus/demo/demobld.sql

[oracle@appsdb admin]$ cd $ORACLE_HOME/sqlplus/demo

[oracle@appsdb demo]$ ls -ltr demobld.sql

-rw-r--r-- 1 oracle oinstall 3676 Aug 30 17:13 demobld.sql

[oracle@appsdb demo]$ vi demobld.sql

after opening demobld.sql go to the line create table dept and place the cursor above create table dept 

and press d g g 

it will delete upper lines above create table dept and come to down remove exit.

above create table dept place these lines:

SET TERMOUT ON

PROMPT Building Demonstration Tables.Please Wait.

SET TERMOUT OFF

DROP TABLE DEPT;

DROP TABLE EMP;

DROP TABLE BONUS;

DROP TABLE SALGRADE;

DROP TABLE DUMMY;

come to last line above commit place these lines:

CREATE TABLE DUMMY (DUMMY number);

INSERT INTO DUMMY VALUES(0);

COMMIT;

SET TERMOUT ON

PROMPT Building Demonstration Tables Build is Complete

:wq

SQL> @?/sqlplus/demo/demobld.sql

Building Demonstration Tables Please wait.

Building Demonstration Table Build is Complete

SQL> select * from tab;

TNAME                           TABTYPE  CLUSTERID

------------------------------ ------- ----------

BONUS                          TABLE

DEPT                             TABLE

DUMMY                        TABLE

EMP                               TABLE

SALGRADE                  TABLE

so here we get the tables emp,dept,bonus,dummy,salgrade.

How to rename a datafile without extension .dbf to datafile with extension .dbf?

For example i added a datafile.but forgotten to give the extension .dbf then how to rename it?

SQL> alter tablespace room add datafile '/u01/app/oracle/room' size 200m;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/system.dbf
/u01/app/oracle/sysaux.dbf
/u01/app/oracle/undotbs.dbf
/u01/app/oracle/userdata.dbf
/u01/app/oracle/system1.dbf
/u01/app/oracle/room2.dbf
/u01/app/oracle/room3.dbf
/u01/app/oracle/room

8 rows selected.

Here a new datafile room is created without extension .dbf  Then shutdown the database.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
Database mounted.

If we shutdown and mount it people cannot acess the data in the database.here database is offline so datafiles are also offline.

SQL> !cp /u01/app/oracle/room  /u01/app/oracle/roomone.dbf

SQL> !ls /u01/app/oracle/room*

/u01/app/oracle/room  /u01/app/oracle/room2.dbf  /u01/app/oracle/roomone.dbf

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/system.dbf

/u01/app/oracle/sysaux.dbf

/u01/app/oracle/undotbs.dbf

/u01/app/oracle/userdata.dbf

/u01/app/oracle/system1.dbf

/u01/app/oracle/room2.dbf

/u01/app/oracle/room3.dbf

/u01/app/oracle/room

8 rows selected.

Here still it is showing the room datafile with out extension.dbf we need to rename in offline.

SQL> alter database rename file '/u01/app/oracle/room' to '/u01/app/oracle/roomone.dbf';

Database altered.

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/system.dbf

/u01/app/oracle/sysaux.dbf

/u01/app/oracle/undotbs.dbf

/u01/app/oracle/userdata.dbf

/u01/app/oracle/system1.dbf

/u01/app/oracle/room2.dbf

/u01/app/oracle/room3.dbf

/u01/app/oracle/roomone.dbf

8 rows selected.

here it is changed from room to roomone.dbf

SQL> !ls /u01/app/oracle/room*    

/u01/app/oracle/room  /u01/app/oracle/room2.dbf  /u01/app/oracle/roomone.dbf

SQL> !rm /u01/app/oracle/room     

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/system.dbf

/u01/app/oracle/sysaux.dbf

/u01/app/oracle/undotbs.dbf

/u01/app/oracle/userdata.dbf

/u01/app/oracle/system1.dbf

/u01/app/oracle/room2.dbf

/u01/app/oracle/room3.dbf

/u01/app/oracle/roomone.dbf

8 rows selected.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

--------------------

READ WRITE

difference between p-file and spfile?

P-FILE                                                                                  SPFILE

1)It is called as parameter file.                                     1)It is called as server parameter file.

2)location:- cd $ORACLE_HOME/dbs                      2)cd $ORACLE_HOME/dbs

3)p-file is ASCII file.                                                     3)spfile is binary file.

4)we can change the parameters by opening p-file.        4)we cannot change the parameters by                                                                                                             opening spfile.

oracle providing 2 options to change the parameters  of spfile

                                1)alter system

                                2)alter session

alter system set resource_limit=true scope=_________;

1)BOTH=changes will be applied for current instance and future instance;

2)MEMORY=changes will be applied only for current instance;

3)SPFILE=changes will be applied after restart the database.

How to create the spfile?

1)first check spfile exists are not?

SQL> sho parameter spfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string

SQL> create spfile from pfile;

File created.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string

SQL> startup force

ORACLE instance started.

Total System Global Area  217157632 bytes

Fixed Size                  2211928 bytes

Variable Size             159387560 bytes

Database Buffers           50331648 bytes

Redo Buffers                5226496 bytes

Database mounted.

Database opened.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      /u01/app/oracle/product/11.2.0

                                                      /dbs/spfilesubbu.ora

archivelog mode in database?

By default the database is in no archive log mode.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/dbs/arch
Oldest online log sequence     22
Current log sequence           23

archive log mode takes the snap shot of the redologfiles and those redologfiles are kept into destination.When the database is running in ARCHIVELOG  mode, the log writer process (LGWR) cannot reuse and hence overwrite a redo log group until it has been archived.archive logs are used to recover the database,update the standby database.

Running a Database in NOARCHIVELOG Mode

When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log. The database control file indicates that filled groups are not required to be archived. Therefore, when a filled group becomes inactive after a log switch, the group is available for reuse by LGWR.

NOARCHIVELOG mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. If a media failure occurs while the database is in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup.

In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups taken earlier while the database was in ARCHIVELOG mode. To restore a database operating in NOARCHIVELOG mode, you can use only whole database backups taken while the database is closed. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals.

Running a Database in ARCHIVELOG Mode

When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.

The archiving of filled groups has these advantages:

• A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.
• If you keep an archived log, you can use a backup taken while the database is open and in normal system use.
• You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby.

How to check the free space available in tablespaces?

SQL> select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by                                        tablespace_name;

             TABLESPACE_NAME                SUM(BYTES/1024/1024)
             ------------------------------                --------------------
                USERDATA                                         99
                SYSAUX                                         130.25
                SYSTEM                                          82.6875

if we want it without decimals then use trunc

SQL> select tablespace_name,trunc(sum(bytes/1024/1024)) from dba_free_space group by                            tablespace_name;

            TABLESPACE_NAME                TRUNC(SUM(BYTES/1024/1024))
            ------------------------------              ---------------------------
            USERDATA                                                99
            SYSAUX                                                    130
            SYSTEM                                                     82