SQL> desc scott.dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> select e.ename,e.sal,d.dname from scott.emp e,scott.dept d
2 where e.deptno=d.deptno;
no rows selected
SQL> select * from scott.dept
2 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select e.ename,e.sal,d.dname from scott.emp e,scott.dept d where e.deptno=d.deptno;
ENAME SAL DNAME
---------- ---------- --------------
CLARK 2450 ACCOUNTING
KING 5000 ACCOUNTING
MILLER 1300 ACCOUNTING
JONES 2975 RESEARCH
FORD 3000 RESEARCH
ADAMS 1100 RESEARCH
SMITH 800 RESEARCH
SCOTT 3000 RESEARCH
WARD 1250 SALES
TURNER 1500 SALES
ALLEN 1600 SALES
ENAME SAL DNAME
---------- ---------- --------------
JAMES 950 SALES
BLAKE 2850 SALES
MARTIN 1250 SALES
14 rows selected.
>>>create an external file use the
SQL> create table wb1
2 organization external
3 (type oracle_datapump
4 default directory dir_18
5 location('demo.dmp')
6 )
7 as
8 select e.ename,e.sal,d.dname from scott.emp e,scott.dept d
9 where e.deptno=d.deptno;
Table created.
SQL> select * from wb1;
ENAME SAL DNAME
---------- ---------- --------------
CLARK 2450 ACCOUNTING
KING 5000 ACCOUNTING
MILLER 1300 ACCOUNTING
JONES 2975 RESEARCH
FORD 3000 RESEARCH
ADAMS 1100 RESEARCH
SMITH 800 RESEARCH
SCOTT 3000 RESEARCH
WARD 1250 SALES
TURNER 1500 SALES
ALLEN 1600 SALES
ENAME SAL DNAME
---------- ---------- --------------
JAMES 950 SALES
BLAKE 2850 SALES
MARTIN 1250 SALES
14 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db logicalbak]$ pwd
/ocm/logicalbak
[oracle@db logicalbak]$ ll
total 155320
-rw-r--r-- 1 oracle oinstall 5679 Mar 18 10:42 aaa.txt
-rw-r----- 1 oracle oinstall 78962688 Mar 18 10:24 db.dmp
-rw-r----- 1 oracle oinstall 12288 Mar 18 11:22 demo.dmp
-rw-r----- 1 oracle oinstall 118784 Mar 18 10:05 emp.dmp
-rw-r--r-- 1 oracle oinstall 1346 Mar 18 10:07 emp.log
-rw-r----- 1 oracle oinstall 118784 Mar 18 10:07 emp_sys.dmp
-rw-r--r-- 1 oracle oinstall 1122 Mar 18 10:39 export.log
-rw-r----- 1 oracle oinstall 53723136 Mar 18 10:17 full_01.dmp
-rw-r----- 1 oracle oinstall 25243648 Mar 18 10:17 full_02.dmp
-rw-r--r-- 1 oracle oinstall 1075 Mar 18 11:21 import.log
-rw-r----- 1 oracle oinstall 221184 Mar 18 10:09 scott.dmp
-rw-r----- 1 oracle oinstall 106496 Mar 18 10:39 text1.dmp
-rw-r----- 1 oracle oinstall 86016 Mar 18 10:39 text.dmp
-rw-r----- 1 oracle oinstall 172032 Mar 18 10:10 users.dmp
-rw-r--r-- 1 oracle oinstall 82 Mar 18 11:23 WB1_4116.log
[oracle@db logicalbak]$ vim info.txt >>>create a script .
~
"info.txt" [New] 45L, 839C written
[oracle@db logicalbak]$ more info.txt
SMITH,CLERK,800
ALLEN,SALESMAN,1600
WARD,SALESMAN,1250
JONES,MANAGER,2975
MARTIN,SALESMAN,1250
BLAKE,MANAGER,2850
CLARK,MANAGER,2450
SCOTT,ANALYST,3000
KING,PRESIDENT,5000
TURNER,SALESMAN,1500
ADAMS,CLERK,1100
MITH,CLERK,800
ALLEN,SALESMAN,1600
WARD,SALESMAN,1250
JONES,MANAGER,2975
MARTIN,SALESMAN,1250
BLAKE,MANAGER,2850
CLARK,MANAGER,2450
SCOTT,ANALYST,3000
KING,PRESIDENT,5000
TURNER,SALESMAN,1500
ADAMS,CLERK,1100
MITH,CLERK,800
ALLEN,SALESMAN,1600
WARD,SALESMAN,1250
JONES,MANAGER,2975
MARTIN,SALESMAN,1250
BLAKE,MANAGER,2850
CLARK,MANAGER,2450
SCOTT,ANALYST,3000
KING,PRESIDENT,5000
TURNER,SALESMAN,1500
ADAMS,CLERK,1100
MITH,CLERK,800
ALLEN,SALESMAN,1600
WARD,SALESMAN,1250
JONES,MANAGER,2975
MARTIN,SALESMAN,1250
BLAKE,MANAGER,2850
CLARK,MANAGER,2450
SCOTT,ANALYST,3000
KING,PRESIDENT,5000
TURNER,SALESMAN,1500
ADAMS,CLERK,1100
~
[oracle@db logicalbak]$ rlwrap sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 18 11:26:59 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
>>>use external to import the data by oracle_loader
SQL> create table wb2(name varchar2(80),job varchar2(80),sal int)
2 organization external
3 (
4 type oracle_loader
5 default directory dir_18
6 location('info.txt')
7 access parameters(
8 records delimited by newline
9 fields terminated by ','
10 missing fields values are null
11 (name,job,sal)
12 )
13 );
access parameters(
*
ERROR at line 7:
ORA-00907: missing right parenthesis
SQL> save o.sql
Created file o.sql
SQL> ho vim o.sql
create table wb2(name varchar2(80),job varchar2(80),sal int)
organization external
(
type oracle_loader
default directory dir_18
location('info.txt')
access parameters(
records delimited by newline
fields terminated by ','
missing fields values are null
(name,job,sal)
)
)
/
~
~
~
"o.sql" 14L, 276C written
SQL> @o.sql
Table created.
SQL> ho more o.sql
create table wb2(name varchar2(80),job varchar2(80),sal int)
organization external
(
type oracle_loader
default directory dir_18
access parameters(
records delimited by newline
fields terminated by ','
missing fields values are null
(name,job,sal)
)
location('info.txt')
)
/
SQL> select * from wb2;
select * from wb2
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "fields": expecting one of: "field"
KUP-01007: at line 3 column 9
SQL> drop table wb2;
Table dropped.
SQL> ho vim o.sql
create table wb2(name varchar2(80),job varchar2(80),sal int)
organization external
(
type oracle_loader
default directory dir_18
access parameters(
records delimited by newline
fields terminated by ','
missing fields values are null
(name,job,sal)
)
location('info.txt')
)
/
~
~
"o.sql" 14L, 275C written
SQL> @o.sql
Table created.
SQL> select * from wb2;
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
SMITH
CLERK
800
ALLEN
SALESMAN
1600
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
WARD
SALESMAN
1250
JONES
MANAGER
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
2975
MARTIN
SALESMAN
1250
BLAKE
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
MANAGER
2850
CLARK
MANAGER
2450
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
SCOTT
ANALYST
3000
KING
PRESIDENT
5000
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
TURNER
SALESMAN
1500
ADAMS
CLERK
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
1100
MITH
CLERK
800
ALLEN
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
SALESMAN
1600
WARD
SALESMAN
1250
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
JONES
MANAGER
2975
MARTIN
SALESMAN
1250
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
BLAKE
MANAGER
2850
CLARK
MANAGER
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
2450
SCOTT
ANALYST
3000
KING
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
PRESIDENT
5000
TURNER
SALESMAN
1500
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
ADAMS
CLERK
1100
MITH
CLERK
800
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
ALLEN
SALESMAN
1600
WARD
SALESMAN
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
1250
JONES
MANAGER
2975
MARTIN
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
SALESMAN
1250
BLAKE
MANAGER
2850
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
CLARK
MANAGER
2450
SCOTT
ANALYST
3000
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
KING
PRESIDENT
5000
TURNER
SALESMAN
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
1500
ADAMS
CLERK
1100
MITH
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
CLERK
800
ALLEN
SALESMAN
1600
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
WARD
SALESMAN
1250
JONES
MANAGER
2975
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
MARTIN
SALESMAN
1250
BLAKE
MANAGER
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
2850
CLARK
MANAGER
2450
SCOTT
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
ANALYST
3000
KING
PRESIDENT
5000
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
TURNER
SALESMAN
1500
ADAMS
CLERK
1100
NAME
--------------------------------------------------------------------------------
JOB
--------------------------------------------------------------------------------
SAL
----------
~
45 rows selected.
SQL> ho more o.sql
create table wb2(name varchar2(80),job varchar2(80),sal int)
organization external
(
type oracle_loader
default directory dir_18
access parameters(
records delimited by newline
fields terminated by ','
missing field values are null
(name,job,sal)
)
location('info.txt')
)
/
SQL> desc wb2
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(80)
JOB VARCHAR2(80)
SAL NUMBER(38)
SQL> col name format a10;
SQL> col job format a10;
SQL> col sal format a10;
SQL> select * from wb2;
NAME JOB SAL
---------- ---------- ----------
SMITH CLERK ##########
ALLEN SALESMAN ##########
WARD SALESMAN ##########
JONES MANAGER ##########
MARTIN SALESMAN ##########
BLAKE MANAGER ##########
CLARK MANAGER ##########
SCOTT ANALYST ##########
KING PRESIDENT ##########
TURNER SALESMAN ##########
ADAMS CLERK ##########
NAME JOB SAL
---------- ---------- ----------
MITH CLERK ##########
ALLEN SALESMAN ##########
WARD SALESMAN ##########
JONES MANAGER ##########
MARTIN SALESMAN ##########
BLAKE MANAGER ##########
CLARK MANAGER ##########
SCOTT ANALYST ##########
KING PRESIDENT ##########
TURNER SALESMAN ##########
ADAMS CLERK ##########
NAME JOB SAL
---------- ---------- ----------
MITH CLERK ##########
ALLEN SALESMAN ##########
WARD SALESMAN ##########
JONES MANAGER ##########
MARTIN SALESMAN ##########
BLAKE MANAGER ##########
CLARK MANAGER ##########
SCOTT ANALYST ##########
KING PRESIDENT ##########
TURNER SALESMAN ##########
ADAMS CLERK ##########
NAME JOB SAL
---------- ---------- ----------
MITH CLERK ##########
ALLEN SALESMAN ##########
WARD SALESMAN ##########
JONES MANAGER ##########
MARTIN SALESMAN ##########
BLAKE MANAGER ##########
CLARK MANAGER ##########
SCOTT ANALYST ##########
KING PRESIDENT ##########
TURNER SALESMAN ##########
ADAMS CLERK ##########
NAME JOB SAL
---------- ---------- ----------
~
45 rows selected.
SQL> col sal format 99999;
SQL> select * from wb2;
NAME JOB SAL
---------- ---------- ------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
NAME JOB SAL
---------- ---------- ------
MITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
NAME JOB SAL
---------- ---------- ------
MITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
NAME JOB SAL
---------- ---------- ------
MITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
NAME JOB SAL
---------- ---------- ------
~
45 rows selected.
SQL> ho more o.sql
create table wb2(name varchar2(80),job varchar2(80),sal int)
organization external
(
type oracle_loader
default directory dir_18
access parameters(
records delimited by newline
fields terminated by ','
missing field values are null
(name,job,sal)
)
location('info.txt')
)
SQL> select ename||','||job||','||sal from scott.emp;
ENAME||','||JOB||','||SAL
-------------------------------------------------------------
SMITH,CLERK,800
ALLEN,SALESMAN,1600
WARD,SALESMAN,1250
JONES,MANAGER,2975
MARTIN,SALESMAN,1250
BLAKE,MANAGER,2850
CLARK,MANAGER,2450
SCOTT,ANALYST,3000
KING,PRESIDENT,5000
TURNER,SALESMAN,1500
ADAMS,CLERK,1100
ENAME||','||JOB||','||SAL
-------------------------------------------------------------
JAMES,CLERK,950
FORD,ANALYST,3000
MILLER,CLERK,1300
14 rows selected.
SQL> ho more info.txt
SMITH,CLERK,800
ALLEN,SALESMAN,1600
WARD,SALESMAN,1250
JONES,MANAGER,2975
MARTIN,SALESMAN,1250
BLAKE,MANAGER,2850
CLARK,MANAGER,2450
SCOTT,ANALYST,3000
KING,PRESIDENT,5000
TURNER,SALESMAN,1500
ADAMS,CLERK,1100
MITH,CLERK,800
ALLEN,SALESMAN,1600
WARD,SALESMAN,1250
JONES,MANAGER,2975
MARTIN,SALESMAN,1250
BLAKE,MANAGER,2850
CLARK,MANAGER,2450
SCOTT,ANALYST,3000
KING,PRESIDENT,5000
TURNER,SALESMAN,1500
ADAMS,CLERK,1100
MITH,CLERK,800
ALLEN,SALESMAN,1600
WARD,SALESMAN,1250
JONES,MANAGER,2975
MARTIN,SALESMAN,1250
BLAKE,MANAGER,2850
CLARK,MANAGER,2450
SCOTT,ANALYST,3000
KING,PRESIDENT,5000
TURNER,SALESMAN,1500
ADAMS,CLERK,1100
MITH,CLERK,800
ALLEN,SALESMAN,1600
WARD,SALESMAN,1250
JONES,MANAGER,2975
MARTIN,SALESMAN,1250
BLAKE,MANAGER,2850
CLARK,MANAGER,2450
SCOTT,ANALYST,3000
KING,PRESIDENT,5000
TURNER,SALESMAN,1500
ADAMS,CLERK,1100
~
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db logicalbak]$ rlwrap sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 18 11:36:30 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter tablespace users read only;
alter tablespace users read only
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> show user
USER is "SCOTT"
SQL> conn sys as sysdbaf
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
<proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter tablespace users read only; >>>make the tablespace readonly,which could make the data coherence
Tablespace altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db logicalbak]$ dbf
[oracle@db logicalbak]$
[oracle@db logicalbak]$ expdp directory=dir_18 dumpfile=tts.dmp tablespaces=users transport_tablespace=y[oracle@db logicalbak]$ expdp directory=dir_18 dumpfile=tts.dmp tablespaces=users transport_tablespace=y
Export: Release 11.2.0.1.0 - Production on Sun Mar 18 11:37:40 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: sys as sysdba
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, Replaced with: "transport_tablespaces=users"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": sys/******** AS SYSDBA directory=dir_18 dumpfile=tts.dmp tablespaces=users reuse_dumpfiles=true
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/AUDIT_OBJ
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/ocm/logicalbak/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS:
/oracle/oradata/etc/users01.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:38:33
[oracle@db logicalbak]$ scp tts.dmp 172.16.10.101:/oracle
oracle@172.16.10.101's password:
tts.dmp 100% 136KB 136.0KB/s 00:00
[oracle@db logicalbak]$ scp /oracle/oradata/etc/users01.dbf 172.16.10.101:/oracle
oracle@172.16.10.101's password:
users01.dbf 100% 36MB 5.2MB/s 00:07
[oracle@db logicalbak]$ expdp directory=dir_18 dumpfile=tts.dmp tablespaces=users transport_tablespace=y[oracle@db logicalbak]$ rlwrap sqlplus scott/tiger
[oracle@db logicalbak]$ rlwrap sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 18 11:43:43 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter tablespace users read write;
Tablespace altered.