Tablespace usage report (w/ autoextend)

If you use auto extending tablespaces in your Oracle environment and use Grid Control to monitor them, you will know that it does not take the auto extension into account. The following script will give you a breakout of your TS usage including the auto extension. I found the original script on Metalink and modified it slightly due to a flaw in the logic that would not include a TS if it was not in DBA_FREE_SPACE. This occurs when a TS has no free space left in it's current extent but has not moved into the next one yet.

select
a.tablespace_name,
SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc

Get chained rows percentage

select owner,table_name,to_char((chain_cnt/num_rows)*100,'99.99') pct_chained
from all_tables
where num_rows > 0
and owner not in ('SYS','SYSTEM','OUTLN')
order by to_char((chain_cnt/num_rows)*100,'99.99') desc

Select random row examples

http://www.petefreitag.com/item/466.cfm

RMAN commands

List current archive logs:

list archivelog all;

Delete range by sequence #:

delete archivelog sequence between N and N;

Constraint Naming Conventions

Doesn't really matter how you feel about this as long as you are consistent. I picked this up from comments on Dizwell's site and I basically like it. The only thing I change is the use of table names except in the case of foreign keys.

• Each table has a full name (employee) and a short name (emp) of three or four characters.
• Each PK is named PM_short_name, so PK_EMP.
• Each FK is named FK_parent_short_name_child_short_name and if more than one required, an additional sequence number is added as a suffix. So, FK_EMP_DEPT.
• Check constraints are CHK_short_name_column_name - if at all possible. Alternatively, CHK_short_name_reason if it fits. Example, CHK_EMP_NAME or CHK_EMP_NAME_PRESENT.
  
• Unique constraints are UQ_short_name_column where possible or UQ_SHORT_NAME_reason. For example, UQ_EMP_EMAIL.

Power Architect is the best ERD drawing tool I've found

I spent 3 days trying everything out there and Power Architect came out the winner:

http://www.sqlpower.ca/page/architect

Free, simple, saves ERD as PDF, reverse engineers, Java based so it will run on anything. A little buggy here and there but overall a great tool.

Replace CRLF with CHR(13) to preserve new lines in Oracle

This example uses Perl to look for a CRLF and replace it with the the proper ASCII, it also concatenates it for Oracle:

$text =~ s/(?:\r\n|[\r\n])/' || chr(13) || '/g;

So, if you wanted to insert the following and preserve line breaks...

Line 1
Line 2

...you can generate the following SQL:

insert into table values ('Line 1' || chr(13) || 'Line 2');