oracle tips & tricks

Oracle Tips & Tricks

 
Sunday, 27 January 2008
Getting Table Details with SQL*Plus
You can use SQL*Plus to provide the following details about a table:

  1. Column Details
  2. PRIMARY KEY
  3. INDEXES
  4. FOREIGN KEYS
  5. CONSTRAINTS
  6. ROWCOUNT
  7. Other Tables That REFER to this Table
  8. PARTITIONED COLUMNS
  9. PARTITIONS
  10. TRIGGERS
  11. DEPENDANTS
Use the following code in SQL*Plus to provide this information:
      SET AUTOTRACE OFF
SET TIMING OFF
COLUMN COMMENTS FORMAT A50
COLUMN column_name FORMAT A35
COLUMN Data_Type FORMAT A15
COLUMN DATA_DEFAULT FORMAT A20
COLUMN "PK Column" FORMAT A35
COLUMN "FK Column" FORMAT A20

UNDEF Owner
ACCEPT Owner PROMPT 'Enter Owner :'

UNDEF Table_Name
ACCEPT Table_Name PROMPT 'Enter Table Name :'


SET HEADING OFF

PROMPT
PROMPT Comments for Table &Table_Name.
SELECT COMMENTS
FROM ALL_TAB_COMMENTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.') ;

SET HEADING ON

PROMPT
PROMPT Column Details for Table &Table_Name.

SELECT
ROWNUM "Sr No", T.COLUMN_NAME , T.Data_Type , T.DATA_LENGTH,
DECODE(T.Nullable, 'N' , 'NOT NULL' , 'Y', ' ') NULLABLE , T.Data_Default , C.Comments
FROM
ALL_TAB_COLS T , All_Col_Comments C
WHERE
T.OWNER = C.OWNER
AND T.TABLE_NAME = C.TABLE_NAME
AND T.COLUMN_NAME = C.COLUMN_NAME
AND T.TABLE_NAME = UPPER('&Table_Name.')
AND T.Owner = UPPER('&Owner.') ;


PROMPT
PROMPT PRIMARY KEY for Table &Table_Name.

select COLUMN_NAME
FROM ALL_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.')
AND CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND CONS! TRAINT_T YPE = 'P'
AND Owner = UPPER('&Owner.')
)
ORDER BY POSITION
/

PROMPT
PROMPT INDEXES for Table &Table_Name.

BREAK ON INDEX_NAME ON UNIQUENESS SKIP 1

SELECT I.INDEX_NAME , C.COLUMN_NAME , I.UNIQUENESS
FROM ALL_IND_COLUMNS C , ALL_INDEXES I
WHERE C.INDEX_NAME = I.INDEX_NAME
AND C.TABLE_NAME = I.TABLE_NAME
AND I.TABLE_NAME = UPPER('&Table_Name.')
AND I.Owner = UPPER('&Owner.')
AND C.Table_Owner = UPPER('&Owner.')
AND NOT EXISTS ( SELECT 'X'
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_NAME = I.INDEX_NAME
AND Owner = UPPER('&Owner.')
)
ORDER BY INDEX_NAME , COLUMN_POSITION
/

CLEAR BREAKS

PROMPT
PROMPT FOREIGN KEYS for Table &Table_Name.

BREAK ON CONSTRAINT_NAME ON TABLE_NAME ON R_CONSTRAINT_NAME SKIP 1
COLUMN POSITION NOPRINT

SELECT UNIQUE A.CONSTRAINT_NAME,
C.COLUMN_NAME "FK Column" ,
B.TABLE_NAME || '.' || B.COLUMN_NAME "PK Column",
A.R_CONSTRAINT_NAME ,
C.POSITION
FROM ALL_CONSTRAINTS A, ALL_CONS_COLUMNS B, ALL_CONS_COLUMNS C
WHERE A.R_CONSTRAINT_NAME=B.CONSTRAINT_NAME
AND B.OWNER=UPPER('&OWNER')
AND A.CONSTRAINT_NAME=C.CONSTRAINT_NAME
AND A.OWNER=C.OWNER
AND A.OWNER = B.OWNER
AND A.TABLE_NAME=C.TABLE_NAME
AND B.POSITION=C.POSITION
AND A.TABLE_NAME LIKE UPPER('&TABLE_NAME')
ORDER BY A.CONSTRAINT_NAME, C.POSITION
/

