impdp remap_schema

How to export and import the schema from one user to another ?

Here I'm going to explain how to use impdp/expdp utility to import schema and export it to another user.

Getting Started

Create a directory object in database that point to a physical location on the Operating system where dumps,log etc are going to store.

CREATE OR REPLACE DIRECTORY java_latte_dir AS '/home/oracle/java_latte' ;

This directory object is only a pointer to a physical directory, it will not create any directory.

You can view all the directories with the help of DBA_DIRECTORIES view.

Those users who are going to use these utility must have READ,WRITE permission to this directory object.

GRANT READ OR WRITE ON DIRECTORY to SCOTT;

Schema Import/Export

Here we first try to export a schema of user TEST using the following expdp command.

expdp scott/tiger schemas=TEST directory=java_late_dir dumpfile=java_latte.dmp logfile=java_latte.log

Output  is look like this :

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  sys/******** AS SYSDBA schemas=test directory=TEST1_DIR dumpfile=test.dmp logfile=test.log 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 55.12 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."BIG_TABLE"                          46.61 MB 1000000 rows
. . exported "TEST"."ABC"                                5.062 KB       1 rows
. . exported "TEST"."LOOKUP"                             5.492 KB       3 rows
. . exported "TEST"."BIG_TABLE2":"BIG_TABLE_2005"            0 KB       0 rows
. . exported "TEST"."BIG_TABLE2":"BIG_TABLE_2006"            0 KB       0 rows
. . exported "TEST"."BIG_TABLE2":"BIG_TABLE_2007"            0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/java_latte/java_latte.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 23:56:15

Now we try to import this schema to different user. There is no need of creating another user, it will automatically create the new user.

impdp scott/tiger dumpfile=java_latte.dmp directory=java_late_dir remap_schema=TEST:TEST1

Output look like this:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  sys/******** AS SYSDBA dumpfile=test.dmp directory=TEST1_DIR remap_schema=TEST:TEST3 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST3"."BIG_TABLE"                         46.61 MB 1000000 rows
. . imported "TEST3"."ABC"                               5.062 KB       1 rows
. . imported "TEST3"."LOOKUP"                            5.492 KB       3 rows
. . imported "TEST3"."BIG_TABLE2":"BIG_TABLE_2005"           0 KB       0 rows
. . imported "TEST3"."BIG_TABLE2":"BIG_TABLE_2006"           0 KB       0 rows
. . imported "TEST3"."BIG_TABLE2":"BIG_TABLE_2007"           0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 00:13:01

Finally, you need to unlock the user and set the password for them.

Connect as sys user and use the following command :

ALTER USER test1 ACCOUNT UNLOCK;

ALTER USER test1 IDENTIFIED BY test1;

if you find this information useful, please comment.

How does an oracle database start ?

Here I'm giving the step by step procedure to show how does an Oracle database start.

1. Start up process looks for a server parameter file or parameter file in the default location.
2. If a parameter file is found,it then parse it and get the info about the initialization parameters.
3. Allocate the SGA and started the background process.If auditing, altering and tracing also brought up.
4. Till this point we can say that oracle instance is started in nomount mode.
5. The same can be achieved by issue the following command :
   STARTUP NOMOUNT
6. Then, control file is read to know about the the details of the database and its related data files like data files , redo log files.
7. At this point, the database has been started. But it can only accessed by the DBA's.
8. This is called the MOUNT mode.
9. The same can be achived by issue the following command :
   STARTUP MOUNT
10. Then once the database has mounted, it get opened for other users.
11. After this it can go the recovery mode if you have done hard shutdown or if there is power failure in order to do synchronization between redo files and data files.
12. Then database is open for business.There will be consistency check not at the time of OPEN mode but periodically through the up time. SMON is required to do this.
13. As the user connect, the PMON process come into picture and monitor those connections.
14. The same can be achived by issue the following command :
    STARTUP OPEN

if you find this information useful, please comment.

Extent, Block & Segment in Oracle

Oracle store data in data blocks also called as logical blocks, oracle blocks .One data block corresponds to specific number of bytes on disk.

Extents: it is a specific number of continuous block allocated for storing special type of information.A segment is set of extents,each of which is allocated for specific data structure and stored in same table space.

Example :
table data is stored in its own data segment.
index's data is stored in its own index segment.

Oracle allocate space to segment in terms of one extent.when existing extents got full, Oracle allocate another extents for the segment.
Because extents are allocated  as needed, so extents of segment may or may not be  contiguous.

How to see all the segment type present in oracle :

SQL> select segment_type ,count(*) from dba_segments group  by segment_type;

SEGMENT_TYPE     COUNT(*)
------------------ ----------
LOBINDEX 1006
INDEX PARTITION  295
TABLE SUBPARTITION   32
TABLE PARTITION  270
NESTED TABLE   40
ROLLBACK    1
LOB PARTITION    1
LOBSEGMENT 1006
INDEX 2183
TABLE 1534
CLUSTER   10
TYPE2 UNDO   20

All segments and its extents stored in one table space  In table space,segment can include extents from more than file means segment can span over multiple data files. But extents contain data from one data files.

To see data files , refer to table DBA_DATA_FILES describes database files.

To see segment and extents info , refer to table DBA_SEGMENTS & DBA_EXTENTS.