研發中心一台windows2003上跑着oracle和sqlserver的資料庫,是一台老機器,已經加挂了3塊硬碟,但是最近接到同僚請求處理說oracle資料庫資料檔案所在磁盤快滿掉了,問改怎麼處理,之前該同僚已經咨詢過另外一個本部門的同僚,本部門同僚竟然給答複說沒辦法處理,處理之後會導緻資料庫無法使用,我囧!無奈研發同僚轉問我,但是給她答複說可以處理,于是跑過去看了下機器磁盤資訊,發現每塊盤都用的差不多快完了,必須購買新硬碟才能處理,無奈之下,研發同僚隻好選擇删除掉1個曆史大文檔檔案,然後先把其中的一個資料檔案遷移過去,同時要求申購新硬碟。
今天新硬碟到貨,上硬碟并分區,然後準備變更檔案存放位置,了解了下該庫有3個應用使用者,而且資料檔案存放的比較混亂,命名也不規範,針對這2點做了統一處理,将資料檔案存放到新增加硬碟所在分區,安裝使用者名稱規範存放,檔案後增加序号辨別。
一下是處理過程,記錄在此!
執行如下腳本:
spool f:\mv_datafiles.txt
--Set linesize 200;
Set pagesize 100;
Column username format a8;
Column dtbspace format a8;
Column dtpspace format a8;
Column command format a75;
Select * From v$version;
select a.username,a.default_tablespace dtbspace,a.temporary_tablespace dtpspace from dba_users a where a.username in ('user1','user2','user3') order by a.username;
break on username skip 1;
break on offtbspace skip 1;
With Tbs As
(Select a.Username,
a.Default_Tablespace,
b.File_Name,
b.File_Id,
b.Bytes / 1024 / 1024 "SIZE-Mb"
From Dba_Users a, Dba_Data_Files b
Where a.Username In ('user1', 'user2', 'user3')
And a.Default_Tablespace = b.Tablespace_Name)
Select 'Datafile total size:' || To_Char(Sum("SIZE-Mb")) || 'Mb' command
From Tbs
Union All
Select '****Run follow script use sqlplus****'
From Dual
Union All
Select 'ALTER TABLESPACE ' || Default_Tablespace || ' OFFLINE;'
From Tbs
Group By Default_Tablespace
Union All
Select '****Run follow script use MS-DOS****'
From Dual
Union All
Select 'echo %date:~0,4%-%date:~5,2%-%date:~8,2% %time:~0,2%:%time:~3,2%:%time:~6,2%'
From Dual
Union All
Select 'ocopy ' || File_Name || ' f:\oradata\' || Username || '\' ||
Username || Row_Number() Over(Partition By Username Order By File_Id) || '.dbf' cptbdatafile
From Tbs
Union All
Select '****Run follow script use sqlplus****'
From Dual
union all
Select 'alter tablespace '||Default_Tablespace || ' rename datafile '''|| File_Name || ''' to ''f:\oradata\' || Username || '\' ||
Username || Row_Number() Over(Partition By Username Order By File_Id) || '.dbf'';' renametbdatafile
From Tbs
Union All
Select 'echo %date:~0,4%-%date:~5,2%-%date:~8,2% %time:~0,2%:%time:~3,2%:%time:~6,2%'
From Dual
Union All
Select '****Run follow script use sqlplus****'
From Dual
Union All
Select 'ALTER TABLESPACE ' || Default_Tablespace || ' ONLINE;'
From Tbs
Group By Default_Tablespace
Union All
Select '****Run follow script use MS-DOS****'
From Dual
Union All
Select 'before you delete datafiles job recommend you check the tbs and datafiles is work' From dual
Union All
Select 'del ' || File_Name Deldatafile From Tbs;
spool Off;
檢視脫機檔案,安裝順序執行腳本,注意在最後os delete的之前最好檢視下表空間和資料檔案的狀态相關資訊:
col defaultt_tablespace For a20;
col tablespacestatus For a17;
col defaultt_tablespace For a20;
col file_name For a50;
col datafilestatus For a15;
break On username Skip 1;
break On Default_Tablespace Skip 1;
Select a.Username,
a.Default_Tablespace,c.status tablespacestatus,b.file_name,b.status datafilestatus
From Dba_Users a, Dba_Data_Files b,Dba_Tablespaces c
Where a.Username In ('user1', 'user2', 'user3')
And a.Default_Tablespace = b.Tablespace_Name And b.tablespace_name=c.tablespace_name Order By username;
順便關注了下拷貝檔案的速度81G多的檔案,拷貝時間1個半小時。
-The End-