COLUMN POSITION NOPRINT
CLEAR BREAKS

PROMPT
PROMPT CONSTRAINTS for Table &Table_Name.

SELECT CONSTRAINT_NAME , SEARCH_CONDITION
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.')
AND CONSTRAINT_TYPE NOT IN ( 'P' , 'R');

PROMPT
PROMPT ROWCOUNT for Table &Table_Name.

SET FEEDBACK OFF
SET SERVEROUTPUT ON
DECLARE N NU MBER ;
V VARCHAR2(100) ;
BEGIN
V := 'SELECT COUNT(*) FROM ' || UPPER('&Table_Name.') ;
EXECUTE IMMEDIATE V INTO N ;
DBMS_OUTPUT.PUT_LINE (N);
END;
/

SET FEEDBACK ON

PROMPT
PROMPT Tables That REFER to Table &Table_Name.

BREAK ON TABLE_NAME ON CONSTRAINT_NAME skip 1

SELECT C.TABLE_NAME , C.CONSTRAINT_Name , CC.COLUMN_NAME "FK Column"
FROM ALL_CONSTRAINTS C
, All_Cons_colUMNs CC
WHERE C.Constraint_Name = CC.Constraint_Name
AND R_CONSTRAINT_NAME = ( SELECT CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND CONSTRAINT_TYPE = 'P'
AND Owner = UPPER('&Owner.')
)
AND C.Owner = UPPER('&Owner.')
/

CLEAR BREAKS


PROMPT
PROMPT PARTITIONED COLUMNS for Table &Table_Name.

SELECT COLUMN_NAME , COLUMN_POSITION
FROM All_Part_Key_Columns
WHERE NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.') ;


PROMPT
PROMPT PARTITIONS for Table &Table_Name.

SELECT PARTITION_NAME , NUM_ROWS
FROM All_Tab_Partitions
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Table_Owner = UPPER('&Owner.') ;


PROMPT
PROMPT TRIGGERS for Table &Table_Name.

SELECT Trigger_Name
FROM All_Triggers
WHERE TABLE_NAME = UPPER('&Table_Name.')
AND Owner = UPPER('&Owner.') ;

PROMPT
PROMPT DEPENDANTS for Table &Table_Name.

BREAK ON TYPE SKIP 1

SELECT TYPE , NAME
FROM ALL_DEPENDENCIES
WHERE REFERENCED_NAME = UPPER('&Table_Name.')
ORDER BY TYPE ;

CLEAR BREAKS

SET TERMOUT OFF
SET AUTOTRACE ON
SET TIMING ON
SET TERMOUT ON

Labels: , ,

posted by popo @ 19:22   0 comments
Monday, 21 January 2008
How To : Displaying Column Data Horisontally
Some time you need to create a result set where the rows need to be columns, or vice versa. This commonly requirement can be done by using pivot (or crosstab) query.

1. A simple pivot query can be done by doing the following
2. Add some kind of count or row number to your query, if necessary for the grouping
3. Then use your (revised) original query as a sub-query
4. Use "decode" to turn rows into columns (ie. a "sparse" matrix).
5. Use "max" to "squash" the multiple rows you moved to columns, into single rows. Don't forget to group by.

(Note: it gets more complicated if you don't know how many columns you'll need).

Here is an example of a pivot query. Say you have the following set of data:


And you would like to make DEPTNO be a column. We have 4 deptno's in EMP, 10,20,30,40.
We can make columns dept_10, dept_20, dept_30, dept_40 that have the values that are
currently in the count column. It would look like this:

