Friday , July 21 2017
Home / Oracle DBA / DATA PUMP USING BACKUP

DATA PUMP USING BACKUP

 

Backups:

Oracle database backups categorized into 3 types

1.logical backups
2.physical backups
1.cold backup/offline
2.hot backup/online
3.rman backups

Logical backups:

1.logical backups can be taken when the database is up and running
2.it is an alternative backup method
3.using logical backup we can be selective(we can take the backup of entire datase,a particular tablespace,particular schema objects or particular table)
4.Using logical backups we can take backup from pariticular schema and we can restore in a another schema.
5.Using logical backups we can export the complete database,we can import the objects in the second database.
6.We can use the logical backups for database upgradation and migration.
7.Unlike a physical backup the logical backup takes only content of the database so compare physical backups logical backups occupies less space.
8.Using logical backup we go for re-organization of the database.
9.Usinfg logical backups incremental backups are possible.ie we can take only modifies objects export.

Dis Adv:

1.Restory is possible but recovery is not possible.
2.Compare physical backups import the data init to disk it is very slow than physical import.

Logical backups

export /import

exp
imp

 

Sun-C
MON-I
TUE-I
WED-CU
THU-I
FRI-I
SAT-CU
SUN-C

Datapump:

Datapump is new technology introduced in oracle 10g

utilies for datapump are expdp and impdp

datapump is much faster compared to the traditional export.

it facilitates the moment of data using the pl/sql APIs like dbms_metadata and dbms_datapump

it is the server side utility here dumpfile is generated at server side.

the dumpfile generated by datafile is not compatible with the dump file generated by export utility there fore using imp we can’t import the
dump file generated by the expdp utility

Advantages:

we can stop the job whenever a performance issue and we can resume at later point of time.
we can attach and detach the jobs.
when ever we invoke the datapump job it created a master table and master table control process keeps this table.

vi imp.par

directory=dpump
dumpfile=sch.dmp
logfile=imp_sch.log
remap_schema=undouser:newuser
#schemas=undouser
exclude=table:”IN(‘EMP’,’DEPT’)”

 

1.Create the directory at server side.

create or replace directory data_pump_dir as ‘/u01/app/oracle/dbpump’;

2.select *from dba_directories;

3.mkdir -p /u01/app/oracle/dbpump

4.$expdp directory=data_pump_dir dumpfile=full_exp.dmp logfile=full_exp.log full=y

 

DATACENTER

4 tables size 4gb

/u01 ->2gb

Network_link

mknod

parallel=4
direct=y
buffer=500000
indexes=n

 

test

4gb
/u01 ->10gb

 

[root@ssss1 ~]# cat /etc/oratab
#

 

# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ‘:’, is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, ‘#’, are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , “Y”, or should not,
# “N”, be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
hrms:/u01/app/oracle/product/11.2.0/dbhome:N
datacenter:/u01/app/oracle/product/11.2.0/dbhome:N
[root@ssss1 ~]# cat /etc/password
cat: /etc/password: No such file or directory
[root@ssss1 ~]# cat /etc/passwd
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
news:x:9:13:news:/etc/news:/sbin/nologin
uucp:x:10:14:uucp:/var/spool/uucp:/sbin/nologin
operator:x:11:0:operator:/root:/sbin/nologin
games:x:12:100:games:/usr/games:/sbin/nologin
gopher:x:13:30:gopher:/var/gopher:/sbin/nologin
ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
nobody:x:99:99:Nobody:/:/sbin/nologin
dbus:x:81:81:System message bus:/:/sbin/nologin
vcsa:x:69:69:virtual console memory owner:/dev:/sbin/nologin
rpm:x:37:37::/var/lib/rpm:/sbin/nologin
haldaemon:x:68:68:HAL daemon:/:/sbin/nologin
ident:x:98:98::/home/ident:/sbin/nologin
netdump:x:34:34:Network Crash Dump user:/var/crash:/bin/bash
nscd:x:28:28:NSCD Daemon:/:/sbin/nologin
sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
rpc:x:32:32:Portmapper RPC user:/:/sbin/nologin
mailnull:x:47:47::/var/spool/mqueue:/sbin/nologin
smmsp:x:51:51::/var/spool/mqueue:/sbin/nologin
rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
nfsnobody:x:65534:65534:Anonymous NFS User:/var/lib/nfs:/sbin/nologin
pcap:x:77:77::/var/arpwatch:/sbin/nologin
apache:x:48:48:Apache:/var/www:/sbin/nologin
squid:x:23:23::/var/spool/squid:/sbin/nologin
webalizer:x:67:67:Webalizer:/var/www/usage:/sbin/nologin
xfs:x:43:43:X Font Server:/etc/X11/fs:/sbin/nologin
ntp:x:38:38::/etc/ntp:/sbin/nologin
gdm:x:42:42::/var/gdm:/sbin/nologin
pegasus:x:66:65:tog-pegasus OpenPegasus WBEM/CIM services:/var/lib/Pegasus:/sbin /nologin
htt:x:100:103:IIIMF Htt:/usr/lib/im:/sbin/nologin
pvm:x:24:24::/usr/share/pvm3:/bin/bash
canna:x:39:39:Canna Service User:/var/lib/canna:/sbin/nologin
wnn:x:49:49:Wnn Input Server:/var/lib/wnn:/sbin/nologin
quagga:x:92:92:Quagga routing suite:/var/run/quagga:/sbin/nologin
cyrus:x:76:12:Cyrus IMAP Server:/var/lib/imap:/bin/bash
dovecot:x:97:97:dovecot:/usr/libexec/dovecot:/sbin/nologin
exim:x:93:93::/var/spool/exim:/sbin/nologin
postfix:x:89:89::/var/spool/postfix:/sbin/nologin
mailman:x:41:41:GNU Mailing List Manager:/usr/lib/mailman:/sbin/nologin
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
amanda:x:33:6:Amanda user:/var/lib/amanda:/bin/bash
uuidd:x:101:105:UUID generator helper daemon:/var/lib/libuuid:/sbin/nologin
named:x:25:25:Named:/var/named:/sbin/nologin
ldap:x:55:55:LDAP User:/var/lib/ldap:/bin/false
fax:x:78:78:mgetty fax spool user:/var/spool/fax:/sbin/nologin
radiusd:x:95:95:radiusd user:/:/bin/false
radvd:x:75:75:radvd user:/:/sbin/nologin
oracle:x:500:500::/home/oracle:/bin/bash
[root@ssss1 ~]# ps -ef|grep
Usage: grep [OPTION]… PATTERN [FILE]…
Try `grep –help’ for more information.
[root@ssss1 ~]# cat /etc/oratab
#

 

# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.

# A colon, ‘:’, is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, ‘#’, are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , “Y”, or should not,
# “N”, be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
hrms:/u01/app/oracle/product/11.2.0/dbhome:N
datacenter:/u01/app/oracle/product/11.2.0/dbhome:N
[root@ssss1 ~]# cat /etc/passwd
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
news:x:9:13:news:/etc/news:/sbin/nologin
uucp:x:10:14:uucp:/var/spool/uucp:/sbin/nologin
operator:x:11:0:operator:/root:/sbin/nologin
games:x:12:100:games:/usr/games:/sbin/nologin
gopher:x:13:30:gopher:/var/gopher:/sbin/nologin
ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
nobody:x:99:99:Nobody:/:/sbin/nologin
dbus:x:81:81:System message bus:/:/sbin/nologin
vcsa:x:69:69:virtual console memory owner:/dev:/sbin/nologin
rpm:x:37:37::/var/lib/rpm:/sbin/nologin
haldaemon:x:68:68:HAL daemon:/:/sbin/nologin
ident:x:98:98::/home/ident:/sbin/nologin
netdump:x:34:34:Network Crash Dump user:/var/crash:/bin/bash
nscd:x:28:28:NSCD Daemon:/:/sbin/nologin
sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
rpc:x:32:32:Portmapper RPC user:/:/sbin/nologin
mailnull:x:47:47::/var/spool/mqueue:/sbin/nologin
smmsp:x:51:51::/var/spool/mqueue:/sbin/nologin
rpcuser:x:29:29:RPC Service User:/var/lib/nfs:/sbin/nologin
nfsnobody:x:65534:65534:Anonymous NFS User:/var/lib/nfs:/sbin/nologin
pcap:x:77:77::/var/arpwatch:/sbin/nologin
apache:x:48:48:Apache:/var/www:/sbin/nologin
squid:x:23:23::/var/spool/squid:/sbin/nologin
webalizer:x:67:67:Webalizer:/var/www/usage:/sbin/nologin
xfs:x:43:43:X Font Server:/etc/X11/fs:/sbin/nologin
ntp:x:38:38::/etc/ntp:/sbin/nologin
gdm:x:42:42::/var/gdm:/sbin/nologin
pegasus:x:66:65:tog-pegasus OpenPegasus WBEM/CIM services:/var/lib/Pegasus:/sbin /nologin
htt:x:100:103:IIIMF Htt:/usr/lib/im:/sbin/nologin
pvm:x:24:24::/usr/share/pvm3:/bin/bash
canna:x:39:39:Canna Service User:/var/lib/canna:/sbin/nologin
wnn:x:49:49:Wnn Input Server:/var/lib/wnn:/sbin/nologin
quagga:x:92:92:Quagga routing suite:/var/run/quagga:/sbin/nologin
cyrus:x:76:12:Cyrus IMAP Server:/var/lib/imap:/bin/bash
dovecot:x:97:97:dovecot:/usr/libexec/dovecot:/sbin/nologin
exim:x:93:93::/var/spool/exim:/sbin/nologin
postfix:x:89:89::/var/spool/postfix:/sbin/nologin
mailman:x:41:41:GNU Mailing List Manager:/usr/lib/mailman:/sbin/nologin
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
amanda:x:33:6:Amanda user:/var/lib/amanda:/bin/bash
uuidd:x:101:105:UUID generator helper daemon:/var/lib/libuuid:/sbin/nologin
named:x:25:25:Named:/var/named:/sbin/nologin
ldap:x:55:55:LDAP User:/var/lib/ldap:/bin/false
fax:x:78:78:mgetty fax spool user:/var/spool/fax:/sbin/nologin
radiusd:x:95:95:radiusd user:/:/bin/false
radvd:x:75:75:radvd user:/:/sbin/nologin
oracle:x:500:500::/home/oracle:/bin/bash
[root@ssss1 ~]# su – oracle
[oracle@ssss1 ~]$ . oraenv
ORACLE_SID = [oracle] ? datacenter
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ssss1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 11 09:47:01 2017

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

Connected to an idle instance.
SQL> select name,open_mode,log_mode from v$database;

NAME OPEN_MODE LOG_MODE
——— ——————– ————
DATACENT READ WRITE ARCHIVELOG

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Pr oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ssss1 ~]$ pwd
/home/oracle
[oracle@ssss1 ~]$ mkdir logical/ ( LOGICAL DIRECTORY IS EMPTY DIRECTORY)
mkdir: cannot create directory `logical/’: File exists

