ITPub博客

首页 > 数据库 > 数据库开发技术 > 表空间基本操作

表空间基本操作

原创 数据库开发技术 作者:huakaibird 时间:2006-10-14 16:14:09 0 删除 编辑

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。

代码:

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
>
[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/545828/viewspace-872080/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    41
  • 访问量
    421483