Note: don't confuse pivot queries with pivot tables. Pivot tables are a different concept, and have different uses (most typically to fill in missing data).

Labels: , ,

posted by popo @ 16:25   0 comments
Monday, 14 January 2008
Trigger on Insert and Delete
A friend of mine is asking, how to insert into TBL_A, but first you have to insert into TBL_B as master table (TBL_A referencing to TBL_B). We could use trigger on this. Here's two example on creating trigger while insert and delete on a table.

create or replace trigger TBL_A_TRIG_INS
before insert on TBL_A
for each row

begin
insert into TBL_B (ID ,NAME )
values ( :new.ID, :new.NAME );
end;



create or replace trigger TBL_A_TRIG_DEL
after
delete
on TBL_A
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
delete from TBL_B
where ID = :new.ID and NAME = :new.NAME
end;

Labels: , ,

posted by popo @ 17:14   0 comments
Tuesday, 8 January 2008
Oracle Optimizer Approach Hints
ALL_ROWS
The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).

Syntax : /*+ ALL_ROWS */
Example :

conn / as sysdba

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%optimizer%';

ALTER SYSTEM SET optimizer_mode=RULE SCOPE=MEMORY;

set autotrace traceonly explain

SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

SELECT /*+ ALL_ROWS */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

ALTER SYSTEM SET optimizer_mode=ALL_ROWS SCOPE=MEMORY;
FIRST_ROWS(n)
The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).

This hint causes the optimizer to make these choices:
  • If an index scan is available, the optimizer may choose it over a full table scan.
  • If an index scan is available, the optimizer may choose a nested loops join over a sort-merge join whenever the associated table is the potential inner table of the nested loops.
  • If an index scan is made available by an ORDER BY clause, the optimizer may choose it to avoid a sort operation.
  • The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following: UNION, INTERSECT, MINUS, UNION ALL, GROUP BY, FOR UPDATE, aggregating function and the DISTINCT operator.
Syntax : /*+ FIRST_ROWS() */
Example :
set autotrace trace exp
SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
RULE
Disables the use of the optimizer. This hint is not supported and should not be used.

Syntax : /*+ RULE */
Example :

set autotrace trace exp

SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

SELECT /*+ RULE */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

Labels:

posted by popo @ 01:35   0 comments
Monday, 7 January 2008
Oracle SQL Hints
/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
All hints except /*+ rule */ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted.
There should be no schema names in hints. Hints must use aliases if alias names are used for table names. So the following is wrong:

select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias
better
select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias

Why using hints

It is a perfect valid question to ask why hints should be used. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all.
Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help.
It must also be noted, that Oracle allows to lock the statistics when they look ideal which should make the hints meaningless again.
Hints can be categorized as follows:

  • Hints for Optimization Approaches and Goals,
  • Hints for Access Paths, Hints for Query Transformations,
  • Hints for Join Orders,
  • Hints for Join Operations,
  • Hints for Parallel Execution,
  • Additional Hints

Labels:

posted by popo @ 23:34   0 comments
Oracle 10g Release 2 Hint List
Getting a list of Oracle hints for SQL tuning is often difficult. The Oracle hint list is inside the Oracle executable and you can extract the Oracle hint list easily with UNIX commands.

You can use grep and strings to get them directly from the Oracle executable:

strings $ORACLE_HOME/bin/oracle > hints.lst

Here are categorized hint list :
Optimizer Approaches
  • ALL_ROWS
  • FIRST_ROWS(n)
  • RULE
General
  • APPEND
  • CACHE
  • CURSOR_SHARING_EXACT
  • DRIVING_SITE
  • DYNAMIC_SAMPLING
  • MODEL_MIN_ANALYSIS
  • NOAPPEND
  • NO_CACHE
  • NO_PUSH_PRED
  • NO_PUSH_SUBQ
  • NO_PX_JOIN_FILTER
  • NO_XML_QUERY_REWRITE
  • PUSH_PRED
  • PUSH_SUBQ
  • PX_JOIN_FILTER
  • QB_NAME
