Examples of syntax to export/import dbms_stats: exec dbms_stats.create_stat_table ( ownname => user , stattab => ‘temp_stats’ ) ; exec dbms_stats.export_table_stats ( ownname => user , stattab => ‘temp_stats’ , tabname => ‘mytable’ , statid => ‘Taken_7Apr2010’ ) ; exec dbms_stats.import_table_stats ( ownname => user , stattab => ‘temp_stats’ , tabname => ‘mytable’ , statid => […]
Read More dbms_stats export import optimizer statisticscp -pR logs logs`date +$d%m%y%T` Creates a timestamped copy directory named like “logs041015:44:42”.
Read More Keep log files by copying into new directory unixTo see when tables (or other objects) were created, first change your date format to display time: alter session set nls_date_format=’Dy DD-Mon-YYYY HH24:MI:SS’; Then in Oracle SQL Developer, right click on the table or object in question, select ‘open‘, and look at the ‘details‘ tab. Or alternatively still in SQL, look at the created column: […]
Read More Show date time tables were createdReplace SYSTEM with the name of the schema you are interested in: set serverout on size 999999 declare cnt number ; begin for c1 in (select owner, table_name from all_tables where owner = ‘SYSTEM’) loop execute immediate ‘select count(1) from ‘||c1.owner||’.’||c1.table_name into cnt ; dbms_output.put_line(c1.table_name||’,’||cnt) ; end loop ; end ; /
Read More PL/SQL to count from all tables in a schemaI got this annoying error when attempting to install Oracle XE: 1608: unable to create InstallDriver instance, return code -2147221164 The fix for this on my PC was: Identify your TEMP folder – choose start -> run -> type “cmd”, then type “set temp” in the command window. Navigate to this folder in windows. It […]
Read More Fix for Oracle XE 1608: unable to create InstallDriver instance, return code -2147221164check it is on with: select state, last_start_date from dba_scheduler_jobs where job_name = ‘GATHER_STATS_JOB’ ; Switch it on and off with: exec dbms_scheduler.disable(‘GATHER_STATS_JOB’) exec dbms_scheduler.enable(‘GATHER_STATS_JOB’)
Read More Automatic gather stats jobThis code cleans up old trace files, log files, core dumps, etc. It is designed to be run from cron. It takes a somewhat brutal approach by deleting files after just 7 days – good for e.g. dev/test servers, but in production you would probably want to modify this to keep files for longer. For […]
Read More shell script to clean old oracle trace and log filesFiles in /etc/cron.d only work if they are not executable files (at least that is the case on newer version of unix/linux).
Read More /etc/cron.d not working failsClick for the Course notes with scripts for a 3 day performance tuning course I did recently.
Read More Oracle Performance Tuning NotesHere’s an example of how to use partition exchange to move partitions or even entire unpartitioned tables from one schema to another. Is mean to be very fast and generate very little redo. Even more so if the partitions and tables are kept in the same tablespace. Process for doing partition exchange is like this: […]
Read More schema moves by the magic of partition exchange