운영환경에서 테이블의 Range 파티션 추가 절차 -1

[ Procedure for adding range partitions in production environment ] DBA 들은 12월이 되면 파티션 테이블에 대해 다음년도의 파티션 추가 작업을 하게 되는데, 파티션 테이블들은 대용량 테이블일 경우가 많고, 쿼리에 자주 사용되는 메인 테이블일 경우가 많아, 연말 파티션 추가 작업시 여러가지 고려해야 할 사항들이 많다.
아마 가장 큰 이슈는 지금 추가 할 파티션이, 3~4년 후 진행될 파티션 drop 작업 후, 그 테이블스페이스가 차지하고 있던 공간을 회수해야 하는 이슈가 있거나(디스크 용량 관리 차원), 2017년 12월에 신규 파티션이 추가됨으로 인해서, 2018년 1월 1일부터는 2018년 1월 파티션 데이터를 조회하게 될때, 빈 파티션 통계정보로 인해 SQL PLAN이 변경되어 그로인한 서비스 장애 이슈가 핵심일 것이다.
먼저 파티션 추가 작업의 예이다.

파티션 키 정보 확인

SELECT A.OWNER, TABLE_NAME, A.COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM DBA_TAB_COLUMNS A,
    (
    select OWNER,NAME,COLUMN_NAME from DBA_PART_KEY_COLUMNS
    WHERE (OWNER,NAME )IN (
         select OWNER,TABLE_NAME from dba_part_tables
         where owner not in ('SYS', 'SYSTEM')
        )
    ) B
WHERE A.OWNER=B.OWNER
AND   A.TABLE_NAME= B.NAME
AND  A.COLUMN_NAME=B.COLUMN_NAME
ORDER BY A.OWNER, TABLE_NAME, A.COLUMN_NAME  ;
 
 
OWNER      TABLE_NAME        COLUMN_NAME      DATA_TYPE     DATA_LENGTH
------     ----------        -----------      ---------     ----------
SCOTT      TB_ORDER          OR_DATE          VARCHAR2       8
SCOTT      TB_SALE           SALE_DATE        VARCHAR2       8
SCOTT      TB_SUM            SUM_ID           NUMBER         22

그외 체크해야 할 뷰
SELECT * FROM DBA_PART_INDEXES ;
SELECT * FROM DBA_PART_TABLES ;
SELECT * FROM DBA_TAB_PARTITIONS ;
SELECT * FROM DBA_IND_PARTITIONS ;

작업 순서

1) MAX 파티션에 데이터가 있는지 체크
   SELECT COUNT(*) FROM SCOTT.TB_SALE PARTITION (P_MAX) ;

2) MAX 파티션에 데이터가 있으면 데이터를 임시 테이블에 저장한 후 MAX 파티션을 DROP
   alter table SCOTT.TB_SALE drop partition P_MAX;
 
3) 새로운 파티션이 저장될 TABLESPACE를 생성
   (만약 대용량 테이블이라면 각 파티션의 테이블스페이스를 특정한 기준별로 분리해준다.
    그러면 나중에 과거 파티션을 삭제하게 될 때, 해당 테이블스페이스만 DROP 할수 있어, 쉽게 공간을 회수할 수 있다) 
   create tablespace TBS_SALE_2018_DAT datafile '/data1/KDB/TBS_SALE_2018_DAT_01.DBF' size 1G autoextend on next 500m ;
   create tablespace TBS_SALE_2018_IDX datafile '/data1/KDB/TBS_SALE_2018_IDX_01.DBF' size 1G autoextend on next 500m ;
 
4) 새로 생성될 파티션이 저장될 곳을 지정한다. 
    (파티션 테이블의 테이블스페이스 속성변경 )
    alter table SCOTT.TB_SALE MODIFY DEFAULT ATTRIBUTES TABLESPACE TBS_SALE_2018_DAT;
    이 부분은 아래 파티션 ADD 스크립트처럼 TABLESPACE 명을 직접 스크립트에 명시해도 된다. 

5)  파티션 인덱스는 아래처럼 인덱스의 각 파티션이 저장될 곳을 지정한다 
    (파티션 인덱스의 테이블스페이스 속성변경 )
    alter index SCOTT.TB_SALE_IX01 MODIFY DEFAULT ATTRIBUTES TABLESPACE TBS_SALE_2018_IDX;
    alter index SCOTT.TB_SALE_IX02 MODIFY DEFAULT ATTRIBUTES TABLESPACE TBS_SALE_2018_IDX;
    alter index SCOTT.TB_SALE_IX03 MODIFY DEFAULT ATTRIBUTES TABLESPACE TBS_SALE_2018_IDX;

