Scribd
Upload
273 views8 pages

Creating and Managing SQL Synonyms

The document shows SQL commands used to create, populate, and modify database tables, sequences, views, and synonyms. A table is created and populated with sample data. A sequence is created and used to populate tables. Views and synonyms are also created and tested. Database objects like tables and synonyms are renamed, dropped, and checked for validity.

Uploaded by

Siddiqa Ayesha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
273 views8 pages

Creating and Managing SQL Synonyms

The document shows SQL commands used to create, populate, and modify database tables, sequences, views, and synonyms. A table is created and populated with sample data. A sequence is created and used to populate tables. Views and synonyms are also created and tested. Database objects like tables and synonyms are renamed, dropped, and checked for validity.

Uploaded by

Siddiqa Ayesha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL> CREATE TABLE EMPLOYEE_PERSONAL_DETAILS_TB

2 (ID NUMBER);

Table created.

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
ANIL TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
EMPLOYEE_PERSONAL_DETAILS_TB TABLE
NEW TABLE
PARVATHY TABLE
PRG1 TABLE
SALGRADE TABLE
V1 VIEW
V2 VIEW

11 rows selected.

SQL> INSERT INTO EMPLOYEE_PERSONAL_DETAILS_TB VALUES(&ID);


Enter value for id: 100
old 1: INSERT INTO EMPLOYEE_PERSONAL_DETAILS_TB VALUES(&ID)
new 1: INSERT INTO EMPLOYEE_PERSONAL_DETAILS_TB VALUES(100)

1 row created.

SQL> /
Enter value for id: 200
old 1: INSERT INTO EMPLOYEE_PERSONAL_DETAILS_TB VALUES(&ID)
new 1: INSERT INTO EMPLOYEE_PERSONAL_DETAILS_TB VALUES(200)

1 row created.

SQL> CREATE SYSNONYM EMP1 FOR EMPLOYEE_PERSONAL_DETAILS_TB;


CREATE SYSNONYM EMP1 FOR EMPLOYEE_PERSONAL_DETAILS_TB
*
ERROR at line 1:
ORA-00901: invalid CREATE command

SQL> ED
Wrote file afiedt.buf

1* CREATE SYNONYM EMP1 FOR EMPLOYEE_PERSONAL_DETAILS_TB


SQL> /

Synonym created.

SQL> ED
Wrote file afiedt.buf

1* CREATE SYNONYM EMP1 FOR EMPLOYEE_PERSONAL_DETAILS_TB


SQL> SELECT * FROM EMP1;

ID
----------
100
200

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
ANIL TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
EMP1 SYNONYM
EMPLOYEE_PERSONAL_DETAILS_TB TABLE
NEW TABLE
PARVATHY TABLE
PRG1 TABLE
SALGRADE TABLE
V1 VIEW
V2 VIEW

12 rows selected.

SQL> SELECT STATUS FROM USER_OBJECTS


2 WHERE OBJECT_NAME='EMPLOYEE_PERSONAL_DETAILS_TB';

STATUS
-------
VALID

SQL> DROP TABLE EMPLOYEE_PERSONAL_DETAILS_TB;

Table dropped.

SQL> SELECT STATUS FROM USER_OBJECTS


2 WHERE OBJECT_NAME='EMPLOYEE_PERSONAL_DETAILS_TB';

no rows selected

SQL> ED
Wrote file afiedt.buf

1 SELECT STATUS FROM USER_OBJECTS


2* WHERE OBJECT_NAME='EMP1'
SQL> /

STATUS
-------
VALID

SQL> CREATE SYNONYM FOR NEW;


CREATE SYNONYM FOR NEW
*
ERROR at line 1:
ORA-00995: missing or invalid synonym identifier

SQL> ED
Wrote file afiedt.buf

1* CREATE SYNONYM S FOR NEW


SQL> /

Synonym created.

SQL> SELECT STATUS FROM USER_OBJECTS


2 WHERE OBJECT_NAME='S';

STATUS
-------
VALID

SQL> DROP TABLE NEW;

Table dropped.

SQL> SELECT STATUS FROM USER_OBJECTS


2 WHERE OBJECT_NAME='S';

