undocumented parameter

Just in case you read my success story on Don Burleson webpage about undocumented parameters. out of metalink thread 460157.996 : “I set appropriate values for pga_aggregate_target and _pga_max_size… alter system set pga_aggregate_target=6G; alter system set “_pga_max_size”=2000000000; …and I gave the query some hints “NOREWRITE FULL USE_HASH ORDERED”. As a result, it boosted my query […]

migrate database with imp exp

I prefer to use exp/imp to migrate databases. I first create a fresh new database with a new spfile, a new system tablespace, a new undo, locally managed tablespace, automatic segment space management. I do not do a full exp. I prefer a schema export. It only exports the schema that I want, not WMSYS […]

select column only if it exists

i need to display tablespace attributes SQL> select tablespace_name, status, contents, logging, extent_management, allocation_type, segment_space_management, retention, bigfile from dba_tablespaces; TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG ——————– ——— ——— ——— ———- ——— —— ———– — SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO looks fine. but what if I try that […]

return code

there is a myth of using sql.sqlcode in sqlplus whenever sqlerror exit sql.sqlcode this not ok. you should prefer whenever sqlerror exit failure or exit 1 Why? because unix return code is 8 bits long. so if you exit ora-600, you will get 88. Let’s try it $ sqlplus “/ as sysdba” SQL> create user […]

deterministic

if I create a function for a materialized view with query rewrite or for a function based index, I must create hte deterministic. f(x) = x*2 is deterministic. for a give x, f(x) will always be the same, f(5) will be always 10; always. f(y) = sysdate+y is non-deterministic. For a given y, the return […]

unexpected results !

It makes you cry! It makes you claim you have found a bug! but it is working as specified! 1) subquery refers to a column of the main query select * from emp where ename in (select ename from dept where deptno=10); the query does not complain that column does not exist in dept. It […]