Show date time tables were created

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 look at the ‘details‘ tab. Or alternatively still in SQL, look at the created column: […]

Read More Show date time tables were created
March 30, 2010

PL/SQL to count from all tables in a schema

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 ‘||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 schema
March 12, 2010

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

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”, 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 -2147221164
March 8, 2010

10 Comments

Automatic gather stats job

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

Read More Automatic gather stats job
March 5, 2010