[oracle@ssss1 logical]$ which exp
/u01/app/oracle/product/11.2.0/dbhome/bin/exp
[oracle@ssss1 logical]$ which imp
/u01/app/oracle/product/11.2.0/dbhome/bin/imp
[oracle@ssss1 logical]$

[oracle@ssss1 logical]$ exp help=y
**(HELP=Y it’s show all the parameters related to logical backup)**

Export: Release 11.2.0.2.0 – Production on Sun Jun 11 14:39:07 2017

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

 

You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:

Format: EXP KEYWORD=value or KEYWORD=(value1,value2,…,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword Description (Default) Keyword Description (Default)
————————————————————————–
USERID username/password FULL export entire file (N)
BUFFER size of data buffer OWNER list of owner usernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental export type
INDEXES export indexes (Y) RECORD track incr. export (Y)
DIRECT direct path (N) TRIGGERS export triggers (Y)
LOG log file of screen output STATISTICS analyze objects (ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS export constraints (Y)

OBJECT_CONSISTENT transaction set to read only during object export (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot back to
FLASHBACK_TIME time used to get the SCN closest to the specified time
QUERY select clause used to export a subset of a table
RESUMABLE suspend when a space related error is encountered(N)
RESUMABLE_NAME text string used to identify resumable statement
RESUMABLE_TIMEOUT wait time for RESUMABLE
TTS_FULL_CHECK perform full or partial dependency check for TTS
VOLSIZE number of bytes to write to each tape volume
TABLESPACES list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE template name which invokes iAS mode export

Export terminated successfully without warnings.

TAKING FULL DATABASE BACKUP:
[oracle@ssss1 logical]$ exp file=fulldb.dmp log=fulldb.log full=y

**(file=one of the parameter ,log=fulldb.log is we check provides of the job)**

 

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@ssss1 logical]$ ls
fulldb.dmp fulldb.log

TAKING SCHEMA LEVEL EXPORT:
[oracle@ssss1 logical]$ exp file=user.dmp log=user.log owner=scott

Export: Release 11.2.0.2.0 – Production on Sun Jun 11 14:48:23 2017

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.2.0 – 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 WE8MSWIN1252 character set (possible charset conversion)

About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT
About to export SCOTT’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT’s tables via Conventional Path …
. . exporting table BONUS 0 rows exported
. . exporting table DEPT 4 rows exported
. . exporting table EMP 14 rows exported
. . 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 without warnings.
[oracle@ssss1 logical]$

TAKING TABLE LEVEL EXPORT:
[oracle@ssss1 logical]$ exp file=table.dmp log=table.log tables=emp,dept

Export: Release 11.2.0.2.0 – Production on Sun Jun 11 14:50:06 2017

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

 

Username: scott/tiger

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 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 WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path …
. . exporting table EMP 14 rows exported
. . exporting table DEPT 4 rows exported
Export terminated successfully without warnings.
[oracle@ssss1 logical]$
TAKING ROW LEVEL EXPORT:
**(open new terminal)**

SQL> show user
USER is “SYS”
SQL> conn scott/tiger
Connected.
SQL> show user
USER is “SCOTT”
SQL> select * from emp where deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7782 CLARK MANAGER 7839 09-JUN-81 2450
10

7839 KING PRESIDENT 17-NOV-81 5000
10

7934 MILLER CLERK 7782 23-JAN-82 1300
10

SQL> select count(*) from emp where deptno=10;

COUNT(*)
———-
3

 

[oracle@ssss1 logical]$ exp file=row.dmp log=row.log tables=emp query=\’where deptno=10\’;

Export: Release 11.2.0.2.0 – Production on Sun Jun 11 14:58:07 2017

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

 

Username: scott/tiger

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 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 WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path …
. . exporting table EMP 3 rows exported
Export terminated successfully without warnings.
[oracle@ssss1 logical]$

META DATA LEVEL EXPORT:

[oracle@ssss1 logical]$ exp file=meta.dmp log=meta.log tables=emp rows=n

Export: Release 11.2.0.2.0 – Production on Sun Jun 11 14:59:39 2017

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

 

Username: scott/tiger

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 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 WE8MSWIN1252 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path …
. . exporting table EMP
Export terminated successfully without warnings.

**IMPORTING**
USER LEVEL IMPORTING:

SQL> drop user scott cascade;

User dropped.

SQL> grant connect,resource to scott identified by tiger;

Grant succeeded.

SQL> conn scott/tiger
Connected.

[oracle@ssss1 ~]$ ls
logical rman.log
[oracle@ssss1 ~]$ cd logical/
[oracle@ssss1 logical]$ ls
fulldb.dmp meta.dmp row.dmp table.dmp user.dmp
fulldb.log meta.log row.log table.log user.log
[oracle@ssss1 logical]$ imp file=user.dmp log=user.log fromuser=scott touser=scott

Import: Release 11.2.0.2.0 – Production on Sun Jun 11 16:07:58 2017

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.2.0 – 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 WE8MSWIN1252 character set (possible charset conversion)
. importing SCOTT’s objects into SCOTT
. . importing table “BONUS” 0 rows imported
. . importing table “DEPT” 4 rows imported
. . importing table “EMP” 14 rows imported
. . importing table “SALGRADE” 5 rows imported
About to enable constraints…
Import terminated successfully without warnings.
[oracle@ssss1 logical]$
SQL> show user
USER is “SCOTT”
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

TABLE LEVEL IMPORTING:
SQL> drop table emp purge;

Table dropped.

SQL> drop table dept purge;

Table dropped.
[oracle@ssss1 logical]$ imp file=table.dmp log=table.log fromuser=scott touser=scott

Import: Release 11.2.0.2.0 – Production on Sun Jun 11 16:13:47 2017

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.2.0 – 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 SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SCOTT’s objects into SCOTT
. . importing table “EMP” 14 rows imported
. . importing table “DEPT” 4 rows imported
About to enable constraints…
Import terminated successfully without warnings.

 

IMPORTING A TABLE DIFFERENT SCHEMA:

[root@ssss1 ~]# su – oracle
[oracle@ssss1 ~]$ sqlplus / as sysdba
[oracle@ssss1 ~]$ . oraenv
ORACLE_SID = [oracle] ? datacenter
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ssss1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Jun 11 16:19:40 2017

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

 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user cheysar identified by cheysar;

User created.

SQL> select * from all_users;

USERNAME USER_ID CREATED
—————————— ———- ———
XS$NULL 2147483638 05-SEP-10
CHEYSAR 85 11-JUN-17
SCOTT 84 11-JUN-17
OWBSYS_AUDIT 82 05-SEP-10
OWBSYS 78 05-SEP-10
APEX_030200 77 05-SEP-10
APEX_PUBLIC_USER 75 05-SEP-10
FLOWS_FILES 74 05-SEP-10
MGMT_VIEW 73 05-SEP-10
SYSMAN 71 05-SEP-10
SPATIAL_CSW_ADMIN_USR 69 05-SEP-10

USERNAME USER_ID CREATED
—————————— ———- ———
SPATIAL_WFS_ADMIN_USR 66 05-SEP-10
MDDATA 64 05-SEP-10
OLAPSYS 60 05-SEP-10
MDSYS 57 05-SEP-10
SI_INFORMTN_SCHEMA 56 05-SEP-10
ORDPLUGINS 55 05-SEP-10
ORDDATA 54 05-SEP-10
ORDSYS 53 05-SEP-10
ANONYMOUS 46 05-SEP-10
XDB 45 05-SEP-10
CTXSYS 43 05-SEP-10

USERNAME USER_ID CREATED
—————————— ———- ———
EXFSYS 42 05-SEP-10
WMSYS 32 05-SEP-10
APPQOSSYS 31 05-SEP-10
DBSNMP 30 05-SEP-10
ORACLE_OCM 21 05-SEP-10
DIP 14 05-SEP-10
OUTLN 9 05-SEP-10
SYSTEM 5 05-SEP-10
SYS 0 05-SEP-10

31 rows selected.

SQL>
SQL> conn / as sysdba
Connected.
SQL> grant connect,resource to cheysar identified by cheysar;

Grant succeeded.

[oracle@ssss1 logical]$ clear

[oracle@ssss1 logical]$ imp file=table.dmp log=table.log fromuser=scott touser=cheysar

Import: Release 11.2.0.2.0 – Production on Sun Jun 11 16:26:01 2017

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.2.0 – 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 SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SCOTT’s objects into CHEYSAR
. . importing table “EMP” 14 rows imported
. . importing table “DEPT” 4 rows imported
About to enable constraints…
Import terminated successfully without warnings.
[oracle@ssss1 logical]$

**(open terminal)**

SQL> show user
USER is “SYS”
SQL> conn cheysar/cheysar
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
DEPT TABLE
EMP TABLESQL> select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
DEPT TABLE
EMP TABLE

ROW LEVEL IMPORT:

SQL> show user
USER is “CHEYSAR”
SQL> drop table emp purge;

Table dropped.
[oracle@ssss1 logical]$ imp file=row.dmp log=row.log fromuser=scott touser=cheysar

Import: Release 11.2.0.2.0 – Production on Sun Jun 11 16:36:40 2017

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.2.0 – 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 SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SCOTT’s objects into CHEYSAR
. . importing table “EMP” 3 rows imported
About to enable constraints…
Import terminated successfully without warnings.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
DEPT TABLE
EMP TABLE
SQL> select * from emp;

META DATA LEVEL IMPORT:

SQL> drop table emp purge;

Table dropped.

[oracle@ssss1 logical]$ imp file=meta.dmp log=meta.log fromuser=scott touser=cheysar

Import: Release 11.2.0.2.0 – Production on Sun Jun 11 16:42:21 2017

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.2.0 – 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 SCOTT, not by you

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing SCOTT’s objects into CHEYSAR
About to enable constraints…
Import terminated successfully without warnings.
[oracle@ssss1 logical]$
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
DEPT TABLE
EMP TABLE

SQL> select * from emp;

no rows selected

SQL> desc emp;
Name Null? Type
—————————————– ——– —————————- EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

 

 

 

 

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

Your email address will not be published. Required fields are marked *