The correlation between decrease SGA and increase lock

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.

The importance of groupping temporary tablespace

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:

Alter tablespace TEMP TABLESPACE GROUP GTEMP;

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;

@gtemp.sql

EXP using where clause

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

F:\Export>EXP “‘/ AS SYSDBA'” TABLES=UCEADMIN01.AUDIT_TRAIL QUERY=’WHERE SUBSTR( AT_DATE,1,4)=”2011”’

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

How to use variable directly from sqlplus

 

SQL> variable deptno number

SQL> exec :deptno := 10

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

How to avoid problems with blank space on sqlplus

Use

set sqlblanklines on

DATA PUMP import without export

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

 

  

 

Simple UTL_FILE_DIR test

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);

 utl_file.fclose(output_file);

 end;

/

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

 end;

/

Extracted from : http://gavinsoorma.com/2009/07/utl_file_dir-path-test/