002-Oracle表空间操作

1、查看已有的表空间情况

2、查看某一用户使用了哪些表空间

3、查看某一用户使用的默认表空间

4、查看某表所在的表空间

5、查看某索引所在的表空间

6、批量修改用户表所在的表空间

7、批量修改用户表索引的表空间

8、扩展表空间容量

9、为表空间增加数据文件

----------------------------------------------------------------------------------------------------------------------------------------------------------

 

1、查看已有的表空间情况（表空间名、表空间大小）

SELECT t.TABLESPACE_NAME, SUM(d.bytes / (1024 * 1024 * 1024)) AS FILE_SIZE_G
  FROM dba_tablespaces t, dba_data_files d
 WHERE t.TABLESPACE_NAME = d.TABLESPACE_NAME
 GROUP BY t.TABLESPACE_NAME;

------------------------------------------------------------------------------

SELECT TABLESPACE_NAME "表空间",
       To_char(Round(BYTES / 1024, 2), '99990.00')
       || ''           "实有",
       To_char(Round(FREE / 1024, 2), '99990.00')
       || 'G'          "现有",
       To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
       || 'G'          "使用",
       To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
       || '%'          "比例"
FROM   (SELECT A.TABLESPACE_NAME                             TABLESPACE_NAME,
               Floor(A.BYTES / ( 1024 * 1024 ))              BYTES,
               Floor(B.FREE / ( 1024 * 1024 ))               FREE,
               Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
        FROM   (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      BYTES
                FROM   DBA_DATA_FILES
                GROUP  BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      FREE
                FROM   DBA_FREE_SPACE
                GROUP  BY TABLESPACE_NAME) B
        WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME)
--WHERE TABLESPACE_NAME LIKE 'CDR%' --这一句用于指定表空间名称
ORDER  BY Floor(10000 * USED / BYTES) DESC;


------------------------------------------------------------------------------


select b.file_id　　文件ID,
　　b.tablespace_name　　表空间,
　　b.file_name　　　　　物理文件名,
　　b.bytes　　　　　　　总字节数,
　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,
　　sum(nvl(a.bytes,0))　　　　　　　　剩余,
　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比
　　from dba_free_space a,dba_data_files b
　　where a.file_id=b.file_id
　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes
　　order by b.tablespace_name

2、查看某一用户使用了哪些表空间

select distinct(tablespace_name) from dba_segments where owner='用户名';

select distinct(tablespace_name) from dba_segments where owner='fzy_2015';

3、 查看某一用户使用的默认表空间
select username, default_tablespace from dba_users where username= '用户名';

select username, default_tablespace from dba_users where username= 'fzy_2015';

4、查看某表所在的表空间
SELECT T.TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES T WHERE OWNER='用户名' AND TABLE_NAME='表名'

SELECT T.TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES T WHERE OWNER='fzy_2015' AND TABLE_NAME='SYS_USER'

5、 查看某索引所在的表空间
SELECT t.TABLE_NAME, T.INDEX_NAME, TABLESPACE_NAME FROM DBA_INDEXES T WHERE OWNER='用户名' AND TABLENAME='表名' AND INDEX_NAME='索引名';

SELECT t.TABLE_NAME, T.INDEX_NAME, TABLESPACE_NAME FROM DBA_INDEXES T WHERE OWNER='fzy_2015' AND TABLENAME='SYS_USER' AND INDEX_NAME='INDEX_NAME';

6、 批量修改用户表所在的表空间（生成SQL语句后执行）

-- 查看某用户下某表空间里的所有表
SELECT * FROM DBA_TABLES WHERE OWNER='VOTE2' AND TABLESPACE_NAME = 'JZPTO';

-- 生成批量修改 某用户下某表空间里的所有表 的表空间SQL语句 
SELECT 'ALTER TABLE VOTE2.' || table_name || ' MOVE TABLESPACE VOTE2;'
FROM dba_tables WHERE OWNER='VOTE2' AND tablespace_name = 'JZPTO';

7、 批量修改用户表索引的表空间（生成SQL语句后执行）

-- 查看某用户下某表空间里的所有索引
select * from dba_INDEXES where table_owner='VOTE2' and tablespace_name = 'JZPTO';

-- 生成批量修改 某用户下某表空间里的所有索引 的表空间SQL语句 
select 'alter index VOTE2.'||index_name||' rebuild online nologging tablespace VOTE2;'
from DBA_indexes WHERE table_owner='VOTE2' and tablespace_name = 'JZPTO'

8、扩展表空间容量

ALTER TABLESPACE fzy_2015AUTOEXTEND ON NEXT 1000M MAXSIZE UNLIMITED

9、为表空间增加数据文件

ALTER TABLESPACE DH
ADD DATAFILE 'D:\APP\ASUS\ORADATA\TEST\TEST02.DBF'
SIZE 1000M
AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED;

10、手工改变已存在数据文件的大小

ALTER DATABASE DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\DH.DBF' RESIZE 148480M; 

 

转载于:https://my.oschina.net/u/1176770/blog/1505121