Oracle
Pinning a table to memory
ALTER TABLE PLAN_TABLE CACHE; If you have configured the KEEP buffer then you can ALTER TABLE PLAN_TABLE STORAGE(BUFFER_POOL KEEP);
Check PSU version
Make sure your opatch version is at or above 10.2.0.4.5 for version 10.2 ORACLE_HOMEs 11.1.0.6.5 for version 11.1 ORACLE_HOMEs 11.2.0.1.0 for version 11.1 ORACLE_HOMEs and run the following command opatch lsinv -bugs_fixed | grep PSU sample output: $ opatch version Invoking OPatch 10.2.0.4.8 OPatch Version: 10.2.0.4.8 OPatch succeeded. $ $ opatch lsinv -bugs_fixed | grep […]
Show DBMS_SCHEDULER job information.
To show details on job run: select log_date , job_name , status , req_start_date , actual_start_date , run_duration from dba_scheduler_job_run_details To show running jobs: select job_name , session_id , running_instance , elapsed_time , cpu_used from dba_scheduler_running_jobs; To show job history: select log_date , job_name , status from dba_scheduler_job_log; show all schedules: select schedule_name, schedule_type, start_date, […]
New 11g DBMS_SCHEDULER jobs
1. ORA$AUTOTASK_CLEAN The job is created by the 11g script catmwin.sql which mentions that this job is an autotask repository data ageing job. It runs the procedure ora$age_autotask_data. 2. HM_CREATE_OFFLINE_DICTIONARY The job is created by the 11g script catmwin.sql which mentions that this is a job for creation of offline dictionary for Database Repair Advisor. […]
Gathering session and SQL information without actually turning on Oracle auditing.
01,15,30,59 * * * * /dba/inser_tp.ksh >> /dba/third_party_tools_usage.txt 2>&1 #!/bin/ksh . /dba/env $ORACLE_HOME/bin/sqlplus “/ as sysdba”
Move a datafile
If you want to move the tablespace you need to: 1 – Quiecse the tablespace (alter tablespace dba_temp_files offline) 2 – Copy the data file to the new location 3 – Rename the tablespace datafile “alter tablespace dba_temp_files rename datafile /oracle/temp2 to /oraclenew/temp2” 4 – Bring the tablespace online (alter tablespace dba_temp_files online; 5 – […]
alter database commands to get backup controlfile
BINARY: alter database backup controlfile to ‘/some/arbitrary/path’; READABLE: alter database backup controlfile to trace; alter database backup controlfile to trace as ‘/some/arbitrary/path’; Others: http://www.adp-gmbh.ch/ora/sql/alter_database.html
10046 trace
To gather 10046 trace at the session level: SQL> alter session set timed_statistics = true; SQL> alter session set statistics_level=all; SQL> alter session set max_dump_file_size = unlimited; SQL> alter session set events ‘10046 trace name context forever,level 12’; SQL> — run your select(s) — SQL> select * from dual; SQL> exit; This will produce a […]
Remove an agent target that won’t delete.
Make sure all targets are deleted in OMS – may try force deletion. Then, run as sysman in repository: exec mgmt_admin.cleanup_agent(‘:‘);