fetch comment lob

如何查詢數據字符集.txt

select * From v$nls_parameters;

批量獲取表的DDL語句腳本.txt

set feedback off
set pagesize 0
set heading off
set verify off
set linesize 200
set serveroutput on size 1000000
set trimspool on
define outputpath
accept outputpath char default '/data/temp/' prompt 'pls input output path:'
define runfile = '/data/temp/runfile.sql'

spool &runfile
prompt set feedback off
prompt set pagesize 0
prompt set heading off
prompt set verify off
prompt set linesize 500
prompt set trimspool on
prompt SET serveroutput on long 999999
prompt EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

declare
cursor cur1 is select table_name from user_tables;
mytable user_tables.table_name%TYPE;
begin
open cur1;
loop
fetch cur1 into mytable;
exit when cur1%notfound;
dbms_output.put_line('spool &outputpath'||mytable||'.sql');
dbms_output.put_line('prompt drop table '||mytable||';;');
dbms_output.put_line('select dbms_metadata.get_ddl("TABLE","'||mytable||"') from dual;');
dbms_output.put_line('prompt ;;');
dbms_output.put_line('spool off');
end loop;
close cur1;
end;
/
spool off
@&runfile

系統表空間中佔用空間的TOP9.txt

SELECT * FROM (SELECT BYTES, segment_name, segment_type, owner FROM
      dba_segments
      WHERE tablespace_name = 'SYSTEM'
      ORDER BY BYTES DESC)
WHERE ROWNUM <10

 

查找低效執行的SQL語句.txt
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

 

mmis.t_om_deptinfo
CREATE UNIQUE INDEX PK_T_OM_DEPTINFO ON T_OM_DEPTINFO
(FDEPTNO, FDATATYPE)

mmis.qcs_delivery_head
SYS_C006198

mmis.mfb_order_head
SYS_C006043

/*+ index(t_om_deptinfo,PK_T_OM_DEPTINFO) index(mfb_order_detail,IDX_BILL) index(qcs_delivery_head,SYS_C006198) index(mfb_order_head,SYS_C006043)*/

sp2-0310

TAB_COL_COMMENT_EXPORT.txt

SELECT      'COMMENT ON COLUMN '
         || OWNER || '.' || TABLE_NAME || '.' || COLUMN_NAME
         || ' IS '|| ''''||COMMENTS||''';'
  FROM   (SELECT   *
            FROM   DBA_COL_COMMENTS
           WHERE   WNER = 'FMIS' AND COMMENTS IS NOT NULL);
          

SELECT      'COMMENT ON COLUMN '
         || OWNER || '.' || TABLE_NAME
         || ' IS '|| ''''||COMMENTS||''';'
  FROM   (SELECT   *
            FROM   DBA_TAB_COMMENTS
           WHERE   WNER = 'FMIS' AND COMMENTS IS NOT NULL);

check_lob.txt

select a.owner,
       a.segment_name,
       decode (a.partition_name,
                 null, a.segment_name,
                 a.segment_name || ':' || a.partition_name) objectname,
       segment_type objecttype,
       nvl ((a.bytes / 1024 / 1024)||'M', 0) "SIZE",
       b.table_name,
       b.column_name,
       a.tablespace_name
  from (select * from dba_segments ) a,
       (select * from sys.dba_lobs ) b
where (a.segment_name=b.segment_name or a.segment_name=b.index_name) --and b.table_name = 'DBS_SO_ATTACHMENT'
and a.owner in ('CHANNEL','WM_WMS')
     order by a.owner,b.table_name,b.column_name

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-688902/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25198367/viewspace-688902/