How big was my database last month

If you backup your database at least weekly, you could check the datafile size from your backup history. Without catalog : select to_char(d,'”W”IW-IYYY’) compl, sum(b)/1024/1024/1024*8192 GB from ( select max(DATAFILE_BLOCKS) b, trunc(completion_time,’IW’) d from v$backup_datafile group by FILE# ,trunc(completion_time,’IW’) ) group by d order by d; COMPL GB ——– —— W30-2012 3.73 W31-2012 4.84 W32-2012 […]

accent insensitive regexp

Ever wanted to find an accent insentive expression like “bébé” in a column ? Maybe you tried to list all possible accents. But Posix has the class for you, the list of éèëê could be refered as [=e=] SELECT * FROM TAB WHERE REGEXP_LIKE(C,’b[[=e=]]b[[=e=]]’) not only [=e=] is easier to read and to type, but […]

return code and sqlplus

Calling a shell script from within sqlplus is buggy… I have reported bug 3798918 in 10.1.0.2 (back in 2004) and bug 13349119 in 11.2.0.3 because some metalink guru closed 3798918 as not reproducible. As written in return code, host does not return the correct code SQL> host exit 7 SQL> def _RC DEFINE _RC = […]

How big was my table yesterday

Oracle saves a lot of information on your behalf. Whenever you get yourself an AWR reported, you access some historic tables (included in the Diagnostic Pack). Those tables could also be accessed manually. SELECT savtime,owner,object_name,rowcnt,blkcnt FROM sys.WRI$_OPTSTAT_TAB_HISTORY w, dba_objects o WHERE o.owner=’SCOTT’ AND o.object_name=’EMP’ and o.object_id = W.OBJ# ORDER BY o.owner, o.object_name, w.savtime; SAVTIME OWNER […]