Monthly Archives: March 2010

Show date time tables were created

Posted on 30 March 2010 by Andrew Fraser

To 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 … Continue reading →

Posted in Uncategorized | Leave a comment

PL/SQL to count from all tables in a schema

Posted on 12 March 2010 by Andrew Fraser

Replace 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 … Continue reading →

Posted in Scripts | Leave a comment

Fix for Oracle XE 1608: unable to create InstallDriver instance, return code -2147221164

Posted on 8 March 2010 by Andrew Fraser

I 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”, … Continue reading →

Posted in Installs | 6 Comments

Automatic gather stats job

Posted on 5 March 2010 by Andrew Fraser

check 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’)

Posted in Performance tuning, Stats | Leave a comment