[Db Admin] 16.Loading and Reorganizing Data

1. Direct-Load Insert 사용법

   SQL> INSERT /*+APPEND*/ INTO scott.emp
     2  NOLOGGING
     3  SELECT * FROM scott.old_emp ;
   
   - /*+APPEND*/ : HINT 옵션, Direct-Load를 실행하라.
   - NOLOGGING : redo log를 남기지 말라.

   * Direct-Load Insert는 High-Water-Mark 위쪽으로 data를 모두 load 한 후에
     High-Water-Mark를 data 끝에 찍어준다.
     
2. Parallel Direct-Load Insert
   ; 작업을 여러개의 process로 나눠서 동시에 작업한다.
   
   SQL> ALTER SESSION ENABLE PARALLEL DML ;
   SQL> INSERT /*+PARALLEL(scott.emp, 2) */    <-- 2는 2개의 process로 처리하라는 뜻이다.
     2      INTO scott.emp
     3  NOLOGGING
     4  SELECT * FROM scott.old_emp ;
   
3. SQL*Loader 사용법
   ; Text File을 읽어서 DB에 저장해주는 Tool
   
   $ sqlldr scott/tiger \
   > control=ulcase6.ctl \
   > log=ulcase6.log  direct=true
   
   [syntax]
   $ sqlldr [keyword=] value [ [ [,] keyword=] value] ...
   

Keyword	Meaning
USERID	Oracle username and password(If password is not specified, the user will be prompted for the password.)
CONTROL	Control file name
LOG	Log file name(name defaults to contolfile.log
BAD	Bad file that stores all rejected records(name defaults to contolfile.bad)
DATA	Input data file names
DISCARD	Optional discard file where records not selected are stored
DISCARDMAX	Maximum number of discards to allow(The default is to allow all discards. Use this parameter as a safety measure to stop the run if the wrong input files are specified.)
SKIP	The number of records to skip, primarily used to continue a load that failed(Use this option only if loading to a single table or to skip an identical number of records for all the tables loaded.)
LOAD	Specifies the number of records to load, after skipping the records specified by SKIP
ERRORS	Maximum number of bad records to allow
ROWS	Specifies the number of rows in the array to be built before each insert for conventional loads(For direct path loads this defines the approximate number of rows read from input for each data save. Direct load builds full blocks, and then rejects discards and invalid rows, before a data save.)
BINDSIZE	Specifies the maximum number of bytes to be used for building an array of rows to be inserted in each database call, for conventional loads(If the ROWS parameter is also specified, SQL*Loader will build as many rows as defined by ROWS, subject to the limit imposed by BINDSIZE.)
DIRECT	SQL*Loader uses the direct path if this parameter is set to TRUE. Conventional path, which is the default, is used otherwise.
PARFILE	Specifies the name of the file that contains all the load parameters(Parameters defined in the command line will override any values defined in the parameter file.)
PARALLEL	This parameter, which is only valid for direct loads, specifies that multiple parallel direct loads are to be carried out.
FILE	Specifies the file in which the temporary segement for a parallel direct load is to be created

4. Export 사용법 (exp scott/tiger file=/backup/orabackup.dmp owner=scott)

   $ exp scott/tiger tables=(dept,emp) \
   > file=emp.dmp log=exp.log compress=n \
   > direct=y recordlength=32768
   
   [syntax]
   $ exp [keyword=]{value | (value, value ...)}
      [ [ [,] keyword=]{value | (value, value ...)} ] ...
   

Keyword	Default	Meaning
USERID		Oracle username and password(If password is not specified, the user will be prompted for the password.)
BUFFER	OS specific	Size of the buffer that will be used for storing the rows fetched before they are written to the export file
COMPRESS	Y	A value of Y specifies that on import the initial extent size will be set to a value that is equal to the current size of the segment. A value of N will cause the current extent sizes to be retained. The choice has to be made at export because the information gets written to the export file. LOB segments are not compressed.
CONSISTENT	N	A value of Y specifies that the whole export operation be performed in one read-only transaction. Export will attempt to get a read consistent image of all the objects exported A value of N specifies that only table level consistency need to be maintained.
CONSTRAINTS	Y	A value of Y specifies that constraints are to be exported with the table. A value of N causes constraints not to be exported.
DIRECT	N	A value of Y specifies that direct path be used for the export. A value of N uses conventional path.
FEEDBACK	0	This parameter is specified as an integer n to request for a dot(.) to be displayed when n rows are exported
FILE	expdat.emp	Output file name
FULL	N	A value of Y specifies full database export.
GRANTS	Y	A value of Y specifies that all the grants on objects exported must also be preserved on import.
HELP	N	A value of Y will display a list of the parameters and their meaning. This parameter is not combined with other parameters.
INDEXES	Y	A value of Y causes indexes to be exported.
LOG	NULL	The name of the file to store all export messages
OWNER		The names of the users for user level export
PARFILE		Specifies the name of the file that contains a list of export parameters
RECORDLENGTH	OS specific	The size of the output record
ROWS	Y	A value of Y specifies that data is to be exported.
STATISTICS	ESTIMATE	Specifies the analyze method to be used on import
TABLES		schema.table for table mode export

5. Import 사용법 (imp scott/tiger file=/backup/orabackup.dmp full=y commit=y)

   $ imp scott/tiger tables=(dept, emp) \
   > file=emp.dmp log=imp.log ignore=y
   

Keyword	Default	Meaning
USERID		Oracle username and password(If password is not specified, user will be prompted for the password.)
BUFFER	O/S specfic	Size, in bytes, of the buffer through which data rows are transferred
COMMIT	N	A value of Y specifies that import should commit after each array insert. A value of N will no explicit commits ; an implicit commit is executed by the Oracle server when the next DDL command is executed by the import, after inserting rows for a table. Specifying COMMIT=Y prevents rollback segments from growing inordinately large.
FEEDBACK	0	This parameter is specified as an integer n to request for a dot(.) to be displayed when n rows are imported.
FILE	expdat.dmp	Input file name
FROMUSER	NULL	A list of users whose objects to import.
FULL	N	A value of Y specifies full database import.
GRANTS	Y	A value of Y specifies that all the grants on objects imported must also be imported.
HELP	N	A value of Y will display a list of the parameters and their meaning. This parameter is not combined with other parameters.
IGNORE	N	If tye value is set to Y, import overlooks object creation errors when it attempts to create database objects. In this case, import continues without reporting the error. For tables, IGNORE=Y causes rows to be imported into existing tables. No message is given. IGNORE=N causes an error to be reorted, and the table is skipped if it already exists. Note that only creation errors are ignored; other errors, such as operating system, database, and SQl errors, are not ignored and may cause processing to stop.
INDEXES	Y	A value Y causes indexes to be imported.
INDEXFILE	NULL	Specifies a file to receive index-creation commands. When this parameter is specified, index-creation commands for the requested mode are extracted and written to the specified file, rather than used to create indexes in the database. Tables and other database objects are not imported. The file can then be edited(for example, to change storage parameters) and used as a SQL script to create the indexes.
LOG	NULL	The name of the file to store all import messages.
PARFILE		Specifies the name of the file that contains a list of import parameters
PARCORDLENGTH	O/S specific	The size of the input record. This is only becessary if data was exported on an operating system with a different record size.
ROWS	Y	A value of Y specifies that data is to be imported.
SHOW	N	If the value is Y, the contents of the export file are listed to the display and not imported. The SQL statements-contained in the export are displayed in the order in which import will execute them. If SHOW=Y, the only other parameters that can be set are FROMUSER, TOUSER, FULL and TABLES.
TABLES	NULL	Names of the tables to import
TOUSER	NULL	A list of usernames to import tables. Only users with IMP_FULL_DATABASE role can use this parameter to import objects into another user's account.

---

[ Lab ]

---

1. Variable length data 의 loading

$ sqlplus system/manager
SQL> create user load identified by load
  2  default tablespace users
  3  temporary tablespace temp ;
SQL> grant connect,resource to load ;
SQL> connect load/load
SQL> @ ulcase1.sql
 

ulcase1.sql

set termout off set feedback off drop table emp; drop table dept; create table emp        (empno number(4) not null,         ename char(10),         job char(9),         mgr number(4),         hiredate date,         sal number(7,2),         comm number(7,2),         deptno number(2));   create table dept        (deptno number(2),         dname char(14) ,         loc char(13) ) ; exit

$ sqlldr load/load ulcase1.ctl
 

ulcase1.ctl

LOAD DATA INFILE * INTO TABLE DEPT  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO, DNAME, LOC) BEGINDATA 12,"RESEARCH","SARATOGA"         10,"ACCOUNTING","CLEVELAND" 11,"ART","SALEM" 13,"FINANCE","BOSTON" 21,"SALES","PHILA" 22,"SALES","ROCHESTER" 42,"INT'L","SAN FRAN"

