表空间操作

1>创建一个表空间

CREATE SMALLFILE TABLESPACE sjtest
DATAFILE '/opt/ora1/app/oracle/oradata/sjdb/sjtest01.dbf' SIZE 256M REUSE
AUTOEXTEND ON NEXT 20M MAXSIZE 512m
ONLINE LOGGING PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
SEGMENT SPACE MANAGEMENT AUTO

2>查询表空间
I) 查询默认的表空间和默认的临时表空间

SELECT * from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE';
SELECT * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

II) 查询用户能使用的表空间和临时表空间

select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username='OPS$WILL';

查询某个表所在的表空间

select OWNER, TABLE_NAME, TABLESPACE_NAME from all_tables where TABLE_NAME='DEPT';

如果已经以该用户登录，还可以

select TABLE_NAME, TABLESPACE_NAME from user_tables where TABLE_NAME='DEPT';

III）查询表空间和临时表空间对应的数据文件

select * from dba_data_files order by tablespace_name;
select * from dba_temp_files;

IV)可以从dba_segments表中查询到某个表空间存储的对象的类型

V)查询表空间名称及大小

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

3>更改默认表空间和默认临时表空间

alter database default temporary tablespace TEMP2;
alter database default tablespace USERS2;