Access Method Hints
  • FULL
  • INDEX
  • INDEX_ASC
  • INDEX_COMBINE
  • INDEX_DESC
  • INDEX_FFS
  • INDEX_JOIN
  • INDEX_SS
  • INDEX_SS_ASC
  • INDEX_SS_DESC
  • NO_INDEX
  • NO_INDEX_FFS
  • NO_INDEX_SS
Cluster Only Access Method Hints
  • CLUSTER
  • HASH
Join Order
  • LEADING
  • ORDERED
Join Operation
  • NO_USE_HASH (table1 table2)
  • NO_USE_MERGE (table1 table2)
  • NO_USE_NL (table1 table2)
  • USE_HASH (table1 table2)
  • USE_MERGE (table1 table2)
  • USE_NL (table1 table2)
  • USE_NL_WITH_INDEX
Parallel Execution
  • PARALLEL
  • NO_PARALLEL
  • PQ_DISTRIBUTE
  • PARALLEL_INDEX
  • NO_PARALLEL_INDEX
Query Transformation
  • FACT
  • NO_EXPAND
  • NO_FACT
  • NO_QUERY_TRANSFORMATION
  • NO_REWRITE
  • NO_STAR_TRANSFORMATION
  • NO_UNNEST
  • REWRITE
  • STAR_TRANSFORMATION
  • UNNEST
  • USE_CONCAT
Others
  • ANTIJOIN
  • BITMAP
  • BUFFER
  • CARDINALITY
  • HASH_AJ
  • INLINE
  • MATERIALIZE
  • MERGE
  • NO_ACCESS
  • NO_BUFFER
  • NO_MERGE
  • NO_MONITORING
  • NO_PUSH_GSETS
  • NO_PUSH_JOIN_PRED
  • NO_QKN_BUFF
  • NO_SEMIJOIN
  • OR_EXPAND
  • PUSH_JOIN_PRED
  • SEMIJOIN
  • SEMIJOIN_DRIVER
  • SWAP_JOIN_INPUTS
  • USE_ANTI
  • USE_SEMI
Undocumented Optimizer Hints
  • BYPASS_RECURSIVE_CHECK
  • BYPASS_UJVC
  • CACHE_CB
  • CACHE_TEMP_TABLE
  • CIV_GB
  • COLLECTIONS_GET_REFS
  • CPU_COSTING
  • CUBE_GB
  • DEREF_NO_REWRITE
  • DML_UPDATE
  • DOMAIN_INDEX_NO_SORT
  • DOMAIN_INDEX_SORT
  • DYNAMIC_SAMPLING_EST_CDN
  • EXPAND_GSET_TO_UNION
  • FORCE_SAMPLE_BLOCK
  • GBY_CONC_ROLLUP
  • GLOBAL_TABLE_HINTS
  • HWM_BROKERED
  • IGNORE_ON_CLAUSE
  • IGNORE_WHERE_CLAUSE
  • INDEX_RRS
  • LIKE_EXPAND
  • LOCAL_INDEXES
  • MV_MERGE
  • NESTED_TABLE_GET_REFS
  • NESTED_TABLE_SET_REFS
  • NESTED_TABLE_SET_SETID
  • NO_ELIMINATE
  • NO_EXPAND_GSET_TO_UNION
  • NO_FILTERING
  • NO_ORDER_ROLLUPS
  • NO_PRUNE_GSETS
  • NO_STATS_GSETS
  • NOCPU_COSTING
  • OB_NAME
  • OVERFLOW_NOMOVE
  • PIV_GB
  • PIV_SSF
  • PQ_MAP
  • PQ_NOMAP
  • REMOTE_MAPPED
  • RESTORE_AS_INTERVALS
  • SAVE_AS_INTERVALS
  • SCN_ASCENDING
  • SELECTIVITY
  • SKIP_EXT_OPTIMIZER
  • SQLLDR
  • SYS_DL_CURSOR
  • SYS_PARALLEL_TXN
  • SYS_RID_ORDER
  • TIV_GB
  • TIV_SSF
  • USE_TTT_FOR_GSETS

