ORACLE - 管理表空间和数据文件

select tablespace_name,sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name;

---剩余容量（在固定大小的情况下）
select tablespace_name, count(*) as extends, round(sum(bytes) / 1024 / 1024, 2) as MB, sum(blocks) as blocks from dba_free_space group by tablespace_name;

--查看表空间与数据文件对应关系
select tablespace_name, file_name, bytes/1024/1024 from dba_data_files;

--查询表空间存放的对象及其拥有者
 select distinct segment_type, owner, tablespace_name from dba_segments where tablespace_name=‘SC‘;

create tablespace you_tabs_name datafile  ‘/opt/oracle/oradata/orcl/ts_01.dbf‘ size 50m autoextend on;

SQL> create temporary tablespace temp2 tempfile ‘/opt/oracle/oradata/temp02.dbf‘ size 100m autoextend on;
--可以为新创建的表空间增加数据文件
SQL> alter tablespace temp2 add tempfile ‘/opt/oracle/oradata/temp03.dbf‘ size 100m ;

create user youuname identified by “yourpwd” default tablespace ts_01 temporary tablespace temp2 profile default quota unlimited on ts_01

select default_tablespace, temporary_tablespace, d.username  from dba_users d where d.username=‘SCOTT‘;

alter user uname quota unlimited on  ts_01;
alter user scott default tablespace ts_01; -- 更改用户的默认表空间

alter database datafile ‘/opt/oracle/oradata/orcl/ts_01.dbf‘ offline;

表空间只读状态下，不能进行insert,update,delete，但可以删除索引和目录。
必须满足以下条件：
(1)    必须为online
(2)    不能包含任何回滚段
(3)    表空间不能在归档模式或数据发行中
SQL>alter tablespace tablespace_name read only;
恢复读写状态
SQL>alter tablespace tablespace_name read write;

更变名称后，原表空间存放的对象也同时更变，包括用户对于这表空间的权限也同步更改
条件:
(1)    只能对一般表空间更名，不能更变system，sysaux更名
(2)    必须处于online状态
SQL>alter tablespace old_name rename to new_name;

SQL> select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files;
SQL> select name,bytes/1024/1024 from v$tempfile;

SQL> create temporary tablespace temp2 tempfile ‘/opt/oracle/oradata/temp02.dbf‘ size 100m autoextend on;
--可以为新创建的表空间增加数据文件
SQL> alter tablespace temp2 add tempfile ‘/opt/oracle/oradata/temp03.dbf‘ size 100m ;

--更改用户临时表空间

  SQL> alter user scott temporary tablespace temp1;

SQL> alter database default temporary tablespace temp1;

SQL> drop tablespace temp;
然后再通过操作系统命令删除数据文件即可

alter database tempfile ‘/opt/oracle/oradata/orcl/sc_temp_01.dbf‘ resize 50M;
alter database tempfile ‘opt/oracle/oradata/oracle/sc_temp_01.dbf‘ autoextend on next 3M maxsize unlimited;

alter database tempfile ‘/opt/oracle/oradata/orcl/sc_temp_01.dbf‘ offline;//先把文件离线
alter database tempfile ‘/opt/oracle/oradata/orcl/sc_temp_01.dbf‘ drop;

--彻底删除一个临时表空间，谨慎使用
drop tablespace temp1 including contents and datafiles

--显示正在使用的undo表空间
show parameter undo_tablespace;

--查询所有undo表空间
select tablespace_name from dba_tablespaces where contents=‘UNDO‘;

SQL>create undo tablespace undotbs02 datafile ‘/xx/xxx/‘ size 2G;

SQL>alter system set undo_tablespace=undotbs02;
SQL>alter tablespace tablespace_name offline;
SQL>drop tablespace undotbs02;

SQL>SELECT SEGMENT_NAME, XACTS, V.STATUS FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = ‘undotbs02‘ AND SEGMENT_ID = USN; 

当没有数据行时才可以离线，如果有数据行，可查看是否有事务在进行： 
SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK" FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R WHERE S.TADDR = T.ADDR AND T.XIDUSN = R.USN;