Category Archives: dba

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 performance from 12 hours to 1.5 hour.”

a few lines below I mentioned :
this parameter often leads to an ORA-4030, even when plenty of memory available, for some obscure reasons

I think last sentence is quite interresting, too.

Well, I must say that I finally opted for a more maintenable solution :
no more hints, no more undocumented parameter, but parallel processing up to 16 threads on a 4 cpus server.

As discussed in the iTar, a supported way to increased the maximum pga memory per single sql query is to increase the degree of parallelism.

As a rule of dumb, if you can avoid hidden parameters, avoid them!

see you soon @ SF

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 or PERFSTAT… I do not want to have old stuff in my system tablespace neither.

What is missing by schema import ? profiles, roles, tablespaces, users, public synonym, public database link, privileges

1) generate create statement profiles, roles, tablespaces, users, public synonym, public database link, privileges

for example with toad or with dbms_metadata. I am using sql + spool. Also possible is PLSQL.

2) export database with OWNER=user1,user2,… so all your users but not SYS, SYSTEM, PERFSTAT, DBSNMP, WMSYS, TSMSYS. Only your own users, not the one created by oracle

3) create a new db

4) create profiles, roles, tablespaces, users on the new db

5) grant dba to public !!! yes. a bit creasy, but it is convenient to do the import without warning/errors.

6) import

7) create the public synonym, public database link, privileges

8) revoke dba from public (!)

9) recompile the db

Well, I have written all that in a script, so migrating a db is no longer a problem to me :-) I can do 7.3 –> 10.2 migration. And I am sure my db is clean. I have undo and temporary tablespace. I can have Java or ASM. I have only 10.2 system objects in my 10.2 database. Since I am using exp/imp, it is no problem to change os/server/domain/bitwordsize.

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 on my oracle7 database? I will get ORA-00904: invalid column name

ok, I will then select only the columns that exist! than select from dba_tablespaces

t.sql:
set termout off
def logging=””
def extent_management=””
def allocation_type=””
def segment_space_management=””
def retention=””
def bigfile=””
col logging new_v logging
col extent_management new_v extent_management
col allocation_type new_v allocation_type
col segment_space_management new_v segment_space_management
col retention new_v retention
col bigfile new_v bigfile
select ‘,logging’ logging from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’LOGGING’;
select ‘,extent_management’ extent_management from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’EXTENT_MANAGEMENT’;
select ‘,allocation_type’ allocation_type from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’ALLOCATION_TYPE’;
select ‘,segment_space_management’ segment_space_management from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’SEGMENT_SPACE_MANAGEMENT’;
select ‘,retention’ retention from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’RETENTION’;
select ‘,bigfile’ bigfile from dba_tab_columns where owner=’SYS’ and table_name=’DBA_TABLESPACES’ and COLUMN_NAME=’BIGFILE’;
set termout on ver off
select tablespace_name, status, contents &logging &extent_management &allocation_type &segment_space_management &retention &bigfile from dba_tablespaces;

let’s try

SYS@LSC69 AS SYSDBA/7.3.4.5
SQL> @t
TABLESPACE_NAME STATUS CONTENTS
——————– ——— ———
SYSTEM ONLINE PERMANENT

SYS@LSC65 AS SYSDBA/8.1.7.4
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO
——————– ——— ——— ——— ———- ———
SYSTEM ONLINE PERMANENT LOGGING DICTIONARY USER

SYS@LSC67 AS SYSDBA/9.2.0.6
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN
——————– ——— ——— ——— ———- ——— ——
SYSTEM ONLINE PERMANENT LOGGING DICTIONARY USER MANUAL

SYS@LSC63 AS SYSDBA/10.2.0.1
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG
——————– ——— ——— ——— ———- ——— —— ———– —
SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO

one script for any version

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 gaston identified by lagaffe quota 1k on users default tablespace users;

User created.

SQL> grant create table to gaston;

Grant succeeded.

SQL> whenever sqlerror exit sql.sqlcode
SQL> create table gaston.x as select * from all_objects;
create table gaston.x as select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘USERS’

Disconnected from …
$ echo $?
0

1536 in binary is 11000000000, so the 8 leftmost bits gives 0, which is definitely not fine in your unix code!

well, there is also a return code from the os, but it stopped working in 10g. it is the _RC defined variable

SQL> host ls /xxx
ls: 0653-341 The file /xxx does not exist.

SQL> def _rc
DEFINE _RC = “2″ (CHAR)

many users asked “how to get the returned code of a procedure”. Well, this is a faq, a procedure is returning nothing. but you could have an out variable, then exit that code.

SQL> create or replace procedure p(o out number) is begin o:=1; end;
2 /

Procedure created.

SQL> var rc number
SQL> exec p(:rc)

PL/SQL procedure successfully completed.

SQL> exit :rc
Disconnected …
$ echo $?
1

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 values may vary.

OK, we cannot use sysdate in mviews. What else should I say?

Well, some are less evident to find out!

Some limit cases:
to_date(’2000′,’YYYY’) is non deterministic, it returns the 1st of current month, 2000, f. ex. 2000-07-01 or 2000-08-01
to_char(date ’2000-01-01′,’DAY’) is non deterministic, it can deliver SATURDAY or SAMSTAG
to_char(date ’2000-01-01′,’DAY’,’NLS_DATE_LANGUAGE=american’) is deterministic
to_char(date ’2000-01-01′,’D’) is non deterministic, it can deliver 6,7,1 or 2, depending what your territory is (in Bangladesh, the week starts on friday)
timestamp ’2005-10-30 02:30:00 Europe/Zurich’ is per default non deterministic, if you try it in summer, you will get time offset +02:00, and in winter you will get +01:00, because the period 02:00-02:59, Oct 30th, exists in both time zones. This is called time boundaries. I can make it deterministic by setting ERROR_ON_OVERLAP_TIME in the session, in which case boundaries will be rejected.

