Monday, 18 February 2013

Oracle Interview questions for experienced developers


1. When you drop a table, what will happen to synonym on that table?

>Answer is Synonym will not be dropped, that structure will be available in
dba_synonyms.

SQL> create table scott.test_tab(num number);

Table created.

SQL> create synonym system.test_synonym for scott.test_tab;

Synonym created.

SQL> select OWNER,TABLE_NAME from dba_tables where table_name='TEST_TAB';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
SCOTT                          TEST_TAB

SQL> SET PAGESIZE 100
SQL> SET LINESIZE 2000
SQL> SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME='TEST_SYNONYM';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ ------------------------------ -------
SYSTEM                         TEST_SYNONYM                   SCOTT                          TEST_TAB

SQL> DROP TABLE TEST_TAB;
DROP TABLE TEST_TAB
           *
ERROR at line 1:
ORA-00942: table or view does not exist
  
SQL> DROP TABLE SCOTT.TEST_TAB;

Table dropped.

SQL> select OWNER,TABLE_NAME from dba_tables where table_name='TEST_TAB';

no rows selected

SQL> SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME='TEST_SYNONYM';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ ------------------------------ -------
SYSTEM                         TEST_SYNONYM                   SCOTT                          TEST_TAB

>When you try to select that synonym then u will get fallowing error:

SQL> select * from system.test_synonym;
select * from system.test_synonym
                     *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

>IF you want to reuse this synonym name create a table using same again.( Here u can use any structure).

SQL> create table scott.test_tab(num number, num2  varchar2(10));

Table created.

SQL> select * from system.test_synonym;

no rows selected

SQL>

2.How do you Alter Not null column to nullable?

Using below syntax You can alter not-null column to nullable.
SQL> alter table <table_name> modify <column_name> null;

Example: alter table EMP modify ename null;

3.What will happen if sub-query table doesn’t have column which main table has and if u refer that column in sub query.

When you are writing sub queries should be conscious about column list.

For example:

create table tab1 (a number,b number)
insert into tab1 values (10,10)
insert into tab1 values (20,20)

create table tab2 (a number,c number,d number)
insert into tab2 values (10,10,50)
insert into tab2 values (30,20,60)

case1:  in this case column b not exists in tab2, so it refers main query table columns.

select * from tab1 where (b) in (select b from tab2)

A        B
-------------
10      10
20      20

Case2:  in this case column a exists in tab2, so it refers from sub query table.

select * from tab1 where (a) in (select a from tab2)

A        B
-------------
10      10

4.When u drop a table what will happen to grants?

>Grants will be dropped automatically along with a table.

5.What happens to view when you drop a table on which u have a view?

>View will not be dropped, view related representation will be available under dba_views. When that table structure is available in database, then that view will be available automatically.

SQL> create view v1 as select * from scott.test_tab;

View created.

SQL> select * from v1;

no rows selected

SQL> drop table scott.test_tab;

Table dropped.

SQL> select * from v1;
select * from v1
              *
ERROR at line 1:
ORA-04063: view "V1" has errors
  
SQL> SELECT VIEW_NAME,TEXT FROM DBA_VIEWS WHERE VIEW_NAME='V1';
  
VIEW_NAME                      TEXT
------------------------------ ---------------------------------------------------------------------
V1                             select "NUM","NUM2" from scott.test_tab

SQL> create table scott.test_tab(num number, num2  varchar2(10));

Table created.

SQL> select * from v1;

no rows selected

6. how to compile ur entire schema?
  
exec dbms_utility.compile_schema('schemaname');

exec dbms_utility.compile_schema('SCOTT');

7. How To Check Errors In Your Schema

Means If Your Package Is Invalid, How Do you See the Errors)

SELECT * FROM DBA_ERRORS WHERE OWNER='your OWNER name' AND TYPE='your PACKAGE NAME';

SELECT * FROM DBA_ERRORS WHERE OWNER='SCOTT' AND TYPE='P1';