1. Get unlimited access to documents by upgrading to Premium Membership.
  2. We are not accepting new members currently. If you would like to join, please contact us.
    Dismiss Notice
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.