创建
- 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]