General DBA Scripts

Create a Primary key column on a table were this is not yet available

DECLARE TYPE oracle_tables IS VARRAY(20) OF VARCHAR2(30); my_tables oracle_tables; v_table_name VARCHAR2(60); v_pk_name VARCHAR2(30); v_schema VARCHAR2(30); BEGIN — Put in your schema name here v_schema := ‘your_schema’; — Put in your tables you want extend here my_tables := oracle_tables(‘table01’, ‘table02’, ‘table03’, ‘table04’, ‘table05’, ‘table06’, ‘table07’, ‘table08’, ‘table09’, ‘table10’, ‘table11’, ‘table12’, ‘table13’, ‘table14’, ‘table15’, ‘table16’, ‘table17’, ‘table18’,…

On-line table reorganizaton using the DBMS_REDEFINITION package

——————————————————————— — Connect as SYSDBA to grant privs to scott… ——————————————————————— connect / as sysdba grant execute on dbms_redefinition to scott; grant dba to scott; ——————————————————————— — Create new empty interim table… ——————————————————————— connect scott/tiger CREATE TABLE emp_work AS SELECT * FROM emp WHERE 1=2; ——————————————————————— — Test if table can be redefined… ——————————————————————— EXEC…

Database cursor usage (open_cursors parameter)

prompt Open Cursor Limit col value format a30 head “Open Cursors Parameter Value” select value from sys.v_$parameter where name = ‘open_cursors’; prompt Summary of Current Curor Usage col name format a25 select min(value) min, max(value) max, avg(value) avg from sys.v_$sesstat where statistic# = (select statistic# from sys.v_$statname where name like ‘opened cursors current’); prompt Top…

Log Miner – extract undo statements from log files

— Create a dictionary file — (init.ora parameter utl_file_dir must be set) exec dbms_logmnr_d.build(‘mydictfile’, ‘/tmp’); — Register log files, can be from a different db — (NEWFILE=start new list/ ADDFILE=add next file) exec dbms_logmnr.add_logfile( LogFileName => ‘/app/oracle/arch/oradba/log_1_0000000027.oradba’, Options => dbms_logmnr.NEW); exec dbms_logmnr.add_logfile( LogFileName => ‘/app/oracle/arch/oradba/log_1_0000000028.oradba’, Options => dbms_logmnr.ADDFILE); — Start the logminer session exec dbms_logmnr.start_logmnr(DictFileName…

Limit resources using the Database Resource Manager

exec dbms_resource_manager.create_pending_area; exec dbms_resource_manager.delete_plan_cascade(‘night_plan’); exec dbms_resource_manager.create_plan(‘night_plan’, ‘Plan to use after 6PM’); exec dbms_resource_manager.create_consumer_group(‘batch’, ‘Group for batch reports’); exec dbms_resource_manager.create_plan_directive(‘night_plan’, ‘batch’, ‘Rules for overnight batch jobs’, – cpu_p1 => 75, parallel_degree_limit_p1 => 20); exec dbms_resource_manager.create_plan_directive(‘night_plan’, ‘OTHER_GROUPS’, ‘Rules for overnight batch jobs’, – cpu_p1 => 25, parallel_degree_limit_p1 => 0, – max_active_sess_target_p1 => 1); exec dbms_resource_manager.validate_pending_area; exec dbms_resource_manager.submit_pending_area;…

Demonstrate database and schema level triggers

create or replace trigger restrict_login after logon on database — after logoff on database — after servererror on database — after startup on database — after shutdown on database — after create on database — after drop on database — after alter on database declare flag number := 0; begin select 1 into flag from…

Log all database errors to a table

drop trigger log_errors_trig; drop table log_errors_tab; create table log_errors_tab ( error varchar2(30), timestamp date, username varchar2(30), osuser varchar2(30), machine varchar2(64), process varchar2(8), program varchar2(48)); create or replace trigger log_errors_trig after servererror on database declare var_user varchar2(30); var_osuser varchar2(30); var_machine varchar2(64); var_process varchar2(8); var_program varchar2(48); begin select username, osuser, machine, process, program into var_user, var_osuser, var_machine,…