SYNONYM:
********
-->Synonym is an alternative name or Permanent alias name given to the table,view,sequence,stored procedure,function or packages for the user's convinence to use it.
-->Synonym can be created for entire object only.
-->It is used to hold the owner of the table.
-->It works like as a mirror image of the tables.
-->Its does not have a own structure.
-->It is depend on the tables.
-->Synonym is a permanent alias name for single Base table or view.
-->It supports DML And DQL,DCL operations.
-->Synonym can be PUBLIC or PRIVATE.
-->By default the synonym is PRIVATE.
-->All synonyms are created in user_synonyms,all_synonyms table.
-->DBA_OBJECTS,ALL_OBJECTS -->Any type of dataobjects
Note :
*******
-->If any changes in the synonym automatically reflected into table and viceversa.
-->Synonym is a dependent object whereas table is independent object, which means if a table is dropped then synonym stops its working.
If a table is recalled back then synonym will start its working.
Synonym is classified into 2 types;
1.Private Synonym
2.Public Synonym
Private Synonym :
******************
-->It is used to create private synonym in current schema and accessed with in that schema only.
SYNTAX:
*******
CREATE SYNONYM SYNONYM_NAME FOR TABLE_NAME;
EX:
***
--Create table in Custom Schema. --custom schena
-->Give grants to APPS Schema --custom schema
-->Connect to Apps Schema, Create synonym in APPS Schema. --Apps schema
/
--Create table in HR Schema(Custom Schema)
CREATE TABLE XX_AP_INVOICES_ALL
(
INVOICE_NUM VARCHAR2(20),
INVOICE_ID NUMBER,
INVOICE_AMOUNT NUMBER,
INVOICE_DATE DATE
);
/
INSERT INTO XX_AP_INVOICES_ALL VALUES('ABC_123',1234,100,SYSDATE);
/
INSERT INTO XX_AP_INVOICES_ALL VALUES('DEF_456',12784,200,SYSDATE);
/
INSERT INTO XX_AP_INVOICES_ALL VALUES('XYZ_123',12564334,300,SYSDATE);
/
COMMIT;
/
2)GRANT ALL ON <SCHEMANAME>.<TABLENAME> TO <SCHEMANAME>;
/
GRANT ALL ON HR.XX_AP_INVOICES_ALL TO APPS;
/
3)Create synonym in APPS Schema. --Apps schema
SYNTAX:
*******
CREATE OR REPLACE SYNONYM <SCHEMANAME>.<SYNONYM NAME> FOR <SCHEMANAME>.<TABLENAME>;
/
CREATE OR REPLACE SYNONYM APPS.XX_AP_INVOICES_ALL FOR HR.XX_AP_INVOICES_ALL;
/
SELECT * FROM XX_AP_INVOICES_ALL;
/
--DML OPERATIONS(APPS SCHEMA)
/
INSERT INTO XX_AP_INVOICES_ALL VALUES('PQRS_345',0987,500,SYSDATE);
/
COMMIT;
/
--Test DATA
same record will be refleted into custom schema
/
SELECT * FROM HR.XX_AP_INVOICES_ALL;
/
DML OPERATIONS(CUSTOM SCHEMA)
/
DELETE FROM HR.XX_AP_INVOICES_ALL WHERE INVOICE_ID=987;
/
COMMIT;
/
--TEST DATA
SELECT * FROM APPS.XX_AP_INVOIUCES_ALL;
/
CREATE TABLE HR.XX_PO_HEADERS_ALL
(
PO_HEADER_ID NUMBER PRIMARY KEY,
TYPE_LOOKUP_CODE VARCHAR2(20),
SEGMENT1 VARCHAR2(20),
START_DATE_ACTIVE DATE
);
/
INSERT INTO HR.XX_PO_HEADERS_ALL VALUES (1,'STANDARD','ABC123',SYSDATE);
/
INSERT INTO HR.XX_PO_HEADERS_ALL VALUES (2,'BLANKET','ABC456',SYSDATE);
/
INSERT INTO HR.XX_PO_HEADERS_ALL VALUES (3,'QUOTATION','ABC789',SYSDATE);
/
COMMIT;
/
--Create table in HR Schema(Custom Schema)
CREATE TABLE HR.XX_EMP_HR_TEST
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
JOB VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER
);
/
INSERT INTO HR.XX_EMP_HR_TEST VALUES (1,'A','MANAGER',1000,10);
/
INSERT INTO HR.XX_EMP_HR_TEST VALUES (2,'B','PRESIDENT',2000,20);
/
INSERT INTO HR.XX_EMP_HR_TEST VALUES (3,'C','CLERK',2000,10);
/
COMMIT;
/
SYNTAX:
*********
GRANT ALL ON <TABLENAME> TO <SCHEMANAME>
GRANT ALL ON <SCHEMANAME>.<TABLENAME> TO <SCHEMANAME>;
/
GRANT ALL ON HR.XX_PO_HEADERS_ALL TO APPS;
/
GRANT ALL ON HR.XX_EMP_HR_TEST TO APPS;
/
--CHECK TABLE IN APPS SCHEMA
/
SELECT *
FROM HR.XX_PO_HEADERS_ALL;
/
SELECT *
FROM APPS.XX_PO_HEADERS_ALL; --IT WLL THROW THE ERROR
/
select *
from HR.XX_EMP_HR_TEST;
/
select *
from APPS.XX_EMP_HR_TEST; --IT WLL THROW THE ERROR
--Create Synonym IN APPS Schema
SYNTAX:
*******
CREATE OR REPLACE SYNONYM <SCHEMANAME>.<SYNONYM NAME> FOR <SCHEMANAME>.<TABLENAME>;
/
CREATE OR REPLACE SYNONYM APPS.XX_PO_HEADERS_ALL FOR HR.XX_PO_HEADERS_ALL;
/
CREATE OR REPLACE SYNONYM APPS.XX_EMP_HR_TEST FOR HR.XX_EMP_HR_TEST;
/
SELECT * FROM APPS.XX_PO_HEADERS_ALL;
/
SELECT * FROM APPS.XX_EMP_HR_TEST;
/
DESC APPS.XX_PO_HEADERS_ALL;
/
DESC APPS.XX_EMP_HR_TEST;
/
INSERT INTO APPS.XX_PO_HEADERS_ALL VALUES (4,'CONTRACT','ABC000',SYSDATE);
/
COMMIT;
/
INSERT INTO APPS.XX_EMP_HR_TEST VALUES (4,'D','MANAGER',5000,30);
/
COMMIT;
/
SELECT * FROM APPS.XX_PO_HEADERS_ALL;
/
SELECT * FROM HR.XX_PO_HEADERS_ALL;
/
DELETE FROM APPS.XX_PO_HEADERS_ALL WHERE PO_HEADER_ID=1;
/
COMMIT;
/
SELECT * FROM APPS.XX_PO_HEADERS_ALL;
/
SELECT * FROM HR.XX_PO_HEADERS_ALL;
/
SELECT * FROM APPS.XX_EMP_HR_TEST;
/
SELECT * FROM HR.XX_EMP_HR_TEST;
/
DELETE FROM HR.XX_EMP_HR_TEST WHERE EMPNO=4;
/
COMMT;
/
SELECT * FROM APPS.XX_EMP_HR_TEST;
/
SELECT * FROM HR.XX_EMP_HR_TEST
/
--I don't want create a table and i don't want insert the data.I want to copy the table data and structure from another table.
--I want to create a new table which is copied from existing table.
--i WANT TO CREATE A TABLE AND COPY THE DATA FROM ANOTHER TABLE WHILE EXECUTING A SINGLE QUERY?
SYNTAX:
*******
CREATE TABLE NEWTABLENAME
AS
SELECT *
FROM OLDTABLENANE OR EXISTING TABLENAME;
/
CREATE TABLE XX_EMP_TD
AS
SELECT *
FROM EMP;
/
SELECT * FROM XX_EMP_TD;
/
CREATE TABLE XX_EMP_TD1
AS
SELECT *
FROM EMP
WHERE 1=1;
/
SELECT * FROM XX_EMP_TD1;
/
--I WANT TO COPY THE STRUCTURE OF THE TABLE WITHOUT DATA WHILE EXECUTING A QUERY.
CREATE TABLE XX_EMP_TS
AS
SELECT *
FROM EMP
WHERE 1=2;
/
SELECT *
FROM TABLE XX_EMP_TS;
/
DESC XX_EMP_TS;
/
--I WANT TO CREATE A NEW TABLE WITH EXISTING SYNONYM IS IT POSIBLE OR NOT.
CREATE TABLE XX_TABLE10_T --NEW TABLE NAME
AS
SELECT *
FROM XX_EMP_HR_TEST; --EXISTING SYNONYM
/
SELECT * FROM XX_TABLE10_T;
/
--I WANT TO INSERT THE DATA IN NEW TABLE,THE DATA IS REFLETED IN SYNONYM IS IT POSIBLE OR NOT.
INSERT INTO XX_TABLE10_T VALUES (4,'D','MANAGER',1000,40);
/
COMMIT;
/
SELECT * FROM XX_TABLE10_T;
/
SELECT * FROM XX_EMP_HR_TEST;
/
--I WANT INSERT THE DATA IN SYNONYM.WHETHER THE DATA S REFLECTED IN BOTH THE TABLES OR ONLY ONE TABLE
INSERT INTO APPS.XX_EMP_HR_TEST VALUES(5,'E','CLERK',2000,20);
/
COMMIT;
/
SELECT * FROM XX_TABLE10_T;
/
SELECT * FROM HR.XX_EMP_HR_TEST;
/
SYNTAX:
*******
CREATE SYNONYM NEWSYNONYM NAME
AS
SELECT *
FROM OLD SYNONYM NANE OR EXISTING SYNONYM NAME
CREATE SYNONYM TABLE10_S2 AS SELECT * FROM TABLE10_S1; --IT WILL THROW THE ERROR.
Public Synonym:
***************
-->This synonym is created from a user, which has got DBA privileges.
When public synonym is created and if the same object gets accessed from other user, table name should not be used as qualifier.
SYNTAX:
******
CREATE [PUBLIC] SYNONYM synonyn_name FOR objectname;
EX:
***
CREATE PUBLIC SYNONYM XX_TABLE10_PS FOR EMP;
/
SELECT * FROM EMP;
/
SELECT * FROM TABLE10_PS;
/
VIEWING SYNONYM INFORMATION:
----------------------------
TAB & CAT : These predefined tables will store only about private synonym names with their type of object.
USER_SYNONYMS : This predefined table will provide the detail information about the user created private synonyms.
SELECT SYNONYM_NAME,TABLE_NAME FROM USER_SYNONYMS;
ALL_SYNONYMS : This predefined table will provide the detail information about the private & public synonyms.
SELECT OWNER,TABLE_OWNER,SYNONYM_NAME,TABLE_NAME FROM ALL_SYNONYMS WHERE TABLE_NAME='SALGRADE';
DROPING A SYNONYM:
------------------
DROP [PUBLIC] SYNONYM synonym_name;
PUBLIC synonyms can be dropped from a user, which has got DBA privileges.
Private synonyms created in one user can be provided with privileges to get access from other users, where synonym name should also be preceeded with user name as qualifier.
--HOW TO DROP SYNONYM?
DROP SYNONYM SG;
DROP PUBLIC SYNONYM LIB1;
No comments:
Post a Comment