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.***

Saturday, 10 November 2012

DBA Interview Question with Answer part 12

I exported one table with a name of user, how to import that table with another name of user?
IMPDP user2/pwd REMAP_SCHEMA=user1:user2 DUMPFILE=test.DMP DIRECTORY=abc ;
IMPDP user2/pwd directory=directory_name tables=table_name dumpfile=dump_name.dmp;
SQL>Grant read, write on directory directory_name to public;
SQL>Grant read, write on directory <dir_name> to <user>;
Just careful to give grant to public if it is production Environment
I have two server of same configuration having single database of 10GB and 20 GB size respectively, I want to merge into single server what are the prerequisites and steps to follow in this case.
In my view Export/Import is the best solution to merge the database. You can export the schemas from one database and import it into other database.
Can one monitor how fast a table is imported?
If you need to monitor how fast rows are imported from a running import job, try one of the following methods:
Method 1: 
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
If the import has more than one table, this statement will only show information about the current table being imported.  
Method 2:
Use the FEEDBACK=n import parameter. This command will tell IMP to display a dot for every N rows imported.
How we will increase performance on particular table? Here I am inserting 2GB data in table, its takes more time to insert in a table. Is there any way to increase performance on a particular table?
Index on huge table while doing insert will not only solution to improve performance. Get your table partitioned that will make table insertion faster and also easy to manage the archive data. Alternatively do one thing first disable constraints as well as index then perform insertion then again enable.
You can use high-speed solid-state disk (RAM-SAN) to make Oracle inserts run up to 300x faster than platter disk.
How to reduce alert log Size?
If you move or delete your Alert log file, it is recreated automatically in next startup, alternatively you can put a script at OS level to move the archives and use new one. So the best way to reduce the size of log is just move your aler.log to some other place. Oracle will recreate it in next startup.
How you will know the instance is Primary or Standby?
By querying v$database one can tell if the host is primary or standby
On the primary database:
SQL> select database_role from v$database;
OR check the value of controlfile_type in V$database i.e is “CURRENT” for primary and "STANDBY" for standby
SQL> SELECT controlfile_type FROM V$database;
On the Standby database:
SQL> select database_role from v$database;
SQL> SELECT controlfile_type FROM V$database;
Note: You may need to connect to as sys if the instance is in mount state
How would you determine what sessions are connected and what resources they are waiting for?
Give two methods you could use to determine what DDL changes have been made.
You could use Logminer or Streams
How would you determine who has added a row to a table?
Turn on fine grain auditing for the table.
Explain the differences between PFILE and SPFILE
A PFILE is a Static, text file that initializes the database parameter in the moment that it’s started. If you want to modify parameters in PFILE, you have to restart the database.
A SPFILE is a dynamic, binary file that allows you to overwrite parameters while the database is already started (with some exceptions).
Name some clients that can connect with Oracle?
There are several such as SQL Developer, SQL-Plus, TOAD, dbvisualizer, PL/SQL Developer.
In which view can you find information about every view and table of oracle dictionary?
DICT or DICTIONARY view. You can query as:
How can we change which databases are started during a reboot in Linux Env.?
Edit the /etc/oratab
How can we reduce the space of TEMP datafile?
Prior to Oracle 11g, you have to re-create the datafile. In Oracle 11g a new feature was introduced and you can shrink the TEMP tablespace.
How can you view all the current users connected in your database in this moment?
What is the difference between a view and a materialized view?
A view is a select that is executed each time a user accesses to it. A materialized view stores the result of this query in memory for faster access purposes.
Can we have different database versions in the same RAC Env.?
Yes, but Clusterware version must be greater than the database version.
How can you difference a usual parameter and an undocumented parameter of oracle?
The undocumented parameters have the prefix ‘_’. Such as: _allow_resetlogs_corruption
What should be the result of logical comparision (NULL != NULL)
‘False’ in both cases:
In case of “SELECT * FROM MY_SCHEMA.MY_TABLE” why we are getting this error: SP2-0678: Column or attribute type can not be displayed by SQL*Plus?
Check for sure the table has a BLOB column.
Which are the default passwords of SYSTEM/SYS?
Is it possible to center an object horizontally in a repeating frame that has a variable horizontal size?
Can a field be used in a report without it appearing in any data group?
When a form is invoked with call_form, Does oracle forms issues a save point?
You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
Explain the difference between a data block, an extent and a segment.
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.
What column differentiates the V$ views to the GV$ views and how?
The INST_ID column which indicates the instance in a RAC environment the information came from.
How would you go about increasing the buffer cache hit ratio?
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.
How would you determine the time zone under which a database was operating?
select DBTIMEZONE from dual;
Explain the use of setting GLOBAL_NAMES equal to TRUE.
Setting GLOBAL_NAMES indicates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.
What background process refreshes materialized views?
The Job Queue Processes.
When a user process fails, what background process cleans up after it?
What are the roles and user accounts created automatically with the database?
DBA - role Contains all database system privileges.
SYS user account - The DBA role will be assigned to this account. All of the base tables and views for the database's dictionary are store in this schema and are manipulated only by ORACLE.
SYSTEM user account - It has all the system privileges for the database and additional tables and views that display administrative information and internal tables and views used by oracle tools are created using this username.
What are the minimum parameters should exist in the parameter file (init.ora) ?
DB NAME - Must set to a text string of no more than 8 characters and it will be stored inside the datafiles, redo log files and control files and control file while database creation.
DB_DOMAIN - It is string that specifies the network domain where the database is created. The global database name is identified by setting these parameters
(DB_NAME & DB_DOMAIN) CONTORL FILES - List of control filenames of the database. If name is not mentioned then default name will be used. 
DB_BLOCK_BUFFERS - To determine the no of buffers in the buffer cache in SGA.
PROCESSES - To determine number of operating system processes that can be connected to ORACLE concurrently. The value should be 5 (background process) and additional 1 for each user.
ROLLBACK_SEGMENTS - List of rollback segments an ORACLE instance acquires at database startup. Also optionally LICENSE_MAX_SESSIONS,LICENSE_SESSION_WARNING and LICENSE_MAX_USERS.
What is the difference between NAME_IN and COPY ?
Copy is package procedure and writes values into a field.
Name in is a package function and returns the contents of the variable to which you apply.
How do you implement the If statement in the Select Statement
We can implement the if statement in the select statement by using the Decode statement. e.g select DECODE (EMP_CAT,'1','First','2','Second'Null); Here the Null is the else statement where null is done .
How many rows will the following SQL return?
Select * from emp Where rownum = 10;
No rows
Can dual table be deleted, dropped or altered or updated or inserted?


Post a Comment