Oracle uses a Data Pump to unload data

Oracle 10 Gb provides a way to use external tables to extract data.

First, you need a directory (here we will continue to use the previously created directory)

1. SQL> showUser
2. USERIs"SYS"
3. SQL>Select*FromDba_directoriesWhereDIRECTORY_NAMELike '% SYS %';
5. OWNER DIRECTORY_NAME DIRECTORY_PATH
6. --------------------------------------------------------------------------------------------------------------
7. SYS SYS_SQLLDR_XT_TMPDIR_00000/u01/sqlldr

Then execute the command

1. SQL> showUser
2. USERIs"ING"
3. SQL>DescSYS_SQLLDR_X_EXT_DEPT
4. Is the name empty? Type
5.  -----------------------------------------------------------------------------
6. Deptno number (10)
7. DNAME VARCHAR2 (20)
8. LOC VARCHAR2 (20)
10. SQL>Create TableSYS_SQLLDR_X_EXT_DEPT_UNLOAD
11. 2 organization external
12. 3 (type oracle_datapump
13. 4DefaultDirectory SYS_SQLLDR_XT_TMPDIR_00000
14. 5 location ('Sys _ SQLLDR_X_EXT_DEPT_UNLOAD.dat')
15. 6)As
16. 7Select*FromSYS_SQLLDR_X_EXT_DEPT;
18. The table has been created.

A SYS_SQLLDR_X_EXT_DEPT_UNLOAD table will be created in the database. Two more files will be created in the directory.

1. [Oracle @ linux sqlldr] $ ll | grep SYS
2. -Rw-r-- R -- 1 oracle oinstall 148 10-04 SYS_SQLLDR_X_EXT_DEPT_UNLOAD_3501.log
3. -Rw-r----- 1 oracle oinstall 12288 10-04 SYS_SQLLDR_X_EXT_DEPT_UNLOAD.dat

Finally, copy the SYS_SQLLDR_X_EXT_DEPT_UNLOAD.dat file to the corresponding directory of other databases, and extract the External table creation script.

1. SQL>SetLong 1, 20000
2. SQL>SetPagesize 99
3. SQL>SelectDbms_metadata.get_ddl ('Table','Sys _ SQLLDR_X_EXT_DEPT_UNLOAD')FromDual;
5. DBMS_METADATA.GET_DDL ('Table','Sys _ SQLLDR_X_EXT_DEPT_UNLOAD')
6. --------------------------------------------------------------------------------
8. CREATE TABLE "ING"."SYS_SQLLDR_X_EXT_DEPT_UNLOAD"
9. ("DEPTNO"NUMBER (10, 0 ),
10. "DNAME"VARCHAR2 (20 ),
11. "LOC"VARCHAR2 (20)
12. )
13. ORGANIZATION EXTERNAL
14. (TYPE ORACLE_DATAPUMP
15. DEFAULTDIRECTORY"SYS_SQLLDR_XT_TMPDIR_00000"
17. LOCATION
18. ('Sys _ SQLLDR_X_EXT_DEPT_UNLOAD.dat'
19. )
20. )

You can even load data directly from an outsourcing table.

1. SQL>Insert/* + Append */IntoDeptSelect*FromSYS_SQLLDR_X_EXT_DEPT_UNLOAD;
3. Four rows have been created.
5. SQL>Commit;
7. Submitted.
9. SQL>Select*FromDept;
11. DEPTNO DNAME LOC
12. --------------------------------------------------
13. 50 Sales Virginia
14. 60 Accounting Virginia
15. 70 Consulting Virginia
16. 80 Finance Virginia