Best Realtime Oracle DBA Interview Questions with Answers

1) How can you see the Current SCN number of the database?

> Select current_scn from v$database;

2) How can you see the Current log sequence number the logwriter is writing in to?
> Select * from v$log;

3) If you are given a database, how will you know how many datafiles each tablespace contain?
> Select distinct tablespace_name,file_name from dba_data_files;

4). How will you know which temporaray tablepsace is allocated to which user?
> Select temporary_tablespace from dba_users where username=’SCOTT’;

5) If you are given a database,how will you know whether it is locally managed or dictionary managed?
> Select extent_management from dba_tablespaces where tablespace_name=’USERS’;
6) How will you list all the tablespaces and their status in a database?
> Select tablespace_name,status from dba_tablespaces;

7) How will you find the system wide 1) default permanent tablespace, 2) default temporary tablespace 3) Database time zone?
> Select property_name,property_value from database_properties where property_name like ‘%DEFAULT%’;

8) How will you find the current users who are using temporary tablespace segments?
> V$TEMPSEG_USAGE

9) How will you convert an existing dictionary managed permanent tablespace to temporary tablespace?
> Not possible

10) Is media recovery requird if a tablespace is taken offline immediate?
> Not required

11) How will you convert dictionary managed tablespace to locally managed tablespace?
> Exec dbms_space_admin.tablespace_migrate_to_local(‘TABLESPACE_NAME’);

12) If you have given command to make a tablespace offline normal, but its not happening.it is in transactional read-only mode. How will you find which are the transactions which are preventing theconversion?
> By looking at queries using by those SID (u can get script from net). I suspect question is not clear.

13) If you drop a tablespace containing 4 datafiles, how many datafiles will be droped at a time by giving a single drop tablespace command?

> All datafiles

14) If database is not in OMF,How will you drop all the datafiles of a tablespace without dropping the tablespace itself?
> Alter database datafile ‘PATH’ offline drop;

15) How will you convert the locally managed tablespace to dictionay managed?What are the limitations?
> Exec dbms_space_admin.tablespace_migrate_from_local(‘TABLESPACE_NAME’);

SYSTEM tablespace should be dictionary

16) Which parameter defines the max number of datafile in database?
> Db_files and MAXDATAFILES in control file

17) Can a single datafile be allocated to two tablespaces?Why?
> No. because segments cannot space multiple datafiles

18) How will you check if a datafile is Autoextinsible?
> Select autoextensible from dba_data_files where file_name=’’;

19) Write command to make all datafiles of a tablespace offline without making the tablspace offline itself?
> Alter database datafile ‘PATH’ offline normal;

20) In 10g, How to allocate more than one temporary tablespace as default temporary tablespace to a single user?
> By using temporary tablespace group

21) What is the relation between db_files and maxdatafiles parameters?
> Both will restrict no of datafiles in the database

22) Is it possible to make tempfiles as read only?
> yes

23) What is the common column between dba_tablespaces and dba_datafiles?
> Tablespace_name

24) Write a query to display the names of all dynamic performance views?
> Select table_name from dictionary where table_name like ‘v$%’;

25) Name the script that needs to be executed to create the data dictionary views after database creation?
> Catalog.sql

26) Grant to the user SCOTT the RESTRICTED SESSION privilege?
SQL> grant restricted session to scott;
Grant succeeded.

27) How are privileged users being authenticated on the database you are currently working on? Which initialization parameter would give me this information?
> Question not clear

28) Which dynamic performance view gives you information about all privileged users who have been granted sysdba or sysoper roles? Query the view?
SQL> desc v$pwfile_users

29) What is the purpose of the DICTIONARY table?
> To know data dictionary and dynamic performance view names

30) Write a query to display the file# and the status of all datafiles that are offline?
> Select file#,status from v$datafile where status=’OFFLINE’;

31) Write the statement to display the size of the System Global Area (SGA)?
> Show parameter sga
Or
> Show sga

