1. 表空间的两种管理方式：一是数据字典管理，而是本地管理

2. 查看表空间信息
SYS@orcl>select TABLESPACE_NAME,STATUS,RETENTION,EXTENT_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME STATUS RETENTION EXTENT_MAN
------------------------------ --------- ----------- ----------
SYSTEM ONLINE NOT APPLY LOCAL
SYSAUX ONLINE NOT APPLY LOCAL
UNDOTBS1 ONLINE NOGUARANTEE LOCAL
TEMP ONLINE NOT APPLY LOCAL
USERS ONLINE NOT APPLY LOCAL
EXAMPLE ONLINE NOT APPLY LOCAL
TBS_GGUSER ONLINE NOT APPLY LOCAL

3. 创建还原（undo）表空间
SYS@orcl>create undo tablespace user_undo datafile '/u01/app/oracle/oradata/orcl/user_undo.dbf' size 50M;
Tablespace created.

4. 查看是否创建成功
SYS@orcl>select tablespace_name,status,EXTENT_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME STATUS EXTENT_MAN
------------------------------ --------- ----------
SYSTEM ONLINE LOCAL
SYSAUX ONLINE LOCAL
UNDOTBS1 ONLINE LOCAL
TEMP ONLINE LOCAL
USERS ONLINE LOCAL
EXAMPLE ONLINE LOCAL
TBS_GGUSER ONLINE LOCAL
USER_UNDO ONLINE LOCAL

5. 查看user_UNDO的存储参数
SYS@orcl>select TABLESPACE_NAME,BLOCK_SIZE,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS,CONTENTS from dba_tablespaces where CONTENTS='UNDO';

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS CONTENTS
----------- ---------
UNDOTBS1 8192 65536 1
2147483645 UNDO

USER_UNDO 8192 65536 1
2147483645 UNDO

6. 查看USER_UNDO的数据文件
SYS@orcl>select FILE_NAME,FILE_ID,BYTES/1024/1024,TABLESPACE_NAME from dba_data_files where tablespace_name='USER_UNDO';

FILE_NAME FILE_ID BYTES/1024/1024 TABLESPACE_NAME
------------------------------ ---------- --------------- --------------------
/u01/app/oracle/oradata/orcl/u 7 50 USER_UNDO
ser_undo.dbf

7. 创建临时表空间USER_TEMP
SYS@orcl>create temporary tablespace user_temp tempfile '/u01/app/oracle/oradata/orcl/user_temp.dbf' size 20M
extent management local uniform size 1M;

8. 查看临时表空间是否创建成功
SYS@orcl>select TABLESPACE_NAME,LOGGING,CONTENTS,STATUS from dba_tablespaces;

TABLESPACE_NAME LOGGING CONTENTS STATUS
------------------------------ --------- --------- ---------
SYSTEM LOGGING PERMANENT ONLINE
SYSAUX LOGGING PERMANENT ONLINE
UNDOTBS1 LOGGING UNDO ONLINE
TEMP NOLOGGING TEMPORARY ONLINE
USERS LOGGING PERMANENT ONLINE
EXAMPLE NOLOGGING PERMANENT ONLINE
TBS_GGUSER LOGGING PERMANENT ONLINE
USER_UNDO LOGGING UNDO ONLINE
USER_TEMP NOLOGGING TEMPORARY ONLINE

9. 默认临时表空间（用于存放用户会话数据如排序）
查看当前数据库默认临时表空间
SYS@orcl>select * from database_properties where property_name like 'DEFAULT%';

10.创建大文件表空间
SYS@orcl>create bigfile tablespace bigfiletbs datafile '/u01/app/oracle/oradata/orcl/bfile_tbs.dba' size 2G;
Tablespace created.



表空间的脱机管理

1. SYS@orcl>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TBS_GGUSER ONLINE
USER_UNDO ONLINE
USER_TEMP ONLINE
BIGFILETBS ONLINE

2. 查看表空间是否存在
SYS@orcl>select table_name,owner from dba_tables where tablespace_name ='EXAMPLE';

TABLE_NAME OWNER
------------------------------ ------------------------------
REGIONS HR
LOCATIONS HR

3. 把表空间脱机
SYS@orcl>alter tablespace example offline;
Tablespace altered.

4. 查看表空间是否脱机
SYS@orcl>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE OFFLINE

5.查看example表空间数据文件的状态
SYS@orcl>select FILE#,name,status from v$datafile;

FILE# NAME STATUS
---------- ---------------------------------------- -------
1 /u01/app/oracle/oradata/orcl/system01.db SYSTEM
f

2 /u01/app/oracle/oradata/orcl/sysaux01.db ONLINE
f

3 /u01/app/oracle/oradata/orcl/undotbs01.d ONLINE
bf

4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE
5 /u01/app/oracle/oradata/orcl/example01.d OFFLINE
bf
6. 这个时候连接hr用户，查看hr自己的表，信息是查不到的

表空间的只读管理

表空间设置为只读，用户只能读取数据，不能进行插入和修改操作
1. 将表空间设置为只读
SYS@orcl>alter tablespace users read only;
Tablespace altered.

2. 查看表空间状态
SYS@orcl>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS READ ONLY

3.查看users中Scott用户的信息
SYS@orcl>select OWNER,TABLE_NAME,TABLESPACE_NAME from dba_tables where owner='SCOTT';

OWNER TABLE_NAME
------------------------------ ------------------------------
TABLESPACE_NAME
------------------------------
SCOTT DEPT
USERS

SCOTT EMP
USERS

SCOTT BONUS
USERS

