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';
No comments:
Post a Comment