[09/30] Admin 1 C 16 Admin 2 C 1

Admin 시험 준비

Admin 수업 내용 정리

control_file , redo_log file 다중화 및 archive_log 파일 경로 설정 복습

14장 요약 부분 15-5 ,6,7

첫 행 스킵 다 char size 넉넉하게

실습

===================================================================================

SYS@orcl>alter session set tracefile_identifier='ocmkorea00';

Session altered.

SYS@orcl>alter system dump datafile '/u01/app/oracle/oradata/orcl/users01.dbf' block 1;

System altered.

SYS@orcl>conn hr/hr

Connected.

HR@orcl>create table sample

2 (a number(5), b number(5), c number(5), d varchar2(10));

[oracle@lnx04 ~]$ vi sample.ctl

[oracle@lnx04 ~]$ ls

afiedt.buf dbstart.sh demodrop.sql hotback.sql monitor.sql sample.dat undo_count.sql

database demobld.sql Desktop labs sample.ctl top_sql.sql

[oracle@lnx04 ~]$ mv -v sample* labs

`sample.ctl' -> `labs/sample.ctl'

`sample.dat' -> `labs/sample.dat'

[oracle@lnx04 ~]$ cd labs/

[oracle@lnx04 labs]$ ls

sample.ctl sample.dat

[oracle@lnx04 labs]$ sqlldr hr/hr control=sample.ctl data=sample.dat

SQL*Loader: Release 11.2.0.1.0 - Production on Wed Sep 30 12:30:33 2015

Commit point reached - logical record count 64

Commit point reached - logical record count 128

Commit point reached - logical record count 192

Commit point reached - logical record count 256

Commit point reached - logical record count 320

Commit point reached - logical record count 384

Commit point reached - logical record count 448

Commit point reached - logical record count 512

Commit point reached - logical record count 576

Commit point reached - logical record count 640

Commit point reached - logical record count 704

Commit point reached - logical record count 768

Commit point reached - logical record count 832

Commit point reached - logical record count 896

Commit point reached - logical record count 960

Commit point reached - logical record count 1024

Commit point reached - logical record count 1088

Commit point reached - logical record count 1152

Commit point reached - logical record count 1216

Commit point reached - logical record count 1280

Commit point reached - logical record count 1344

Commit point reached - logical record count 1408

Commit point reached - logical record count 1472

Commit point reached - logical record count 1536

Commit point reached - logical record count 1600

Commit point reached - logical record count 1664

Commit point reached - logical record count 1704

Table created.

HR@orcl>select count(*) from sample;

COUNT(*)

----------

1704

[oracle@lnx04 labs]$ sqlldr hr/hr control=hotel.ctl data=hotel.dat

SQL*Loader: Release 11.2.0.1.0 - Production on Wed Sep 30 14:00:14 2015

http://cafe.naver.com/ocmkorea/1098

[oracle@lnx04 ~]$ mkdir -p /home/oracle/datapump

SYS@orcl>create directory dirpump as '/home/oracle/datapump';

Directory created.

SYS@orcl>grant read,write on directory dirpump to scott;

Grant succeeded.

SCOTT@orcl>create table dtest ( col number, address varchar2(20));

Table created.

SCOTT@orcl>begin

for i in 1..3000000 loop

insert into dtest values (i,'No address!!');

end loop;

commit;

end;

PL/SQL procedure successfully completed.

SCOTT@orcl>select count(*) from dtest;

COUNT(*)

----------

3000000

SCOTT@orcl>insert into dtest select * from dtest;

3000000 rows created.

SCOTT@orcl>/

6000000 rows created.

SCOTT@orcl>/

12000000 rows created.

SCOTT@orcl>select sum(bytes)/1024/1024 as MB from user_segments

2 where segment_name='DTEST';

----------

622

SCOTT@orcl>!vi expdp_check.par

userid=scott/tiger

directory=dirpump

job_name=datapump

estimate=statistics

estimate_only=y

logfile=expdp_pump.log

filesize=100M

tables=dtest

[oracle@lnx04 ~]$ expdp parfile=expdp_check.par