$ sqlplus load/load
SQL> select * from dept ;

2. Fixed-format records 의 loading

$ sqlldr load/load ulcase2.ctl
 

ulcase2.ctl

LOAD DATA INFILE '$ORACLE_HOME/rdbms/demo/ulcase2.dat' append INTO TABLE EMP ( EMPNO    POSITION(01:04) INTEGER EXTERNAL,   ENAME    POSITION(06:15) CHAR,   JOB      POSITION(17:25) CHAR,   MGR      POSITION(27:30) INTEGER EXTERNAL,   SAL      POSITION(32:39) DECIMAL EXTERNAL,   COMM     POSITION(41:48) DECIMAL EXTERNAL,   DEPTNO   POSITION(50:51) INTEGER EXTERNAL)

ulcase2.dat

7782 CLARK      MANAGER   7839  2572.50          10 7839 KING       PRESIDENT       5500.00          10 7934 MILLER     CLERK     7782   920.00          10 7566 JONES      MANAGER   7839  3123.75          20 7499 ALLEN      SALESMAN  7698  1600.00   300.00 30 7654 MARTIN     SALESMAN  7698  1312.50  1400.00 30 7658 CHAN       ANALYST   7566  3450.00          20

3. ITEMS2 테이블을 생성하고, export/import utility를 이용하여 reorganizing 하십시오.
$ sqlplus system/manager
SQL> create table items2
  2  tablespace data01
  3  ad select * from items ;
SQL> insert into items2
  2  select * from items ;
SQL> !
$ exp system/manager tables=items2
$ exit
SQL> drop table items2 ;
SQL> !
$ imp system/manager tables=items2
$ exit
SQL> select * from items2 ;