Oracle Logbook

April 29, 2011

The correlation between decrease SGA and increase lock

Filed under: Uncategorized — Helio Dias @ 10:52 pm

A junior DBA claims that after decrease SGA  the number of locks increased.

It actually might happen if the performance associate with this DMLs decrease to a point that take so much time each operation, that increase the chance of a requests overlap.

April 25, 2011

The importance of groupping temporary tablespace

Filed under: Uncategorized — Helio Dias @ 4:00 pm

Sometimes one of your users run a bad query (often a cartesian join) and use the whole space of your temporary tablespace.

The next thing is all other users complaining due to the lack of space.

The simple solution, rather than one temporary tablespace, you simple create a group with at least two temporary tablespaces, and the maximum that one could use is half of your whole space.

First step is create the group, to do it you must create one tablespace together:

Create temporary tablespace TEMP2 TEMPFILE ‘/u01/temp2.dbf’ size 2g tablespace group GTEMP;

Then you put your former temporary tablespace at the same group:


Finally you change your users to the group:

Spool gtemp.sql;

Select ‘Alter user ‘||username|| ‘temporary tablespace GTEMP;’ from dba_users where temporary_tablespace=’TEMP’;

Spool off;


April 17, 2011

EXP using where clause

Filed under: Uncategorized — Helio Dias @ 12:54 am

Sometimes you have a log table with LONG type that you want delete most of the rows and recover the space. If wasn´t the long detail you could for instance CTAS truncate and insert back. But with the LONG, your best shot is use EXP filtering the data that you don´t want and import back


Remember to use the plics the same way as inside the execute immediate, (doubling them)

April 14, 2011

How to use variable directly from sqlplus

Filed under: Uncategorized — Helio Dias @ 10:08 pm


SQL> variable deptno number

SQL> exec :deptno := 10

SQL> select * from emp where deptno = :deptno;

April 5, 2011

How to avoid problems with blank space on sqlplus

Filed under: Uncategorized — Helio Dias @ 6:29 pm


set sqlblanklines on

DATA PUMP import without export

Filed under: Uncategorized — Helio Dias @ 4:46 pm

Is the fast way to do a refresh on test environment.

The only drawback is that you can´t import objects with long/long raw.

You will need a database link between both instances.

And you also need to remove the parameter dumpfile, and include the NETWORK_LINK parameter.

That´s all

Below is sample of usage

impdp userid=helio@TESTEuce 


NETWORK_LINK=prod schemas=uceadmin01 remap_schema=uceadmin01:testeuce remap_tablespace=dadosadv:dadostst remap_tablespace=indexadv:indextst logfile=testeimp.log DIRECTORY=f_export




April 1, 2011

Simple UTL_FILE_DIR test

Filed under: Uncategorized — Helio Dias @ 9:56 pm

CREATE OR REPLACE procedure gc_dba_utl_file_test_write

 ( path in varchar2, filename in varchar2, firstline in varchar2, secondline in varchar2)

 is output_file utl_file.file_type; begin output_file := utl_file.fopen (path,filename, ‘W’);

 utl_file.put_line (output_file, firstline);

 utl_file.put_line (output_file, secondline);




begin gc_dba_utl_file_test_write ( ‘/tmp’, ‘utl_file_test’, ‘first line’, ‘second line’ );



Extracted from :

Create a free website or blog at