Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 13 June 2012

Interview Question with Answer part 11

What is SID and what is it used for? Where can I find out the SID of my database?
The SID is a site identifier.  It plus the Oracle_home are hashed together in Unix to create a unique key name for attaching an SGA. If your Oracle_sid or Oracle_home is not set correctly, you will get "oracle not available". you can get instance name with the following command:
select instance from v$thread;
select instance_name from v$instance;
If you are buying a new server that will be a mirror image of the current Production Server what would be the step for that?
In same environment and directory structure setup server, install oracle, use oradim to setup the registry (register the instance) and restore from backup.
I am cloning database A as database B, both exactly identical, running in NOARCHIVELOG mode. Database A will be shutdown before copying files. I am using the CREATE CONTROLFILE statement to clone.
a) Do I need to copy redo log files from A to B if I need to open B with RESETLOGS option?
b) Do I need to copy control files from A to B since I will be creating controlfile for B?
a) You do not need to, but you would avoid having to open resetlogs if that makes you feel better.
b) Not if you are doing the create controlfile trick.  You could just copy EVERYTHING, startup mount, and issue a series of alter database rename file 'old name' to 'new names'; and then alter database open (assuming logs are in the same place, else you'll drop and create them).
Note: My understanding is that if you use RESETLOGS option in CREATE CONTROLFILE, the redo log files will be created by Oracle as per the specifications given in the create controlfile statement.
I have a new server. What is the best way I can have the same oracle setup that is there on a prodn db? Either we need to restore the file systems and relink oracle without doing any installation?
My suggestion is install the same software on another server then then apply restore and recover procedure on the same environment or directory structure.
No idea about "relink oracle without doing any installation", see the admin guide for your OS for details on things like this.
There is any difference between Oracle TCL and DCL command? 
DCL stands for Data Control Language. These command are used to configure and control database objects such as GRANT, REVOKE where as TCL stands for Transaction Control language. It is used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions such as
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use 
What happens when the lock is disabling on the table?
When you disabling the lock on table then you are not able to perform DDL operation on that table but you still to manage DML operation easily
For Example:
Create Table s1 (Eno number(2), ename varchar2(15), salary number(5,2));
insert into s1 values (1, 'shahid', 400);
insert into s1 values (1, 'javed', 200);
insert into s1 values (2, 'karim', 100);
--disable lock on table
Alter table s1 disable table lock;
-- cannot drop/truncate table as table lock is disable
drop table s1;
truncate table s1;
--you cannot able to add/modify/drop column
Alter table s1 add comm number(5,2);
Alter table s1 modify s1 salary number (10,4);
Alter table s1 drop column salary;
-- But still you are able to perform DML
update s1 set salary= 800 where eno=2;
select * from s1;
delete from s1 where eno=2;
insert into s1 values (2, 'mohan', 250);
What is the importance of clock time in case of database cloning?
My personal experience sometimes just cloning a database is not enough if moving it to another machine you also have to ensure:
1. The environment on the new machine is setup, to match the cloned system this would include memory & disc allocation space.
2. The "new" machine time is the same or greater than the machine you were cloning from
How much space does it take to clone a database?
The clone needs the same space.
In which case %LIKE (before or after use) operator performance increases?
LIKE% works the fastest because it uses the index to search on the column provided an index is specified on the column. Using % after LIKE, results in faster results.
Do you have idea about Fuzz testing or fuzzing?
Fuzz testing or fuzzing is a software testing technique that provides random data ("fuzz") to the inputs of a program. If the program fails (for example, by crashing, or by failing built-in code assertions), the defects can be noted. The great advantage of fuzz testing is that the test design is extremely simple, and free of preconceptions about system behavior.
Using the expdp/impdp (Data Pump in 10g), can export and import data from one schema/Database to another schema/Data is it possible?
Yes, you can use dblink for that
What is DataMapper?
DataMAPPER is a high-performance data migration tool designed for large-scale data movement projects. Its distinct client/server design allows users to work in a graphical environment, without sacrificing the performance.
How to Start Enterprise Manager from command line?
C:\emctl start dbconsole
Now type on the browser http://localhost.localdomain:5500/em/
How will you find current and max utilization of session and number of processes?
SQL>select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in ('processes','sessions');
---------------------   ------------------- ---------------
processes                               14              18
sessions                                12              17
As the table is being modified, can ROWID of a row change?
A rowid is assigned to a row upon insert and is imutable (never changing) unless the row is deleted and re-inserted (meaning it is another row, not the same row!).
What happened when I updated narrow rows, setting character to wide values?
In this case row will migrate but the rowid for the row stays the same even when the row migrate.
Session 1: retrieves a row with rowid X
Session 2: deletes the row with rowid X, commits
/* rowid X is now free for re-use */
Session 3: inserts a new row with rowid X, commits
Session 1: update .... where rowid = X
Session 1's update is not updating the same row that it had earlier retrieved.
Consider the above scenario what should be the solution
Use the Primary Key with the table. If you combine rowid with the primary key then it will be perfectly safe to use rowid id in all cases.
If you have a single delete statement that deletes many records using rowids. Would there ever be a time when the rowid within this table change during the execution of this delete statement?
In order for a rowid to change you have to enable row movement first so if row movement is not enabled then answer is NO. If it is, then flashback table could change a rowid incase of DDL statement and would not happen concurrently with a delete (so it would not affect it).
For Example:
Alter table s1 shrink space compact, that moves rows and would change rowids.
Update of a partition key that causes a row to move, that moves rows and would change rowids.
If I fire two inserts in a table, whether the rowid of the 2nd record will be greater than
rowid of the 1st record?
The answer is NO see the example below
if you insert A
then    insert B
later    insert C
           delete A
           insert D
It is quite possible in above example that D will be "first" in the table as it took over A's place. If rowids always "grew", than space would never be reused (that would be an implication of rowids growing always. We would never be able to reuse old space as the rowid is just a file.block.slot-on-block - a physical address).
Difference between Stored Procedure and Macro?
Stored Procedure:
It does not return rows to the user.
It has to use cursors to fetch multiple rows
It used Inout/out to send values to user
It is stored in DATABASE or USER PERM
A stored procedure also provides output/Input capabilities
It returns set of rows to the user.
It is stored in DBC PERM space
A macro that allows only input values
If the port 1521 is default port for the TNSLinstener. I have a database server on port 1527 how can I make the clients connect on this port or can I have one listener service connect to listen for 2 servers?
If you are using "Host naming" convention (this is a method that does not require the client to have a tnsnames.ora file at all.  You must be using TCP or you must only have one default database per host.  The client only needs to know the hostname of the server to connect) then yes, 1521 is the default and only port.
If you are using tnsnames.ora, the Oracle nameserver, or any other method to connect then no, 1521 is not a default port.  In this case, 1521 is simply the port used by "convention".  The clients would, typically in their tnsnames.ora, connect to the listener on some specified port number.  1521 is the convention used by many people; it is neither mandatory nor necessary.
What is an IPC protocol and where and how it is used? I have experience only in TCP/IP protocol. Is there any advantage in using IPC over TCP?
IPC is interposes communication you have messages, pipes, socket pairs and so on it is alot like just using sockets with TCP/IP. IPC is generally limited to "a machine", not over a network. IPC used to be a tad faster than TCP but recent tests have shown this to be less and less true.
In your absence any body has done any alteration then how did you notice or How to know last DDL fired from the particular schema and particular table?
To find the last ddl performed check out the last_ddl_time from all_objects, dba_objects, user_objects view because each time and object changes the last_ddl_time is updated from these view.
Select CREATED, TIMESTAMP, last_ddl_time from all_objects
In the above query ‘HRMS’ is the schema name and payroll_main_file is the table name.
How to find tables that have a specific column name?
SELECT owner, table_name, column_name
FROM dba_tab_columns
WHERE column_name like 'AMOUNT'
ORDER by table_name;
Differentiate Row level and statement level Trigger?
Row Level Trigger is fired each time row is affected by Insert, Update or Delete command. If statement doesn’t affect any row then no trigger action happens where as Statement level trigger fires when a SQL statement affects the rows of the table. The trigger activates and performs its activity irrespective of number of rows affected due to SQL statement. They get fired once for each triggering statement.


  1. Hi there, I enjoy reading through your post. I wanted to
    write a little comment to support you.
    my site - utility to restore Microsoft Outlook exchange

  2. Searching for the Best Dating Website? Create an account to find your perfect match.