【收集】一些常用的SQL语句

1、查询表空间使用

  SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
          D.TOT_GROOTTE_MB "表空间大小(M)",
          D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
          TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /
                        D.TOT_GROOTTE_MB * 100,
                        2),
                  '990.99') "使用比",
          F.TOTAL_BYTES "空闲空间(M)",
          F.MAX_BYTES "最大块(M)"
     FROM (SELECT TABLESPACE_NAME,
                  ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
                  ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
             FROM SYS.DBA_FREE_SPACE
            GROUP BY TABLESPACE_NAME) F,
          (SELECT DD.TABLESPACE_NAME,
                  ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
             FROM SYS.DBA_DATA_FILES DD
            GROUP BY DD.TABLESPACE_NAME) D
    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
    ORDER BY F.TABLESPACE_NAME;

2、创建临时表空间

create temporary tablespace clienttmp 
 TEMPFILE '/oracle/oradata/xxx/client_tmp01.dbf' SIZE 5120m 
 AUTOEXTEND ON 
 NEXT 5120 MAXSIZE 20480m 
 EXTENT MANAGEMENT LOCAL;

3、创建表空间

CREATE TABLESPACE clientdb 
 LOGGING
  datafile '/oracle/oradata/xxx/clientdb01.dbf' size 5120m 
  AUTOEXTEND ON 
  NEXT 5120m MAXSIZE 30720m 
  EXTENT MANAGEMENT LOCAL;