Oracle 常用脚本整理

一、统计表所占空间大小（表容量）

/*一般情况下，表所占空间分为三部分：表数据、表索引、表blob字段数据*/
--1 统计含(blob字段)的单表所占用的空间

SELECT TABLE_NAME, SUM(SIZE_MB)
  FROM (SELECT A.SEGMENT_NAME AS TABLE_NAME,
               SUM(BYTES) / 1024 / 1024 SIZE_MB
          FROM USER_SEGMENTS A
         GROUP BY A.SEGMENT_NAME  --文本信息容量
        UNION ALL
        SELECT A.TABLE_NAME, SUM(B.BYTES) / 1024 / 1024 SIZE_MB
          FROM USER_LOBS A, USER_SEGMENTS B
         WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
         GROUP BY A.TABLE_NAME)   --lob字段容量
 WHERE TABLE_NAME = '&table_name'
 GROUP BY TABLE_NAME
 ORDER BY 2 DESC;

--2 统计含(blob字段)的表所占用的空间

SELECT TABLE_NAME, SUM(SIZE_MB)
  FROM (SELECT A.SEGMENT_NAME AS TABLE_NAME,
               SUM(BYTES) / 1024 / 1024 SIZE_MB
          FROM USER_SEGMENTS A
         GROUP BY A.SEGMENT_NAME  --文本信息容量
        UNION ALL
        SELECT A.TABLE_NAME, SUM(B.BYTES) / 1024 / 1024 SIZE_MB
          FROM USER_LOBS A, USER_SEGMENTS B
         WHERE A.SEGMENT_NAME = B.SEGMENT_NAME
         GROUP BY A.TABLE_NAME)   --lob字段容量
 GROUP BY TABLE_NAME
 ORDER BY 2 DESC;
 
--3 统计不含lob字段的表所占空间

SELECT A.SEGMENT_NAME AS TABLE_NAME,
       SUM(BYTES) / 1024 / 1024 SIZE_MB
FROM USER_SEGMENTS A
WHERE A.segment_type = 'TABLE'
GROUP BY A.SEGMENT_NAME ;

二、统计数据文件使用率
select b.file_name 物理文件名,
          b.tablespace_name 表空间,
          b.AUTOEXTENSIBLE,
          b.MAXBYTES,
          b.bytes / 1024 / 1024 大小M,
          (b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用M,
       substr((b.bytes - sum(nvl(a.bytes, 0))) / (b.bytes) * 100, 1, 5) 利用率      
  from dba_free_space a, dba_data_files b
 where a.file_id = b.file_id
 --and b.tablespace_name in('TBS_CRJ_SQ','TBS_CRJ_RZ_INDEX')
 group by b.tablespace_name, b.AUTOEXTENSIBLE,b.MAXBYTES, b.file_name, b.bytes
 order by b.tablespace_name

三、查看表空间是否已满
select
  a.tablespace_name,trunc(sum(a.bytes)/1024/1024) total,
  trunc(sum(a.bytes)/1024/1024 - sum(b.bytes)/1024/1024) used,
  trunc(sum(b.bytes)/1024/1024) free,
  to_char(trunc((sum(a.bytes)/1024/1024 - sum(b.bytes)/1024/1024)/(sum(a.bytes)/1024/1024),4)*100)||'%' pused,
  to_char(trunc((sum(b.bytes)/1024/1024)/(sum(a.bytes)/1024/1024),4)*100)||'%' pfree
from
  (select sum(bytes) bytes,tablespace_name from dba_data_files group by tablespace_name) a,
  (select sum(bytes) bytes,tablespace_name from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name(+)
group by a.tablespace_name
order by to_number(rtrim(pused,'%')) desc;

四、杀进程

select a.MACHINE,a.PROGRAM,a.SID,a.SERIAL#,a.STATUS,b.SQL_TEXT,
'alter system kill session'||''''||a.SID||','||a.SERIAL#||'''immediate ;'kill_sql
from v$session a , v$sql b
where a.USERNAME is not null
and a.SQL_ID=b.SQL_ID;

五、SCN查询
select version,
       to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') DATE_TIME,
       ((((((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60)+
       ((to_number(to_char(sysdate, 'MM')) - 1) * 31 * 24 * 60 * 60) +
       (((to_number(to_char(sysdate, 'DD')) - 1)) * 24 * 60 * 60) +
       (to_number(to_char(sysdate, 'HH24')) * 60 * 60) +
       (to_number(to_char(sysdate, 'MI')) * 60) +
       (to_number(to_char(sysdate, 'SS')))) * (16 * 1024)) -
       dbms_flashback.get_system_change_number) /
       (16 * 1024 * 60 * 60 * 24)) headroom
  from v$instance;

--SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x, x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm LIKE '%_external_scn_rejection_threshold_hours%';
--alter system set  "_external_scn_rejection_threshold_hours"=8;

六、数据库用户

--1、用户挂起（锁定用户）
alter user crjapp  account lock;

--2、用户解锁
alter user crjapp  account unlock;

--3、删除用户
drop user user_name cascade ;

七、权限的授予、收回

--1、授权

grant select ,insert,udate,delete on table_name to user1;

--2、收回权限

revoke select ,insert,udate,delete on table_name from user1;

--3、允许授权的对象继续授权

grant select ,insert,udate,delete on table_name to user1 with grant option;

八、表的改动

--1、重命名表名

ALTER TABLE old_tablename RENAME TO new_tablename;
--ALTER INDEX old_tablename RENAME TO new_tablename; /*重命名索引名*/

--2、修改表的字段长度
alter table table_name modify emsjjdh VARCHAR2(30);

--3、新增表字段

alter table table_name add column_name VARCHAR2(30);

--4、删除表字段

alter table table_name drop column column_name;

--5、创建同义词并授权

select 'grant all on user1.'||u.table_name||' to user2;' from dba_tables u where owner = 'user1';
select 'create or replace synonym user2.'||u.table_name||' for user1.'||u.TABLE_NAME||';' from dba_tables u where owner = 'user1';