32) Obtain the information about the current database? What is its name and creation date?
> Select name,created from v$database;

33) What is the size of the database buffer cache? Which two initialization Parameters are used to determine this value?

> Db_cache_size or db_block_buffers

34) What value should the REMOTE_LOGIN_PASSWORDFILE take if you need to set up Operating System authentication?
> exclusive

35) Which initialization parameter holds this value? What does the shared pool comprise of?
> Library cache and data dictionary cache.
> Parameter : shared_pool_size

36) Which initialization parameter holds the name of the database?
> Db_name

37) Which dynamic performance view displays information about the active transactions in the database? Which view returns session related information?
> V$transaction, v$session

38) Which dynamic performance view is useful for killing user sessions? Which columns of the view will you require to kill a user session? Write the statement to kill any of the currently active sessions in your database?
> V$session (SID, SERAIL#)
> Alter system kill session ‘SID,SERIAL#’;

39) What is the difference between the ALTER SYSTEM and ALTER SESSION commands?
> Changes performed using ALTER SYSTEM are either permanent for the memory or database. But for ALTER SESSION, its only for that session

40) Write down the mandatory steps that a DBA would need to perform before the CREATE DATABASE command may be used to create a database?
> Create a pfile or spfile
> Create password file
> If windows, create instance using ORADIM utility

41) What does the script utlexcpt.sql create? What is this table used for?

> It will create EXECEPTIONS table. See below link

42) In which Oracle subdirectory are all the SQL scripts such as catalog.sql/ catproc.sql /utlexcpt.sql etc…? Located?
> $ORACLE_HOME/rdbms/admin/

43) Which dynamic performance view would you use to display the OPTIMAL size of the rollback segment RBS2. Write a query to retrieve the OPTIMAL size and Rollback segment name?
> V$undostat (but many scripts are available in google or even in my blog)
44) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?
> Extent size

45) How would I start the database if only users with the RESTRICTED SESSION privilege need to access it?
> Startup restrict

46) Which data dictionary view would you query to find out information about free extents in your database? Write a query to display a count of the number of free extents in your database?
> We can use scripts. Exactly its difficult to know

47) Write a query to display the tablespace name, datafile name and type of extent management (local or dictionary) from the data dictionary?
> You need to combine dba_data_files and dba_tablespaces

48) Which two types of tablespace cannot be taken offline or dropped?
> SYSTEM and UNDO

49) When a tablespace is offline can it be made read only? Perform the
Required steps to confirm your answer?
> Didn’t got the answer

50) Which parameter specifies the percentage of space in each data block that is reserved for future updates?
> PCTFREE
51) write down two reasons why automatic extent allocation for an extent may fail?
> If the disk space reached max limit
> If autoextend reached maxsize limit

52) Query the DBA_CONSTRAINTS view and display the names of all the constraints that are created on the CUSTOMER table?
> Select constraint_name from dba_constraints where table_name=’CUSTOMER’;

53) Write a command to display the names of all BITMAP indexes created in the database?
> Select index_name from dba_indexes where index_type=’BITMAP’;

54) Write a command to coalesce the extents of any index of your choice?
> Alter tablespace <tablespace_name> coalesce;
> Don’t know for extents

55) . What happens to a row that is bigger than a single block? What is this called? Which data dictionary view can be queried to obtain information about such blocks?
> Row will be chained into multiple blocks. CHAINED_ROWS is the view

56) Write a query to retrieve the employee number and ROWIDs of all rows that belong to the EMP table belonging to user SCOTT?
> Select rowid,empno from scott.emp;

57) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?
> Repeated question

58) How to compile a view? How to compile a table?
> Alter view <view_name> compile;
> Tables cannot be compiled

59) What is the block size of your database and how do you see it?
> Db_block_size

60) At one time you lost parameter file accidentally and you don’t have any backup. How you will recreate a new parameter file with the parameters set to previous values.?
> We can recover it from alert log file which contains non-default values

61) You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?
> By configuring backup retention policy to redundancy 3