I can always define a function as deterministic, at my own risk…

For exemple if I have t(id,gender) {1,male;2,female}, I could have a function

f(id)=select gender from t where t.id=f.id;

and I could define it as deterministic. However, if I decide to set id=2 for female later, I am prepared to have corrupted data, wrong results, inconsistencies and even ORA-600 errors.

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 t66(n);

Index created.

SQL> insert into t66 values (null);

1 row created.

SQL> insert into t66 values (null);

1 row created.

The nulls are not indexed. In that post today, the user is using a two column index, and do not want to enforce that constraint when one of the column is null. No problem, we can use FBI to enforce this.

create index i on t(decode(col2,null,null,col1), decode(col1,null,null,col2));

so the index will contain only entries were both columns are not null.

Yesterday a user on forums.oracle.com wanted to have a not-different constraint, that is was only accepting entries [p;r] if [p;s] does not exist. It is quite hard to solved. I have read an interresting solution using ON-COMMIT-REFRESH materialized view with aggregates and constraints.

A long time ago, one user wanted a constraint “table should contain only one row”.

create unique index i on t(col*0);
would ensure at most one row, with col NOT NULL

I think I can do better.

Imagine the user wants always exactly one row in STATUS(code number)

create table STATUS_MAXONE(code number, n number default 0 primary key);
create view STATUS as select code from STATUS_MAXONE;
insert into STATUS values (null);
create table STATUS_MINONE(n number references STATUS_MAXONE(n));
insert into STATUS_MINONE values (0);

Now the user can update the view STATUS, but neither delete nor insert…

Sometimes, you can also have circular foreign key constraints, for example, a PERSON can only marry with someone who exists in PERSON, that is a PERSON.SPOUSE => PERSON.ID relation. This is also quite special… Actually, a person can only marry his spouse, that means if I am your SPOUSE, you are my SPOUSE! But there is no way to reference a column that may be null (we must reference a primary key).

Using unique index on FBI has the limitation of FBI: function must be deterministic. Same with triggers. complex constraints needs additional tables to enforce your business rules.

Like the p;r; accepting p;r; and q;s; but not p;s;
we could simply have a table containing p;s; with p as primary key…

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 is perfectly legal to specify a non-prefixed column of the main query in the subquery. This could be like

select * from tab where 'foo' in (select 'foo' from dict);

so the “in” clause is always true

2) “not in” does not deliver result

select sysdate from dual where 400 not in (select comm from emp);

this is because 400!=null is UNKOWN. Check the 3 state booleans operations!

it could be rewritten with a not exists, or in 10g with LNNVL

select sysdate from dual where lnnvl(400 in (select comm from emp));

3) rows are not delivered in the correct order
it is a wrong assumption to think that the rows will be delivered in a specific order if you do not specify order by.

4) table not found


SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
GreatTable                     TABLE

SQL> desc GreatTable
ERROR:
ORA-04043: object GreatTable does not exist

Whoever it is, someone created a case sensitive table name.


SQL> desc "GreatTable"
 Name                    Null?    Type
 ----------------------- -------- ----------------
 MagicColumn                      NUMBER(38,2)

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 java. The horrible regexp there is parsing df.

create or replace and compile
java source named “Df”
as
import java.io.*;
public class Df
{
public static int getFree(String args)
{
return Integer.parseInt(df(args).replaceAll(“[^0-9]*[ ]*[1 ][0-9 ][0-9][0-9][%-][^ ]* [^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*”,””).trim());
}
public static String getFS(String args)
{
return df(args).replaceAll(“[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[1 ][0-9 ][0-9][0-9][%-][^ ]* [^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ /]*”,””).trim();
}
public static String df(String args)
{
String rc = “”;
try
{
Process p = Runtime.getRuntime().exec(“/bin/df -kt “+args);
int bufSize = 4096;
BufferedInputStream bis = new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
while ((len = bis.read(buffer, 0, bufSize)) != 1)
rc += new String(buffer, 0, len-1);
p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
return rc;
}
}
}
/

Now I create two functions

create or replace
function getFree( p_cmd in varchar2) return number
as
language java
name ‘Df.getFree(java.lang.String) return int’;
/

create or replace
function getFS( p_cmd in varchar2) return varchar2
as
language java
name ‘Df.getFS(java.lang.String) return String’;
/

Ok, let’s see if my files can autoextend

select file_name, BYTES/1024 K, INCREMENT_BY*BYTES/BLOCKS/1024 INC, MAXBYTES/1024 MAXKBYTES, GETFREE(FILE_NAME) FREE, GETFS(FILE_NAME) FS
from dba_data_files

FILE_NAME K INC MAXKBYTES FREE FS
—————————————— ———- ———- ———- ———- ————–
/dbms/oracle/LSC68/data/system01LSC68.dbf 332800 25600 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/undo01LSC68.dbf 184320 2048 204800 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/sysaux01LSC68.dbf 228352 25600 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/users01LSC68.dbf 24576 2048 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/sysaud01_LSC68.dbf 4096 5120 204800 3579528 /dev/lsc68
/app/oracle/product/10.1.0.3/dbs/t.dbf 1024 0 0 851784 /dev/ora10103

Sounds good! plenty of free space to let the files grow!