- 论坛徽章:
- 0
|
修改数据库文件路径问题
7. 移動數據庫文件
第一種方法﹕(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
8. 統計分析
SPOOL C:\ANALYZE.SQL
SELECT 'ANALYZE TABLE '||TRIM(OBJECT_NAME)||' COMPUTE STATISTICS; '
FROM USER_OBJECTS
WHERE OBJECT_TYPE='TABLE'
然后運行analyze.sql文件即可
最后,對TABLESPACE碎片進行清理.
SELECT TABLESPACE_NAME, PERCENT_BLOCKS_COALESCED
FROM DBA_FREE_SPACE_COALESCED
去看那一個TABLESPACE對應的值不是100的就運行如下命令:
ALTER TABLESPACE TABLESPACE_NAME COALESCE
注意:如果TABLESPACE的值為100就沒有必要作此命令.
9. 移動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
10. MAINTAINING THE CONTROLFILE
1>; SVRMGRL
2>; CONNECT INTERNAL
3>; SHUTDOWN
4>; EXIT
5>; CP /U/PRODUCT/ORADATA/FOXMOLD/CONTROL01.CTL
/DB4/ORADATA/CONTROL01.CTL
6>; CHMOD 660 /DB4/ORADATA/CONTROL01.CTL
7>; 修改INIT.ORA CONTROL_FILES=……
8>; STARTUP MOUNT FOXMOLD |
|