Some common scripts of Schema ####################################################################################################################################################################################################################################################################################### ############### Getcode.sql - get a stored procedure, package, function code script set feed offset linesize 1000set Termout Offset Linesize 1000set Trimspool OnSet Verify Offspool & 1..sqlprompt set define Offselect decode (TYPE || '-' || to_char (line, 'fm99999),' package body-1 ',' / '|| chr (10), null) || Decode (Line, 1,' Create OR replace ',' ') || text text from user_sourcewhere name = upper (' && 1 ') order by type, line; prompt / prompt set define onspool offset feedback onset heading onset termout onset linesize 100 ######### ######################################################################################################################################################################################################################################################################################################## # getallcode.sql - so obtained procedures, packages, the function code script set termout offset heading offset feedback offset linesize 50spool xtmpx.sqlselect '@getcode' || object_namefrom user_objectswhere object_type in ( 'pROCEDURE', 'fUNCTION', 'pACKAGE' ) / spool offspool getAllcode_install.sqlselect '@' || Object_nameFrom User_ObjectSwhere Object_type i n ('procedure', 'function', 'package') / spool offset heading onset feedback online linesize 130set termout on@xtmpx.sql######################################################################################################################################################################################################################################################## ############################## GetaView.sql - get a view script set heading offset feedback offset linesize 1000set trimspool onset verify offset termout offset embedded onset long 50000column column_name format a1000column text format a1000spool & 1..sqlprompt create or replace view & 1 (select decode (column_id, 1, '', ',') || column_name column_namefrom User_tab_columnswhere Table_name = Upper ('& 1'
) Order by column_id / prompt) assselect textFrom user_viewswhere view_name = Upper ('& 1') / prompt / spool offset heading overset feedback onset verify online termout on ########### ################################################################################ set heading offset feedback offset linesize 1000set trimspool onset verify offset termout offset embedded onspool tmp.sqlselect '@getaview' || view_namefrom user_views / spool offset termout onset heading onset feedback onset verify on @ tmp ########### ########################################## Gettrig.sql - obtaining trigger scripts set heading offset feedback offset linesize 1000set trimspool onset verify offset termout offset embedded onspool & 1..sqlselect 'create or replace trigger "' || trigger_name || '"' || chr (10) || decode ( Substr (Trigger_Type, 1, 1), 'A', 'After', 'B', Before ',' I ',' INSTEAD OF '|| CHR (10) || Triggering_Event || CHR (10) ||'On "' || table_owner || '" "' || Table_name || '" || CHR (10) || Decode (INSTR (Trigger_Type,' Each Row '), 0, NULL,' For Each ROW ') || CHR (10), trigger_bodyfrom user_triggerswhere trigger_name = Upper (' & 1 ') / prompt feeding on ################################################################################ ######################################### ionze.sql - Analyze the tables and indexes under a user (the big table will be analyzed by the way)
set serveroutput on size 100000declare v_per number (3); v_start number: = dbms_utility.get_time; v_end number; beginfor rec in (select segment_name, segment_type, ceil (sum (bytes) / 1024/1024) segment_size from user_segments group by segment_name, segment_type ) loop if rec.segment_type = 'INDEX' then dbms_stats.gather_index_stats (ownname => '???', - change it own INDNAME => rec.segment_name); - dbms_output.put_line (rec.segment_name || '' || REC.SEGMENT_SIZE || 'M' || CEIL ((DBMS_UTILITY.GET_TIME - V_START) / 100) || 's'); v_start: = dbms_utility.get_time; elsif rec.segment_type = 'table' Ten Case When Rec .SEGMENT_SIZE <32 THEN V_PER: = 100; When Rec.SEGMENT_SIZE <320 THEN V_PER: = 10; Else V_Per: = 1; END CASE; DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '???', tabname => Rec.segment_name Estimate _PERCENT => v_per, METHOD_OPT => 'FOR ALL INDEXED COLUMNS'); - dbms_output.put_line (rec.segment_name || '' || rec.segment_size || 'm' || ceil ((dbms_utility.get_time - v_start) / 100) || 's'); v_start: = dbms_utility.get_time; end if; end loop;
/ ################################################################## ############## print_table - a line of longitudinal create or replaceprocedure print_table (p_query in varchar2) AUTHID CURRENT_USERis l_theCursor integer default dbms_sql.open_cursor; l_columnValue varchar2 (4000); l_status integer; l_descTbl dbms_sql. desc_tab; l_colCnt number; begin dbms_sql.parse (l_theCursor, p_query, dbms_sql.native); dbms_sql.describe_columns (l_theCursor, l_colCnt, l_descTbl); for i in 1 .. l_colCnt loop dbms_sql.define_column (l_theCursor, i, l_columnValue, 4000) ; end loop; l_status: = dbms_sql.execute (l_theCursor); while (dbms_sql.fetch_rows (l_theCursor)> 0) loop for i in 1 .. l_colCnt loop dbms_sql.column_value (l_theCursor, i, l_columnValue); dbms_output.put_line (rpad (l_desctbl (i) .COL_NAME, 30) || ':' || l_columnvalue; end loop; dbms_output.put_line ('---------------'); - Notes if the output is more, Increase dbms_output.enable (value) end loop; Exception when otHEN DBMS_SQL.CLOSE_CURSOR (L_Thecursor); Raise; End; / Grant Execute on print_table to public;
- The script is implemented into a transverse vertical display example: a row is shown below: ADMIN_MEMBER_ID: dealexpressVIEW_NAME: Deal ExpressBUSINESS_TYPE: 5FIRST_NAME: TimLAST_NAME: HortonJOB_TITLE: PROVINCE: WisconsinCOUNTRY: USPHONE_COUNTRY: 1920PHONE_AREA: 623PHONE_NUMBER: 9528FAX_COUNTRY: 1920FAX_AREA: 623FAX_NUMBER: 9528MOBILE_NO: ZIP : 53925Address: 215 Robbins Rdcity: Columbus ############################################################################################################################################################################################################################################################################ See the object space user mode usage create or replaceprocedure show_space (p_segname in varchar2, p_owner in varchar2 default user, p_type in varchar2 default 'TABLE', p_partition in varchar2 - ############# show_space Default Null) Authi Current_USERAS l_free_blks number; l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_LastUsedExtFileId number; l_LastUsedExtBlockId number; l_LAST_USED_BLOCK number; procedure p (p_label in varchar2, p_num in number) is begin dbms_output.put_line (rpad (p_label, 40, '.' ) || p_num); end; begin for x in (select TableSpace_name from user_tablespaces where tablespace_name =
(Select tablespace_name from user_segments where segment_type = p_type and segment_name = p_segname and SEGMENT_SPACE_MANAGEMENT <> 'AUTO')) loop dbms_space.free_blocks (segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, freelist_group_id => 0, free_blks => l_free_blks); end loop; dbms_space.unused_space (segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, Unused_bytes => l_unused_bytes, last_used_extent_file_id => l_lastusedextFileId, last_used_extent_ BLOCK_ID => l_LastUsedExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK); p ( 'Free Blocks', l_free_blks); p ( 'Total Blocks', l_total_blocks); p ( 'Total Bytes', l_total_bytes); p ( 'Total MBytes', trunc ( l_total_bytes / 1024/1024)); p ( 'Unused Blocks', l_unused_blocks); p ( 'Unused Bytes', l_unused_bytes); p ( 'Last Used Ext FileId', l_LastUsedExtFileId); p ( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); 'LAST USED block', L_last_USED_BLOCK; END;
