ORACLE表空间创建及资源使用情况分析

1 查询某个用户下所有表占用空间

select segment_name, bytes/1024/1024/1024 from dba_segments where owner = user_name;
 查询表的内存

```sql
-- 单表查询
select sum(bytes)/(1024*1024*1024) as size_GB FROM USER_SEGMENTS WHERE SEGMENT_NAME = table_name;
-- 多表查询
select segment_name, round(sum(bytes)/(1024*1024*1024) ,4) as size_GB 
from user_segments 
where segment_type = 'TABLE'
and TABLESPACE_NAME = 表空间名称
group by segment_name
ORDER BY sum(bytes)/(1024*1024*1024)  DESC; 

-- 查法2： 说明(一个BLOCK大于2KB)
SELECT OWNER,
       TABLE_NAME, 
       SUM(BLOCKS * 2)/1024  AS SIZE_MB  
 FROM ALL_ALL_TABLES 
 WHERE OWNER IN ( 'ower1', 'ower2')
 GROUP BY OWNER, TABLE_NAME 
 ORDER BY TABLE_NAME DESC

2 查询表空间文件及表空间

-- 查询表空间
select * from dba_tablespace;
-- 查询表空间文件
select * from dba_data_files where tablespace = spaceName;  -- spaceName 自己指定

  -- 查询表中单条记录平均字节数 
   SELECT  '事件地区电量' AS TABLE_NAME , 
                   round(RES1.B/ RES2.CNT_N ,4)AS 单条记录平均字节数 
 FROM
  (
      SELECT sum(BYTES)  AS B FROM DBA_SEGMENTS WHERE SEGMENT_NAME='事件地区电量' AND OWNER='SYSTEM' 
    ) RES1,
          (
    SELECT COUNT(1)  AS CNT_N  FROM SYSTEM.事件地区电量
    ) RES2;

3 新增数据文件，并且允许数据文件自动增长

ALTER TABLESPACE spaceName ADD DATAFILE
'文件位置\文件名称.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

-- 备注：文件位置可根据select * from dba_data_files where tablespace = spaceName;  查看

4 创建表空间

create tablespace spaceName LOGGING 
DATAFile '文件位置\文件名称.DBF' size 20480M
AUTOEXTEND ON 500M MAXSIZE 30720M;

10 查询表空间的数据文件

-- 查看表数据文件情况    
     SELECT A.TABLESPACE_NAME AS 表空间,
                     A.FILE_NAME AS 物理文件名 ,
                     A.FILE_ID    AS 文件ID,
                     ROUND(A.BYTES / 1024 / 1024/ 1024,4)   文件大小G,
                     NVL(B.FREE_SPACE ,0) AS 未利用空间,
                     ROUND(1- NVL(B.FREE_SPACE ,0)/ROUND(A.BYTES / 1024 / 1024/ 1024,4) ,4) as 利用率
              
     FROM DBA_DATA_FILES A 
     LEFT JOIN 
     (
     SELECT FILE_ID , 
                    TABLESPACE_NAME   ,
                   ROUND(  SUM(BYTES/1024 / 1024/ 1024) ,4) AS FREE_SPACE
     FROM  DBA_FREE_SPACE 
     GROUP BY FILE_ID , 
                         TABLESPACE_NAME 
     ) B
     ON A.FILE_ID = B.FILE_ID;

-- 根据表名找到表空间
select * from all_all_tables where table_name= TABLE_NAME;

– 查询表空间文件数量

SELECT  TABLESPACE_NAME AS 表空间名称 , COUNT(1)  AS 数据文件数量 FROM dba_data_files GROUP BY TABLESPACE_NAME; 

–查询被锁的sql语句以及其他的信息

SELECT A.OWNER,
               A.OBJECT_NAME,
               A.OBJECT_ID,
               A.DATA_OBJECT_ID,
               A.OBJECT_TYPE,
               A.STATUS,
               A.NAMESPACE,
               V.SESSION_ID,
               V.ORACLE_USERNAME,
               V.OS_USER_NAME,
               V.PROCESS,
               V.LOCKED_MODE,
               S.SQL_ID,
               S.SQL_TEXT
FROM   ALL_OBJECTS A , V$LOCKED_OBJECT V , V$SESSION V2 , V$SQL S
WHERE V.OBJECT_ID = A.OBJECT_ID
AND V.SESSION_ID = V2.SID
AND S.SQL_ID = V2.SQL_ID;