Labels:

posted by popo @ 17:08   0 comments
Oracle Optimizer Hints
Optimizer hints can be used with SQL statements to alter execution plans.Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.

You can use hints to specify the following:
  • The optimization approach for a SQL statement
  • The goal of the cost-based optimizer for a SQL statement
  • The access path for a table accessed by the statement
  • The join order for a join statement
  • A join operation in a join statement
Note : The use of hints involves extra code that must be managed, checked, and controlled.

Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the following criteria:
  • Join order
  • Join method
  • Access path
  • Parallelization
Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used. Hints apply only to the optimization of the statement block in which they appear. A statement block is any one of the following statements or parts of statements:
  • A simple SELECT, UPDATE, or DELETE statement.
  • A parent statement or subquery of a complex statement.
  • A part of a compound query.
For example, a compound query consisting of two component queries combined by the UNION operator has two statement blocks, one for each component query. For this reason, hints in the first component query apply only to its optimization, not to the optimization of the second component query.

You can send hints for a SQL statement to the optimizer by enclosing them in a comment within the statement. A statement block can have only one comment containing hints. This comment can only follow the SELECT, UPDATE, or DELETE keyword.
Exception:
The APPEND hint always follows the INSERT keyword, and the PARALLEL hint can follow the INSERT keyword.

The following syntax shows hints contained in both styles of comments that Oracle supports within a statement block.

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

where:
  • DELETE, INSERT, SELECT, and UPDATE are keywords that begin a statement block. Comments containing hints can appear only after these keywords.
  • + causes Oracle to interpret the comment as a list of hints. The plus sign must immediately follow the comment delimiter; no space is permitted.
  • hint is one of the hints discussed in this section. If the comment contains multiple hints, then each hint must be separated from the others by at least one space.
  • text is other commenting text that can be interspersed with the hints.
If you specify hints incorrectly, then Oracle ignores them but does not return an error:
  • Oracle ignores hints if the comment containing them does not follow a DELETE, SELECT, or UPDATE keyword.
  • Oracle ignores hints containing syntax errors, but considers other correctly specified hints within the same comment.
  • Oracle ignores combinations of conflicting hints, but considers other hints within the same comment.
  • Oracle ignores hints in all SQL statements in those environments that use PL/SQL version 1, such as Forms version 3 triggers, Oracle Forms 4.5, and Oracle Reports 2.5. These hints can be passed to the server, but the server ignores them.
The optimizer recognizes hints only when using the cost-based approach. If you include a hint (except the RULE hint) in a statement block, then the optimizer automatically uses the cost-based approach.

Labels:

posted by popo @ 16:22   0 comments
Thursday, 3 January 2008
Oracle, SQL, PL / SQL Technical Interview Questions
Listed here are Interview Questions for Database Interviews.
This mainly lists Oracle, SQL, PL SQL frequently asked questions in technical Interviews

1. What is DDL, DML ? How are they different?
2. What are different types of joins in SQL?
3. How do you select unique rows using SQL?
4. What is the difference between DELETE and TRUNCATE ?
5. What is the difference between a "where" clause and a "having" clause?
6. What is the difference between "procedure" and "function"?
7. What is the difference between "translate" and "replace" ?
8. How to remove duplicate records from a table?
9. What is a "trigger"?
10.What is the difference between "translate" and "replace"?
11.What is a VIEW?
12.What is the difference among "dropping a table", "truncating a table"
and "deleting all records" from a table
13.Explain new feature of 9i Database ? Explain new feature of 10g Database ?
14.How to use DECODE function?
15.What is “Group by” clause?
16.What are cursors and what are the situations you will use them?
17.What default packages are provided by Oracle?
18.How do you debug a oracle procedure /function?
19.How many triggers are available?
20.How are procedures executed?

