1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!
lvthuylinh

Extend oracle tablespace instructions (vietnamese) 2011-12-27

Extend oracle tablespace instructions (vietnamese)

  1. lvthuylinh
    --vao user apps: scripts de kiem tra xem khi nao thi het tablepsace

    [highlight=sql]SELECT distinct 'Freespace ' ||
    round(((nvl(sum(b.bytes) / 1048576, 0)) / c.total_allocated) * 100) ||
    '% in tablespace ' || a.tablespace_name
    FROM dba_tablespaces a,
    dba_free_space b,
    (SELECT tablespace_name, nvl(sum(bytes) / 1048576, 0) total_allocated
    FROM dba_data_files
    GROUP BY tablespace_name) c
    WHERE a.tablespace_name = b.tablespace_name(+)
    AND c.tablespace_name = a.tablespace_name
    AND a.status = 'ONLINE'
    --HAVING((nvl(sum(b.bytes) / 1048576, 0)) / c.total_allocated) * 100 < 15
    GROUP BY a.tablespace_name, c.total_allocated;


    -- ==> Xem cai nao bi 0% hoac <10% thi phai add them datafile cho no.
    ------------------------------------------------------------------------------------------

    Select ts.tablespace_name, df.file_name, df.bytes / (1024 * 1024) file_size
    From dba_data_files df, dba_tablespaces ts Where df.tablespace_name = ts.tablespace_name

    -- ==> Dem xem co bao nhieu file cua tablespace can tao.

    -----------------------------------------------------------------------------------------------
    -- cau lenh alter chay boi user system/manager: scripts de add them datafile vao tablespace: chay tren Command window

    --alter tablespace tablespace_name add datafile 'llocation and name of datafile' size xxx autoextend off

    --(vd: alter tablespace APPS_TS_TX_DATA add datafile '/ora/prod/vietadata/a_txn_data09.dbf' size 1024M autoextend off)

    -----VD: tablespace APPS_TS_TX_DATA dem duoc 11 file, bay gio can add them file tiep theo la "a_txn_data12.dbf":


    alter tablespace APPS_TS_TX_DATA add datafile '/ora/prod/vietadata/a_txn_data12.dbf' size 1024M autoextend off;


    -----VD: tablespace APPS_TS_TX_IDX dem duoc 09 file, bay gio can add them file tiep theo la "a_txn_data10.dbf":

    alter tablespace APPS_TS_TX_IDX add datafile '/ora/prod/vietadata/a_txn_ind10.dbf' size 1024M autoextend off;

    ---------------------------------------------------------------------------------------------------------------------------------
    [/highlight]
    -- Kiem tra dung luong o cung may chu:

    1. Login vao SSH Secure Shell Client.
    2. go: df -h
    3. Xem /arc la bao nhieu % roi

    Neu day (100%) thi phai xoa bot cac archive log file nay di.

    4. Login vao SSH Secure File Transfer Client.
    5. xoa bot cac file /arc/erp/vieta/xxx.arc
    6. Sau do Stop, start lai concurrent.