DB File/Tablespace Size Monitor

查看每個File的使用率
SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES/1024/1024/1024 as USED_G,D.MAXBYTES/1024/1024/1024 AS MAX_G,D.STATUS,(d.bytes/d.maxbytes)*100 AS UseRate
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME and d.maxbytes!=0 ORDER BY UseRate desc

查看Tablespace的平均使用率及剩餘空間
select t.tablespace_name ,avg(d.bytes/d.maxbytes)*100 as Used_Rate,sum(d.maxbytes)/1024/1024/1024 as max_g,sum(d.bytes)/1024/1024/1024 as Used_g,
       (sum(d.maxbytes)-sum(d.bytes))/1024/1024/1024 as free_space_g
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME and d.maxbytes!=0 group by t.tablespace_name
ORDER BY Free_space_g

Read More

DB常用指令

Create Function:

CREATE INDEX DWPIA.IDX_SAP_STB_MTIDX ON DWPIA.SAP_STB
(MTIDX, RDATE)
LOGGING
TABLESPACE DWC5_IDX
NOPARALLEL online;

CREATE TABLESPACE C5_SYS_DATA DATAFILE '+DBDATA/c5tasm/datafile/C5_SYS_DATA.dbf' SIZE 5M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

CREATE USER C5TST3 IDENTIFIED BY C5TST3 ACCOUNT UNLOCK;

Drop Function:

DROP TABLESPACE tspace INCLUDING CONTENTS AND DATAFILES;

Grant Function:

GRANT RESOURCE TO C5TST3;
GRANT CREATE SESSION TO C5TST3

Read More

Oracle ASM To File File:：DBMS_FILE_TRANSFER

Oracle ASM To File File:：DBMS_FILE_TRANSFER
建立Directory：
SQL> create directory souredir as '+DBDATA/c5tasm/datafile';
SQL> create directory destdir as '/orasys';
Directory created

確認directories:
SQL>select * from dba_directories;

確認File的Status:
SQL>select file_name, file_id, online_status from dba_data_files order by File_id;

Tablespace C5OPI Offline:
SQL> alter tablespace C5OPI offline;
Tablespace altered.

做資料轉換：
SQL>begin
  dbms_file_transfer.copy_file(source_directory_object => 'destdir',
                               source_file_name => 'C5OPI_DATA_01.DBF',
                               destination_directory_object =>'souredir',
                               destination_file_name => 'C5OPI_DATA_01.DBF');
end;
/

把位置rename一下：
SQL> alter database rename file '/orasys/C5OPI_DATA_01.DBF' to  '+DBDATA/c5tasm/datafile/c5opi_data_01.dbf';
Database altered.

Recover datafile:
SQL> alter database recover datafile 6;

把Tablespace轉Online:
SQL> alter tablespace c5opi online;

確認最後狀況：
SQL>select file_name, file_id, online_status from dba_data_files;


Reference:
11gr2轉換:透過ASMCMD
DBMS_FILE_TRANSFER包方法
RMAN轉換
http://blog.itpub.net/17203031/viewspace-1065094/
================================================================
This e-mail and any files transmitted with it are CONFIDENTIAL and intended solely for the use of the intended recipient.  If you are not the intended recipient or the named addressee, please notify the sender immediately and delete this e-mail and any files transmitted with it from your system; you should not disseminate, distribute, or copy this e-mail and any files transmitted with it, or take any action in reliance on the contents of the said e-mail and files.
================================================================

Read More