ITPub博客

SELECT rowid,       DBMS_ROWID.Rowid_object(ROWID) "OBJECT",       DBMS_ROWID.Rowid_relative_fno(ROWID) "FILE",       DBMS_ROWID.Rowid_block_number(ROWID) "BLOCK",       DBMS_ROWID.Rowid_row_number(ROWID) "ROW"&nbsp...

  TOM 大师的show_space 存储过程脚本create or replace procedure show_space( p_segname_1 in varchar2,p_owner_1 in varchar2default user,p_type_1 in varchar2 default 'TABLE',p_space in varchar2 default 'AUTO',p_analyzed in varchar2default '...

临时表空间使用情况 A.tablespace_name , D.mb_total, (A.used_blocks * D.block_size) / / mb_used,SUM10241024FROM( B.name, C.block_size, (C.BYTES) / / mb_total v$tablespace B, v$tempfile C B.ts#= C.ts# B.name, C.block_size...

 select distinct   ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,KSUSEMNM module,   ob.kglnaown obj_owner, ob.kglnaobj obj_name   ,lk.kgllkc...

--  select distinct   ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,ses.ksuseunm machine,   ob.kglnaown obj_owner, ob.kglnaobj obj_name   ,pn.kg...

========Script. tfstsfgm========SET ECHO off REM NAME:TFSTSFRM.SQL REM USAGE:"@path/tfstsfgm" REM ------------------------------------------------------------------------ REM...

方式一:按照天数批量生成awr输入参数: 快照截止时间snap_date输入参数: 收集的天数输入参数: 类型(text/html)注意 :此脚本需要先保存成文件如batch_awrrpy.sql然后再sqlplus 里执行     ...

SELECT B.TABLE_NAME, C.COLUMN_NAME,C.POSITIONFROM USER_CONSTRAINTS A,USER_CONSTRAINTS B,USER_CONS_COLUMNS CWHEREA.CONSTRAINT_NAME=B.R_CONSTRAINT_NAMEAND A.TABLE_NAME = C.TABLE_NAMEANDA.CONSTRAINT...

set linesize 132column name format a30column value format a25select x.ksppinm name, y.ksppstvl value, y.ksppstdf isdefault, decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE...

select to_char(FORCE_MATCHING_SIGNATURE),count(1)  from v$sql where FORCE_MATCHING_SIGNATURE >0   and FORCE_MATCHING_SIGNATURE!= EXACT_MATCHING_SIGNATURE group by F...

统计表的高水位情况:SELECT TABLE_NAME ,  (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)"Data lower than HWM in MB"   FROM  DBA_TABLES WHERE  UPPER(owner) =UP...

表控制使用比例:select a.tablespace_name,       (sum(a.bytes) / (count(*) * 32 * 1024 * 1024 * 1024))*100  from dba_data_files a group by a.tablespace_name order by 2...

declare  VC_SQL   varchar2(200);  N_NUM NUMBER;begin  FOR I IN 1 .. 100 LOOP    for a in (select * from user_sequences) loop      VC_SQL := 'select ' || a....

1 删除无数据用分区(按照用户)declare  cnt int;  p_name varchar2(50);begin  for aa in (select t.partition_name,t.table_name               f...

1 buffer cacheselect substr(sql_text,1,500) "SQL",sql_fulltext,                                    &nb...

select  x.ksppinm  name,  y.ksppstvl  value,  y.ksppstdf  isdefault,  decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,  decode(bita...

select checkpoint_change# from v$database;select name,checkpoint_change# from v$datafile ;select a.name,a.CHECKPOINT_CHANGE# from v$datafile_header a ;select file#,online_status,change# from v$rec...

create or replace trigger tr_ddlbefore ddl on databasedeclare  l_errmsg varchar2(100) := '你无权修改,项目组!!!';begin  if (Ora_dict_obj_type = 'TABLE' or Ora_dict_obj_type='INDEX') AND ...

select 'grant unlimited tablespace to '|| r.GRANTEE ||';'  from dba_sys_privs r,dba_users u where grantee not in(select distinct grantee from dba_sys_privs where privilege='UNLIMITED TABLESPACE')...

SELECT s.sid,       s.serial#,       sl.TARGET_DESC,       sl.TARGET,       s.machine,       (select...