表空间管理

创建

1. CREATE TABLESPACE tablespace_name DATAFILE ‘filename’ SIZE size
   [AUTOEXTEND [OFF|ON NEXT size] ]
   [MAXSIZE size]
   [PERMANENT|TEMPORARY]
   [EXTENT MANAGEMENT
   [DICTIONARY|LOCAL
   [AUTOALLLOCATE|UNIFORM.[SIZE integer[K|M]]]
   ]
   ]
   2.创建表空间：create tablespace user_data
   datafile ‘/u01/tablespace/tb1.dbf’ size 100M;
   3.创建表空间自动扩展：create tablespace user_data
   datafile ‘/u01/tablespace/tb1.dbf’ size 100M
   autoextend on;
   4.创建***的表空间：create tablespace user_data
   datafile ‘/u01/tablespace/tb1.dbf’ size 100M
   extent managment ***;
   5.默认创建大文件表空间：create database set default bigfile tablespace user_undo
   datafile ‘/u01/tablespace/tb1.dbf’ size 2G;
   6.创建大文件表空间：create bigfile tablespace bigfietbs
   datafile ‘/u01/tablespace/tb1.dbf’ size 2G;
   7.创建还原表空间：create undo tablespace user_undo
   datafile ‘/u01/tablespace/tb1.dbf’ size 100M;
   8.创建临时表空间：create temporary tablespace tablespace_name
   tempfile ‘filename.dbf’ SIZE size

修改

1.将表空间置为只读：alter database tablespace_name read only
2.将表空间置为正常状态：alter tablespace users read write;
3.设置表空间的可用状态：ALTER TABLESPACE tablespace_name {ONLINE|OFFLINE [NORMAL|TEMPORARY|IMMEDIATE]}
4.重命名表空间：ALTER TABLESPACE old_name to new_name
5.更改默认类型为大文件表空间类型；alter tablespace set default bigfile tablespace
6.更改大文件表空间的大小：alter tablespace bigfiletbs resize 4G;
7.修改大文件表空间大小为自动扩展：alter tablespace bigfiletbs autoextend on next 1G;
8.修改表空间的数据文件为自动扩展：
alter database datafile ‘/u01/tablespace/db1.dbf’ autoextend on next 1MB;
9.增加数据文件：alter tablespace manage_tbs
add datafile ‘/u01/tablespace/tb2.dbf’ size 50MB;
10.修改表空间数据文件大小：alter database datafile ‘/u01/tablespace/tb1.dbf’ resize 100M;
11.限制使用表空间:alter user 用户名 quota 10M on 表空间名
12.设置为备份模式：alter tablespace 表空间 begin backup；
13.结束备份模式；alter tablespace 表空间 end backup;

查看

1.查看用户对应的表空间：select default_tablespace from dba_users where username=‘登录用户’
2.查看表空间：select tablespace_name from dba_tablespaces;
3.查看所有表空间及表空间大小：
select tablespace_name,sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name;
4.查看所有默认表空间：select * from dba_tablespaces/V$tablespace
5.查看表空间对应的数据文件：select file_name,tablespace_name,status
from dba_data_files
where tablespace_name=’… .’;
6.查询有分区的表空间：SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
FROM USER_TAB_PARTITIONS;
7.查看：status-状态，owner-用户，table_name-表名，
8.查看表空间：
select sysdate STATISTICAL_TIME,a.tablespace_name,round(used_space,5) as USED_MB,round(total_space-used_space,5) as AVAILABLE_MB,round(total_space,5) as MAX_MB,round(used_space/total_space,5)*100 as RATIO from (select tablespace_name,sum(bytes)/1024/1024 as used_space from dba_segments group by tablespace_name) a,dba_tablespaces b,(select tablespace_name,sum(decode(autoextensible,‘YES’,maxbytes,‘NO’,bytes))/1024/1024 as total_space from dba_data_files group by tablespace_name) c where a.tablespace_name=b.tablespace_name and a.tablespace_name=c.tablespace_name and B.contents=‘PERMANENT’ order by 4;
9.查看临时表空间：select * from V&TEMPFILE;

删除

1.drop tablespace tablespace_name
2.删除表空间并删除数据文件：drop tablespace manag_tbs including contents and datafiles;
3.DROP TABLESPACE tablespace_name [INCLUDING CONTENTS] [CASCADE CONSTRAINTS]