oracle表空间设置

oracle表空间设置

创建永久表空间

CREATE TABLESPACE PJY5
DATAFILE ‘/home/test2/pjy5.dbf’
SIZE 20M
ONLINE;

这个CREATE TABLESPACE语句创建一个名为tbs_perm_01的永久表空间，其中有一个名为tbs_perm_01.dat的数据文件。

创建永久表空间-可扩展型

CREATE TABLESPACE tbs_perm_02
DATAFILE ‘tbs_perm_02.dat’
SIZE 10M
REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

创建临时表空间

CREATE TEMPORARY TABLESPACE tbs_temp_01
TEMPFILE ‘tbs_temp_01.dbf’
SIZE 5M
AUTOEXTEND ON;

此CREATE TABLESPACE语句创建一个名为tbs_temp_01的临时表空间，其中有一个名为tbs_temp_01.dbf的临时文件。

创建撤销表空间

CREATE UNDO TABLESPACE tbs_undo_01
DATAFILE ‘tbs_undo_01.f’
SIZE 5M
AUTOEXTEND ON
RETENTION GUARANTEE;

这个CREATE TABLESPACE语句创建一个名为tbs_undo_01的撤销表空间，其大小为5MB，并且有一个名为tbs_undo_01.f的数据文件。

-查看所有表空间

select * from user_tablespaces

–查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system

select username from dba_users;

–查看你能管理的所有用户！

select * from all_users

–查看当前用户信息

select * from user_users;

–查看表所属的变空间

select table_name 表名 ,tablespace_name 所使用表空间 from user_tables;

查看所有表所属的表空间

SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES;

–给已存在的用户指定一个表空间

alter user username default tablespace userspace;

–创建用户的时候指定表空间

create user username identified by userpassword default tablespace userspace;

–查看当前用户所在表空间

select username,default_tablespace from user_users;

创建表指定表空间

create table tablename(id int) tablespace tablespacename;

删除表空间

删除空的表空间，但是不包含物理文件

drop tablespace tablespace_name;

–删除非空表空间，但是不包含物理文件

drop tablespace tablespace_name including contents;

–删除空表空间，包含物理文件

drop tablespace tablespace_name including datafiles;

–删除非空表空间，包含物理文件

drop tablespace tablespace_name including contents and datafiles;

–如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS

drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

以system用户登录，查找需要删除的用户：
–查找用户

select *　from dba_users;

–查找工作空间的路径

select * from dba_data_files;

–删除用户

drop user 用户名称 cascade;

–删除表空间

drop tablespace 表空间名称 including contents and datafiles cascade constraint;

例如：删除用户名成为ABC，表空间名称为ABC
–删除用户，及级联关系也删除掉
drop user ABC cascade;
–删除表空间，及对应的表空间文件也删除掉
drop tablespace ABC including contents and datafiles cascade constraint;

删除无任何数据对象的表空间：
首先使用PL/SQL界面化工具，或者使用oracle自带的SQL PLUS工具，连接需要删除的表空间的oracle数据局库。
确认当前用户是否有删除表空间的权限，如果没有 drop tablespace,请先用更高级的用户(如sys)给予授权或者直接用更高级的用户。
用drop tablespace xxx ，删除需要删除的表空间。
删除有任何数据对象的表空间
使用drop tablespace xxx including contents and datafiles;来删除表空间。
注意事项：
如果drop tablespace语句中含有datafiles,那datafiles之前必须有contents关键字,不然会提示ora-01911错误

更改临时表空间

查看当前表空间位置、大小、是否自动扩展

select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;

创建中转临时表空间

SQL> create temporary tablespace TEMP1 TEMPFILE ‘C:\app\Administrator\oradata\orcl\temp02.DBF’ SIZE 30M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

改变缺省临时表空间 为刚刚创建的新临时表空间temp1

SQL > alter database default temporary tablespace temp1;

删除原来临时表空间

SQL > drop tablespace temp including contents and datafiles;

重新创建临时表空间

SQL > create temporary tablespace TEMP TEMPFILE ‘C:\app\Administrator\oradata\orcl\temp01.DBF’ SIZE 30M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

重置缺省临时表空间为新建的temp表空间

SQL > alter database default temporary tablespace temp;

删除中转用临时表空间

SQL > drop tablespace temp1 including contents and datafiles;

表空间查询状态

select file#, status from v$ datafile where ts#=(select ts# from v$tablespace where name = ‘xxx’);

select name, status from v$ datafile where ts# = (select ts# from v$tablespace where name = ‘xxx’);

select tablespace_name,status from dba_tablespaces where tablespace_name=‘xxx’;