Labels: , ,

posted by popo @ 17:22   0 comments
Oracle SQL, PL/SQL Interview Question List
These are some Oracle SQL, PL/SQL Interview Question List
1. How do you convert a date to a string?
2. What is an aggregate function?
3. What is the dual table?
4. What are cursors? Distinguish between implicit and explict cursors?
5. Explain how cursors are used by Oracle?
6. What is PL/SQL? Describe the block structure of PL/SQL?
7. What is a nested subquery?
8. What are the various types of queries ?
9. Which of the following is not a schema object : Index, table, public synonym, trigger and package ?
10. What is dynamic sql in oracle?
11. What is the difference between a package, procedure and function?
12. What is the difference between delete, drop and truncating a table?
13. How many triggers are supported in Oracle
14. Are you aware of FLASHBACK concept ? What is it?
15. Describe oracle’s logical and physical structure?
16. What is data dictionary ?
17. What is the use of control files ?
18. How would store XML data in table ? What data type would be used for the columns?
19. Difference between post and commit?
20. Difference between commit and rollback?
21. What are savepoints?
22. What is the Difference between a View and Synonym?
23. How would you fetch system date from oracle?
24. What is the difference between primary key, unique key, foreign key?
25. What is the difference between NO DATA FOUND and %NOTFOUND?
26. What is cursor for loop?
27. What are cursor attributes?
28. What will you use in Query : IN or EXISTS? Why?
29. Explain the difference between a data block, an extent and a segment.?
30. What's the difference between logical and physical I/O?
31. What is an anonymous block?
32. What is a PL/SQL collection?
33. How can you tell if an UPDATE updated no rows?
34. How can you tell if a SELECT returned no rows?

Labels: , ,

posted by popo @ 17:15   0 comments
The Difference Between %TYPE and %ROWTYPE
%TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automically picks up the new definition from the database without having to make any code changes.

The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

%TYPE is used to declare a field with the same type as that of a specified table's column. Example:
DECLARE
v_EmpName emp.ename%TYPE;
BEGIN
SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/


%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Examples:
DECLARE
v_emp emp%ROWTYPE;
BEGIN
v_emp.empno := 10;
v_emp.ename := 'XXXXXXX';
END;
/

DECLARE
v_EmpRecord emp%ROWTYPE;
BEGIN
SELECT * INTO v_EmpRecord FROM emp WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpRecord.ename);
DBMS_OUTPUT.PUT_LINE('Salary = ' || v_EmpRecord.sal);
END;
/

Labels:

