创建大数据表索引

首先感谢：潇湘隐者 https://www.cnblogs.com/kerrycode/p/5673224.html
1.查询临时表空间大小
SQL> col file_name for a30;
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 file_size,autoextensible from dba_temp_files;

TABLESPACE_NAME      FILE_NAME			     FILE_SIZE AUT
-------------------- ------------------------------ ---------- ---
TEMP		     /oracle/oradata/yuanzj/temp01.dbf	 32 YES

2.创建索引
SQL> create index YUANZJ.STATIC_YZJ_AUTO_SN_COPY1 on YUANZJ.STATIC_YZJ_ALL (AUTO_SN)
  tablespace INDEX_YUANZJ
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 18M
    next 2M
    minextents 1
    maxextents unlimited
  )
  parallel 2;
*
第 1 行出现错误:
ORA-12801: 并行查询服务器 P000 中发出错误信号 ORA-01652:
无法通过 128 (在表空间 TEMP 中) 扩展 temp 段

3.在测试库还原表创建索引
3.1登录数据库查看索引状态
SQL> select 
  index_name,
  table_name,
  index_type,
  status,
  tablespace_name
from dba_indexes 
where table_name=upper('static_yzj_all')
order by table_name,index_name;

INDEX_NAME		          TABLE_NAME	       INDEX_TYPE	        STATUS   TABLESPACE_NAME
------------------------- -------------------- -------------------- -------- ------------------------------
STATIC_YZJ_AUTO_SN_COPY1  STATIC_YZJ_ALL       NORMAL		        VALID    YUANZJ

3.2查看索引的大小
SQL> col SEGMENT_NAME for a30;
SQL> select segment_name,
  bytes/1024/1024/1024  indexsizem
  from dba_segments
  where segment_name =('STATIC_YZJ_AUTO_SN_COPY1');

SEGMENT_NAME		       INDEXSIZEM
------------------------------ ----------
STATIC_YZJ_AUTO_SN_COPY1       39.3491821

   
4.在正式库上添加temp数据文件
2.增加临时表空间数据文件
alter  tablespace temp add tempfile '/oracle/oradata/yuanzj/temp02.dbf' size 10g  AUTOEXTEND ON NEXT 10M maxsize 30g;
   
   
5.查看临时表空间大小
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 file_size,autoextensible from dba_temp_files;

TABLESPACE_NAME      FILE_NAME			     FILE_SIZE AUT
-------------------- ------------------------------ ---------- ---
TEMP		     /oracle/oradata/yuanzj/temp01.dbf	 32 YES   
TEMP		     /oracle/oradata/yuanzj/temp02.dbf	 30 NO   
   
6.在正式库上创建索引
SQL> create index YUANZJ.STATIC_YZJ_AUTO_SN_COPY1 on YUANZJ.STATIC_YZJ_ALL (AUTO_SN)
  tablespace INDEX_yuanzj
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 18M
    next 2M
    minextents 1
    maxextents unlimited
  )
  parallel 2;

7.重新打开另外一个窗口，查看当前活动会话
SQL> select s.username,
       s.sid,
       s.serial#,
       s.inst_id,
       s.event,
       s.wait_class,
       s.sql_exec_start,
       s.logon_time,
       s.action,
       sq.sql_text
from gv$session s, gv$sqlarea sq
where s.status = 'ACTIVE'
and s.username is not null
and s.sql_id = sq.sql_id; 

USERNAME   SID       SERIAL#	INST_ID   EVENT               WAIT_CLASS	 SQL_EXEC_START    LOGON_TIME ACTION  SQL_TEXT
--------- ---------- ---------- --------------------------------------------------------------------------------------------------------------
SYS		   403       13483	      1       PX Deq: Execute      Reply         Idle			  05-JUN-20 05-JUN-20    
create index YUANZJ.STATIC_YZJ_AUTO_SN_COPY1 on YUANZJ.STATIC_YZJ_ALL (AUTO_SN)   tablespace INDEX_yuanzj   pctfree 10
  initrans 2   maxtrans 255   storage	(     initial 18M     next 2M	  minextents 1	   maxextents unlimited   )   pa
rallel 2

SYS		   412      32771	      1      direct path write      User I/O					  05-JUN-20 05-JUN-20
create index YUANZJ.STATIC_YZJ_AUTO_SN_COPY1 on YUANZJ.STATIC_YZJ_ALL (AUTO_SN)   tablespace INDEX_yuanzj   pctfree 10
  initrans 2   maxtrans 255   storage	(     initial 18M     next 2M	  minextents 1	   maxextents unlimited   )   pa
rallel 2

SYS		  795      30271	      1      direct path write       User I/O					  05-JUN-20 05-JUN-20
create index YUANZJ.STATIC_YZJ_AUTO_SN_COPY1 on YUANZJ.STATIC_YZJ_ALL (AUTO_SN)   tablespace INDEX_yuanzj   pctfree 10
  initrans 2   maxtrans 255   storage	(     initial 18M     next 2M	  minextents 1	   maxextents unlimited   )   pa
rallel 2

SYS		 1158      63725	      1      SQL*Net message to client      Network 			  05-JUN-20 05-JUN-20

select s.username,	  s.sid,	s.serial#,	  s.inst_id,	    s.event,	    s.wait_class,	 s.sql_e
xec_start,	  s.logon_time,        s.action,	sq.sql_text from gv$session s, gv$sqlarea sq where s.status = 'A
CTIVE' and s.username is not null and s.sql_id = sq.sql_id

   
8. 创建索引(如果加索引的话，不建议使用sid看)
8.1通过sid查看当前创建索引事件
col opname format a32
col target format a32
col perwork format a12
set linesize 1200
select 
  sid,
  opname,
  target,
  sofar,
  totalwork,
  trunc(sofar/totalwork*100,2)||'%' as perwork
from v$session_longops where sofar!=totalwork and sid=&sid;

8.2如果你根本不知道会话信息，如果我只知道是在那个表上重建索引，也可以根据表名来查询，如下所示，我们还增加了开始时间等字段
col opname format a32
col target format a32
col start_time format a24
col elapsed_seconds format 99
col perwork format a12
select sid
        ,opname
        ,target
        ,start_time
       ,elapsed_seconds
        ,sofar
        ,totalwork
        ,trunc(sofar/totalwork*100,2)||'%' as perwork
from v$session_longops where sofar!=totalwork and target like ⌖
Enter value for target:  '%YUANZJ.STATIC_YZJ_ALL%'
old   9: from v$session_longops where sofar!=totalwork and target like &target
new   9: from v$session_longops where sofar!=totalwork and target like  '%TEST_INDX%'
 
    SID            OPNAME            TARGET                   START_TIME           ELAPSED_SECONDS    SOFAR  TOTALWORK PERWORK
---------- -------------------- --------------------------- --------------------- ----------------- ---------- ---------- -----
    412    Index Fast Full Scan   YUANZJ.STATIC_YZJ_ALL     2016-07-13 23:47:57         30          99732     157907 63.15%
    795    Index Fast Full Scan   YUANZJ.STATIC_YZJ_ALL     2016-07-13 23:47:57         30          99732     157907 64.15%
   
9.关闭索引并行度
alter index YUANZJ.STATIC_YZJ_AUTO_SN_COPY1 noparallel;