1)How to execute DOS Commands from SQL Prompt?
ans) By using HOST we can execute the dos commands
2)What are CBO and RBO? What is the diff between these two?
ans) Cost Based optimization
for details you can go to this link http://docs.oracle.com/cd/B10501_01/server.920/a96533/opt_ops.htm#1656
Role Based optimization
for details you can go to this link http://docs.oracle.com/cd/B10501_01/server.920/a96533/rbo.htm#721
3) What is the RND and WND?
ans) RND – Read No Database
WND – Write No Database
4) What is CURSOR? What are the Cursor types? What are cursor declaration steps?
ans) Cursor is nothing but a private SQL work area which is used to store process information.
Types: 1.Implicit, 2.Explicit
cursor declaration steps:
-------------------------- 1. Declare
2. Open
3. Close
Cursor Attributes:
------------------
1. %not found
2. %found
3. %is open
4. %row count
5) What is the diff between Implicit and Explicit and Ref Cursor?
ans)
1.Implicit – It is defined by the Oracle Server for queries that return only one row.
2.Explicit – Which is defined by the Users, for queries that return more than one row
3.Ref – With this we can change the select statement dynamically.
for this first we have to declare ref cursor. and we have to associate a select
statement at run time. before going to associate another select statement
we have to close the cursor and again we have to open cursor.
6) What is Procedure and what is Function?
ans) Procedure: Is used perform an action
Function: Is used to compute a value
procedure:
---------- generally we will use procedures when we have to perform dml operations and
procedure no need to return a value.
function:
--------- generally we will use functions to perform computation's and function
must return a value.
Procedure function
1.may or may not return a value. 1.Function must return a value.
2.can't call from select statement. 2.can call from select statement.
3. It can return more than one value through 3. It can return only one value.
OUT Parameter.
7). If we drop the table which we have used in the procedure do we need to recompile
the procedure then how?
ans) No need to Recompile the procedure.
error: <SCHEMA>.<PROCEDURE NAME> INVALID.
to recompile the procedure, use the below command.
- ALTER PROCEDURE proc1 compile;
8). How to get the Procedure Source code from database?
ans) To get the procedure source code from the database u need to query the below the query
select TEXT from user_source where object_type='PROCEDURE' and object_name='PROC1';
9). What are the other objects we can group inside of Package?
ans) Packages provide a method of encapsulating related procedures, functions, and
associated cursors and variables together as a unit in the database.
Applications for Packages:
Packages are used to define related procedures, variables, and cursors and are
often implemented to provide advantages in the following areas:
1.encapsulation of related procedures and variables
2.declaration of public and private procedures, variables, constants, and cursors
3.separation of the package specification and package body
4.better performance:
--------------------
Using packages rather than stand-alone stored procedures results
in the following improvements:
1.The entire package is loaded into memory when a procedure within the
package is called for the first time. This load is completed in one operation,
as opposed to the separate loads required for standalone procedures.
Therefore, when calls to related packaged procedures occur,
no disk I/O is necessary to execute the compiled code already in memory.
2.A package body can be replaced and recompiled without affecting the specification.
As a result, objects that reference a package's constructs
(always via the specification) never need to be recompiled unless
the package specification is also replaced. By using packages,
unnecessary recompilations can be minimized,
resulting in less impact on overall database performance.
10). Can we declare Procedure directly in the package body without declaring
in the package specification?
ans) No, We can not declare procedure directly in package body.
11).Can we commit inside of trigger? How to delete the Trigger?
How many triggers we can use maximum?
ans:
=== yes, we can issue commit inside of the trigger body by using the
autonomus transactions.
drop trigger j_trg;
There is no limit on triggers.
12). What are SRW Packages we have?
ans:
----
SRW.DO_SQL (DDL &DML STMT).
SRW.RUN_REPORT.
13).How to recompile the invalid objects?
ans). You can invoke the utl_recomp package To recompile the invalid objects.
Schema Level
-------------------
EXEC UTL_RECOMP.RECOMP_SERIAL('SCOTT');
EXEC UTL_RECOMP.RECOMP_PARALLEL(4, 'SCOTT');
Database Level
--------------------
EXEC UTL_RECOMP.RECOMP_SERIAL( );
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);
using job_queue_processes_ value.
------------------------------------------
EXEC UTL_RECOMP.RECOMP_PARALLEL( );
EXEC UTL_RECOMP.RECOMP_PARALLEL(NULL, 'SCOTT');
14) How to find the invalid objects?
ans). to find the invalid objects we have to execute the following query.
select owner
,object_type
,object_name
from dba_objects
where
status != 'VALID'
ORDER BY
owner,
object_type;
Here is a script to recompile the invalid pl/sql packages, procedures and functions.
You may need to run it more than once for dependencies, if you get errors from the script.
INVALID.SQL
Set Heading off;
Set feedback off;
Set lines 999;
Spool run_invalid.sql
select 'ALTER '|| OBJECT_TYPE || ' ' ||OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS= 'INVALID'
AND OBJECT_TYPE IN ('PACKAGE','FUNCTION','PROCEDURE');
SPOOL OFF;
set heading on;
set feedback on;
set echo on;
@run_invalid.sql
ans) By using HOST we can execute the dos commands
2)What are CBO and RBO? What is the diff between these two?
ans) Cost Based optimization
for details you can go to this link http://docs.oracle.com/cd/B10501_01/server.920/a96533/opt_ops.htm#1656
Role Based optimization
for details you can go to this link http://docs.oracle.com/cd/B10501_01/server.920/a96533/rbo.htm#721
3) What is the RND and WND?
ans) RND – Read No Database
WND – Write No Database
4) What is CURSOR? What are the Cursor types? What are cursor declaration steps?
ans) Cursor is nothing but a private SQL work area which is used to store process information.
Types: 1.Implicit, 2.Explicit
cursor declaration steps:
-------------------------- 1. Declare
2. Open
3. Close
Cursor Attributes:
------------------
1. %not found
2. %found
3. %is open
4. %row count
5) What is the diff between Implicit and Explicit and Ref Cursor?
ans)
1.Implicit – It is defined by the Oracle Server for queries that return only one row.
2.Explicit – Which is defined by the Users, for queries that return more than one row
3.Ref – With this we can change the select statement dynamically.
for this first we have to declare ref cursor. and we have to associate a select
statement at run time. before going to associate another select statement
we have to close the cursor and again we have to open cursor.
6) What is Procedure and what is Function?
ans) Procedure: Is used perform an action
Function: Is used to compute a value
procedure:
---------- generally we will use procedures when we have to perform dml operations and
procedure no need to return a value.
function:
--------- generally we will use functions to perform computation's and function
must return a value.
Procedure function
1.may or may not return a value. 1.Function must return a value.
2.can't call from select statement. 2.can call from select statement.
3. It can return more than one value through 3. It can return only one value.
OUT Parameter.
7). If we drop the table which we have used in the procedure do we need to recompile
the procedure then how?
ans) No need to Recompile the procedure.
error: <SCHEMA>.<PROCEDURE NAME> INVALID.
to recompile the procedure, use the below command.
- ALTER PROCEDURE proc1 compile;
8). How to get the Procedure Source code from database?
ans) To get the procedure source code from the database u need to query the below the query
select TEXT from user_source where object_type='PROCEDURE' and object_name='PROC1';
9). What are the other objects we can group inside of Package?
ans) Packages provide a method of encapsulating related procedures, functions, and
associated cursors and variables together as a unit in the database.
Applications for Packages:
Packages are used to define related procedures, variables, and cursors and are
often implemented to provide advantages in the following areas:
1.encapsulation of related procedures and variables
2.declaration of public and private procedures, variables, constants, and cursors
3.separation of the package specification and package body
4.better performance:
--------------------
Using packages rather than stand-alone stored procedures results
in the following improvements:
1.The entire package is loaded into memory when a procedure within the
package is called for the first time. This load is completed in one operation,
as opposed to the separate loads required for standalone procedures.
Therefore, when calls to related packaged procedures occur,
no disk I/O is necessary to execute the compiled code already in memory.
2.A package body can be replaced and recompiled without affecting the specification.
As a result, objects that reference a package's constructs
(always via the specification) never need to be recompiled unless
the package specification is also replaced. By using packages,
unnecessary recompilations can be minimized,
resulting in less impact on overall database performance.
10). Can we declare Procedure directly in the package body without declaring
in the package specification?
ans) No, We can not declare procedure directly in package body.
11).Can we commit inside of trigger? How to delete the Trigger?
How many triggers we can use maximum?
ans:
=== yes, we can issue commit inside of the trigger body by using the
autonomus transactions.
drop trigger j_trg;
There is no limit on triggers.
12). What are SRW Packages we have?
ans:
----
SRW.DO_SQL (DDL &DML STMT).
SRW.RUN_REPORT.
13).How to recompile the invalid objects?
ans). You can invoke the utl_recomp package To recompile the invalid objects.
Schema Level
-------------------
EXEC UTL_RECOMP.RECOMP_SERIAL('SCOTT');
EXEC UTL_RECOMP.RECOMP_PARALLEL(4, 'SCOTT');
Database Level
--------------------
EXEC UTL_RECOMP.RECOMP_SERIAL( );
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);
using job_queue_processes_ value.
------------------------------------------
EXEC UTL_RECOMP.RECOMP_PARALLEL( );
EXEC UTL_RECOMP.RECOMP_PARALLEL(NULL, 'SCOTT');
14) How to find the invalid objects?
ans). to find the invalid objects we have to execute the following query.
select owner
,object_type
,object_name
from dba_objects
where
status != 'VALID'
ORDER BY
owner,
object_type;
Here is a script to recompile the invalid pl/sql packages, procedures and functions.
You may need to run it more than once for dependencies, if you get errors from the script.
INVALID.SQL
Set Heading off;
Set feedback off;
Set lines 999;
Spool run_invalid.sql
select 'ALTER '|| OBJECT_TYPE || ' ' ||OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS= 'INVALID'
AND OBJECT_TYPE IN ('PACKAGE','FUNCTION','PROCEDURE');
SPOOL OFF;
set heading on;
set feedback on;
set echo on;
@run_invalid.sql
