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.

Oracle Reports 10g : Creating Drill Down Reports

The technique of calling one report from another report is called as Drill Down Reports.

Steps to build a simple department - employee Drill Down Report.

(Example is using default scott schema)

1) Create a simple Employee report.

• Open reports builder and connect to a schema.
• Click on Add module in navigation bar and select wizard option.
• Select emp table and give the query like select * from emp where deptno = :deptno
  
• Create a simple tabular report.
• Save the file as emp.rdf(use Shift+Crtl+k before saving).

2) Create a simple Department report.

• Open reports builder and connect to a schema.
• Click on Add module in navigation bar and select wizard option.
• Select dept table and create a simple tabular report.
• Select department number column press F4 to open Property inspector
• Navigate to Hyperlink property under Web Settings and provide the link in the below format

http://:8889/reports/rwservlet?userid=SCOTT/TIGER@DB+report=emp.rdf+destype=cache+desformat=html+P_deptno=&deptno

• Save the file as dept.rdf (use Shift+Crtl+k before saving).

3) Start report sever.

4) Call the dept.rdf report from url(if report is not called check the path is in reports path in registry).

Oracle Forms 10g :Change Data source at runtime

Change Data Source of a Block at run time.

Let us take an example wherein a form contains a data block and a list item. This list item consists of table names, which are similar in structure. When user selects one of the entries that table data has to be populated into the data block.

This kinda requirement can be implemented by changing the data source name of the data block at run time. We can do that by writing the below statment in the WHEN-LIST-CHANGED trigger of the list item.

SET_BLOCK_PROPERTY('',QUERY_DATA_SOURCE_NAME,'TABLE_NAME');