STATUS
-------
VALID

SQL> SELECT * FROM S;


SELECT * FROM S
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

SQL> CREATE TABLE NEW


2 AS
3 SELECT * FROM EMP;

Table created.

SQL> SELECT * FROM S;

EMPNO ENAME JOB MGR HIREDATE SAL


COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------
- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500
0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> CREATE VIEW NEW_V


2 AS
3 SELECT * FROM NEW;

View created.

SQL> SELECT STATUS FROM USER_OBJECTS


2 WHERE OBJECT_NAME='NEW_V';

STATUS
-------
VALID

SQL> DROP TABLE NEW;

Table dropped.

SQL> SELECT STATUS FROM USER_OBJECTS


2 WHERE OBJECT_NAME='NEW_V';

STATUS
-------
INVALID

SQL> CREATE TABLE NEW


2 AS
3 SELECT * FROM EMP;

Table created.

SQL> SELECT STATUS FROM USER_OBJECTS


2 WHERE OBJECT_NAME='NEW_V';

STATUS
-------
INVALID

SQL> SELECT * FROM NEW_V;

EMPNO ENAME JOB MGR HIREDATE SAL


COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------
- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250
500 30
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250
1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7844 TURNER SALESMAN 7698 08-SEP-81 1500
0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> SELECT STATUS FROM USER_OBJECTS


2 WHERE OBJECT_NAME='NEW_V';

STATUS
-------
VALID

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID


------------------------------ ------- ----------
ANIL TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
EMP1 SYNONYM
NEW TABLE
NEW_V VIEW
PARVATHY TABLE
PRG1 TABLE
S SYNONYM
SALGRADE TABLE
V1 VIEW
V2 VIEW

13 rows selected.

SQL> RENAME PARVATHY TO CARDINAL;

Table renamed.

SQL> SELECT * FROM TAB;


TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
ANIL TABLE
BONUS TABLE
CARDINAL TABLE
DEPT TABLE
EMP TABLE
EMP1 SYNONYM
NEW TABLE
NEW_V VIEW
PRG1 TABLE
S SYNONYM
SALGRADE TABLE
V1 VIEW
V2 VIEW

13 rows selected.

SQL> CREATE TABLE DEMO


2 (ID NUMBER);

Table created.

SQL> CREATE TABLE PRG(EMPID NUMBER);

Table created.

SQL> CREATE
2 .
SQL> ED
Wrote file afiedt.buf

1 CREATE SEQUENCE S1
2 START WITH 10
3 INCREMENT BY 10
4 MINVALUE 5
5 MAXVALUE 100
6 CYCLE
7* CACHE 5
SQL> /

Sequence created.

SQL> ED
Wrote file afiedt.buf

1 CREATE SEQUENCE S1
2 START WITH 10
3 INCREMENT BY 10
4 MINVALUE 5
5 MAXVALUE 100
6 CYCLE
7* CACHE 5
SQL> INSERT INTO DEMO VALUES(S1.NEXTVAL);

1 row created.

SQL> /

1 row created.
SQL> /

1 row created.

SQL> /

1 row created.

SQL> SELECT * FROM DEMO;

ID
----------
10
20
30
40

SQL> INSERT INTO PRG VALUES(S1.NEXTVAL);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> SELECT * FROM PRG;

EMPID
----------
50
60
70
80
90
100
5

7 rows selected.
SQL> SELECT S1.CURRVAL FROM DUAL;

CURRVAL
----------
5

SQL> DROP TABLE DEMO;

Table dropped.

SQL> INSERT INTO PRG VALUES(S1.NEXTVAL);

1 row created.

SQL> /

1 row created.

SQL> SELECT * FROM PRG;

EMPID
----------
50
60
70
80
90
100
5
15
25

9 rows selected.

SQL> CONN SCOTT/TIGER


Connected.
SQL> CONN SYS AS SYSDBA
Connected.
SQL> CREATE PUBLIC SYNONYM C FOR SCOTT.CARDINAL;

Synonym created.

SQL> CONN SCOTT/TIGER


Connected.
SQL> SELECT * FROM C;

no rows selected

SQL> SPOOL OFF;

You might also like