6)  파티션을 추가한다.
 
    alter table SCOTT.TB_SALE add partition P_201801 values less than ('20180201') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_201802 values less than ('20180301') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_201803 values less than ('20180401') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_201804 values less than ('20180501') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_201805 values less than ('20180601') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_201806 values less than ('20180701') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_201807 values less than ('20180801') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_201808 values less than ('20180901') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_201809 values less than ('20181001') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_201810 values less than ('20181101') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_201811 values less than ('20181201') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_201812 values less than ('20190101') tablespace TBS_SALE_2018_DAT;
    alter table SCOTT.TB_SALE add partition P_MAX    values less than (MAXVALUE)   tablespace TBS_SALE_2018_DAT;

 

Zamong is free  oracle monitoring software with DMA method.

dma_picture

How to display the content of LOB column in SQL*PLUS

dbms_lob.getlength() function: Return the length of the LOB value
dbms_lob.read() procedures : Read data from the LOB

SQL>  select orderid ,  dbms_lob.getlength( enc_data)  from tb_order where rownum < 6  ;

orderid                dbms_lob.getlength( enc_data)
-------------------- -----------------------------
2017120100069138                              4332
2017120100069139                              4544
2017120100069140                              4140
2017120100069142                              4396
2017120100069145                              4672

SQL> set serveroutput on
SQL> 
SQL> DECLARE
  2   clob_data    clob ;
  3   out_buffer   varchar2(30001)  ;
  4   read_count   integer   ;
  5  BEGIN
  6
  7    read_count := 30000 ;
  8   
  9    SELECT  enc_data  INTO clob_data FROM tb_order  where orderid = '2017120100069138' ;
 10
 11    dbms_lob.read( clob_data, read_count, 1, out_buffer )  ;   -- 1 : from first character
 12    dbms_output.put_line( read_count) ;
 13    dbms_output.put_line( out_buffer) ;
 14    
 15  END;
 16  /

