Script collection — the essential scripts for DBA and operations work

begin
	pkg_common_logging.init_log (
            your_package_name,
            your_subject,
            'start'
        );

-- some code
   pkg_common_logging.write_log('started: '||v_owner||'.'||v_table_name);
-- some insert
   pkg_common_logging.write_log_ins;
-- some delete
   pkg_common_logging.write_log_del;
-- some update
   pkg_common_logging.write_log_upd;
-- rest of code
   pkg_common_logging.reset_log;

exception
  when others then
   -- log errors
   PKG_COMMON_LOGGING.WRITE_LOG_ERROR;
   commit;
   raise;
end;

-- Partitionsweiser Umzug einer Tabelle in einen neuen (default) Tablespace
exec pkg_common_logging.set_log_level(3);
exec PKG_OBJ_RBLD.set_force_ddl(true);
exec PKG_OBJ_RBLD.use_orig_tbsp(false);
exec PKG_OBJ_RBLD.set_keep_intermed(true);
exec PKG_OBJ_RBLD.set_ddl_source_table('REBUILD_TABLE');
exec PKG_OBJ_RBLD.RUN_TBL_RBLD('OWNER','ORIGINAL_TABLE');

-- aus einer partitionierten Tabelle eine einfache Tabelle in einen neuen (default) Tablespace erstellen

exec pkg_common_logging.set_log_level(3);
exec PKG_OBJ_RBLD.set_force_ddl(true);
exec PKG_OBJ_RBLD.use_orig_tbsp(false);
exec PKG_OBJ_RBLD.set_remove_part(true);
exec PKG_OBJ_RBLD.RUN_TBL_RBLD('OWNER','ORIGINAL_TABLE');

-- welche Tabelle soll partitiniert werden
exec pkg_tab_part.set_ddl_source_table('owner.Table_Name_not_partitioned');
-- nach welcher Spalte soll partitioniet werden, bei welchem Datum wird begonnen
exec pkg_tab_part.create_base_tab('part_column_name',sysdate -100);
-- erstellen initialer Partitionen
exec pkg_tab_part.add_parts('MONTH|WEEK|DAY',sysdate -100, sysdate +10);
-- ausführen von dbms_redefinition
exec pkg_tab_part.run_redefinition;

CREATE OR REPLACE package body pkg_dp_transfer as

    c_version constant varchar2(32) := '01.00 / 20121105';
    c_remote_link constant varchar2(32):= 'DB_LINK';  -- hier wird der DB Link eingetragen
    type  tbsp_type is table of varchar2(128);

    -- hier werden die zu Mappenden Tablespaces angegeben
    v_ar_remap_tbsp_rule tbsp_type  := tbsp_type('TBSP1:TBSP_NEW,TBSP2:TBSP_NEW');

    -- in diesem Fall findet kein Tablespace Mapping statt
    --v_ar_remap_tbsp_rule tbsp_type  := tbsp_type();

function show_version return varchar2
is
...
end;

-- kopieren einer Tabelle
exec pkg_dp_transfer.run_table('OWNER', 'TABLE_NAME');
--Kopieren eines Schemas ohne Tabelleninhalte
exec pkg_dp_transfer.run_schema('OWNER', true);
--Kopieren eines Schemas mit Tabelleninhalte
exec pkg_dp_transfer.run_schema('OWNER', false);

-- top 5 full table scans
--

SELECT Disk_Reads DiskReads,
       Executions,
       SQL_ID,
       SQL_Text SQLText,
       SQL_FullText SQLFullText
  FROM (  SELECT Disk_Reads,
                 Executions,
                 SQL_ID,
                 LTRIM (SQL_Text) SQL_Text,
                 SQL_FullText,
                 Operation,
                 Options,
                 ROW_NUMBER ()
                 OVER (PARTITION BY sql_text
                       ORDER BY Disk_Reads * Executions DESC)
                    KeepHighSQL
            FROM (SELECT AVG (Disk_Reads) OVER (PARTITION BY sql_text)
                            Disk_Reads,
                         MAX (Executions) OVER (PARTITION BY sql_text)
                            Executions,
                         t.SQL_ID,
                         sql_text,
                         sql_fulltext,
                         p.operation,
                         p.options
                    FROM v$sql t, v$sql_plan p
                   WHERE     t.hash_value = p.hash_value
                         AND p.operation = 'TABLE ACCESS'
                         AND p.options = 'FULL'
                         AND p.object_owner NOT IN ('SYS', 'SYSTEM')
                         AND t.Executions > 1)
        ORDER BY DISK_READS * EXECUTIONS DESC)
 WHERE KeepHighSQL = 1 AND ROWNUM <= 5;

--
-- top sql's
--

  SELECT *
    FROM (SELECT sql_id,
                 sql_text,
                 cpu_time / 1000000 cpu_time,
                 elapsed_time / 1000000 elapsed_time,
                 disk_reads,
                 buffer_gets,
                 rows_processed
            FROM v$sqlarea)
ORDER BY cpu_time DESC

SELECT *
  FROM (  SELECT sql_fulltext,
                 sql_id,
                 child_number,
                 disk_reads,
                 executions,
                 first_load_time,
                 last_load_time
            FROM v$sql
        ORDER BY elapsed_time DESC)
 WHERE ROWNUM < 10;

--
-- show execution plan
--

SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR ('&sql_id', &child));

set serveroutput on;
/

DECLARE
  l_sql               VARCHAR2(500);
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
-- hier kommt das sql script!
  l_sql := 'select cp_trf.* FROM rpt_summary_x x, vic_cp_trf cp_trf '||
            'where  x.toi_no = cp_trf.toi_no(+) AND x.suffix = cp_trf.suffix(+)';

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_text    => l_sql,
                          bind_list   => sql_binds(anydata.ConvertNumber(100)),
                          user_name   => owner,
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'vic_cp_trf',
                          description => 'Tuning task for an vic_cp_trf.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'vic_cp_trf');

SELECT task_name, status FROM dba_advisor_log WHERE owner = OWNER;

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('vic_cp_trf') AS recommendations FROM dual;

SET PAGESIZE 24