SCOTT SALGRADE
USERS

4. 查询dept表
SYS@orcl>select * from scott.dept;

DEPTNO DNAME LOC 显然可以查询出来
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

5. 删除信息操作
SYS@orcl>delete from scott.dept where deptno=40;
delete from scott.dept where deptno=40 *
ERROR at line 1:
ORA-00372: file 4 cannot be modified at this time
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf' 无法删除

6. 将users改为正常状态
SYS@orcl>alter tablespace users read write;
Tablespace altered.

表空间和数据文件管理

1. 查看数据文件的自动扩展模式
SYS@orcl>select FILE_NAME,FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME AUT
------------------------------ ---------- ------------------------------ ---
/u01/app/oracle/oradata/orcl/u 4 USERS YES
sers01.dbf

/u01/app/oracle/oradata/orcl/u 3 UNDOTBS1 YES
ndotbs01.dbf

/u01/app/oracle/oradata/orcl/s 2 SYSAUX YES
ysaux01.dbf

/u01/app/oracle/oradata/orcl/s 1 SYSTEM YES
ystem01.dbf

/u01/app/oracle/oradata/orcl/e 5 EXAMPLE YES
xample01.dbf

/u01/app/oracle/oradata/orcl/g 6 TBS_GGUSER YES
guser.dbf

/u01/app/oracle/oradata/orcl/u 7 USER_UNDO NO
ser_undo.dbf

/u01/app/oracle/oradata/orcl/b 8 BIGFILETBS NO
file_tbs.dba

2. 向表空间添加数据文件
SYS@orcl>alter tablespace USER_UNDO add datafile '/u01/app/oracle/oradata/orcl/000.dbf' size 20M;
Tablespace altered.

3. 验证数据文件是否添加
SYS@orcl>select FILE_NAME,BYTES/1024/1024,STATUS,AUTOEXTENSIBLE,ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='USER_UNDO';

FILE_NAME BYTES/1024/1024 STATUS AUT ONLINE_
------------------------------ --------------- --------- --- -------
/u01/app/oracle/oradata/orcl/u 50 AVAILABLE NO ONLINE
ser_undo.dbf

/u01/app/oracle/oradata/orcl/0 20 AVAILABLE NO ONLINE
00.dbf

4. 删除表空间的某个数据文件
SYS@orcl>alter tablespace USER_UNDO drop datafile '/u01/app/oracle/oradata/orcl/000.dbf';
Tablespace altered.

迁移数据文件（分为系统表空间数据文件和非系统表空间数据文件）

迁移系统表空间数据文件

1. 查看system表空间信息
SYS@orcl>select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;

FILE_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ---------
/u01/app/oracle/oradata/orcl/u USERS AVAILABLE
sers01.dbf

/u01/app/oracle/oradata/orcl/u UNDOTBS1 AVAILABLE
ndotbs01.dbf

/u01/app/oracle/oradata/orcl/s SYSAUX AVAILABLE
ysaux01.dbf

/u01/app/oracle/oradata/orcl/s SYSTEM AVAILABLE
ystem01.dbf

2. 关闭数据库并且将库启动到mount状态，进行rename操作
SYS@orcl>alter database rename file '/u01/app/oracle/oradata/orcl/system01.dbf' to '/u01/app/oracle/oradata/system01.dbf';

3. 开库并且进行验证
SYS@orcl>alter database open;
Database altered.

SYS@orcl>col file_name for a30
11:15:58 SYS@orcl>select FILE_NAME,TABLESPACE_NAME,STATUS from dba_data_files;

FILE_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ---------
/u01/app/oracle/oradata/orcl/u USERS AVAILABLE
sers01.dbf

/u01/app/oracle/oradata/orcl/u UNDOTBS1 AVAILABLE
ndotbs01.dbf

/u01/app/oracle/oradata/orcl/s SYSAUX AVAILABLE
ysaux01.dbf

/u01/app/oracle/oradata/system SYSTEM AVAILABLE
01.dbf

迁移非系统表空间数据文件

1. 将要迁移的表空间offline
SYS@orcl>alter tablespace users offline;

2. 拷贝数据文件并且做rename操作
YS@orcl>alter database rename file '/u01/app/oracle/oradata/orcl/users01.dbf' to '/u01/app/oracle/oradata/users01.dbf';
Database altered.

3. 将表空间online
YS@orcl>alter tablespace users online;
Tablespace altered.

4. 查看数据文件的状态
SYS@orcl>col fale_name for a30
11:36:33 SYS@orcl>select FILE_NAME,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024,status from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME
------------------------------ ---------- ------------------------------
BYTES/1024/1024 STATUS
--------------- ---------
/u01/app/oracle/oradata/users0 4 USERS
1.dbf
3000 AVAILABLE

/u01/app/oracle/oradata/orcl/u 3 UNDOTBS1
ndotbs01.dbf
105 AVAILABLE

/u01/app/oracle/oradata/orcl/s 2 SYSAUX

FILE_NAME FILE_ID TABLESPACE_NAME
------------------------------ ---------- ------------------------------
BYTES/1024/1024 STATUS
--------------- ---------
ysaux01.dbf
510 AVAILABLE

/u01/app/oracle/oradata/system 1 SYSTEM
01.dbf
680 AVAILABLE

/u01/app/oracle/oradata/orcl/e 5 EXAMPLE
xample01.dbf

FILE_NAME FILE_ID TABLESPACE_NAME
------------------------------ ---------- ------------------------------
BYTES/1024/1024 STATUS
--------------- ---------
100 AVAILABLE

ITPub博客

表空间与数据文件管理

最新文章