Oracle flash_recovery_area folder full

目前在處理一些關於Oracle Folder Full 的Case, 也發現我對於整體Oracle 的架構瞭解有限。

解問題也只能靠Google大神...

關於 flash_recovery_area 佔用太多硬碟空間，下面這位網友的Blog 紀錄的很詳細。

實做後也將所有佔空間的DBF file 移除掉。

不過我還是搞不清楚RMAN的機制...

http://freetoad.pixnet.net/blog/post/23715270

關於 Oracle 的 Redo log

查詢 redo log 資訊
select * From v$logfile;

select * from v$log;

詳情可以參閱
http://space.itpub.net/519536/viewspace-557736

常用的SQL Command

由於同事問到，所以把一些之前收集的Sql command 貼出來，方便找～
最近發現一堆檔案都不知道放去哪...所以還是丟到blog上來吧

1. 加Table Space
alter tablespace SYSAUX add
datafile '/dev/vx/rdsk/dg01/vol86' size 1999M;
2. View % of free space
select 100 - ((select sum(bytes) from sys.dba_free_space where tablespace_name = 'TCLASSLOG') / (select sum(bytes) as usage from sys.dba_data_files where tablespace_name = 'TCLASSLOG') * 100 ) as Usage from dual
3. View partitions info
select * from sys.user_tab_partitions
4. View DB free space info
select * from sys.dba_free_space
select TABLESPACE_NAME, sum(BYTES)/1048576 as MB from sys.dba_free_space group by TABLESPACE_NAME order by TABLESPACE_NAME
5. View Data File (Doesn't include TEMP)
select * from sys.dba_data_files
select TABLESPACE_NAME, File_Name,BYTES/1048576 as "SIZE (MB)" from sys.dba_data_files order by TABLESPACE_NAME,File_Name

Add DataFile for TableSpace

1. List the DataFile information for reference:
   select TABLESPACE_NAME, File_Name,BYTES/1048576 as "SIZE (MB)" from sys.dba_data_files order by TABLESPACE_NAME,File_Name
2. Add DataFile into the TableSpace of LogData
   alter tablespace LOG adddatafile ' /oradata/mcs/log02.dbf ' size 5000M;

在Oracle中處理 用Java System.currentTimeMillis() 儲存的資料

為了系統的需求，資料需紀錄到毫秒（Millisecond），所以我們會將System.currentTimeMillis() 取得的值寫入db的systemdate欄位，但是如果要用SQL 做時間範圍的搜尋，有以下做法
( systemdate欄位存放System.currentTimeMillis() 取得的值）

--找出myTable資料表中 systemdate欄位 在 2006/11/28 14:00:00 ~ 14:10:00 的資料

select * from myTable 
where systemdate >= (to_date('20061128 14:00:00','YYYYMMDD hh24:mi:ss') - to_date('19700101 8:00:00','YYYYMMDD hh24:mi:ss'))*1000*60*60*24 
and systemdate <= (to_date('20061128 14:10:00','YYYYMMDD hh24:mi:ss') - to_date('19700101 8:00:00','YYYYMMDD hh24:mi:ss'))*1000*60*60*24

--找出myTable資料表中日期最小的時間
select to_date('19700101 8:00:00','YYYYMMDD hh24:mi:ss') + ((select min(SYSTEMDATE) from myTable )/1000/60/60/24) from dual