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

--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.