CURSOR_ALREADY_OPEN ~ You tried to OPEN a cursor that was already OPEN. You must CLOSE a cursor before you try to OPEN or re-OPEN it.
DUP_VAL_ON_INDEX ~ Your INSERT or UPDATE statement attempted to store duplicate values in a column or columns in a row which is restricted by a unique index.
INVALID_CURSOR ~ You made reference to a cursor that did not exist. This usually happens when you try to FETCH from a cursor or CLOSE a cursor before that cursor is OPENed.
INVALID_NUMBER ~ PL/SQL executes a SQL statement that cannot convert a character string successfully to a number. This exception is different from the VALUE_ERROR exception, as it is raised only from within a SQL statement.
LOGIN_DENIED ~ Your program tried to log onto the Oracle RDBMS with an invalid username-password combination. This exception is usually encountered when you embed PL/SQL in a 3GL language.
NO_DATA_FOUND ~ This exception is raised in three different scenarios:
(1) You executed a SELECT INTO statement (implicit cursor) that returned no rows.
(2) You referenced an uninitialized row in a local PL/SQL table.
(3) You read past end of file with UTL_FILE package.
NOT_LOGGED_ON ~ Your program tried to execute a call to the database (usually with a DML statement) before it had logged into the Oracle RDBMS.
PROGRAM_ERROR ~ PL/SQL encounters an internal problem. The message text usually also tells you to "Contact Oracle Support."
STORAGE_ERROR ~ Your program ran out of memory or memory was in some way corrupted.
TIMEOUT_ON_RESOURCE ~ A timeout occurred in the RDBMS while waiting for a resource.
TOO_MANY_ROWS ~ A SELECT INTO statement returned more than one row. A SELECT INTO can return only one row; if your SQL statement returns more than one row you should place the SELECT statement in an explicit CURSOR declaration and FETCH from that cursor one row at a time.
TRANSACTION_BACKED_OUT ~ The remote part of a transaction is rolled back, either with an explicit ROLLBACK command or as the result of some other action.
VALUE_ERROR ~ PL/SQL raises the VALUE_ERROR whenever it encounters an error having to do with the conversion, truncation, or invalid constraining of numeric and character data. This is a very general and common exception. If this same type of error is encountered in a SQL DML statement within a PL/SQL block, then the INVALID_NUMBER exception is raised.
ZERO_DIVIDE ~ Your program tried to divide by zero.
Wednesday, October 6, 2010
Managing Backup & Recovery
What are the different methods of backing up oracle database ?
- Logical Backups
- Cold Backups
- Hot Backups (Archive log)
What is a logical backup ?
Logical backup involves reading a set of databse records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.
What is cold backup ? What are the elements of it ?
Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.
- All Data files.
- All Control files.
- All on-line redo log files.
- The init.ora file (Optional)
What are the different kind of export backups ?
Full back - Complete database
Incremental - Only affected tables from last incremental date/full backup date.
Cumulative backup - Only affected table from the last cumulative date/full backup date.
What is hot backup and how it can be taken ?
Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up.
All data files. All Archive log, redo log files. All control files.
What is the use of FILE option in EXP command ?
To give the export file name.
What is the use of COMPRESS option in EXP command ?
Flag to indicate whether export should compress fragmented segments into single extents.
What is the use of GRANT option in EXP command ?
A flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'.
What is the use of INDEXES option in EXP command ?
A flag to indicate whether indexes on tables will be exported.
What is the use of ROWS option in EXP command ?
Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the databse objects will be created.
What is the use of CONSTRAINTS option in EXP command ?
A flag to indicate whether constraints on table need to be exported.
What is the use of FULL option in EXP command ?
A flag to indicate whether full databse export should be performed.
What is the use of OWNER option in EXP command ?
List of table accounts should be exported.
What is the use of TABLES option in EXP command ?
List of tables should be exported.
What is the use of RECORD LENGTH option in EXP command ?
Record length in bytes.
What is the use of INCTYPE option in EXP command ?
Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL.
What is the use of RECORD option in EXP command ?
For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.
What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.
What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.
What is the use of ANALYSE ( Ver 7) option in EXP command ?
A flag to indicate whether statistical information about the exported objects should be written to export dump file.
- Logical Backups
- Cold Backups
- Hot Backups (Archive log)
What is a logical backup ?
Logical backup involves reading a set of databse records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup.
What is cold backup ? What are the elements of it ?
Cold backup is taking backup of all physical files after normal shutdown of database. We need to take.
- All Data files.
- All Control files.
- All on-line redo log files.
- The init.ora file (Optional)
What are the different kind of export backups ?
Full back - Complete database
Incremental - Only affected tables from last incremental date/full backup date.
Cumulative backup - Only affected table from the last cumulative date/full backup date.
What is hot backup and how it can be taken ?
Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up.
All data files. All Archive log, redo log files. All control files.
What is the use of FILE option in EXP command ?
To give the export file name.
What is the use of COMPRESS option in EXP command ?
Flag to indicate whether export should compress fragmented segments into single extents.
What is the use of GRANT option in EXP command ?
A flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'.
What is the use of INDEXES option in EXP command ?
A flag to indicate whether indexes on tables will be exported.
What is the use of ROWS option in EXP command ?
Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the databse objects will be created.
What is the use of CONSTRAINTS option in EXP command ?
A flag to indicate whether constraints on table need to be exported.
What is the use of FULL option in EXP command ?
A flag to indicate whether full databse export should be performed.
What is the use of OWNER option in EXP command ?
List of table accounts should be exported.
What is the use of TABLES option in EXP command ?
List of tables should be exported.
What is the use of RECORD LENGTH option in EXP command ?
Record length in bytes.
What is the use of INCTYPE option in EXP command ?
Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL.
What is the use of RECORD option in EXP command ?
For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export.
What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.
What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.
What is the use of ANALYSE ( Ver 7) option in EXP command ?
A flag to indicate whether statistical information about the exported objects should be written to export dump file.
Tuesday, October 5, 2010
How to convert a report into excel sheet
PROCEDURE U_1ButtonAction is
CURSOR cur is
SELECT A.EMP_CODE,A.EMP_TITLE ||' '||A.EMP_FNAME ||' '||A.EMP_LNAME EMP_NAME,A.EMP_DEPT,A.EMP_TYPE,EMP_DOJ
FROM BPAYT030 A WHERE A.EMP_TYPE = DECODE(:P_EMPTYPE,'A',A.EMP_TYPE,:P_EMPTYPE)
AND A.EMP_DEPT = DECODE(:P_EMPDEPT,'A',A.EMP_DEPT,:P_EMPDEPT) AND EMP_STATUS ='Y';
--AND A.JOB_TITLE = DECODE(:P_JOB,'A',A.EMP_DEPT,:P_JOB)
V_OUTFILE TEXT_IO.FILE_TYPE;
V_OUTSTRING VARCHAR2(1000);
L_FILENAME VARCHAR2(100);
L_SR NUMBER:=0;
L_DESC VARCHAR2(200);
L_DESC1 VARCHAR2(200);
BEGIN
L_FILENAME := 'C:'||'EMPLOYEE LIST'||TO_CHAR(SYSDATE,'DDMMRR')||'.CSV';
V_outfile := text_io.fopen(L_FILENAME,'W');
V_outstring := ','||','||'EMPLOYEE TYPE/DEPARTMENT WISE DETAILS';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
V_outstring :='';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
V_outstring := :P_COMP;
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
--EXCEL FILE HEADS
V_outstring :='EMPLOYEE TYPE'||','||'DEPARTMENT'||','||'CODE'||','||'DOJ' ||','||'EMPLOYEE NAME';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
FOR REC IN CUR LOOP
SELECT CODE_DESC INTO L_DESC FROM BCOMT011 WHERE TYPE_CODE = REC.EMP_TYPE;
SELECT CODE_DESC INTO L_DESC1 FROM BCOMT011 WHERE TYPE_CODE = REC.EMP_DEPT;
--DATA IN EXCEL
V_outstring := L_DESC||','||L_DESC1||','||REC.EMP_CODE||','||EC.EMP_DOJ||','||REC.EMP_NAME;
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
END LOOP;
text_io.fclose(v_outfile);
SRW.MESSAGE(100,'File '||L_FILENAME|| ' Generated Successfully....');
EXCEPTION
WHEN OTHERS THEN
SRW.MESSAGE('1',SQLERRM);
text_io.fclose(v_outfile);
END;
CURSOR cur is
SELECT A.EMP_CODE,A.EMP_TITLE ||' '||A.EMP_FNAME ||' '||A.EMP_LNAME EMP_NAME,A.EMP_DEPT,A.EMP_TYPE,EMP_DOJ
FROM BPAYT030 A WHERE A.EMP_TYPE = DECODE(:P_EMPTYPE,'A',A.EMP_TYPE,:P_EMPTYPE)
AND A.EMP_DEPT = DECODE(:P_EMPDEPT,'A',A.EMP_DEPT,:P_EMPDEPT) AND EMP_STATUS ='Y';
--AND A.JOB_TITLE = DECODE(:P_JOB,'A',A.EMP_DEPT,:P_JOB)
V_OUTFILE TEXT_IO.FILE_TYPE;
V_OUTSTRING VARCHAR2(1000);
L_FILENAME VARCHAR2(100);
L_SR NUMBER:=0;
L_DESC VARCHAR2(200);
L_DESC1 VARCHAR2(200);
BEGIN
L_FILENAME := 'C:'||'EMPLOYEE LIST'||TO_CHAR(SYSDATE,'DDMMRR')||'.CSV';
V_outfile := text_io.fopen(L_FILENAME,'W');
V_outstring := ','||','||'EMPLOYEE TYPE/DEPARTMENT WISE DETAILS';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
V_outstring :='';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
V_outstring := :P_COMP;
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
--EXCEL FILE HEADS
V_outstring :='EMPLOYEE TYPE'||','||'DEPARTMENT'||','||'CODE'||','||'DOJ' ||','||'EMPLOYEE NAME';
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
FOR REC IN CUR LOOP
SELECT CODE_DESC INTO L_DESC FROM BCOMT011 WHERE TYPE_CODE = REC.EMP_TYPE;
SELECT CODE_DESC INTO L_DESC1 FROM BCOMT011 WHERE TYPE_CODE = REC.EMP_DEPT;
--DATA IN EXCEL
V_outstring := L_DESC||','||L_DESC1||','||REC.EMP_CODE||','||EC.EMP_DOJ||','||REC.EMP_NAME;
TEXT_IO.PUT_LINE(V_OUTFILE, V_OUTSTRING);
END LOOP;
text_io.fclose(v_outfile);
SRW.MESSAGE(100,'File '||L_FILENAME|| ' Generated Successfully....');
EXCEPTION
WHEN OTHERS THEN
SRW.MESSAGE('1',SQLERRM);
text_io.fclose(v_outfile);
END;
What is a stored procedure ?
A stored procedure is a subprogram that is stored in the database as a PCODE and thus called as a standalone schema object.
a) It can be invoked directly from any calling environments, or other subprograms.
b) It is similar to any subprogram that accepts parameters and performs an action. It may or may not return a value. This attribute differentiates it from another subprogram type called FUNCTION.
c) It can be nested
d) Is created by using the syntax CREATE OR REPLACE
e) This stored procedure can be seen as an object in the User_Objects and is different from the procedures that are created in packages.
a) It can be invoked directly from any calling environments, or other subprograms.
b) It is similar to any subprogram that accepts parameters and performs an action. It may or may not return a value. This attribute differentiates it from another subprogram type called FUNCTION.
c) It can be nested
d) Is created by using the syntax CREATE OR REPLACE
e) This stored procedure can be seen as an object in the User_Objects and is different from the procedures that are created in packages.
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
Generally In triggers you can't use TCL commands.
But you can use TCL commands in Autonomous Triggers.
You can declare a trigger as Autonomous by providing PRAGMA AUTONOMOUS_TRANSACTION in the beginning of the trigger.
At a same time you have to end your trigger with commit/rollback.
You can use these type of triggers to maintain log details of a table.
But you can use TCL commands in Autonomous Triggers.
You can declare a trigger as Autonomous by providing PRAGMA AUTONOMOUS_TRANSACTION in the beginning of the trigger.
At a same time you have to end your trigger with commit/rollback.
You can use these type of triggers to maintain log details of a table.
What are the cursor attributes used in PL/SQL?
For Implicit Cursors:-
Found - Returns TRUE If the last DML Statement affect atleast 1 row otherwise FALSE.
NotFound - Returns TRUE If last DML Statement doesn't affect atleast 1 row otherwise FALSE.
Rowcount - Returns number of rows affected by the last DML statement.
Isopen - Returns TRUE if the cursor is open otherwise FALSE. Its always returns FALSE incase of implicit cursor. Because after executing the DML statement Oracle Server automatically close the cursor.
For Explicit Cursors:-
Found - Returns TRUE if the Last fetch returns a Row otherwise FALSE.
NotFound - Returns TRUE if the last fetch doesn't return any row otherwise FALSE.
Rowcount - Returns number of rows returned so far from the active set.
Isopen - Returns TRUE if the cursor is open otherwise FALSE.
Found - Returns TRUE If the last DML Statement affect atleast 1 row otherwise FALSE.
NotFound - Returns TRUE If last DML Statement doesn't affect atleast 1 row otherwise FALSE.
Rowcount - Returns number of rows affected by the last DML statement.
Isopen - Returns TRUE if the cursor is open otherwise FALSE. Its always returns FALSE incase of implicit cursor. Because after executing the DML statement Oracle Server automatically close the cursor.
For Explicit Cursors:-
Found - Returns TRUE if the Last fetch returns a Row otherwise FALSE.
NotFound - Returns TRUE if the last fetch doesn't return any row otherwise FALSE.
Rowcount - Returns number of rows returned so far from the active set.
Isopen - Returns TRUE if the cursor is open otherwise FALSE.
What is a cursor ? Why Cursor is required ?
CURSOR
Whenever a sql statement is issued at that time oracle server will allocate some memory area to execute and parse that sql statement. That data area is called as cursor.
Two types : IMPLICIT EXPLICIT
IMPLICIT
-----------
(1) Called as SQL.
(2) Never user CURSOR keyword explicitly
(3) Memory allocation and program controll will done automatically
(4) Returns or updates or deletes only one record.
(5) Attributes : SQL FOUND SQL NOTFOUND
EXPLICIT
-----------
(1) called as cursors
(2) Uses CURSOR keyword explicitly
(3) Memory allocation and program control thru the cursor written.
(4) Returns or updates or deletes multiple records.
(5) Attributes : SQL FOUND SQL NOTFOUND ISOPEN ROWCOUNT
Whenever a sql statement is issued at that time oracle server will allocate some memory area to execute and parse that sql statement. That data area is called as cursor.
Two types : IMPLICIT EXPLICIT
IMPLICIT
-----------
(1) Called as SQL.
(2) Never user CURSOR keyword explicitly
(3) Memory allocation and program controll will done automatically
(4) Returns or updates or deletes only one record.
(5) Attributes : SQL FOUND SQL NOTFOUND
EXPLICIT
-----------
(1) called as cursors
(2) Uses CURSOR keyword explicitly
(3) Memory allocation and program control thru the cursor written.
(4) Returns or updates or deletes multiple records.
(5) Attributes : SQL FOUND SQL NOTFOUND ISOPEN ROWCOUNT
What is PL/SQL table ?
A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogenous elements, indexed by integers
One-dimensional
Unbounded or Unconstrained
Sparse
Homogeneous elements
Indexed by integers
There is no predefined limit to the number of rows in a PL/SQL table. The PL/SQL table grows dynamically as you add more rows to the table. The PL/SQL table is, in this way, very different from an array.
Related to this definition, no rows for PL/SQL tables are allocated for this structure when it is defined.
In a PL/SQL table, a row exists in the table only when a value is assigned to that row. Rows do not have to be defined sequentially. Instead you can assign a value to any row in the table. So row 15 could have a value of `Fox' and row 15446 a value of `Red', with no other rows defined in between.
Because a PL/SQL table can have only a single column, all rows in a PL/SQL table contain values of the same datatype. It is, therefore, homogeneous.
With PL/SQL Release 2.3, you can have PL/SQL tables of records. The resulting table is still, however, homogeneous. Each row simply contains the same set of columns.
What are the datatypes a available in PL/SQL ?
Following are the datatype supported in oracle PLSQL
Scalar Types
BINARY_INTEGER
DEC
DECIMAL
DOUBLE PRECISION
FLOAT
INT
INTEGER
NATURAL
NATURALN
NUMBER
NUMERIC
PLS_INTEGER
POSITIVE
POSITIVEN
REAL
SIGNTYPE
SMALLINT
CHAR
CHARACTER
LONG
LONG RAW
NCHAR
NVARCHAR2
RAW
ROWID
STRING
UROWID
VARCHAR
VARCHAR2
DEC
DECIMAL
DOUBLE PRECISION
FLOAT
INT
INTEGER
NATURAL
NATURALN
NUMBER
NUMERIC
PLS_INTEGER
POSITIVE
POSITIVEN
REAL
SIGNTYPE
SMALLINT
CHAR
CHARACTER
LONG
LONG RAW
NCHAR
NVARCHAR2
RAW
ROWID
STRING
UROWID
VARCHAR
VARCHAR2
DATE
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH TIME ZONE
INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH TIME ZONE
BOOLEAN
Composite TypesRECORD
TABLE
VARRAY
LOB TypesBFILE
BLOB
CLOB
NCLOB
BLOB
CLOB
NCLOB
Reference Types
REF CURSOR
REF object_type
What is the basic structure of PL/SQL ?
A PL/SQL block has three parts:
a declarative part
an executable part
and an exception-handling part.
First comes the declarative part in which items can
be declared. Once declared items can be manipulated in the executable part.
Exceptions raised during execution can be dealt with in the exception-handling part.
Programmatic Constructs
What are the different types of PL/SQL program units that can be defined and stored in ORACLE database?
Procedures, functions, packages and database triggers.
What is a Procedure?
A Procedure consist of a set of SQL and PL/SQL statements that are grouped together as a unit to solve a Specific problem or perform a set of related tasks.
What is difference between Procedures and Functions?
A Function returns a value to the caller where as a Procedure does not.
What is a Package?
A Package is a collection of related procedures, functions, variables and other package constructs together as a unit in the database.
What are the advantages of having a Package?
Increased functionality (for example, global package variables can be declared and used by any procedure in the package) and performance (for example all objects of the package are parsed compiled, and loaded into memory once)
What is Database Trigger?
A Database Trigger is procedure (set of SQL and PL/SQL statements) that is automatically executed as a result of an insert in, update to, or delete from a table.
What are the uses of Database Trigger?
Database triggers can be used to automatic data generation, audit data modifications, enforce complex Integrity constraints, and customize complex security authorizations.
What are the differences between Database Trigger and Integrity constraints?
A declarative integrity constraint is a statement about the database that is always true. A constraint applies to existing data in the table and any statement that manipulates the table. A trigger does not apply to data loaded before the definition of the trigger; therefore, it does not guarantee all data in a table conforms to the rules established by an associated trigger. A trigger can be used to enforce transitional constraints where as a declarative integrity constraint cannot be used.
Logical & Physical Architecture of Database
What is Database Buffers ?
Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters. DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.
What is dictionary cache ?
Dictionary cache is information about the database objects stored in a data dictionary table.
What is meant by recursive hits ?
Number of times processes repeatedly query the dictionary table is called recursive hits. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of Data Dictionary Cache.
What is meant by redo log buffer ?
Changes made to entries are written to the on-line redo log files so that they can be used in roll forward operation during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.
How will you swap objects into a different table space for an existing database ?
Export the user
Perform import using the command imp system/manager file=export.dp indexfile=newfile.sql. This will create all definitions into newfile.sql.
Drop necessary objects.
Run the script newfile.sql after altering the tablespaces.
Import from the backup for the necessary objects.
List the Optimal Flexible Architecture (OFA) of Oracle database ? or
How can we organise the tablespaces in Oracle database to have maximum performance ?
SYSTEM - Data dictionary tables
DATA - Standard operational tables
DATA2 - Static tables used for standard operations
INDEXES - Indexes for Standard operational tables
INDEXES1 - Indexes of static tables used for standard operations
TOOLS - Tool table
TOOLS1 - Indexes for tools table
RBS - Standard Operations Rollback Segments
RBS1,RBS2 - Additional/Special rollback segments
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users
USERS - User tablespaces.
How will you force database to use particular rollback segment ?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name
What is meant by free extent ?
A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.
How free extents are managed in Ver 6 and Ver 7. ?
Free extents cannot be merged together in Ver 6.0
Free extents are periodically coalesces with the neighboring free extent Ver 7.0.
Which parameter in Storage clause will reduce no of rows per block ?
PCTFREE parameter
Row size also reduces no of rows per block.
What is significance of having storage clause ?
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.
How does space allocation take place within a block ?
Each block contains entries as follows :
Fixed block header
Variable block header
Row header, row date (Multiple rows may exists)
PCTFREE (% of free space for row updation in future)
What is the role of PCTFREE parameter is Storage clause ?
This is used to reserve certain amount of space in a block for expansion of rows.
What is the OPTIMAL parameter ?
It is used to set the optimal length of rollback segment.
What is the functionality of SYSTEM tablespace ?
To manage the database level of transactions such as modifications of the data dictionary table that record information about the free space usage.
How will you create multiple rollback segments in a database ?
Create a database which implicitly creates a SYSTEM Rollback Segment in a SYSTEM tablespace.
Create a Second Rollback Segment name R0 in the SYSTEM tablespace.
Make new rollback segment available (After shutdown, modify init.ora file and Start database)
Create other tablespace (RBS) for rollback segments.
Create additional Rollback segment in tablespace (RBS)
Deactivate Rollback Segment R0 and activate the newly created rollback segments.
How the space utilisation takes place within rollback segments ?
It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to acquire a new extent. (No of extents is based on the OPTIMAL size).
Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters. DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.
What is dictionary cache ?
Dictionary cache is information about the database objects stored in a data dictionary table.
What is meant by recursive hits ?
Number of times processes repeatedly query the dictionary table is called recursive hits. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of Data Dictionary Cache.
What is meant by redo log buffer ?
Changes made to entries are written to the on-line redo log files so that they can be used in roll forward operation during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.
How will you swap objects into a different table space for an existing database ?
Export the user
Perform import using the command imp system/manager file=export.dp indexfile=newfile.sql. This will create all definitions into newfile.sql.
Drop necessary objects.
Run the script newfile.sql after altering the tablespaces.
Import from the backup for the necessary objects.
List the Optimal Flexible Architecture (OFA) of Oracle database ? or
How can we organise the tablespaces in Oracle database to have maximum performance ?
SYSTEM - Data dictionary tables
DATA - Standard operational tables
DATA2 - Static tables used for standard operations
INDEXES - Indexes for Standard operational tables
INDEXES1 - Indexes of static tables used for standard operations
TOOLS - Tool table
TOOLS1 - Indexes for tools table
RBS - Standard Operations Rollback Segments
RBS1,RBS2 - Additional/Special rollback segments
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users
USERS - User tablespaces.
How will you force database to use particular rollback segment ?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name
What is meant by free extent ?
A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free.
How free extents are managed in Ver 6 and Ver 7. ?
Free extents cannot be merged together in Ver 6.0
Free extents are periodically coalesces with the neighboring free extent Ver 7.0.
Which parameter in Storage clause will reduce no of rows per block ?
PCTFREE parameter
Row size also reduces no of rows per block.
What is significance of having storage clause ?
We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc.
How does space allocation take place within a block ?
Each block contains entries as follows :
Fixed block header
Variable block header
Row header, row date (Multiple rows may exists)
PCTFREE (% of free space for row updation in future)
What is the role of PCTFREE parameter is Storage clause ?
This is used to reserve certain amount of space in a block for expansion of rows.
What is the OPTIMAL parameter ?
It is used to set the optimal length of rollback segment.
What is the functionality of SYSTEM tablespace ?
To manage the database level of transactions such as modifications of the data dictionary table that record information about the free space usage.
How will you create multiple rollback segments in a database ?
Create a database which implicitly creates a SYSTEM Rollback Segment in a SYSTEM tablespace.
Create a Second Rollback Segment name R0 in the SYSTEM tablespace.
Make new rollback segment available (After shutdown, modify init.ora file and Start database)
Create other tablespace (RBS) for rollback segments.
Create additional Rollback segment in tablespace (RBS)
Deactivate Rollback Segment R0 and activate the newly created rollback segments.
How the space utilisation takes place within rollback segments ?
It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to acquire a new extent. (No of extents is based on the OPTIMAL size).
Sunday, October 3, 2010
Importent Oracle & Sql Query
1) How delete duplicate records in a table
delete from emp where rowid not in (select max(rowid) from emp group by empno);
2) How to make a column into not null column.
Ans: alter table emp
modify (empno not null); ->this can be done only when all the values in empno are non null (i.e not empty)
?h To make a not null column into null column
alter table emp
modify (empno null);
The fallowing are the rules for
i. adding null or not null property
>You may change a column??s null property to not null only when that field does contain null values(i. It can??t be empty)
>At any time you may change a column??s not null property to null
ii. adding a colum to a table
>You may add a column at any time if NOT NULL isn??t specified (i.e when new column can accept null values )
>You may add a NOT NULL column in three steps.
a. Add the column without NOT NULL specified.
b. Fill every row in that column with data.
c. Modify the column to be NOT NULL.
iii. modifying a column
>You can increase a CHAR column??s width at any time.
>You can increase the number of digits and the number of decimal places in a NUMBER column at any time
>To CHANGE data types or to DECREASE column??s width the column should be null for every row
3)Write queries for the fallowing
ex. select * from emp_self;
EMP_NO EMPN_NAME SAL MGR DEPTID
--------- -------------------- ---------- ---------- ----------
400 jane 20000 110 20
102 Mary 19000 110 20
101 charles 8000 105 50
104 Linda 9000 100 10
110 john 25000 105 20
105 newton 2000 50
100 ALEN 15000 105 50
200 BORIS 3000 110 20
103 DAVID 10000 100 10
300 monica 7000 105 50
i) Query to get the employees who are working under mgr with salary > 10000
select emp_no,mgr from emp_self where mgr in (select emp_no from emp_self where sal > 10000);
ii) Query to get the employees who are getting salaries more than their managers
select a.emp_no from emp_self a,emp_self b where a.mgr =b.emp_no and a.sal > b.sal;
EMP_NO
-------
101
100
110
300
iii) Query to find the nth highest salary
select a.empn_name,a.sal from emp_self a where &n = (select count(*) from emp_self b where a.sal < b.sal);
When n=1 , sal =20000 ->second highest salary ; n=4 , sal =10000 ->fifth highest salary ;
iv) Query to find the second highest salary in different departments.
select a.deptid,min(a. sal) from emp_self a where 1 in (select count(*) from emp_self b
where a.sal < b.sal group by b.deptid) group by a.deptid;
DEPTID MIN(A.SAL)
------- ----------
10 9000
20 20000
50 8000
v) Query to find departments with total salary >25000
select deptid from emp_self having sum(sal) >25000 group by deptid;
DEPTID
------
20
50
4) study the fallowing pl/sql block and find the type of error ->syntax,semantic(logical) or precedence
begin
for i in 1..5 loop
update emp
set sal = 1000 where empno =100 ;
end loop;
end;
5) Difference between (MAX,MIN) and (GREATEST ,LEAST)
The functions max and min compares different rows . Whereas greatest and least work on a group of columns ,either actual or calculated values within a single row.
Ex. select * from emp_self;
EMP_NO EMPN_NAME SAL MGR
------ -------------------- ------- ----------
100 alen 9,000 105
200 boris 10,000 110
101 charles 8,000 105
SQL> select max(sal),min(sal) from emp_self;
MAX(SAL) MIN(SAL)
---------- ----------
10000 8000
SQL> select greatest(sal),least(sal) from emp_self;
GREATEST(SAL) LEAST(SAL)
------------- ----------
9000 9000
10000 10000
8000 8000
delete from emp where rowid not in (select max(rowid) from emp group by empno);
2) How to make a column into not null column.
Ans: alter table emp
modify (empno not null); ->this can be done only when all the values in empno are non null (i.e not empty)
?h To make a not null column into null column
alter table emp
modify (empno null);
The fallowing are the rules for
i. adding null or not null property
>You may change a column??s null property to not null only when that field does contain null values(i. It can??t be empty)
>At any time you may change a column??s not null property to null
ii. adding a colum to a table
>You may add a column at any time if NOT NULL isn??t specified (i.e when new column can accept null values )
>You may add a NOT NULL column in three steps.
a. Add the column without NOT NULL specified.
b. Fill every row in that column with data.
c. Modify the column to be NOT NULL.
iii. modifying a column
>You can increase a CHAR column??s width at any time.
>You can increase the number of digits and the number of decimal places in a NUMBER column at any time
>To CHANGE data types or to DECREASE column??s width the column should be null for every row
3)Write queries for the fallowing
ex. select * from emp_self;
EMP_NO EMPN_NAME SAL MGR DEPTID
--------- -------------------- ---------- ---------- ----------
400 jane 20000 110 20
102 Mary 19000 110 20
101 charles 8000 105 50
104 Linda 9000 100 10
110 john 25000 105 20
105 newton 2000 50
100 ALEN 15000 105 50
200 BORIS 3000 110 20
103 DAVID 10000 100 10
300 monica 7000 105 50
i) Query to get the employees who are working under mgr with salary > 10000
select emp_no,mgr from emp_self where mgr in (select emp_no from emp_self where sal > 10000);
ii) Query to get the employees who are getting salaries more than their managers
select a.emp_no from emp_self a,emp_self b where a.mgr =b.emp_no and a.sal > b.sal;
EMP_NO
-------
101
100
110
300
iii) Query to find the nth highest salary
select a.empn_name,a.sal from emp_self a where &n = (select count(*) from emp_self b where a.sal < b.sal);
When n=1 , sal =20000 ->second highest salary ; n=4 , sal =10000 ->fifth highest salary ;
iv) Query to find the second highest salary in different departments.
select a.deptid,min(a. sal) from emp_self a where 1 in (select count(*) from emp_self b
where a.sal < b.sal group by b.deptid) group by a.deptid;
DEPTID MIN(A.SAL)
------- ----------
10 9000
20 20000
50 8000
v) Query to find departments with total salary >25000
select deptid from emp_self having sum(sal) >25000 group by deptid;
DEPTID
------
20
50
4) study the fallowing pl/sql block and find the type of error ->syntax,semantic(logical) or precedence
begin
for i in 1..5 loop
update emp
set sal = 1000 where empno =100 ;
end loop;
end;
5) Difference between (MAX,MIN) and (GREATEST ,LEAST)
The functions max and min compares different rows . Whereas greatest and least work on a group of columns ,either actual or calculated values within a single row.
Ex. select * from emp_self;
EMP_NO EMPN_NAME SAL MGR
------ -------------------- ------- ----------
100 alen 9,000 105
200 boris 10,000 110
101 charles 8,000 105
SQL> select max(sal),min(sal) from emp_self;
MAX(SAL) MIN(SAL)
---------- ----------
10000 8000
SQL> select greatest(sal),least(sal) from emp_self;
GREATEST(SAL) LEAST(SAL)
------------- ----------
9000 9000
10000 10000
8000 8000
Introduction to DBA
1. What is a database instance ? Explain
A database instance (server) is a set of memory structures and background processes that access a set of database files.
The process can be shared by all users.
The memory structures that 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.
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 (Ver 7.0)
5. What is clustres ?
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 Base configuration of an oracle Database ?
It consists of
one or more data files
one or more control files
two or more redo log files
The database contains
Multiple users/schemas
one or more rollback segments
one or more tablespaces
Data 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.
A database instance (server) is a set of memory structures and background processes that access a set of database files.
The process can be shared by all users.
The memory structures that 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.
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 (Ver 7.0)
5. What is clustres ?
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 Base configuration of an oracle Database ?
It consists of
one or more data files
one or more control files
two or more redo log files
The database contains
Multiple users/schemas
one or more rollback segments
one or more tablespaces
Data 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.
Thursday, September 30, 2010
PL/SQL Interview Question
- Data types are NUMBER, CHAR/VARCHAR2, DATE & BOOLEAN.
- Arrays are not allowed & only one identifier per line is allowed.
- Attributes of PL/SQL objects are %TYPE, %ROWTYPE.
- PL/SQL Block is a standard PL/SQL code segment. Block consists of three parts.
- Declarative Section for variables, constants & exceptions. This section is optional.
- Executable Section which is mandatory.
- Exception Handlers which is optional.
- PL/SQL supports only DML i.e. INSERT, UPDATE, DELETE & SELECT...INTO.
- SQL Functions can be referenced within a SQL statement i.e. Numeric (SQRT,ROUND,POWER),
- Character (LENGTH,UPPER), DATE (ADD_MONTHS,MONTHS_BETWEEN) &Group (AVG,MAX,COUNT). Most SQL functions are available outside SQL statement except for group functions.
Syntax : LOOP
<Sequence of Statements>
END LOOP;
Code Numeric FOR Loops repeat a sequence of statements a fixed number of times.
Syntax : FOR <index> IN [[ REVERSE ]] <integer>..<integer> LOOP
<sequence of statements>
END LOOP;
<index> is implicitly of type number. Defined only within the loop & Value can be referenced in an expression, but a new value cannot be assigned to the index within the loop.
Code While Loops repeats a sequence of statements until a specific condition is no longer TRUE.
Syntax : WHILE <condition> LOOP
<sequence of statements>
END LOOP;
<condition> can be any legal PL/SQL condition & statements will be repeated as long as condition evaluates to TRUE.
Code GOTO Statements jumps to a different place in the PL/SQL block.
Syntax : GOTO label_name;
Legally use a GOTO a statement that is in the same sequence of statements as the GOTO.
In the sequence of statements that encloses the GOTO statement (outer block).
Labels can label any statement. Used as targets for GOTO statements, use labels for blocks and loops, Label a block to allow referencing of DECLAREd objects that would otherwise not be visible because of scoping rules, Label a block to allow a variable to be referenced that might be hidden by a column name, Label a loop to allow an object to be reference that would otherwise not be visible because of scoping rules & Label an EXIT as a convenient way to specify exits from outer loops.
Cursors are associated with every SQL DML statement processed by PL/SQL. Two types are Explicit i.e. Multiple row SELECT statements & Implicit i.e. INSERT, UPDATE, DELETE & SELECT...INTO statements. Implicit cursor is called the SQL cursor-it stores info concerning the processing of the last SQL statement not associated with an explicit cursor. OPEN, FETCH & CLOSE do not apply. All cursor attributes apply.
Cursor has to be explicitly defined when a query returns multiple rows to process beyond the first row returned by the query & to keep track of which row is currently being processed.
Declare the cursor to associate its name with a SELECT statement.
Syntax : DECLARE
CURSOR <cursor_name>
IS <regular_select_statement>;
Open the cursor to process the SELECT statement and store the returned rows in the cursor.
Syntax : OPEN <cursor_name>;
Fetch data from the cursor and store it in specified variables.
Syntax : FETCH <cursor_name> INTO <var1, var2...>;
Close the cursor to free up resources. Cursors must be closed before they can be reopened.
Syntax : CLOSE <cursor_name>
Explicit Cursor Attributes are %NOTFOUND, %FOUND, %ROWCOUNT & %ISOPEN.
Reference the current cursor row with the WHERE CURRENT OF statement. The cursor must be declared with a FOR UPDATE OF clause.
Syntax : WHERE CURRENT OF <cursor_name>
Reference Cursors FOR Loops to specify a sequence of statements to be repeated once for each row that is returned by the cursor with the Cursor FOR Loop.
Syntax : FOR <record_name> IN <cursor_name> LOOP
--statements to be repeated go here
END LOOP;
Cursor FOR loops (CFL) are similar to Numeric For Loops(NFL). CFL specify a set of rows from a table using the cursor's name. NFL specify an integer range. CFL record takes on vales of each row. NFL index takes on each value in the range. Record_name is implicitly declared as
record_name cursor_name%ROWTYPE
When a CFL is initiated, an implicit OPEN cursor_name is initiated.
For each row that satisfies the query associated with the cursor, an implicit FETCH is executed into the components of record_name.
When there are no more rows left to FETCH, an implicit CLOSE cursor_name is executed and the loop is exited.
Declare cursors to use parameters
Syntax : DECLARE
CURSOR <cursor_name> [[(param_name param_type)]]
IS <regular select statement>;
Exception Handlers : In PL/SQL, errors are called exceptions. When an exception is raised, processing jumps to the exception handlers. An exception handler is a sequence of statements to be processed when a certain exception occurs. When an exception handler is complete, processing of the block terminates. Two types are Predefined Internal Exceptions which corresponds to approximately 20 common ORACLE errors & Raised automatically by PL/SQL in response to an ORACLE error.
Eg.too_many_rows,no_data_found,invalid_cursor,value_errori.e. arithmetic,numeric,string,conversion or constraint error occurred, zero_divide, dup_val_on_index,cursor_already_open etc.
User-Defined Exceptions must be declared & must be RAISEd explicitly.
Only one handler per block may be active at a time & If an exception is raised in a handler, the search for a handler for the new exception begins in the enclosing block of the current block.
Exception-Init : Exceptions may only be handled by name not ORACLE error number. So, name an ORACLE error so that a handler can be provided specifically for that error.
Syntax : PRAGMA EXCEPTION_INIT (<user_defined_exception_name>, <ORACLE_error_number>);
SQLCODE & SQLERRM provides info on the exception currently being handled & especially useful in the OTHERS handler.
- SQLCODE returns the ORACLE error number of the exception, or 1 if it was a user-defined exception.
- SQLERRM returns the ORACLE error message associated with the current value of SQLCODE & can also use any ORACLE error number as an argument.
- SQLCODE & SQLERRM cannot be used within a SQL statement. If no exception is active SQLCODE = 0 & SQLERRM = 'normal, successful completion'.
Oracle Interview Question Part 1
What is the difference between file server and a database server ?
A file server just transfers all the data requested by all its client and the client processes the data while a database server runs the query and sends only the query output.
What is inheritance ?
Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it.
What are the two components of ODBC ?
1. An ODBC manager/administrator and
2. ODBC driver.
What is the function of a ODBC manager ?
The ODBC Manager manages all the data sources that exists in the system.
What is the function of a ODBC Driver ?
The ODBC Driver allows the developer to talk to the back end database.
What description of a data source is required for ODBC ?
The name of the DBMS, the location of the source and the database dependent information.
How is a connection establised by ODBC ?
ODBC uses the description of the datasource available in the ODBC.INI file to load the required drivers to access that particular back end database.
A file server just transfers all the data requested by all its client and the client processes the data while a database server runs the query and sends only the query output.
What is inheritance ?
Inheritance is a method by which properties and methods of an existing object are automatically passed to any object derived from it.
What are the two components of ODBC ?
1. An ODBC manager/administrator and
2. ODBC driver.
What is the function of a ODBC manager ?
The ODBC Manager manages all the data sources that exists in the system.
What is the function of a ODBC Driver ?
The ODBC Driver allows the developer to talk to the back end database.
What description of a data source is required for ODBC ?
The name of the DBMS, the location of the source and the database dependent information.
How is a connection establised by ODBC ?
ODBC uses the description of the datasource available in the ODBC.INI file to load the required drivers to access that particular back end database.
Oracle Interview Question
How to implement ISNUMERIC function in SQL *Plus ?
Method 1:
Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;
Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)
Method 2:
select instr(translate('wwww',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;
It returns 0 if it is a number, 1 if it is not.
----------------------------------------------
What is the difference between Truncate and Delete interms of Referential Integrity?
DELETE removes one or more records in a table, checking referential
Constraints (to see if there are dependent child records) and firing any
DELETE triggers. In the order you are deleting (child first then parent)
There will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers.
Also, it only checks for the existence (and status) of another foreign key
Pointing to the table. If one exists and is enabled, then you will get
The following error. This is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You should disable the foreign key constraints in the child tables before
issuing the TRUNCATE command, then re-enable them afterwards.
Method 1:
Select length (translate (trim (column_name),' +-.0123456789',' ')) from dual ;
Will give you a zero if it is a number or greater than zero if not numeric (actually gives the count of non numeric characters)
Method 2:
select instr(translate('wwww',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X')
FROM dual;
It returns 0 if it is a number, 1 if it is not.
----------------------------------------------
What is the difference between Truncate and Delete interms of Referential Integrity?
DELETE removes one or more records in a table, checking referential
Constraints (to see if there are dependent child records) and firing any
DELETE triggers. In the order you are deleting (child first then parent)
There will be no problems.
TRUNCATE removes ALL records in a table. It does not execute any triggers.
Also, it only checks for the existence (and status) of another foreign key
Pointing to the table. If one exists and is enabled, then you will get
The following error. This is true even if you do the child tables first.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
You should disable the foreign key constraints in the child tables before
issuing the TRUNCATE command, then re-enable them afterwards.
Subscribe to:
Posts (Atom)