Export: Release 11.2.0.1.0 - Production on Wed Sep 30 15:25:06 2015

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

Starting "SCOTT"."DATAPUMP": scott/******** parfile=expdp_check.par

Estimate in progress using STATISTICS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. estimated "SCOTT"."DTEST" 5.433 KB

Total estimation using STATISTICS method: 5.433 KB

Job "SCOTT"."DATAPUMP" successfully completed at 15:25:30

[oracle@lnx04 ~]$ vi expdp_pump.par

userid=scott/tiger

directory=dirpump

job_name=datapump

logfile=expdp_pump.log

dumpfile=expdp_pump%U.dmp

filesize=100M

tables=dtest

[oracle@lnx04 ~]$ time expdp parfile=expdp_pump.par

Export: Release 11.2.0.1.0 - Production on Wed Sep 30 15:27:04 2015

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

Starting "SCOTT"."DATAPUMP": scott/******** parfile=expdp_pump.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 622 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "SCOTT"."DTEST" 518.4 MB 24000000 rows

Master table "SCOTT"."DATAPUMP" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.DATAPUMP is:

/home/oracle/datapump/expdp_pump01.dmp

/home/oracle/datapump/expdp_pump02.dmp

/home/oracle/datapump/expdp_pump03.dmp

/home/oracle/datapump/expdp_pump04.dmp

/home/oracle/datapump/expdp_pump05.dmp

/home/oracle/datapump/expdp_pump06.dmp

Job "SCOTT"."DATAPUMP" successfully completed at 15:27:36

real 0m35.141s

user 0m0.019s

sys 0m0.020s

[oracle@lnx04 datapump]$ ls -lh exp*.dmp

-rw-r----- 1 oracle oinstall 100M Sep 30 15:27 expdp_pump01.dmp

-rw-r----- 1 oracle oinstall 100M Sep 30 15:27 expdp_pump02.dmp

-rw-r----- 1 oracle oinstall 100M Sep 30 15:27 expdp_pump03.dmp

-rw-r----- 1 oracle oinstall 100M Sep 30 15:27 expdp_pump04.dmp

-rw-r----- 1 oracle oinstall 100M Sep 30 15:27 expdp_pump05.dmp

-rw-r----- 1 oracle oinstall 19M Sep 30 15:27 expdp_pump06.dmp

[oracle@lnx04 datapump]$ time exp userid=scott/tiger file =/home/oracle/datapump/noexp.dmp tables=dtest;

Export: Release 11.2.0.1.0 - Production on Wed Sep 30 15:29:12 2015

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

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table DTEST 24000000 rows exported

Export terminated successfully without warnings.

real 0m37.249s

user 0m15.484s

sys 0m1.524s

[oracle@lnx04 datapump]$ ls -lh noexp.dmp

-rw-r--r-- 1 oracle oinstall 519M Sep 30 15:29 noexp.dmp

[oracle@lnx04 ~]$ vi impdp_pump.par

userid=scott/tiger

directory=dirpump

job_name=datapump

logfile=mpdp_pump.log

dumpfile=expdp_pump%U.dmp

tables=dtest

table_exists_action=append

[oracle@lnx04 ~]$ time impdp parfile=impdp_pump.par

Import: Release 11.2.0.1.0 - Production on Wed Sep 30 15:32:50 2015

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

Master table "SCOTT"."DATAPUMP" successfully loaded/unloaded

Starting "SCOTT"."DATAPUMP": scott/******** parfile=impdp_pump.par

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39152: Table "SCOTT"."DTEST" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."DTEST" 518.4 MB 24000000 rows

Job "SCOTT"."DATAPUMP" completed with 1 error(s) at 15:35:38

real 2m53.043s

user 0m0.012s

sys 0m0.042s

[oracle@lnx04 datapump]$ time imp userid=scott/tiger file=noexp.dmp ignore=y

Import: Release 11.2.0.1.0 - Production on Wed Sep 30 15:37:19 2015

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

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 AL32UTF8 character set (possible charset conversion)

. importing SCOTT's objects into SCOTT

. . importing table "DTEST" 24000000 rows imported

Import terminated successfully without warnings.

real 3m16.045s

user 0m12.958s

sys 0m3.070s

[oracle@lnx04 backup]$ expdp system/oracle dumpfile=full.dmp directory=dump full=y job_name=lee

0 KB 0 rows

Master table "SYSTEM"."LEE" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.LEE is:

/home/oracle/backup/full.dmp

Job "SYSTEM"."LEE" successfully completed at 15:49:05

[oracle@lnx04 backup]$ impdp system/oracle directory=dump dumpfile=full.dmp schemas=scott sqlfile=ddl_scott.sql

Import: Release 11.2.0.1.0 - Production on Wed Sep 30 15:50:55 2015

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

Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/******** directory=dump dumpfile=full.dmp schemas=scott sqlfile=ddl_scott.sql

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 15:51:12

ddl_scott.sql 내용 -- 이 스크립트를 돌리면 SCOTT 스키마가 생성된다.

[oracle@lnx04 backup]$ cat import.log

;;;

Import: Release 11.2.0.1.0 - Production on Wed Sep 30 15:50:55 2015

;;;

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

Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/******** directory=dump dumpfile=full.dmp schemas=scott sqlfile=ddl_scott.sql

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 15:51:12

[oracle@lnx04 backup]$ cat ddl_scott.sql

-- CONNECT SYSTEM

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: DATABASE_EXPORT/SCHEMA/USER

CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:293C007A6F553608EB7E13C56EB6883C070A0138617C64DCA5BFB8B3BD38;F894844C34402B67'

DEFAULT TABLESPACE "USERS"

TEMPORARY TABLESPACE "TEMP";

-- new object type path: DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

GRANT UNLIMITED TABLESPACE TO "SCOTT";

-- new object type path: DATABASE_EXPORT/SCHEMA/ROLE_GRANT

GRANT "CONNECT" TO "SCOTT";

GRANT "RESOURCE" TO "SCOTT";

-- new object type path: DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

ALTER USER "SCOTT" DEFAULT ROLE ALL;

-- new object type path: DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

-- CONNECT SCOTT

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'ORCL', inst_scn=>'2837932');

COMMIT;

END;

-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/TABLE

-- CONNECT SYSTEM

CREATE TABLE "SCOTT"."DEPT"

( "DEPTNO" NUMBER(2,0),

"DNAME" VARCHAR2(14 BYTE),

"LOC" VARCHAR2(13 BYTE)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" ;

CREATE TABLE "SCOTT"."EMP"

( "EMPNO" NUMBER(4,0),

"ENAME" VARCHAR2(10 BYTE),

"JOB" VARCHAR2(9 BYTE),

"MGR" NUMBER(4,0),

"HIREDATE" DATE,

"SAL" NUMBER(7,2),

"COMM" NUMBER(7,2),

"DEPTNO" NUMBER(2,0)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" ;

CREATE TABLE "SCOTT"."BONUS"

( "ENAME" VARCHAR2(10 BYTE),

"JOB" VARCHAR2(9 BYTE),

"SAL" NUMBER,

"COMM" NUMBER

) SEGMENT CREATION DEFERRED

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

TABLESPACE "USERS" ;

CREATE TABLE "SCOTT"."SALGRADE"

( "GRADE" NUMBER,

"LOSAL" NUMBER,

"HISAL" NUMBER

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" ;

CREATE TABLE "SCOTT"."DTEST"

( "COL" NUMBER,

"ADDRESS" VARCHAR2(20 BYTE)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" ;

-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

-- CONNECT SCOTT

CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")

PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" PARALLEL 1 ;

ALTER INDEX "SCOTT"."PK_DEPT" NOPARALLEL;

CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")

PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" PARALLEL 1 ;

ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL;

-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

-- CONNECT SYSTEM

ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" ENABLE;

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS" ENABLE;

-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

DECLARE I_N VARCHAR2(60);

I_O VARCHAR2(60);

c DBMS_METADATA.T_VAR_COLL;

df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

BEGIN

DELETE FROM "SYS"."IMPDP_STATS";

i_n := 'PK_DEPT';

i_o := 'SCOTT';

INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES ('I',5,2,I_N,NULL,NULL,I_O,4,1,4,1,1,1,0,4,NULL,NULL,NULL,NULL,TO_DATE('2015-09-16 22:41:21',df),NULL);

DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');

DELETE FROM "SYS"."IMPDP_STATS";

END;

DECLARE I_N VARCHAR2(60);

I_O VARCHAR2(60);

c DBMS_METADATA.T_VAR_COLL;

df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

BEGIN

DELETE FROM "SYS"."IMPDP_STATS";

i_n := 'PK_EMP';

i_o := 'SCOTT';

INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,d1,cl1) VALUES ('I',5,2,I_N,NULL,NULL,I_O,14,1,14,1,1,1,0,14,NULL,NULL,NULL,NULL,TO_DATE('2015-09-16 22:41:22',df),NULL);

DBMS_STATS.IMPORT_INDEX_STATS('"' || i_o || '"','"' || i_n || '"',NULL,'"IMPDP_STATS"',NULL,'"SYS"');

DELETE FROM "SYS"."IMPDP_STATS";

END;

-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;

-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

DECLARE

c varchar2(60);

nv varchar2(1);

df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

s varchar2(60) := 'SCOTT';

t varchar2(60) := 'DEPT';

p varchar2(1);

sp varchar2(1);

stmt varchar2(300) := 'INSERT INTO "SYS"."IMPDP_STATS" (type,version,c1,c2,c3,c4,c5,n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,d1,r1,r2,ch1,flags) VALUES (''C'',5,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21)';

BEGIN

DELETE FROM "SYS"."IMPDP_STATS";

INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n10,n11,n12,d1) VALUES ('T',5,2,t,p,sp,s,

4,5,20,4,NULL,NULL,NULL,

TO_DATE('2015-09-16 22:41:21',df));

c := 'DEPTNO';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

4,.25,4,4,0,10,40,3,nv,nv,nv,

TO_DATE('2015-09-16 22:41:21',df),'C10B','C129',nv,2;

c := 'DNAME';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

4,.25,4,4,0,3.38863550087541E+35,4.32285038677786E+35,10,nv,nv,nv,

TO_DATE('2015-09-16 22:41:21',df),'4143434F554E54494E47','53414C4553',nv,2;

c := 'LOC';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

4,.25,4,4,0,3.44300505052090E+35,4.06405544089997E+35,8,nv,nv,nv,

TO_DATE('2015-09-16 22:41:21',df),'424F53544F4E','4E455720594F524B',nv,2;

DBMS_STATS.IMPORT_TABLE_STATS('"SCOTT"','"DEPT"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');

DELETE FROM "SYS"."IMPDP_STATS";

END;

DECLARE

c varchar2(60);

nv varchar2(1);

df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

s varchar2(60) := 'SCOTT';

t varchar2(60) := 'EMP';

p varchar2(1);

sp varchar2(1);

BEGIN

DELETE FROM "SYS"."IMPDP_STATS";

INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n10,n11,n12,d1) VALUES ('T',5,2,t,p,sp,s,

14,5,38,14,NULL,NULL,NULL,

TO_DATE('2015-09-16 22:41:22',df));

c := 'EMPNO';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

14,.0714285714285714,14,14,0,7369,7934,4,nv,nv,nv,

TO_DATE('2015-09-16 22:41:22',df),'C24A46','C25023',nv,2;

c := 'ENAME';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

14,.0714285714285714,14,14,0,3.38883673419062E+35,4.53054701071074E+35,6,nv,nv,nv,

TO_DATE('2015-09-16 22:41:22',df),'4144414D53','57415244',nv,2;

c := 'JOB';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

5,.2,5,14,0,3.39086497213261E+35,4.32285038678150E+35,8,nv,nv,nv,

TO_DATE('2015-09-16 22:41:22',df),'414E414C595354','53414C45534D414E',nv,2;

c := 'MGR';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

6,.166666666666667,6,13,1,7566,7902,4,nv,nv,nv,

TO_DATE('2015-09-16 22:41:22',df),'C24C43','C25003',nv,2;

END;

DECLARE

c varchar2(60);

nv varchar2(1);

df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

s varchar2(60) := 'SCOTT';

t varchar2(60) := 'EMP';

p varchar2(1);

sp varchar2(1);

BEGIN

NULL;

c := 'HIREDATE';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

13,.0769230769230769,13,14,0,2444591,2446939,8,nv,nv,nv,

TO_DATE('2015-09-16 22:41:22',df),'77B40C11010101','77BB0517010101',nv,2;

c := 'SAL';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

12,.0833333333333333,12,14,0,1,5000,4,nv,nv,nv,

TO_DATE('2015-09-16 22:41:22',df),'C102','C233',nv,2;

c := 'COMM';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

4,.25,4,4,10,0,1400,2,nv,nv,nv,

TO_DATE('2015-09-16 22:41:22',df),'80','C20F',nv,2;

c := 'DEPTNO';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

3,.0357142857142857,3,14,0,10,30,3,1,3,10,

TO_DATE('2015-09-16 22:41:22',df),'C10B','C11F',nv,2;

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

3,.0357142857142857,3,14,0,10,30,3,1,8,20,

TO_DATE('2015-09-16 22:41:22',df),'C10B','C11F',nv,2;

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

3,.0357142857142857,3,14,0,10,30,3,1,14,30,

TO_DATE('2015-09-16 22:41:22',df),'C10B','C11F',nv,2;

DBMS_STATS.IMPORT_TABLE_STATS('"SCOTT"','"EMP"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');

DELETE FROM "SYS"."IMPDP_STATS";

END;

DECLARE

c varchar2(60);

nv varchar2(1);

df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

s varchar2(60) := 'SCOTT';

t varchar2(60) := 'BONUS';

p varchar2(1);

sp varchar2(1);

BEGIN

DELETE FROM "SYS"."IMPDP_STATS";

INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n10,n11,n12,d1) VALUES ('T',5,2,t,p,sp,s,

0,0,0,0,NULL,NULL,NULL,

TO_DATE('2015-09-16 22:41:09',df));

c := 'ENAME';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

0,0,0,nv,0,0,0,0,nv,nv,nv,

TO_DATE('2015-09-16 22:41:09',df),nv,nv,nv,2;

c := 'JOB';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

0,0,0,nv,0,0,0,0,nv,nv,nv,

TO_DATE('2015-09-16 22:41:09',df),nv,nv,nv,2;

c := 'SAL';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

0,0,0,nv,0,0,0,0,nv,nv,nv,

TO_DATE('2015-09-16 22:41:09',df),nv,nv,nv,2;

c := 'COMM';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

0,0,0,nv,0,0,0,0,nv,nv,nv,

TO_DATE('2015-09-16 22:41:09',df),nv,nv,nv,2;

DBMS_STATS.IMPORT_TABLE_STATS('"SCOTT"','"BONUS"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');

DELETE FROM "SYS"."IMPDP_STATS";

END;

DECLARE

c varchar2(60);

nv varchar2(1);

df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS';

s varchar2(60) := 'SCOTT';

t varchar2(60) := 'SALGRADE';

p varchar2(1);

sp varchar2(1);

BEGIN

DELETE FROM "SYS"."IMPDP_STATS";

INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2,n3,n4,n10,n11,n12,d1) VALUES ('T',5,2,t,p,sp,s,

5,5,10,5,NULL,NULL,NULL,

TO_DATE('2015-09-16 22:41:22',df));

c := 'GRADE';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

5,.2,5,5,0,1,5,3,nv,nv,nv,

TO_DATE('2015-09-16 22:41:22',df),'C102','C106',nv,2;

c := 'LOSAL';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

5,.2,5,5,0,700,3001,4,nv,nv,nv,

TO_DATE('2015-09-16 22:41:22',df),'C208','C21F02',nv,2;

c := 'HISAL';

EXECUTE IMMEDIATE stmt USING t,p,sp,c,s,

5,.2,5,5,0,1200,9999,4,nv,nv,nv,

TO_DATE('2015-09-16 22:41:22',df),'C20D','C26464',nv,2;

DBMS_STATS.IMPORT_TABLE_STATS('"SCOTT"','"SALGRADE"',NULL,'"IMPDP_STATS"',NULL,NULL,'"SYS"');

DELETE FROM "SYS"."IMPDP_STATS";

END;

SCOTT@orcl>alter table emp

2 drop constraint fk_deptno;

Table altered.

SCOTT@orcl>alter table emp

2 add constraint fk_dept foreign key(deptno)

3 references dept(deptno) on delete cascade;

Table altered.

SCOTT@orcl>delete from dept;

4 rows deleted.

SCOTT@orcl>select * from dept;

no rows selected

SCOTT@orcl>select * from emp;

no rows selected

SCOTT@orcl>commit;

Commit complete.

[oracle@lnx04 backup]$ impdp system/oracle dumpfile=full.dmp directory=dump content=data_only job_name=data_import logfile=table_log tables=scott.dept,scott.emp

Import: Release 11.2.0.1.0 - Production on Wed Sep 30 16:01:14 2015

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

Master table "SYSTEM"."DATA_IMPORT" successfully loaded/unloaded

Starting "SYSTEM"."DATA_IMPORT": system/******** dumpfile=full.dmp directory=dump content=data_only job_name=data_import logfile=table_log tables=scott.dept,scott.emp

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . imported "SCOTT"."DEPT" 5.937 KB 4 rows

. . imported "SCOTT"."EMP" 8.570 KB 14 rows

Job "SYSTEM"."DATA_IMPORT" successfully completed at 16:01:33

[oracle@lnx04 backup]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 30 16:02:12 2015

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

SCOTT@orcl>select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 1 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1 10

14 rows selected.

SCOTT@orcl>select * from dept;

DEPTNO DNAME LOC

---------- -------------- -------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SYS@orcl>drop user scott cascade;

User dropped.

[oracle@lnx04 ~]$ impdp system/oracle dumpfile=full.dmp directory=dump job_name=data_import logfile=table_log schemas=scott

Import: Release 11.2.0.1.0 - Production on Wed Sep 30 16:04:19 2015

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

Master table "SYSTEM"."DATA_IMPORT" successfully loaded/unloaded

Starting "SYSTEM"."DATA_IMPORT": system/******** dumpfile=full.dmp directory=dump job_name=data_import logfile=table_log schemas=scott

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

. . imported "SCOTT"."DTEST" 518.4 MB 24000000 rows

. . imported "SCOTT"."DEPT" 5.937 KB 4 rows

. . imported "SCOTT"."EMP" 8.570 KB 14 rows

. . imported "SCOTT"."SALGRADE" 5.867 KB 5 rows

. . imported "SCOTT"."BONUS" 0 KB 0 rows

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."DATA_IMPORT" successfully completed at 16:07:31

[oracle@lnx04 ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 30 16:08:18 2015

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

SCOTT@orcl>set lines 150

SCOTT@orcl>select * from tab;

TNAME TABTYPE CLUSTERID

------------------------------ ------- ----------

BONUS TABLE

DEPT TABLE

DTEST TABLE

EMP TABLE

SALGRADE TABLE

SCOTT@orcl>select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 1 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1 10

14 rows selected.

저작자표시

'스터디북' 카테고리의 다른 글

[10/02] Admin2 C3-1 (0)	2015.10.02
[10/01] admin1 test admin c2 (0)	2015.10.01
[09/23] Expert Oracle Database 16장 데이터 암호화 (0)	2015.09.23
[09/22] Expert Oracle Database 5장 오라클 프로세스 (0)	2015.09.23
[09/09] admin 4일차 실습 (0)	2015.09.10

선발투수

[09/30] Admin 1 C 16 Admin 2 C 1

'스터디북' 카테고리의 다른 글

티스토리툴바

[09/30] Admin 1 C 16 Admin 2 C 1

'스터디북' 카테고리의 다른 글

관련글

티스토리툴바