Oracle SQL Interview Questions

1. How to get the running Oracle version? 
Ans : select * from v$version; 

2. How to get the time zone under which the DB is running?
Ans: select dbtimezone from dual; 
SQL> select dbtimezone from dual;
 
DBTIME
——
+00:00
 
3. When an user process fails for any reason, which background process cleans it up? 
Ans: PMON (Process Monitor)
 
4. Which background process refreshes the materialized views? 
Ans : Job Queue Processes. 
 
5. How to determine what sessions are connected?
Ans : Using the table V$SESSION; 
 
6. How to provide security for the PL/SQL application code?
Ans : By encrypting using WRAP command. 
 
7. How do you determine the size of a data file? 
Ans : By using the table DBA_DATA_FILES. 
 
8. How to add a data file to a tablespace? 
Ans : 
ALTER TABLESPACE
ADD DATAFILE SIZE ;
 
9. How to resize a data file? 
Ans : ALTER DATABASE DATAFILE RESIZE ;
 
10. How to determine the free space available in a tablespace? 
Ans : using the table DBA_FREE_SPACE
SQL> desc dba_free_space;
 Name                                      Null?    Type
 —————————————– ——– ————
 TABLESPACE_NAME                                    VARCHAR2(30)
 FILE_ID                                                            NUMBER
 BLOCK_ID                                                        NUMBER
 BYTES                                                               NUMBER
 BLOCKS                                                            NUMBER
 RELATIVE_FNO                                            NUMBER


11. How to rebuild an index in Oracle? 
Ans : ALTER INDEX REBUILD;

12. How to gather statistics on a table? 
Ans : Using the ANALZE command.

13. How to enable a trace for a session? 
Ans :  ALTER SESSION SET SQL_TRACE=TRUE;

14. How to view errors of a recently compiled PL/SQL code? 
Ans : SHOW ERRORS

15. What are synonyms in Oracle? 
Ans : Synonyms are the alternative names given tables, sequences, views and other db objects.

16. What are the original Import and Export utilities? 
Ans : External tables and SQL* Loader.

17. What is the tablespace that automatically gets created when you create a database? 
Ans :  SYSTEM tablespace.

18. How to force a log switch? 
Ans : ALTER SYSTEM SWITCH LOGFILE;

19. What is the difference between PERMANENT and TEMPORARY tablespaces? 
Ans : Permanent tablespace is used to store the true objects of the database whereas temporary tablespace is used to store the temporary objects like sort structures (during a join operation or sub queries etc).

20. How to determine what are the DDL changes done?
Ans : By using streams or Logminer. 

Tags: Oracle SQL Interview Questions
Related Posts Plugin for WordPress, Blogger...