4332
02yuVfDFpIHtvBCFe50OYoWZsWa1aFN6EATUhKh4j/WeWB8rHvucazhOGLgOZWdkc/hDm7d62YqW6VvY
Hoa93KGX4Gwamz1apsaODaMT/+4F4H6Fy9zGZ1cEm6bd7GhdrAFhIhx6SnzgPR2sIV7mPpFSk4VQbpif
tHk/9BGBorIwO4OAASPoTXRxfnbh/scDKTnD8ejF9WhkrjyeNQF7u2pVv57A20PNnEXqg/Uwu6fiDA8n
shHnkcVJq37+FGsWHnH4XC90AtRB6iYvchFjLaK3Ss02BJYLZG9m4NLyKxSsPbY+MbtwkgjswKd9hAOx
4V1ITUwrdBCtAt6Zk0aiDswjTvYCy9KsTKj+QtDs7A46WnJewdNDUmOto7EcYrGW5r67X9KaN66JWm9H
6BJ6fpnUDfY8tiMID+glmhady00GZsQoidTq4NomVkFmGIyZh8Vngc1PO/2AeV1K7m7pOAIQHOL1vSta
Gx4/kJhS7hDIxGzE30RG4KLowihdBPvVj1hk9A4DOZok7fR48xcN0dtASQxAu9/jVkvc+T1bc5ahUkKx
PjTU40yhc9Ieprk7DkAJQ1ezHH2xSX8eZ72q7Osw0zyQCMoPUCzG8YDgToP6hYJ3KuLG41mRk03v0siv
WcGvILj3yDVi9rAzdyV6Xqg2cQxS5xjwQr8wKs4Zuz9vTeXSEDp3fFirITnR7PkaEXyfYfpCXrYqrH7d
1rAkjeftY+D6XjIEiZS1axm9NZJjS4ef2hXusaTbqeBIkHWNP7BiYWfLPb2Kb82O2Dgq/3qWY2qRVruN
wfY0TViaylKwW2KVulLzQjnLn/AD5pmmelIquo3vB7CmejYh5hUt8ZychYZJW27hoYRMRE/PCHCa+58p
0/ZKAGsUFQQbtlyp2Pjk5gEje1Wx2j3d7CUQGgg4kKA513RLVHN6d3sMXAb6gzisya9pKrlI0D3UFPuv
2wDIdndWu5zbEnu/zOwUMtGVkYmHu00mGWal1M/y6SvYeOdK4xs0gj3MU9FirXtjMbQpvLZyF9pvRBU9
NJCctgpEdNjAgZoOZpDMGU6cdfHSoqDs5bk4o8bOXnnjeNZgBF1rd+RipObDUEmszHe+ibhw4w9A7cGa
fQMPhRrXdNiSLRLx5PW/yVTk6wlKeZP17f7YWNn+WytF3FAHIWiLaFMdx8iRUrJSPenl07LGSnILf4cf
mOVDEGuvjxxggmXDLj1niQA6Iwhk9gGmnLepXaUpPOEB1kcE4R36f38ptC3SAXvfZ4uRerF6KuLa7Pw/
eFy9HFxlqZeSyUiFj0FJB6GljpSixBIyPI5IswJmJsQx/g/bUiFa9mPHoDwVXPzLv7D/owawqo6O3Y2v
Bt/yKzk5H48ABuQIqoYL29HfBbckzLgzb41qF7Ej3Zy3+spFllkAZLcKkxZGV79N8goeZMz+TVprhjlo
RTszvG3Ct0/mARBPJ6Www4j7DiILhYHVnxSfQYFkHtref0DqHhiPgxzvRRKHmXjbPk2RzRLlfYXp39Ah
pcXkGmXj7cQvpWJ9hDqpqTvjmlUv1u1Pd4cePBL+YH9LaFOUD+SlOFUTzmSUiy2CHBzP6PHUNSSYF8YL
aNU5R0aPANlkFdkPUdP5Qfk6F4VDJx7Ep7KDcTGlT1Yz6u8VuWoEPKyi5vQZl7HxyvN+6NGE19P2I+Xd
VbXwGp7QhSdbhrCLlyLnDHt1OzZh6nebJ/fumrn+3TtwyLgJ/1uAQXzrQqY5PzPHw00OoBPtStTafXjW
8afCfHV566KJNZe7LscVkdBUVU5wURfCBDdTmSyycNX1FCHdurU7wyYa42nC8pRjz071xxqRM5UiieWK
8zzThltAgR/3IDyGsXEhmy3NJy9TAChkUITB0dQGx15fIC8mXvDWshtNlZA6Q2f1C9utPxk8hl86JKOh
KAgW/04dNuzI6BTnP3lA2jEFv52qdjE4YGee/CQqiM/qiM4yDaV/5jxhh39acA748uhjVEpxnXg9+hNH
6il6BJXjjhi4BSrL1i1e4CMCq1FO95GAc4QECuIBqccddDPlXpGD5hPiiEoZRSmxc6PqzdVtkmGllY4O
x1nHmN9BhczgJNdnyANcWWglZ2RaG2yOJoMwnQyLgRWEuPKizu5pNPJxr+auApqFJnEdJMlZrxQvFjV0
WqWGhxqIGxuiNzJ0dNMNelQM88pzem0yG0JwtSWbEso26qpC2RVawP3kVTRAizNotVuZlaUVQ4rtd+qH
ePnSVCyZEIV3w5BELdHPS4X7v7KHEemHT1/E4SnxOeCjS/Hx0aTkAtdTA2r/yUq1zC6nNryoWpOJTjMi
b3TG5mWv82Whk21MGB2tgYWWLEdvr3HyJz5aOEJVH1H65fnp/JqnWGvbdH/ACMd2/8l/YIWgjIpgOgBD
tTzbv7OV/rnwlITCPYJdmvZdvrEzJJy7/JBA21m/UzvW+o1Xkd4YBRYrmgaoFG0EGpAND+X+rZi2xk1z
RwP1vfgoNChIIzXfDheQVUi3Cm3m1LB6V/moYtuRzXoDmOxqNLQKaML+A/GohSKFaSFTy4WIzdU9CnEq
GVvE90itHu0kbPIbfWCYyL7tVZ580HoCMpJlNcU+PMQpdJa7kfpeagChSva6oJheagW86NFzF96x70DS
2UUJOH+ekhHCIC49J6+eNOEPXbUUs1CVU3lQw3gEjgjKemtzOZ9+ig34mvBcU+vSwWnb6SwyhTs48U2I
CLHEiwCaiYk/r8+WSnF90bq8R7oRQSINyJ1OXKzQJPpofwY7inYtSdOA5vlyfEoVNUKbSdHjQykloBsP
BB9W3r1RffuVdrrrP3AENPRMpnsTk1Y5gY78OeOHvs+pEOpJ+tnKsgonNtQacJGt3KgfJTxBNCSDdS9W
0TQEWJbwAx7Cx+dsKziJ60Yd+w5/u2SjoyFzacEnntpGLClYQlUZAsLsanSLk8ooUefw1U1rS5deEtTc
YU5HEsvq0PgC044Lf7ZxPhLGf2V+YbvyEuN/cKgWcOrtytESC4rjhz94qzvuo3Rzca1lUxhJJfL1Dk3B
b/ufp6OaL6jKxzTHoXQnxtWFk/nw3wrL0x9Z/4bH8tFHaC+b0uUCRBA+cipa4G5CSdFUFvsRrVaX595T
QgMYz4x1Z4Z8rPZLCRJnV2qoYajC8CiBDqqvh6f8MUZMFdy4bf2GgPvipx+EmtzROOkEyG1JSqvofJ3z
YueaZ0aAduU8t6PwxsfLUMrNnEht1/iJBjIYTobJuEdEWNa+Y7LApOCug9MQajUK54ZtcU0u6iZ6qpFD
2n0ryJ0m1TqkbEORQOzTT9ct60rXmRQ1NasnN9vn3wyp6gN6MYiGBN/ro7SRbVUuLGdmTyNbc+15F1Kv
IgqjozBxAXzfDipVCWXFCPH8gRRbQ0u88dxcuwcsY7YDEY3TQbCGWdQH2u1s0IMTe3v/dN01nEUxiIYE
3+ujtJFtVS4sZ2ZPmVedBK6AkLxlNEWbN+O57KAqSdDPO+TVHb16SQ8ZimvMCYzO+BipmTf+093j87UY
S3J/gG3Zax+M9hYxFj5HYR5RwYJzABac1vrg0yf7yz2AMs0Abq+lWHLBm0Lw8YTmHbzFC+z+S5V51omw
+4k85Zc1ENB8t9VfjgHZqA/rHQiQaomkukSmJPkGZTx9hHcNlmOeP4+u7PIred2qPQF7DNTUKIZXkqjN
XPMBrrTY9U6ZCT1sGKmAc4MGtcumvyI94AScPuxPWJY9wRrJjRw7BxQO03gcczHgEYIPBmPmhajmUvVl
Pe1TwaRjiJe1gGDnDn1BDs/pshyjQ2FFEWFXvH6RK8dvneHcrFKinJJxZEPU1CiGV5KozVzzAa602PVO
nSbVOqRsQ5FA7NNP1y3rSprcPmzZOr+yIZ++PxuJwdXs4VUr0FwgejwWO8Fe5E3qbIJi2KKp8DuJmSQb
/BOOfdTUKIZXkqjNXPMBrrTY9U6dJtU6pGxDkUDs00/XLetKmtw+bNk6v7Ihn74/G4nB1ezhVSvQXCB6
PBY7wV7kTepsgmLYoqnwO4mZJBv8E4591NQohleSqM1c8wGutNj1TvWqoshzarUONU3rz+rfQ5KjGSf2
bBn+TwlYJOefhs+PFaL1NK5GTCb9bApKbzsW0dTUKIZXkqjNXPMBrrTY9U49FR1QHJM+XU+rQbHOuupd
p/wxRkwV3Lht/YaA++KnH4Sa3NE46QTIbUlKq+h8nfNmR7AhzXZQNQaRkuBCJbPdZhzhqY81YKPjQUUY
qlBt1p0m1TqkbEORQOzTT9ct60qa3D5s2Tq/siGfvj8bicHV7OFVK9BcIHo8FjvBXuRN6v/ZT8hSnlUv
CudUzXXf4JE=

