type bsp trim cas dia flash ins tables
一、统计表所占空间大小(表容量)
/*一般情况下,表所占空间分为三部分:表数据、表索引、表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_MBFROM USER_SEGMENTS AWHERE 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)||‘%‘ pfreefrom (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) bwhere 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_sqlfrom v$session a , v$sql bwhere a.USERNAME is not nulland 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 ;
Oracle 常用脚本整理
来源: http://www.bubuko.com/infodetail-2134558.html