Blogs I Frequent

Vamshi : http://saivamsi.blogspot.com/

SQL Queries : Who is taking all the cpu ?

column sid for 999;
column os_user format a15;
column logon_time format a15;
rem Query vraagt om unix proces id en geeft oracle SID
select s.sid SID, s.osuser OS_USER, s.status STATUS,p.spid OS_PID,
to_char(s.logon_time, 'DD-MON-YYYY HH24:MI:SS') LOGIN_TIME,
s.program PROGAMMA
from v$session s, v$process p
where s.paddr = p.addr
and p.spid='&nr'
/
rem Query vraagt om oracle SID en geeft unix process id
select p.spid PROCESS, s.osuser OS_USER, s.status STATUS,s.sid SID,
to_char(s.logon_time, 'DD-MON-YYYY HH24:MI:SS') login_time,
s.program PROGRAM
from v$session s, v$process p
where s.paddr = p.addr
and s.sid='&nr'

SQL Queries : Database Links

Database link is a db object which enables the access of objects between databases. Databases that are linked can be oracle or different one.

Steps to Create Database Link

Step 1: Create an entry in tnsnames.ora file for service naming.
test_link =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
Note : If you are working in Client - Server setup for database then create on at server as well

Step 2: Connect to database with DB Admin user privileges.

Conn dbuser/dbpwd

Step 3: Create database link.

CREATE DATABASE LINK TEST_DB_LINK
USING 'TEST_LINK';

Step 4: Using database links.

SELECT * FROM EMP@TEST_DB_LINK;


Note: To upgrade the database links existing in the schema execute the below statement
EXEC DBMS_DBLINK.UPGRADE;

SQL Queries : Meta Data

DBMS_METADATA

http://www.psoug.org/reference/dbms_metadata.html

SQL Queries : Recursive CPU Usage

Query:

SELECT A.NAME, A.VALUE / B.VALUE CPU_USAGE

FROM V$SYSSTAT A, V$SYSSTAT B

WHERE A.NAME = 'RECURSIVE CPU USAGE'

AND B.NAME = 'CPU USED BY THIS SESSION';

Gives the "amount" of cpu usage for recursive. It includes SQL and PL/SQL. PL/SQL code can lead to high values for this statistic, and thus "mask" the actual state for SQL-statements.

Oralce PL/SQL : Creating calendar table

create table calendar(c_date date);

insert into calendar
select to_date(to_char(19900101), 'yyyymmdd') + lvl d
from ( select level -1 lvl
from dual
connect by level <= 20000);

Oracle Reports 10g : Inline HTML Formatting

Inline HTML formatting is a technique wherein, text displayed in a single text box can be of different formats. This formatting is taken care at run time by the settings done at design time.

Steps to create an example of inline html formatting in a text field.

• Open reports builder and connect to a schema.
• Navigate to layout builder option and create a text item.
• Copy the html code of the formatted text into the text item.
• Navigate to the text item and open property inspector.
• Set the "Contains HTML Tags" option to 'Yes'.
• Use the Paper Design to view the formatted text display.