PL/SQL procedure successfully completed.

SQL> 

RAC with asm on AIX, CSS Initialization wait event , ORA-01114 error


Need to check the oracle bug : OCSSD threads are not set to the correct priority.
[oracle document id : 1493943.1]
OCSSD.BIN threads must be running in Real-Time.


SOLUTION :
1. bug 13940331, fixed in 11.2.0.4, request/apply patch 13940331 if it affects business.
2. bug 16586971, fixed in 12.1.0.2, request/apply patch 13940331 if it affects business.

/usr/sysv/bin/ps -eLo user,s,pid,lwp,pri,args | grep ocss

[on AIX]
/usr/sysv/bin/ps -eLo  user,s,pid,lwp,pri,args | grep ocss 
       grid S   6947068  16187409  60 /data/grid/bin/ocssd.bin  
       grid S   6947068  25690307   0 /data/grid/bin/ocssd.bin  


60 is not real time priority.

1. Our Environment

  • 11g RAC (with asm) R2 on AIX
  • version : 11.2.0.3

2. Our Symptoms

  • Session is waiting for a long time, “CSS Initialization” 
  • ORA-01114 raised
  • Database alert log
  • Tue Oct 02 16:02:39 2012
    Errors in file mydb_ora_55707154.trc:
    ORA-01114: IO error writing block to file (block # )
  • Trace file
2012-09-02 16:02:39.409: [ CSSCLNT]clssscConnect: gipcWait failed with 16 (12)
2012-09-02 16:02:39.409: [ CSSCLNT]clsssInitNative: connect to (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=61100)) failed, rc 16
kgxgncin: CLSS init failed with status 3
kgxgncin: return status 3 (1311719766 SKGXN not av) from CLSS
kjfmsgr: unable to connect to NM for reg in shared group
ORA-01114 .....
  • ocssd.log
  • 2012-10-02 16:01:25.645: [GIPCXCPT][1029] gipcmodClscCallback: async request failed req 122591870 [0000000000737522] { gipcSendRequest : addr '', data 122174250, len 48, olen 0, parentEndp 128bd5250, ret gipcretConnectionLost (12), objFlags 0x0, reqFlags 0x224 }, ret gipcretConnectionLost (12)
    2012-10-02 16:01:25.648: [GIPCXCPT][1029] gipcmodMuxTransferAccept: internal accept request failed endp 111274cd0, child 128bd5250, ret gipcretConnectionInvalid (13)
    2012-10-02 16:01:25.648: [ GIPCMUX][1029] gipcmodMuxTransferAccept: EXCEPTION[ ret gipcretConnectionInvalid (13) ] error during accept on endp 111274cd0
    2012-10-02 16:01:25.649: [GIPCXCPT][1029] gipcmodClscCallback: async request failed req 1221466b0 [000000000073754d] { gipcSendRequest : addr '', data 1157026f0, len 48, olen 0, parentEndp 128eeae10, ret gipcretConnectionLost (12), objFlags 0x0, reqFlags 0x224 }, ret gipcretConnectionLost (12)
    2012-10-02 16:01:25.650: [GIPCXCPT][1029] gipcmodMuxTransferAccept: internal accept request failed endp 111274cd0, child 128eeae10, ret gipcretConnectionInvalid (13)
    2012-10-02 16:01:25.650: [ GIPCMUX][1029] gipcmodMuxTransferAccept: EXCEPTION[ ret gipcretConnectionInvalid (13) ] error during accept on endp 111274cd0

    ORA-06512: SYS.DBMS_SCHEDULER ORA-27486: insufficient privileges, Grant create job role to user

    grant ‘create job’ role

    SQL>  CONNECT SYSTEM/*******
    SQL>
    SQL> GRANT CREATE JOB TO SCOTT ;

    How to find out the details of the session that caused ORA-01555 snapshot old error

    alert.log

    Mon Nov 13 12:15:56 2017
    Incremental checkpoint up to RBA [0x19a7f.1ae2.0], current log tail at RBA [0x19a7f.4c4a.0]
    Mon Nov 13 12:17:59 2017
    Incremental checkpoint up to RBA [0x19a7a.5b46.0], current log tail at RBA [0x19a7b.3792.0]
    Mon Nov 13 11:27:57 2017
    Completed checkpoint up to RBA [0x19a7b.2.10], SCN: 97348896171
    Mon Nov 13 11:34:45 2017
    ORA-01555 caused by SQL statement below (SQL ID: k2f5t8ux6vbqa, Query Duration=2423 sec,
    select *
    from   (
            select trade_num,
                   (SELECT trade_status
                    FROM   tb_trade X ..
    Mon Nov 13 11:34:58 2017
    Beginning log switch checkpoint up to RBA [0x19a7c.2.10], SCN: 97349449916
    Thread 2 advanced to log sequence 105134 (LGWR switch)
      Current log# 20 seq# 105134 mem# 0: +DATA/pgdb/onlinelog/group_20.328.827412967
    Mon Nov 13 11:34:59 2017
    Archived Log entry 261632 added for thread 2 sequence 105133 ID 0xffffffffb744a9fd dest 1:
    Mon Nov 13 11:35:43 2017
    Incremental checkpoint up to RBA [0x19a7b.37aa.0], current log tail at RBA [0x19a7c.4f4.0]
    Mon Nov 13 11:45:03 2017
    Completed checkpoint up to RBA [0x19a7c.2.10], SCN: 97349449916
    Mon Nov 13 11:45:44 2017
    Incremental checkpoint up to RBA [0x19a7c.513.0], current log tail at RBA [0x19a7c.4971.0]
    Mon Nov 13 11:51:54 2017
    ALTER SYSTEM ARCHIVE LOG
    Mon Nov 13 11:51:56 2017
    Beginning log switch checkpoint up to RBA [0x1
    

     
    Use zamong
    It collects Oracle DB internal state(v$sysstat, v$system_event) and active session details(v$session, sql-text .. ) every second using direct memory access method without accessing Oracle DB.
     
    In addition, Use V$ACTIVE_SESSION_HISTORY, DBA_HIST_ACTIVE_SESS_HISTORY
    If you are using an Oracle Standard Edition Products, You should not use this AWR features. To use oracle AWR features, you must purchase an ‘Oracle Database Diagnostics Pack’ license separately from the oralce enterprise license. The diagnostics pack license is an additional license item that you can purchase with ‘oracle database enterprise edition’ license only.
    You can use this query to see if the AWR features were used on your system

    select * from dba_feature_usage_statistics

     select * from  v$active_session_history
     where sql_id = 'k2f5t8ux6vbqa'
     
     
     select * from  DBA_HIST_ACTIVE_SESS_HISTORY
     WHERE SQL_ID = 'k2f5t8ux6vbqa'
     
    

     
    Get sql text using this query
    Refer to ‘How to display the content of LOB column in SQL*PLUS’

    --SQL_TEXT clob
    SELECT  sql_id,sql_text FROM  dba_hist_sqltext   
    where sql_id= 'k2f5t8ux6vbqa' 
    
    
    --sql_text       varchar2(1000)
    --sql_fulltext   clob
    select sql_id, sql_text , sql_fulltext from v$sql 
    where sql_id= 'k2f5t8ux6vbqa' 
    

     

    Monitoring oracle tablespace growth and checking usable days

    If you proceed as below, You will be able to monitor available days. Tablespace usable days is calculated by considering that the data file is automatically extended or not.
    Using the db link to collect data with this query, You can centrally check the growth of the tablespace in many databases. In addition you can create a tablespace size growth chart using the data contained in the tables.
     
     

    -- tablespace size infomation table
    
    CREATE TABLE SYSTEM.TB_TBS_SIZE_DAILY 
    (
    	DT                VARCHAR2(20),  -- 'yyyymmddhh24', Run at 8 every day, 
    	DBNAME            VARCHAR2(10),
    	TABLESPACE_NAME   VARCHAR2(40),
            CONTENTS          VARCHAR2(20),  -- temporary, permanent 
    	CURR_SIZE         NUMBER,   -- dbf file current size (mb) 
    	CURR_FREE_SIZE    NUMBER,   -- free space size (mb) 
    	EXTEND_FREE_SIZE  NUMBER,   -- free space size(mb) when files was extended to the maximum
    	EXTEND_TOTAL_SIZE NUMBER,   -- dbf file size(mb) when files was automatically extended to the maximum
    	DBF_ON_COUNT      VARCHAR2(10),  -- number of autoextensible files  / number of files
    	EXTEND_FREE_RATE  NUMBER         -- free space ratio when files was automatically extended to the maximum 
    )
    TABLESPACE USERS ;
    
    CREATE UNIQUE INDEX SYSTEM.TB_TBS_SIZE_DAILY_IX01 ON SYSTEM.TB_TBS_SIZE_DAILY  ( DBNAME, DT, TABLESPACE_NAME ) TABLESPACE USERS ;
    
    
    
    -- tablespace usable days report table 
    
    CREATE TABLE SYSTEM.TB_TBS_DAILY_USABLE_DAYS 
    (
    	DT                VARCHAR2(20),  -- 'yyyymmddhh24', Run at 8 every day, 
    	DBNAME            VARCHAR2(10),
    	TABLESPACE_NAME   VARCHAR2(40),
            CONTENTS          VARCHAR2(20),  -- temporary, permanent 
    	YESTERDAY_FREE         NUMBER,   -- yesterday free space size (mb) 
    	TODAY_FREE             NUMBER,   -- today free space size (mb) 
    	INCREASE_SIZE          NUMBER,   -- YESTERDAY_FREE  - TODAY_FREE  (mb) 
    	EXTEND_TOTAL_SIZE      NUMBER,   -- dbf file size(mb) when files was automatically extended to the maximum
    	DBF_ON_COUNT     VARCHAR2(10),   -- number of autoextensible files  / number of files
    	EXTEND_FREE_RATE       NUMBER,   -- free space ratio when files was automatically extended to the maximum
            USABLE_DAYS            NUMBER    -- available days. EXTEND_TOTAL_SIZE / INCREASE_SIZE  
    )
    TABLESPACE USERS ;
    
    CREATE UNIQUE INDEX SYSTEM.TB_TBS_DAILY_USABLE_DAYS_IX01  ON SYSTEM.TB_TBS_DAILY_USABLE_DAYS   ( DBNAME, DT, TABLESPACE_NAME ) TABLESPACE USERS ;
    
    

     
    You can run the script at 8 every day.
     

       
    INSERT INTO SYSTEM.TB_TBS_SIZE_DAILY 
    SELECT  K3.DT ,
            K4.DBNAME,
            K1.TABLESPACE_NAME, 
            (SELECT DECODE( CONTENTS, 'TEMPORARY', CONTENTS, 'PERMANENT') FROM DBA_TABLESPACES  S WHERE S.TABLESPACE_NAME =  K1.TABLESPACE_NAME ) CONTENTS,
            CURR_SIZE, 
            CURR_FREE_SIZE,  
            EXTEND_FREE_SIZE,
            EXTEND_TOTAL_SIZE,                   
            DBF_ON_COUNT   ,
            EXTEND_FREE_RATE 
    FROM
           (
                  SELECT TABLESPACE_NAME, 
                         CURR_SIZE, 
                         CURR_FREE_SIZE,  
                         EXTEND_FREE_SIZE,
                         EXTEND_TOTAL_SIZE, 
                         ROUND( EXTEND_FREE_SIZE  / decode(EXTEND_TOTAL_SIZE,0,1,EXTEND_TOTAL_SIZE)  * 100, 2)  EXTEND_FREE_RATE
                  FROM ( SELECT  A.TABLESPACE_NAME,
                                 CURR_SIZE      "CURR_SIZE",
                                 NVL(FREE, 0)   "CURR_FREE_SIZE",
                                 ROUND(EXTEND_TOTAL - A.CURR_SIZE +  NVL(FREE, 0)) "EXTEND_FREE_SIZE",
                                 ROUND(EXTEND_TOTAL)   "EXTEND_TOTAL_SIZE"
                          FROM  
                                 (SELECT TABLESPACE_NAME,
                                         ROUND(SUM(BYTES / 1024 / 1024)) "CURR_SIZE",
                                         ROUND(SUM( DECODE( AUTOEXTENSIBLE,'YES', MAXBYTES ,BYTES) / 1024 / 1024)) "EXTEND_TOTAL"
                                  FROM  
                                      ( SELECT TABLESPACE_NAME, BYTES, AUTOEXTENSIBLE,  MAXBYTES  FROM DBA_DATA_FILES
                                        UNION ALL
                                        SELECT TABLESPACE_NAME, BYTES, AUTOEXTENSIBLE,  MAXBYTES  FROM DBA_TEMP_FILES 
                                       )    
                                  GROUP BY TABLESPACE_NAME
                                ) A,
                                (SELECT TABLESPACE_NAME,
                                     ROUND(SUM(BYTES) / 1024 / 1024) "FREE"
                                 FROM   DBA_FREE_SPACE
                                 GROUP BY TABLESPACE_NAME
                                ) B
                         WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
                       )
              ) K1,
              ( 
                    SELECT  TABLESPACE_NAME  ,  
                             TO_CHAR(ON_COUNT) || ' / ' || TO_CHAR(TOTAL_COUNT)  DBF_ON_COUNT
                     FROM
                       ( SELECT 
                         TABLESPACE_NAME,
                         SUM(DECODE(AUTOEXTENSIBLE, 'YES', 1, 0)) ON_COUNT,
                         COUNT(*) TOTAL_COUNT
                         FROM  
                            ( SELECT TABLESPACE_NAME,  AUTOEXTENSIBLE  FROM DBA_DATA_FILES
                              UNION ALL
                              SELECT TABLESPACE_NAME,  AUTOEXTENSIBLE  FROM DBA_TEMP_FILES 
                            )    
                         GROUP BY TABLESPACE_NAME 
                        )
               ) K2 ,
               (SELECT TO_CHAR( SYSDATE, 'YYYYMMDDHH24' ) DT FROM DUAL ) K3 ,
               (SELECT NAME  DBNAME FROM V$DATABASE ) K4
    WHERE K1.TABLESPACE_NAME = K2.TABLESPACE_NAME
    ORDER BY   EXTEND_FREE_RATE ;
    
    
    commit;
    
    

     
    Next day, Run this script.

    
    INSERT INTO SYSTEM.TB_TBS_DAILY_USABLE_DAYS 
    SELECT  DT,  DBNAME, TABLESPACE_NAME, CONTENTS, YESTERDAY_FREE,  TODAY_FREE, INCREASE_SIZE, EXTEND_TOTAL_SIZE, DBF_ON_COUNT, EXTEND_FREE_RATE,
           DECODE(SIGN(ROUND(TODAY_FREE/DECODE(INCREASE_SIZE, 0, 1, INCREASE_SIZE))), 1, ROUND(TODAY_FREE/DECODE(INCREASE_SIZE, 0, 1, INCREASE_SIZE)), -1) USABLE_DAYS 
    FROM
      (   SELECT 
                TO_CHAR(SYSDATE, 'YYYYMMDD') || '08'  DT,
                A.DBNAME,
                A.TABLESPACE_NAME,
                A.CONTENTS,
                DECODE( B.EXTEND_FREE_SIZE, NULL, nvl(A.EXTEND_FREE_SIZE,0) , B.EXTEND_FREE_SIZE)  YESTERDAY_FREE,
                nvl(A.EXTEND_FREE_SIZE,0)          TODAY_FREE,
                DECODE(B.EXTEND_FREE_SIZE, NULL, nvl(A.EXTEND_FREE_SIZE,0), B.EXTEND_FREE_SIZE ) - nvl(A.EXTEND_FREE_SIZE,0)  INCREASE_SIZE,
                A.EXTEND_TOTAL_SIZE,
                A.DBF_ON_COUNT,
                A.EXTEND_FREE_RATE    
           FROM (SELECT DT,
                        DBNAME,
                        TABLESPACE_NAME,
                        CONTENTS,
                        EXTEND_FREE_SIZE,
                        EXTEND_TOTAL_SIZE,
                        DBF_ON_COUNT,
                        EXTEND_FREE_RATE
                   FROM SYSTEM.TB_TBS_SIZE_DAILY 
                   WHERE DT = TO_CHAR(SYSDATE, 'YYYYMMDD') || '08'    -- today,  if you run the script at 8 every day,
                ) A,
                (SELECT DT,
                        DBNAME,
                        TABLESPACE_NAME,
                        CONTENTS,
                        EXTEND_FREE_SIZE,
                        EXTEND_TOTAL_SIZE,
                        DBF_ON_COUNT,
                        EXTEND_FREE_RATE
                   FROM SYSTEM.TB_TBS_SIZE_DAILY 
                   WHERE DT = TO_CHAR(SYSDATE - 1, 'YYYYMMDD') || '08'  -- yesterday
                ) B
          WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
          AND A.DBNAME = B.DBNAME(+)
     )     
     ORDER BY  DT,  DBNAME, CONTENTS,  11 ;
     
     COMMIT ;
    

     

    대용량 테이블의 통계관리

    테이블사이즈가 100GB 이상이라고 한다면, 통계정보 생성을 무작정 자동으로 맡기기보다, 대부분의 테이블은 오라클이 자동으로 통계를 생성하게 하고, 특정 대용량 테이블에 대해서 통계정보 생성을 오라클이 자동으로 수행하게 하지말고 DBA가 적절하게 조정해줄 필요가 있다.

    테이블:  SCOTT.TB_BIG_DATA , 100 GB , non partitioned table

    • Temporary tablespace 사이즈 확인 (system user)
    • 자동으로 통계정보가 생성되지 않게  LOCK 시킴
     

    • 특정 테이블의 통계정보를 생성하는 프로시저 생성
     

    • 특정 테이블 통계정보 생성 job 등록
    • scheduler 확인 및 실행 로그 확인
     

    find out free space on tablespace

    query result :