posted by popo @ 16:59   0 comments
Oracle DBA Interview Questions
Here are Oracle DBA Questions and Answers in Interview (don't expect too much on this)

1. Explain database instance ?

A database instance (server) is a set of memory structures and background processes that access a set of database files.
The memory structures are used to store most queried data from database. This helps us to improve database performance by decreasing the amount of I/O performed against data file.
The process can be shared by all users.

2. What is parallel server?
Multiple instances accessing the same database (Only in Multi-CPU environments).

3. What is Schema ?
The set of objects owned by user account is called the schema

4. What is an Index ? How it is implemented in Oracle Database ?
An index is a database structure used by the server to have direct access of a row in a table.An index is automatically created when a unique or primary key constraint clause is specified in create table command

5. What is a clusters? Explain
Group of tables physically stored together because they share common columns and are often used together is called Clusters.

6. What is a cluster key ?
The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stores only once for multiple tables in the cluster.

7. What are the basic element of an oracle Database ?
It consists ofone or more data filesone or more control filestwo or more redo log files
The database containsMultiple users/schemasone or more rollback segmentsone or more tablespacesData dictionary tables
User objects (tables,indexes,views etc)
The server that access the database consists of
SGA (Database buffer, Dictionary Cache Buffers, redo log buffers,Shared SQL pool)
SMON
PMON
LGWR
DBWR
ARCH
CKPT
RECO
Dispatcher
User process with associated PGA

8. What is deadlock ? Explain.
Two processes waiting to update the rows of a table which are locked by the other process then deadlock arises.
In a database environment this will often happen because of not issuing proper row lock commands. Poor design of front-end application may cause this situation and the performance of server will reduce drastically.
These locks will be released automatically when a commit/rollback operation performed or any one of this processes being killed externally.

9. What is SGA ?
The System Global Area in a Oracle database is the area in memory to facilitates the transfer of information between users. It holds the most recently requested structural information about the database.

10. What is Shared SQL pool ?
The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.

11. What is meant by Program Global Area (PGA) ?
It is area in memory that is used by a Single Oracle User process.

12. What is a data segment ?
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

13. What are the factors causing the reparsing of SQL statements in SGA ?
Due to insufficient Shared SQL pool size

Labels:

posted by popo @ 16:14   0 comments
Wednesday, 2 January 2008
Oracle 10g UTL_COMPRESS
Oracle 10g includes many PL/SQL enhancements including: utl_compress
The UTL_COMPRESS package provides an API to allow compression and decompression of binary data (RAW, BLOB and BFILE). It uses the Lempel-Ziv compression algorithm which is equivalent to functionality of the gzip utility. A simple example of it's usage would be:

SET SERVEROUTPUT ON
DECLARE
l_original_blob BLOB;
l_compressed_blob BLOB;
l_uncompressed_blob BLOB;
BEGIN
-- Initialize both BLOBs to something.
l_original_blob := TO_BLOB(UTL_RAW.CAST_TO_RAW('1234567890123456789012345678901234567890'));
l_compressed_blob := TO_BLOB('1');
l_uncompressed_blob := TO_BLOB('1');

-- Compress the data.
UTL_COMPRESS.lz_compress (src => l_original_blob,
dst => l_compressed_blob);

-- Uncompress the data.
UTL_COMPRESS.lz_uncompress (src => l_compressed_blob,
dst => l_uncompressed_blob);

-- Display lengths.
DBMS_OUTPUT.put_line('Original Length : ' || LENGTH(l_original_blob));
DBMS_OUTPUT.put_line('Compressed Length : ' || LENGTH(l_compressed_blob));
DBMS_OUTPUT.put_line('Uncompressed Length: ' || LENGTH(l_uncompressed_blob));

-- Free temporary BLOBs.
DBMS_LOB.FREETEMPORARY(l_original_blob);
DBMS_LOB.FREETEMPORARY(l_compressed_blob);
DBMS_LOB.FREETEMPORARY(l_uncompressed_blob);
END;
/

Labels: ,

posted by popo @ 19:53   0 comments
Tuesday, 1 January 2008
Oracle DBA : Backup on Tape Drive
For oracle DBA, here are some cases on Tape Drive Backup:

You want to take backup on tape drive but rman always store the backup on a default location

  1. how can we take backup on tape drive, should we have to install any extra softwares for tape drive or rman handle it itself?

    You need thrid party Media managment software like veritas ,omnibackup,tivoli for backing up to tape.If you don't already have Media Management software, you can consider Oracle Secure Backup as a free alternative (assuming you are backing up one server to one tape drive).
  2. how can we set the tape drive as our default location for backup?
    how can we take backup on specified location e.g. d:\backup and how can we set this location as default location for backup?
    To set the tape drive as the default, see Configuring the Default Device Type for Backups.

    If you want the tape drive to be the default backup location, then you'll have to allocate a disk channel to produce backups to d:\backup. You can configure the disk format as CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'd:\backups'; and then allocate a disk channel during backup (Manually Overriding Configured Channels).

Labels:

posted by popo @ 16:02   0 comments
 
Search

Google
Previous Post
Category
Blogrush
Blogroll
Legal
Archives
Template by
Free Blogger templates