Interview question for oracle DBA

INTERVIEW QUESTION FOR ORACLE DBA


for UNIX

1. How do you see how many instances are running?-
2. How do you automate starting and shutting down of databases in Unix?
3. You have written a script to take backups. How do you make it run automatically every week?
4. What is OERR utility?
5. How do you see Virtual Memory Statistics in Linux?
6. How do you see how much hard disk space is free in Linux? -
7. What is SAR? http://www.ibm.com/developerworks/aix/library/au-
8. What is SHMMAX?
9. Swap partition must be how much the size of RAM?
10. What is DISM in Solaris?
11. How do you see how many memory segments are acquired by Oracle Instances?
12. How do you see which segment belongs to which database instances?
13. What is VMSTAT? See Question no 5
14. How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?
15. How do you remove Memory segments?
16. What is the difference between Soft Link and Hard Link?
17. What is stored in oratab file? -->
18. How do you see how many processes are running in Unix?
19. How do you kill a process in Unix?
20. Can you change priority of a Process in Unix?




oracle question


what is sga?
diff bw hot backup & coldbackup
diff bw 9i & 10g features
what is rac and how it is faster then other database
what is temp tablepspace? how will you add size in temp tbsp?
RELM
which backup you have in your organisation?
export & import
tell about ur project
what Performance Tuning you have handled for ur project?
what is shared pool?
tablespace concepts
have u handled any db crash? if so how u rectified?
you lost control file? so how do u recover the file without backup?


what is tbsp?
what is partitions?
how do u find which sessions are connected to the db?
which 2 network file have been used while creating a database?
what is triggers? have u used in ur project?
tell abt ur project
diff bw temp tbsp and permanent tbsp
what is the tool / utility u have used in your orgn?
tell abt logical backup and what backup u did for ur orgn?
what is sql * loader?
performance tuning
constraints , diff bw primary key and unique
how do u resize and add the datafile?
what is oracle precompiler?


what is collections
select * from emp
where empno=<******>
group by <*****>
orderby <******>;
what this above command does internally in the oracle?
psedocolumns
diff bw function and procedure
merge cmd
have u used functions, procedures and triggers in ur project?
how do u analyse diff types of functions in a table?
what is sysnonyms and types



tell abt urself
have u done cloning
tell abt hot backup and steps
Hve u done performance tuning work?
one question was based on AWR report.. but i am not sure what it was
how do u rename a database
while importing a table, can u send datas from one user to another use?
list 10 parameters in pfile

EXPORT AND IMPORT USING DATAPUMP

C:\Documents and Settings\Chetan>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 30 11:55:29 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME

---------

ORA9

SQL> create directory data_p_test as 'c;\data_p_test';

Directory created.

Create manually a directory in the same location with same name ..

SQL> grant read ,write on directory data_p_test to scott;

Grant succeeded.

SQL> select * from dba_directories;

to check you directory is created or not.........................

SQL> grant exp_full_database to scott;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options.

C:\Documents and Settings\Chetan>expdp scott/tiger@ora9 full=y directory=data_p_test dumpfile=full.dmp logfile=full.log

Now connect with your new database where u wanna import .

my database name is "newtest"

SQL> create directory data_p_test as 'c:\data_p_test';

Directory created.

SQL> grant read ,write on directory data_p_test to scott;

Grant succeeded.

SQL> grant imp_full_database to scott;

Grant succeeded.

SQL> alter tablespace temp add tempfile 'tempfilelocation' size 200M autoextend on next 30M;

Come on your command prompt

C:\Documents and Settings\Chetan>impdp scott/tiger@newtest  full=y directory=data_p_test dumpfile=full.dmp logfile=full2.log

for export and import table  use all above command and just use the blow command for imp and exp

 export
SQL>expdp scott/tiger@new test tables=CHETAN directory=table_test dumpfile=table.dmp logfile=tableimp.log;

import

SQL>impdp scott/tiger@ora9 tables=CHETAN directory=table_test dumpfile=table.dmp logfile=tableimp2.log;

any mistake is there or u wanna give any suggestion so please write to me ............

how to create a controlfile

CREATE CONTROLFILE SET DATABASE "fsdemo" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'E:\oracle\product\10.2.0\oradata\fsdemo\redo01.log' SIZE 200M,
GROUP 2 'E:\oracle\product\10.2.0\oradata\fsdemo\redo02.log' SIZE 200M,
GROUP 3 'E:\oracle\product\10.2.0\oradata\fsdemo\redo03.log' SIZE 200M
DATAFILE
'E:\oracle\product\10.2.0\oradata\fsdemo\system01.dbf',
'E:\oracle\product\10.2.0\oradata\fsdemo\undotbs01.dbf',
'E:\oracle\product\10.2.0\oradata\fsdemo\users01.dbf'
CHARACTER SET WE8ISO8859P1;

"fsdemo" is the  database name for which u wanna create controlfile

"E:\oracle\product\10.2.0\oradata\fsdemo\" this is the path where your logfile and datafile are store .
just past this file and edit path and name . and save it as "control.sql' in notepad
run this file from sql promt
SQL>@control.sql

remember your database should be in no mount stage  for creating controlfile

any mistake is there or u wanna give any suggestion so please write to me ............