Oracle Logbook

June 25, 2008

Using DBMS_SHARED_POOL.KEEP TO PINNING

Filed under: Uncategorized — heliodias @ 6:03 pm

CREATE TABLE GC_PINNING
(OBJ_OWNER VARCHAR2(50), OBJ_NAME VARCHAR2(50),OBJ_TYPE VARCHAR2(50),
OBJ_SIZE NUMBER,PINNING_DATE DATE DEFAULT SYSDATE,obj_LOADS NUMBER);

insert into GC_PINNING
(OBJ_OWNER , OBJ_NAME,OBJ_TYPE,
OBJ_SIZE,PINNING_DATE ,obj_LOADS)
(select
OWNER , NAME,TYPE,
sharable_mem,
sysdate
,LOADS
from v$db_object_cache where
type in (‘PACKAGE’,’PACKAGE BODY’,’TRIGGER’,’SEQUENCE’,’PROCEDURE’)
and nvl(sharable_mem,0)>0 and kept=’NO’);

CREATE OR REPLACE PROCEDURE GC_PINNER AS
/*
gc_Pinner by Helio Dias.com
Subject : pinning object that had been saved on GC_PINNING table
June 24, 2008,
*/
cursor c_pinning is
select OBJ_OWNER||’.’||OBJ_NAME OBJ_NAME,OBJ_TYPE from GC_PINNING;
V_NAME VARCHAR2(50);
V_TYPE VARCHAR(50);
begin
open c_pinning;
loop
fetch c_pinning into v_name,v_type;
exit when c_pinning%notfound;
IF V_TYPE=’FUNCTION’
THEN V_TYPE:=’P’;
END IF;
IF V_TYPE=’SEQUENCE’
THEN V_TYPE:=’Q’;
END IF;
IF V_TYPE=’TRIGGER’
THEN V_TYPE:=’R’;
END IF;
V_TYPE:=SUBSTR(V_TYPE,1,1);
dbms_shared_pool.keep(V_NAME,V_TYPE);
end loop;
END;
/

Advertisements

June 23, 2008

Oracle interview questions

Filed under: Uncategorized — heliodias @ 3:53 pm

Usually I see these type of question on forums, so I decide to put some basics questions:

What is the difference between cold backup and hot backup?

What is the difference between and constraints and triggers?

What is normalization? what is the advantage of normalization (briefly)

What is structure of Database?

What is the difference between primary key, unique key, sorrougate key?

What is the different between Stand Alone Procedure and Package?

How create database statistics?

What is Mutating Trigger?

What is Full Table Scan?

What is Tablespace Quota? what is usefull views?

You are going to out of tablespace space , what are you gonna do?

Describe a dead lock

Is lock a bad thing?

What is ORA-1555?

What is ORA-0600?

What is ORA-04031?

Difference of Physical and Logical Backup?

CBO Means?

What is difference between SQLNET.ORA AND TNSNAMES.ORA AND LISTENER.ORA?

Most of this questions had been extracted from :
http://www.geekinterview.com/Interview-Questions/Oracle

June 13, 2008

Oracle Architecture and Tuning on AIX

Filed under: Uncategorized — heliodias @ 2:51 pm

http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100883

45 pages of concise material

Finding ORA-04031 When not appear on alert

Filed under: Uncategorized — heliodias @ 1:52 pm

Sometimes just the user session receive the message, but it not logged on alert.
On this X$ Table you can see this.

select sum(kghlunfu) Total_4031 ,kghlunfs Size_miss from x$kghlu group by kghlunfs;

List of all X$ tables

Filed under: Uncategorized — heliodias @ 1:42 pm

select kqftanam from x$kqfta;

June 7, 2008

Crack Oracle Password

Filed under: Uncategorized — heliodias @ 2:57 pm

If you have the hash password, you can try this:

http://www.red-database-security.com/whitepaper/oracle_password_cracker.html

June 6, 2008

Find free space on tablespace x Allocated space %

Filed under: Uncategorized — heliodias @ 5:10 pm

Today i answered this question on OTN,

select * from (
Select tbs2,free_space,allocated,trunc((free_space/allocated)*100) pct_free from
(select tablespace_name tbs1,sum(bytes)/1024/1024 free_space from dba_free_space
group by tablespace_name),
(select tablespace_name tbs2,sum(bytes)/1024/1024 allocated from dba_data_files
group by tablespace_name)
where tbs2=tbs1(+) ) where pct_free <&min_pct
and TBS2 not in
(select tablespace_name from dba_TABLESPACES where contents=’UNDO’)
order by 3;

June 2, 2008

11g new features

Filed under: Uncategorized — heliodias @ 5:27 pm

Good doc.

http://www.ahmedbaraka.com/computer/docs/oracle_11g_nf_1.0.pdf

Correcting invalid spfile parameters

Filed under: Uncategorized — heliodias @ 1:54 pm

A common way is create pfile from spfile .

But a easy way is starting with PFILE and SPFILE at the same time. on PFILE you put the calling to SPFILE and the CORRECTED parameter

Read the complete article at:
http://www.shutdownabort.com/quickguides/spifles_and_pfiles_trick.php

How to find duplicate record in Oracle

Filed under: Uncategorized — heliodias @ 1:23 pm

It´s easy, you just query twice your table when rowid are lower and all columns are the same. e.g.

select t1.rowid from table1 tb1,table1 tb2
where t1.rowid<t2.rowid
and
t1.col1=t2.col1 and
t1.col2=t2.col2 and
t1.col3=t2.col2 and
t1.col4=t2.col2 and
t1.col5=t2.col2 and …….

Blog at WordPress.com.