Archive for the ‘SQL Tips’ Category

h1

[Oracle Sql] – set unlimited quota for user on tablespace

August 28, 2007

ALTER USER username
IDENTIFIED BY password
QUOTA UNLIMITED ON tablespace_name;

h1

[Oracle] – End Line Char [enter]

April 5, 2007

chr(10) and chr(13) is the end line char.

chr(10) || chr(13) is equivalent to [enter] key in windows.

h1

[Oracle] – SQL to list all tables

April 4, 2007

SELECT TNAME FROM TAB;

h1

[Oracle] SQL to optimize performance

April 4, 2007

You can try to run the following statement to improve the performance of table querying.

ANALYZE TABLE <table_name> COMPUTE STATISTICS;

If your table is big enough, it will drastically improve the query timing.

This may not be the best way to improve the performance as some school of thought suggested that this is ‘old-fashioned’ way of performance optimization.

Do read more if you are serious about performance optimization. I found this link which may be useful to you: http://www.stanford.edu/dept/itss/docs/oracle/10g/text.101/b10729/aoptim.htm

h1

[Oracle] – Check for any NON-NUMERIC value in a column

March 30, 2007

select *
from table_name
where
instr(translate(column_name,
‘0123456789’,
‘XXXXXXXXXX’),’X’) != length(column_name);

Only record with NON-NUMERIC value in column_name will be displayed.