表空間基本操作
create tablespace users
datafile '/opt/ora9/oradata/big58/users01.dbf' size 26M autoextend on
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
//這樣的表空間段空間是手動管理的
drop tablespace users including contents and datafiles;
連物理檔案一起刪除。
create tablespace users
datafile '/opt/ora9/oradata/big58/users01.dbf' size 26M autoextend on
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
alter tablespace users add
2 datafile '/opt/ora9/oradata/big58/users02.dbf' size 20M;
alter tablespace users offline;
//在作業系統下刪除users02.dbf
alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/opt/ora9/oradata/big58/users02.dbf'
quote:
最初由 huakaibird 釋出
表空間有8，9兩個資料檔案，刪除9。
由於表空間是offline的，所以生成的trace檔案檔案中沒有該表空間的datafile 8和9,
在trace檔案中新增未刪除的表空間資料檔案8，alter database open resetlogs,會報 file 8（未刪除的） was not restored from a sufficiently old backup。然後dba_data_files中兩個資料檔案變成MISSING00008，MISSING00009。
[@more@]程式碼:
SQL> select t.name as tablespace_name,
2 a.status as tablespace_status,
3 d.name as datafile_name,
4 d.STATUS as datafile_status
5 from v$datafile D, v$tablespace t, dba_tablespaces a
6 where d.ts# = t.ts#
7 and a.tablespace_name = t.NAME
8 ;
TABLESPACE_NAME TABLESPACE_STATUS DATAFILE_NAME DATAFILE_STATUS ------------------------------ ----------------- -------------------------------------------------------------------------------- --------------- SYSTEM ONLINE D:ORACLEORADATARTESTSYSTEM01.DBF SYSTEM
UNDOTBS1 ONLINE D:ORACLEORADATARTESTUNDOTBS01.DBF ONLINE
USERS ONLINE D:ORACLEORADATARTESTUSERS01.DBF ONLINE
USERS ONLINE D:ORACLEORADATARTESTUSERS01_2.DBF ONLINE
SQL> /
TABLESPACE_NAME TABLESPACE_STATUS DATAFILE_NAME DATAFILE_STATUS ------------------------------ ----------------- -------------------------------------------------------------------------------- --------------- SYSTEM ONLINE D:ORACLEORADATARTESTSYSTEM01.DBF SYSTEM
UNDOTBS1 ONLINE D:ORACLEORADATARTESTUNDOTBS01.DBF ONLINE
USERS ONLINE D:ORACLEORADATARTESTUSERS01.DBF ONLINE
USERS ONLINE D:ORACLEORADATARTESTUSERS01_2.DBF ONLINE
SQL> alter tablespace users offline;
Tablespace altered
SQL> SQL> select t.name as tablespace_name,
2 a.status as tablespace_status,
3 d.name as datafile_name,
4 d.STATUS as datafile_status
5 from v$datafile D, v$tablespace t, dba_tablespaces a
6 where d.ts# = t.ts#
7 and a.tablespace_name = t.NAME
8 ;
TABLESPACE_NAME TABLESPACE_STATUS DATAFILE_NAME DATAFILE_STATUS ------------------------------ ----------------- -------------------------------------------------------------------------------- --------------- SYSTEM ONLINE D:ORACLEORADATARTESTSYSTEM01.DBF SYSTEM
UNDOTBS1 ONLINE D:ORACLEORADATARTESTUNDOTBS01.DBF ONLINE
USERS OFFLINE D:ORACLEORADATARTESTUSERS01.DBF OFFLINE
USERS OFFLINE D:ORACLEORADATARTESTUSERS01_2.DBF OFFLINE
這裡刪除'D:ORACLEORADATARTESTUSERS01_2.DBF'.
SQL> alter tablespace users online;
alter tablespace users online
ORA-01157: 無法標識/鎖定資料檔案 4 - 請參閱 DBWR 跟蹤檔案
ORA-01110: 資料檔案 4: 'D:ORACLEORADATARTESTUSERS01_2.DBF'
SQL> alter database datafile 'D:ORACLEORADATARTESTUSERS01.DBF' online;
Database altered
SQL> SQL> select t.name as tablespace_name,
2 a.status as tablespace_status,
3 d.name as datafile_name,
4 d.STATUS as datafile_status
5 from v$datafile D, v$tablespace t, dba_tablespaces a
6 where d.ts# = t.ts#
7 and a.tablespace_name = t.NAME
8 ;
TABLESPACE_NAME TABLESPACE_STATUS DATAFILE_NAME DATAFILE_STATUS ------------------------------ ----------------- -------------------------------------------------------------------------------- --------------- SYSTEM ONLINE D:ORACLEORADATARTESTSYSTEM01.DBF SYSTEM
UNDOTBS1 ONLINE D:ORACLEORADATARTESTUNDOTBS01.DBF ONLINE
USERS OFFLINE D:ORACLEORADATARTESTUSERS01.DBF ONLINE
USERS OFFLINE D:ORACLEORADATARTESTUSERS01_2.DBF OFFLINE
SQL> select * from file$
2 ;
FILE# STATUS$ BLOCKS TS# RELFILE# MAXEXTEND INC CRSCNWRP CRSCNBAS OWNERINSTANCE SPARE1 SPARE2 SPARE3 SPARE4 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ---------- ---------- -------------------------------------------------------------------------------- -----------
1 2 25600 0 1 4194302 1280 0 7 4194306
2 2 19456 1 2 4194302 640 0 4602 8388610
3 2 3200 3 3 0 0 0 49348 12582914
4 2 256 3 4 0 0 0 69739 16777218
SQL> select * from v$datafile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME ---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- --------------------------------------------------------------------------------
1 7 2006-6-8 17:0 0 1 SYSTEM READ WRITE 70265 2006-10-18 10:1 0 49222 49223 2006-6-8 17 209715200 25600 209715200 8192 D:ORACLEORADATARTESTSYSTEM01.DBF 0 8192 NONE
2 4602 2006-6-8 17:0 1 2 ONLINE READ WRITE 70265 2006-10-18 10:1 0 49222 49223 2006-6-8 17 159383552 19456 159383552 8192 D:ORACLEORADATARTESTUNDOTBS01.DBF 0 8192 NONE
3 49348 2006-6-8 17:4 3 3 ONLINE DISABLED 70310 2006-10-18 10:1 0 70243 70310 2006-10-18 26214400 3200 26214400 8192 D:ORACLEORADATARTESTUSERS01.DBF 0 8192 NONE
4 69739 2006-10-18 9: 3 4 OFFLINE DISABLED 70243 2006-10-18 10:1 0 70243 2006-10-18 0 0 0 0 2097152 8192 D:ORACLEORADATARTESTUSERS01_2.DBF 0 4294967295 NONE
SQL> delete from file$ where file#=4;
1 row deleted
SQL> commit;
Commit complete
SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> STARTUP NOMOUNT
ORACLE 例程已經啟動。
Total System Global Area 105978600 bytes
Fixed Size 453352 bytes
Variable Size 79691776 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> edit
已寫入檔案 afiedt.buf
1 CREATE CONTROLFILE REUSE DATABASE "RTEST" NORESETLOGS NOARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 1 'D:ORACLEORADATARTESTREDO01.LOG' SIZE 10M,
10 GROUP 2 'D:ORACLEORADATARTESTREDO02.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:ORACLEORADATARTESTSYSTEM01.DBF',
14 'D:ORACLEORADATARTESTUNDOTBS01.DBF',
15 'D:ORACLEORADATARTESTUSERS01.DBF'
16 CHARACTER SET UTF8
17* ;
18 /
;
* ERROR 位於第 17 行: ORA-00911: ????
已用時間: 00: 00: 00.00
SQL> edit
已寫入檔案 afiedt.buf
1 CREATE CONTROLFILE REUSE DATABASE "RTEST" NORESETLOGS NOARCHIVELOG
2 -- SET STANDBY TO MAXIMIZE PERFORMANCE
3 MAXLOGFILES 5
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 1
7 MAXLOGHISTORY 226
8 LOGFILE
9 GROUP 1 'D:ORACLEORADATARTESTREDO01.LOG' SIZE 10M,
10 GROUP 2 'D:ORACLEORADATARTESTREDO02.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'D:ORACLEORADATARTESTSYSTEM01.DBF',
14 'D:ORACLEORADATARTESTUNDOTBS01.DBF',
15 'D:ORACLEORADATARTESTUSERS01.DBF'
16* CHARACTER SET UTF8
17 ;
控制檔案已建立
已用時間: 00: 00: 01.00
SQL> alter database open;
資料庫已更改。
已用時間: 00: 00: 03.03
SQL> select * from file$;
FILE# STATUS$ BLOCKS TS# RELFILE# MAXEXTEND INC CRSCNWRP CRSCNBAS OWNERINSTANCE SPARE1 SPARE2 SPARE3 SPARE4 ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ---------- ---------- -------------------------------------------------------------------------------- -----------
1 2 25600 0 1 4194302 1280 0 7 4194306
2 2 19456 1 2 4194302 640 0 4602 8388610
3 2 3200 3 3 0 0 0 49348 12582914
SQL> select * from v$datafile;
FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET AUX_NAME ---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- --------------------------------------------------------------------------------
1 7 2006-6-8 17:0 0 1 SYSTEM READ WRITE 70525 2006-10-18 10:2 0 0 0 209715200 25600 0 8192 D:ORACLEORADATARTESTSYSTEM01.DBF 0 8192 NONE
2 4602 2006-6-8 17:0 1 2 ONLINE READ WRITE 70525 2006-10-18 10:2 0 0 0 159383552 19456 0 8192 D:ORACLEORADATARTESTUNDOTBS01.DBF 0 8192 NONE
3 49348 2006-6-8 17:4 3 3 ONLINE DISABLED 70525 2006-10-18 10:2 0 0 0 26214400 3200 0 8192 D:ORACLEORADATARTESTUSERS01.DBF 0 8192 NONE
SQL> SQL> select t.name as tablespace_name,
2 a.status as tablespace_status,
3 d.name as datafile_name,
4 d.STATUS as datafile_status
5 from v$datafile D, v$tablespace t, dba_tablespaces a
6 where d.ts# = t.ts#
7 and a.tablespace_name = t.NAME
8 ;
TABLESPACE_NAME TABLESPACE_STATUS DATAFILE_NAME DATAFILE_STATUS ------------------------------ ----------------- -------------------------------------------------------------------------------- --------------- SYSTEM ONLINE D:ORACLEORADATARTESTSYSTEM01.DBF SYSTEM
UNDOTBS1 ONLINE D:ORACLEORADATARTESTUNDOTBS01.DBF ONLINE
USERS OFFLINE D:ORACLEORADATARTESTUSERS01.DBF ONLINE
SQL> alter tablespace users online;
Tablespace altered
SQL> SQL> select t.name as tablespace_name,
2 a.status as tablespace_status,
3 d.name as datafile_name,
4 d.STATUS as datafile_status
5 from v$datafile D, v$tablespace t, dba_tablespaces a
6 where d.ts# = t.ts#
7 and a.tablespace_name = t.NAME
8 ;
TABLESPACE_NAME TABLESPACE_STATUS DATAFILE_NAME DATAFILE_STATUS ------------------------------ ----------------- -------------------------------------------------------------------------------- --------------- SYSTEM ONLINE D:ORACLEORADATARTESTSYSTEM01.DBF SYSTEM
UNDOTBS1 ONLINE D:ORACLEORADATARTESTUNDOTBS01.DBF ONLINE
USERS ONLINE D:ORACLEORADATARTESTUSERS01.DBF ONLINE
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/545828/viewspace-872080/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle SQL 基本操作之 表空間OracleSQL
- oracle 臨時表空間基本常識和操作Oracle
- oracle表空間操作Oracle
- Oracle 表空間 的操作Oracle
- 命令建立表空間的基本格式
- 傳輸表空間操作-OracleOracle
- 表空間常見的操作
- oracle表空間日常操作管理Oracle
- Oracle表空間操作詳解Oracle
- Oracle表空間相關操作Oracle
- 臨時表空間操作總結
- 表空間的建立修改等操作
- 【原創】表空間相關操作
- oracle 10g表空間操作Oracle 10g
- undo表空間中常用的操作
- Oracle切換undo表空間操作步驟Oracle
- Oracle - 表空間相關常用操作語句Oracle
- 臨時表空間temporary tablespace相關操作
- 資料庫維護常用操作4--表空間操作資料庫
- 分析表空間空閒率並收縮表空間
- Oracle表空間操作詳解-入門基礎Oracle
- MySQL InnoDB 共享表空間和獨立表空間MySql
- 管理表空間（表空間的屬性）轉貼
- 表空間管理之bigfile表空間設定
- 遷移SYSTEM表空間為本地管理表空間
- MySQL InnoDB 共享表空間和獨立表空間MySql
- Oracle表空間Oracle
- PostgreSQL：表空間SQL
- Mysql表空間MySql
- 表空間sqlSQL
- 空間RESUMABLE操作（三）
- 空間RESUMABLE操作（二）
- 空間RESUMABLE操作（一）
- 關於undo表空間的一些常用操作
- Oracle 10g的可傳輸表空間操作Oracle 10g
- 刪除表空間和表空間包含的檔案
- 跨平臺表空間遷移(傳輸表空間)
- Shell磁碟空間和表空間告警程式