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 ;

    AIX command for DBA

    What do all those numbers mean on oslevel -s ?
    first four numbers show the AIX base level. Next is the Technology Level (TL), followed by the number of the Service Pack (SP).

    how to check if a fix is installed on aix

      server1:/home/aixuser> instfix -i | grep IV48087    
        All filesets for IV48087 were found.
    

    lists installed software products (fileset)

      server1:/home/aixuser> lslpp -L | grep bos
      bos.64bit                 7.1.3.45    C     F    Base Operating System 64 bit
      bos.acct                  7.1.3.45    C     F    Accounting Services
      bos.adt.base              7.1.3.45    C     F    Base Application Development
      bos.adt.data               7.1.0.0    C     F    Base Application Development
      bos.adt.debug             7.1.3.45    C     F    Base Application Development
      .......
    

    display physical volume 

     server1:/home/aixuser> lspv
    hdisk0          00f754ed016d9c0a                    rootvg          active      
    hdisk1          00f754ed07c6d35d                    rootvg          active       
    hdisk2          none                                None                        
    hdisk4          none                                None                        
    hdisk5          none                                None                        
    hdisk6          none                                None                        
    hdisk7          none                                None                        
    hdisk8          none                                None                        
    hdisk9          none                                None                        
    hdisk10         none                                None                        
    hdisk11         none                                None                        
    hdisk12         none                                None                        
    hdiskpower0     00f754eddf167b65                    vg00            active      
    hdiskpower1     00f754eddf1aa617                    vg00            active      
    hdiskpower2     00f754eddf1ef78f                    vg00            active      
    hdiskpower3     00f754eddf138e5a                    vg00            active      
    hdiskpower4     00f754eddf17939e                    vg00            active
    

    physical volume size

     server1:/home/aixuser> bootinfo  -s hdisk4
      153600
    

    check reserve policy

     server1:/home/aixuser> lsattr -El hdisk10
    PR_key_value   none               Reserve Device on open           True
    clr_q          no                 Device CLEARS its Queue on error True
    location                          Location Label                   True
    lun_id         0x2000000000000    Logical Unit Number ID           False
    lun_reset_spt  yes                FC Forced Open LUN               True
    max_coalesce   0x100000           Maximum Coalesce Size            True
    max_retries    5                  Maximum Number of Retries        True
    max_transfer   0x100000           Maximum TRANSFER Size            True
    node_name      0x50060160c1e0ac34 FC Node Name                     False
    pvid           none               Physical volume identifier       False
    q_err          yes                Use QERR bit                     True
    q_type         simple             Queue TYPE                       True
    queue_depth    32                 Queue DEPTH                      True
    reassign_to    120                REASSIGN time out value          True
    reserve_policy single_path        Reserve Device on open           True
    rw_timeout     30                 READ/WRITE time out value        True
    scsi_id        0xef               SCSI ID                          False
    start_timeout  60                 START UNIT time out value        True
    ww_name        0x5006016141e0ac34 FC World Wide Name               False
    

    display EMC storage powerpath device

    server1:/home/aixuser> powermt display dev=all
    Pseudo name=hdiskpower0
    CLARiiON ID=CK200081101044 [server1]
    Logical device ID=60060160DF621A008601684F9981E511 [LUN 0]
    state=alive; policy=CLAROpt; queued-IOs=0
    Owner: default=SP A, current=SP A       Array failover mode: 3
    ==============================================================================
    --------------- Host ---------------   - Stor -  -- I/O Path --   -- Stats ---
    ###  HW Path               I/O Paths    Interf.  Mode     State   Q-IOs Errors
    ==============================================================================
    1 fscsi2                 hdisk8      SP A1    active   alive      0      0
    0 fscsi0                 hdisk2      SP B1    active   alive      0      0
    
    Pseudo name=hdiskpower1
    CLARiiON ID=CK200081101044 [server1]
    Logical device ID=60060160DF621A00F2B751709981E511 [LUN 2]
    state=alive; policy=CLAROpt; queued-IOs=0
    Owner: default=SP A, current=SP A       Array failover mode: 3
    ==============================================================================
    --------------- Host ---------------   - Stor -  -- I/O Path --   -- Stats ---
    ###  HW Path               I/O Paths    Interf.  Mode     State   Q-IOs Errors
    ==============================================================================
    1 fscsi2                 hdisk9      SP A1    active   alive      0      0
    0 fscsi0                 hdisk4      SP B1    active   alive      0      0
    
    ................
    

    disk  device

     server1:/home/aixuser> lsdev -Cc disk
    hdisk0 Available 00-00-00 SAS Disk Drive
    hdisk1 Available 00-00-00 SAS Disk Drive
    hdisk2 Available 00-00-00 SAS Disk Drive
    hdisk3 Available 00-00-00 SAS Disk Drive
    hdisk4 Available 05-00-02 EMC CLARiiON FCP RAID 1/0 Disk
    hdisk5 Available 05-00-02 EMC CLARiiON FCP RAID 1/0 Disk
    hdisk6 Available 05-00-02 EMC CLARiiON FCP RAID 1/0 Disk
    hdisk7 Available 06-00-02 EMC CLARiiON FCP RAID 1/0 Disk
    hdisk8 Available 06-00-02 EMC CLARiiON FCP RAID 1/0 Disk
    hdisk9 Available 06-00-02 EMC CLARiiON FCP RAID 1/0 Disk
    hdiskpower0 Available 06-00-02 PowerPath Device
    hdiskpower1 Available 06-00-02 PowerPath Device
    hdiskpower2 Available 06-00-02 PowerPath Device
    

    tape device

     server1:/home/aixuser> lsdev -Cc tape
    rmt0 Available 00-00-00 SAS 4mm Tape Drive
    rmt1 Available 07-00-02 Other FC SCSI Tape Drive
    rmt2 Available 07-00-02 Other FC SCSI Tape Drive
    rmt3 Available 07-00-02 Other FC SCSI Tape Drive
    rmt4 Available 07-00-02 Other FC SCSI Tape Drive
    

    how to check iocp configuration for oracle 12c installation
    The resulting output should match the following example:

    server1:/home/aixuser> lsdev -C | grep iocp
    iocp0       Available               I/O Completion Ports
    

     

    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 :