create user roye identified by roye;
grant connect,resource to roye;
2、创建5000万数据的表
1.create创建集合
create table t nologging parallel 8 as select rownum x from dual connect by level<5000000;
****************************
ROYE@oggm >create table t nologging parallel 8 as select rownum x from dual connect by level<5000000;
create table t nologging parallel 8 as select rownum x from dual connect by level<5000000
*
ERROR at line 1:
ORA-30009: Not enough memory for CONNECT BY operation
***************************
2.insert采用集合写法(不到3分钟)
insert into t_insert select rownum from dual connect by level<=20000000;
**************************
3.xmltable并行不记日志
create table t nologging parallel 8 as select rownum id from xmltable('1 to 50000000');--30s
由于建立的字段属于number型，number型字段列靠右，导出前面会有空格
Table created.
Elapsed: 00:00:32.12
col SEGMENT_NAME for a20
select segment_name,bytes/1024/1024 m from user_segments where segment_name='T';
SEGMENT_NAME M
-------------------- ----------
T 604
create table t nologging parallel 8 as select rownum||'' id from xmltable('1 to 50000000');--字段是varchar2型
16:41:25 ROYE@oggm >create table t nologging parallel 8 as select rownum||'' id from xmltable('1 to 50000000');
Table created.
Elapsed: 00:00:38.91
select segment_name,bytes/1024/1024 m from user_segments where segment_name='T';
SEGMENT_NAME M
-------------------- ----------
T 754.875
--分析表
analyze table roye.t compute statistics;
col OWNER for a10
col table_name for a10
select t.owner,t.table_name,t.tablespace_name,t.num_rows,t.last_analyzed from dba_tables T where T.owner='SCOTT' and table_name='T';
OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS LAST_ANALYZED
---------- ---------- -------------------- ---------- -------------------
ROYE T USERS 50000000 2016-07-06 14:16:07
3、将表数据到出到文件中
--1.sqlplus的方式很慢
cd /home/oracle/dir
vi net.sql
set echo off
set feedback off
set newpage none
set verify off
set pagesize 0
set term off
set trims on
set linesize 600
set heading off
set timing off
set numwidth 38
SPOOL aa.txt
select id FROM roye.t;
SPOOL OFF
sqlplus / as sysdba;
conn scott/tiger;
@/home/oracle/dir/net.sql
----------------------------------------------------
[oracle@PHY-026 ~]$ ps -ef|grep p0
oracle 45799 1 0 14:26 ? 00:00:00 ora_p000_oggm
oracle 45801 1 0 14:26 ? 00:00:00 ora_p001_oggm
oracle 45803 1 0 14:26 ? 00:00:00 ora_p002_oggm
oracle 45808 1 0 14:26 ? 00:00:00 ora_p003_oggm
oracle 45810 1 0 14:26 ? 00:00:00 ora_p004_oggm
oracle 45813 1 0 14:26 ? 00:00:00 ora_p005_oggm
oracle 45815 1 0 14:26 ? 00:00:00 ora_p006_oggm
oracle 45817 1 0 14:26 ? 00:00:00 ora_p007_oggm
oracle 50590 47841 0 14:28 pts/6 00:00:00 grep p0
oracle 86857 1 0 Jun14 ? 00:07:36 ora_psp0_oggm
--查看活动会话(touse)
SET LINE 1000 PAGES 1000
col username for a20
col machine for a20
col event for a40
col MODULE for a15
select INST_ID,sid,serial#,username, machine,status,MODULE,last_call_et,event,p1,p2,p3 from gv$session
where username is not null
and status='ACTIVE'
order by 1,4,8,7,5;
----------------------------------------------------------------------------
INST_ID SID SERIAL# USERNAME MACHINE STATUS MODULE LAST_CALL_ET EVENT P1 P2 P3
---------- ---------- ---------- ---------- -------------------- -------- --------------- ------------ -------------------- ---------- ---------- --------------
1 118 31 SYS PHY-026.PRI01.SF.Bei ACTIVE sqlplus@PHY-026 0 SQL*Net message to c 1650815232 1 0
jing .PRI01.SF.Beiji lient
ng (TNS V1-V3)
1 94 4061 SYS PHY-026.PRI01.SF.Bei ACTIVE sqlplus@PHY-026 274 PX Deq Credit: send 268566527 1 10099505456
jing .PRI01.SF.Beiji blkd
ng (TNS V1-V3)
1 97 1665 SYS PHY-026.PRI01.SF.Bei ACTIVE sqlplus@PHY-026 274 PX Deq Credit: send 268566527 1 10099502704
jing .PRI01.SF.Beiji blkd
ng (TNS V1-V3)
1 100 611 SYS PHY-026.PRI01.SF.Bei ACTIVE sqlplus@PHY-026 274 PX Deq Credit: send 268566527 1 10099501328
jing .PRI01.SF.Beiji blkd
ng (TNS V1-V3)
1 106 153 SYS PHY-026.PRI01.SF.Bei ACTIVE sqlplus@PHY-026 274 PX Deq Credit: send 268566527 1 10099528160
jing .PRI01.SF.Beiji blkd
ng (TNS V1-V3)
1 109 123 SYS PHY-026.PRI01.SF.Bei ACTIVE sqlplus@PHY-026 274 PX Deq Credit: send 268566527 1 10099497784
jing .PRI01.SF.Beiji blkd
ng (TNS V1-V3)
1 112 119 SYS PHY-026.PRI01.SF.Bei ACTIVE sqlplus@PHY-026 274 PX Deq Credit: send 268566527 1 10099490904
jing .PRI01.SF.Beiji blkd
ng (TNS V1-V3)
1 91 2689 SYS PHY-026.PRI01.SF.Bei ACTIVE sqlplus@PHY-026 274 PX Deq Credit: send 268566527 1 10099499160
jing .PRI01.SF.Beiji blkd
ng (TNS V1-V3)
1 103 293 SYS PHY-026.PRI01.SF.Bei ACTIVE sqlplus@PHY-026 274 PX Deq Credit: send 268566527 1 10099500640
jing .PRI01.SF.Beiji blkd
ng (TNS V1-V3)
9 rows selected.
---------------------------------------------------------
--恢复表的并行度
alter table t parallel(degree 1);
--2.通过存储过程导出数据
cd /home/oracle/dir
mkdir roye_dir
conn / as sysdba
grant create any directory to roye;
conn roye/roye
create directory r_dir as '/home/oracle/dir/roye_dir';
DECLARE
row_result varchar2(1024);
selectsql varchar2(1024);
qrycursor SYS_REFCURSOR;
txt_handle UTL_FILE.file_type;
BEGIN
selectsql := 'select id from roye.t';
txt_handle := UTL_FILE.FOPEN('R_DIR','bb.txt','w');
open qrycursor for selectsql;
loop
fetch qrycursor into row_result;
exit when qrycursor%notfound;
UTL_FILE.PUT_LINE(txt_handle,row_result);
end loop;
--关闭游标
close qrycursor;
UTL_FILE.FCLOSE(txt_handle);
end;
/
********************
用时：15分12秒
生成文件大小
[oracle@PHY-026 ~]$ du -sh bb.txt
419M bb.txt
***************************
--3.通过sqluldr2工具来导出
./sqluldr2_linux64_10204.bin user=roye/roye query="select * from t" file="t.txt"
0 rows exported at 2016-07-07 18:21:06, size 0 MB.
1000000 rows exported at 2016-07-07 18:21:07, size 4 MB.
2000000 rows exported at 2016-07-07 18:21:08, size 12 MB.
3000000 rows exported at 2016-07-07 18:21:09, size 24 MB.
4000000 rows exported at 2016-07-07 18:21:11, size 32 MB.
5000000 rows exported at 2016-07-07 18:21:12, size 40 MB.
6000000 rows exported at 2016-07-07 18:21:14, size 48 MB.
7000000 rows exported at 2016-07-07 18:21:15, size 56 MB.
8000000 rows exported at 2016-07-07 18:21:16, size 64 MB.
9000000 rows exported at 2016-07-07 18:21:18, size 72 MB.
10000000 rows exported at 2016-07-07 18:21:19, size 80 MB.
11000000 rows exported at 2016-07-07 18:21:21, size 88 MB.
12000000 rows exported at 2016-07-07 18:21:22, size 100 MB.
13000000 rows exported at 2016-07-07 18:21:23, size 108 MB.
14000000 rows exported at 2016-07-07 18:21:25, size 116 MB.
15000000 rows exported at 2016-07-07 18:21:26, size 124 MB.
16000000 rows exported at 2016-07-07 18:21:27, size 132 MB.
17000000 rows exported at 2016-07-07 18:21:29, size 140 MB.
18000000 rows exported at 2016-07-07 18:21:30, size 148 MB.
19000000 rows exported at 2016-07-07 18:21:31, size 156 MB.
20000000 rows exported at 2016-07-07 18:21:33, size 164 MB.
21000000 rows exported at 2016-07-07 18:21:34, size 172 MB.
22000000 rows exported at 2016-07-07 18:21:35, size 180 MB.
23000000 rows exported at 2016-07-07 18:21:36, size 192 MB.
24000000 rows exported at 2016-07-07 18:21:38, size 200 MB.
25000000 rows exported at 2016-07-07 18:21:39, size 208 MB.
26000000 rows exported at 2016-07-07 18:21:40, size 216 MB.
27000000 rows exported at 2016-07-07 18:21:41, size 224 MB.
28000000 rows exported at 2016-07-07 18:21:42, size 232 MB.
29000000 rows exported at 2016-07-07 18:21:44, size 240 MB.
30000000 rows exported at 2016-07-07 18:21:45, size 248 MB.
31000000 rows exported at 2016-07-07 18:21:46, size 256 MB.
32000000 rows exported at 2016-07-07 18:21:48, size 264 MB.
33000000 rows exported at 2016-07-07 18:21:49, size 272 MB.
34000000 rows exported at 2016-07-07 18:21:50, size 280 MB.
35000000 rows exported at 2016-07-07 18:21:51, size 292 MB.
36000000 rows exported at 2016-07-07 18:21:53, size 300 MB.
37000000 rows exported at 2016-07-07 18:21:54, size 308 MB.
38000000 rows exported at 2016-07-07 18:21:55, size 316 MB.
39000000 rows exported at 2016-07-07 18:21:57, size 324 MB.
40000000 rows exported at 2016-07-07 18:21:58, size 332 MB.
41000000 rows exported at 2016-07-07 18:22:00, size 340 MB.
42000000 rows exported at 2016-07-07 18:22:01, size 348 MB.
43000000 rows exported at 2016-07-07 18:22:02, size 356 MB.
44000000 rows exported at 2016-07-07 18:22:03, size 368 MB.
45000000 rows exported at 2016-07-07 18:22:05, size 372 MB.
46000000 rows exported at 2016-07-07 18:22:06, size 384 MB.
47000000 rows exported at 2016-07-07 18:22:07, size 392 MB.
48000000 rows exported at 2016-07-07 18:22:08, size 400 MB.
49000000 rows exported at 2016-07-07 18:22:10, size 408 MB.
50000000 rows exported at 2016-07-07 18:22:11, size 416 MB.
output file t.txt closed at 50000000 rows, size 419 MB.
用时：1分04秒
大小419MB
4、建立被导入的表
--创建表
create table t_sqlldr(id number);
--创建索引
create index idx_ldr_id on t_sqlldr(id);
--查看索引是否失效
set linesize 300
col owner for a10
col index_name for a15
col table_name for a15
col INDEX_TYPE for a15
select owner,index_name,index_type,table_name,num_rows,status from dba_indexes where index_name='IDX_LDR_ID';
5、创建sqlldr控制文件
vi ldr.ctl
load data
infile 't.txt'
into table roye.t
fields terminated by ','
optionally enclosed by '"'
TRAILING NULLCOLS
(id)
6、直接路径导入数据，死循环查询数据
--非直接路径写法(索引是valid有效的)
sqlldr userid=scott/tiger control=ldr.ctl
--直接路径写法
sqlldr userid=scott/tiger control=ldr.ctl direct=true
declare
begin
while (true) loop
execute immediate 'select id from roye.t_sqlldr where id=round(DBMS_RANDOM.VALUE(1,10000))';
end loop;
end ;
/
--索引会自动失效，导入成功之后变为valid有效状态
select owner,index_name,index_type,table_name,num_rows,status from dba_indexes where index_name='IDX_LDR_ID';
OWNER INDEX_NAME INDEX_TYPE TABLE_NAME NUM_ROWS STATUS
-------------------- -------------------- -------------------- -------------------- -------- ----------
ROYE IDX_LDR_ID NORMAL T_SQLLDR 0 UNUSABLE
--查看持有锁的进程
set line 1000 pages 1000
col machine for a20
col EVENT FOR a30
col username for a10
col name for a10
SELECT C.INST_ID,c.sid,c.serial#,c.username,c.machine,b.name,C.status,c.last_call_et,c.EVENT--,c.p1,c.p2,c.p3
FROM Gv$locked_object a,sys.obj$ b,gv$session c
WHERE a.OBJECT_ID=b.obj#
AND a.session_id=c.sid
AND A.INST_ID=C.INST_ID and c.status='ACTIVE'
order by 1,4,7,9,5,6
;
INST_ID SID SERIAL# USERNAME MACHINE NAME STATUS LAST_CALL_ET EVENT
---------- ---------- ---------- ---------- -------------------- ---------- ---------------- ------------ ------------------------------
1 91 2945 ROYE PHY-026.PRI01.SF.Bei T_SQLLDR ACTIVE 0 direct path write temp
jing
SYS@oggm >/
INST_ID SID SERIAL# USERNAME MACHINE NAME STATUS LAST_CALL_ET EVENT
---------- ---------- ---------- ---------- -------------------- ---------- ---------------- ------------ ------------------------------
1 91 2945 ROYE PHY-026.PRI01.SF.Bei T_SQLLDR ACTIVE 14 direct path write
jing
--查看活动会话(touse)
SET LINE 1000 PAGES 1000
col username for a20
col machine for a20
col event for a40
col MODULE for a15
select INST_ID,sid,serial#,username, machine,status,MODULE,last_call_et,event--,p1,p2,p3
from gv$session
where username is not null
and status='ACTIVE'
order by 1,4,8,7,5;
INST_ID SID SERIAL# USERNAME MACHINE STATUS MODULE LAST_CALL_ET EVENT
---------- ---------- ---------- -------------------- -------------------- ---------------- --------------- ------------ ----------------------------------------
1 85 5087 ROYE PHY-026.PRI01.SF.Bei ACTIVE SQL*Plus 842 library cache lock
结论：sqlldr直接路径导入会导致索引失效，进而查询变成全表扫面，生成library cache lock事件
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29302187/viewspace-2121746/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29302187/viewspace-2121746/
1018
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
