如何查詢數據字符集.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/