轉發些ORACLE好文章或好SQL語句和大家共勉!

ccbzzp · 发表于 2004-5-7 08:52

15
移動數據庫文件
第一種方法﹕(ALTER DATABASE)
關閉數據庫-移動數據文件-裝載并改名-啟動
1> SVRMGRL
2> CONNECT  INTERNAL
3> SHUTDOWN
4> EXIT
5> MV  /U/PRODUCT/ORADATA/FOXMOLD/USER01.DBF    /DB3/ORADATA
6> SVRMGRL
7> CONNECT  INTERNAL
8> STARTUP  MOUNT  FOXMOLD
9> ALTER DATABASE RENAME FILE
‘/U/PRODUCT/ORADATA/FOXMOLD/USER01.DBF’ TO
‘/DB3/ORADATA/USER01.DBF’
10> ALTER  DATABASE  OPEN
第二種方法﹕(ALTER  TABLESPACE)
關閉數據庫-移動數據文件-裝載并改名-啟動
1> SVRMGRL
2> CONNECT  INTERNAL
3> SHUTDOWN
4> EXIT
5> MV  /U/PRODUCT/ORADATA/FOXMOLD/USER01.DBF    /DB3/ORADATA
6> SVRMGRL
7> CONNECT  INTERNAL
8> ALTER TABLESPACE DATA RENAME DATAFILE
‘/U/PRODUCT/ORADATA/FOXMOLD/USER01.DBF’ TO
‘/DB3/ORADATA/USER01.DBF’
9> ALTER  DATABASE  OPEN

ccbzzp · 发表于 2004-5-7 08:53

16

統計分析
SPOOL C:\ANALYZE.SQL
SELECT 'ANALYZE  TABLE  '||TRIM(OBJECT_NAME)||' COMPUTE STATISTICS; '
   FROM  USER_OBJECTS
WHERE  OBJECT_TYPE='TABLE'
然后咝衋nalyze.sql文件即可
最后,對TABLESPACE碎片進行清理.
SELECT  TABLESPACE_NAME, PERCENT_BLOCKS_COALESCED
FROM  DBA_FREE_SPACE_COALESCED
去看那一個TABLESPACE對應的值不是100的就咝腥缦旅?

ccbzzp · 发表于 2004-5-7 08:54

17
移動TABLE INDEX 到另一個TABLESPACE
1> SPOOL C:\MV_INDEX.SQL
2> SELECT ‘ALTER INDEX ‘||TRIM(OBJECT_NAME)||’ REBUILD TABLESPACE FOXMOLD1;’ FROM USER_OBJECTS WHERE OBJECT_TYPE=’INDEX’
3> 將此命令生成MV_INDEX.SQL文件
4> @C:\MV_INDEX.SQL

ccbzzp · 发表于 2004-5-7 08:55

18
檢查表空間使用狀況
SELECT TABLESPACE_NAME,SUM(BLOCKS) AS FREE_BLK,TRUNC(SUM(BYTES)/(1024*1024)) AS FREE_BLK
MAX(BYTES)/(1024) AS BIG_CHUNK_K,COUNT(*) AS NUM_CHUNKS
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
TABLESPACE_NAME  FREE_BLK  FREE_M  BIG_CHUNK_K  NUM_CHUNKS
FOXMOLD          87813       686    492168       46
FOXMOLD1       114938       897    785320       17