10.2.0.1 hidden parameters

In order to get a clean database configuration, I add the following two hidden parameters in my 10.2.0.1 parameter file. Do not hurl that loud, I hear you from here! Well, as I said already about the _pga_max_size, I never recommend using hidden parameters when you can do the same without. However, I am going… Continue reading 10.2.0.1 hidden parameters

Published
Categorized as Blogroll, dba

no more tnsnames

with netca, it is easy to configure your sqlnet.ora to use LDAP instead of tnsnames.ora. The ldap.ora and sqlnet.ora are updated… than it works, sqlplus user@db is correctly looking in the ldap oracle content

restore to a new host : nid++

Great challenge today: restore to a new host from a closed noarchivelog backup on tape library. In oracle 8i and before, the only way to rename a database was to recreate the controlfile. In 9i, I could change it with nid, in 10gR2, I should never have a reason again to recreate the controlfile, because… Continue reading restore to a new host : nid++

Published
Categorized as Blogroll, dba

FAILED_LOGIN_ATTEMPTS part 2

Ref: part 1 I reported this lack of documentation on http://forums.oracle.com/forums/thread.jspa?threadID=330359 Here is my test case (take care, it will create a new db!) : SQL> startup force quiet nomount; ORACLE instance started. SQL> create database controlfile reuse extent management local default tablespace users default temporary tablespace temp undo tablespace undotbs1; Database created. SQL> @?/rdbms/admin/catalog… Continue reading FAILED_LOGIN_ATTEMPTS part 2

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… Continue reading undocumented parameter

Published
Categorized as Blogroll, dba

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… Continue reading migrate database with imp exp

Published
Categorized as Blogroll, dba

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… Continue reading select column only if it exists

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… Continue reading return code

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… Continue reading deterministic

exotic constraints

Today I read a post on metalink where the user wanted a unique constraint for not-null values… Sounds easy, because Oracle never indexes null in btree index. If I have only one column, I simply index it, it will work. SQL> create table t66 ( n number); Table created. SQL> create unique index i66 on… Continue reading exotic constraints

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… Continue reading unexpected results !

Get disk space

I just read today on sun.com that checking disk space in java will be platform independent in the java.io.File class before 2007, add a few years until it is integrated in Oracle. But I cannot wait that long, so I decided to write my own code with “df” on my AIX box. Ok, let’s do… Continue reading Get disk space