SQL> select * from t1;
EID
----------
10
10
10
SQL> select * from t2;
EID
----------
SQL> desc user_constraints;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)
SQL> select constraint_name,constraint_type,r_owner,R_constraint_name from user_constraints where table_name='T2';
CONSTRAINT_NAME C R_OWNER
------------------------------ - ------------------------------
R_CONSTRAINT_NAME
------------------------------
T2FK2T1 R SCOTT
SYS_C005442
SQL> select constraint_name,constraint_type from user_constraints where constraint_name='SYS_C005442';
CONSTRAINT_NAME C
------------------------------ -
SYS_C005442 P
SQL> select constraint_name,constraint_type,table_name from user_constraints where constraint_name='SYS_C005442';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
SYS_C005442 P T1
SQL> select constraint_name,constraint_type,table_name,deferrable,deferred from user_constraints where constraint_name='SYS_C005442';
CONSTRAINT_NAME C TABLE_NAME DEFERRABLE
------------------------------ - ------------------------------ --------------
DEFERRED
---------
SYS_C005442 P T1 NOT DEFERRABLE
IMMEDIATE
SQL> select constraint_name,constraint_type,table_name,deferrable,deferred from user_constraints where constraint_name='SYS_C005442';
CONSTRAINT_NAME C TABLE_NAME DEFERRABLE
------------------------------ - ------------------------------ --------------
DEFERRED
---------
SYS_C005442 P T1 NOT DEFERRABLE
IMMEDIATE
SQL> alter table t2 modify constraint t2fk2t1 disable;
Table altered.
SQL> alter table t2 drop constraint t2fk2t1;
Table altered.
SQL> alter table t1 drop constraint sys_c005442;
Table altered.
SQL> select * from t1;
EID
----------
10
10
10
SQL> delete t1;
3 rows deleted.
SQL> commit
2 ;
Commit complete.
SQL> select * from t1;
no rows selected
SQL> insert into t1 values (101);
1 row created.
SQL> commit;
Commit complete.
SQL> alter table t1 add constraint t1pk primary key (eid);
Table altered.
SQL> alter table t1 drop constraint t1pk;
Table altered.
SQL> alter table t1 add constraint t1pk primary key (eid) deferrable initially deferred;
Table altered.
SQL> insert into t1 values(10);
1 row created.
SQL> insert into t1 values(10);
1 row created.
SQL> insert into t1 values(10);
1 row created.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SCOTT.T1PK) violated
SQL> select * from t1;
EID
----------
101
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
7698 BLAKE MANAGER 7839 01-MAY-81 2850
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000
7839 KING PRESIDENT 17-NOV-81 5000
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500
7876 ADAMS CLERK 7788 23-MAY-87 1100
10
7900 JAMES CLERK 7698 03-DEC-81 950
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB NOT NULL VARCHAR2(9)
MGR NUMBER(7)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER
DEPTNO NUMBER(2)
SQL> conn / as sysdba
Connected.
SQL> desc dba_role_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
SQL> select grantee,granted_role from dba_role_privs where grantee='SCOTT';
GRANTEE GRANTED_ROLE
------------------------------ ------------------------------
SCOTT RESOURCE
SCOTT CONNECT
SQL> desc dba_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SQL> select privilege from dba_sys_privs where grantee='SCOTT';
PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE
SQL> desc dba_tab_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
SQL> select privilege,table_name from dba_tab_privs where grantee='SCOTT';
no rows selected
SQL> grant create view to scott;
Grant succeeded.
SQL> conn scott/lion
Connected.
SQL> create view eview as select empno,ename,sal from emp where mgr is not null;
View created.
SQL> select * from eview;
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
EMPNO ENAME SAL
---------- ---------- ----------
7902 FORD 3000
7934 MILLER 1300
13 rows selected.
SQL> create or replace view eview as select empno,ename,sal,deptno from emp where mgr is not null;
View created.
SQL> select * from eview;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 10
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7844 TURNER 1500
7876 ADAMS 1100 10
7900 JAMES 950 10
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7902 FORD 3000
7934 MILLER 1300 10
13 rows selected.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING MUM
20 SALES & MKT DEL
SQL> update eview set deptno=20 where empno=7499;
1 row updated.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
7698 BLAKE MANAGER 7839 01-MAY-81 2850
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000
7839 KING PRESIDENT 17-NOV-81 5000
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500
7876 ADAMS CLERK 7788 23-MAY-87 1100
10
7900 JAMES CLERK 7698 03-DEC-81 950
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
SQL> select * from eview
2 ;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 10
7499 ALLEN 1600 20
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7844 TURNER 1500
7876 ADAMS 1100 10
7900 JAMES 950 10
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7902 FORD 3000
7934 MILLER 1300 10
13 rows selected.
SQL> create table eemp as select * from emp;
Table created.
SQL> drop table emp;
drop table emp
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL> rename emp to eeemp;
Table renamed.
SQL> select * from eview;
select * from eview
*
ERROR at line 1:
ORA-04063: view "SCOTT.EVIEW" has errors
SQL> desc eview
ERROR:
ORA-24372: invalid object for describe
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
SALGRADE TABLE
T1 TABLE
T2 TABLE
BIN$C1kk83C8R6iB4GtPWXIE6g==$0 TABLE
SALES TABLE
BIN$dnChwqpbSmaRM2g5ESNlfw==$0 TABLE
ITEMS TABLE
BONUS1 TABLE
BIN$wrCacPWdRE68ckB5C6nGiA==$0 TABLE
AREA TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
QU TABLE
DUPQUA TABLE
BONUS11 TABLE
EMP1 TABLE
DEPT1 TABLE
EMP2 TABLE
T3 TABLE
TMP TABLE
EVIEW VIEW
EEMP TABLE
EEEMP TABLE
22 rows selected.
SQL> rename eeemp to emp;
Table renamed.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
SALGRADE TABLE
T1 TABLE
T2 TABLE
BIN$C1kk83C8R6iB4GtPWXIE6g==$0 TABLE
SALES TABLE
BIN$dnChwqpbSmaRM2g5ESNlfw==$0 TABLE
ITEMS TABLE
BONUS1 TABLE
BIN$wrCacPWdRE68ckB5C6nGiA==$0 TABLE
AREA TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
QU TABLE
DUPQUA TABLE
BONUS11 TABLE
EMP1 TABLE
DEPT1 TABLE
EMP2 TABLE
T3 TABLE
TMP TABLE
EVIEW VIEW
EEMP TABLE
EMP TABLE
22 rows selected.
SQL> select * from eview;
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7369 SMITH 800 10
7499 ALLEN 1600 20
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7844 TURNER 1500
7876 ADAMS 1100 10
7900 JAMES 950 10
EMPNO ENAME SAL DEPTNO
---------- ---------- ---------- ----------
7902 FORD 3000
7934 MILLER 1300 10
13 rows selected.
SQL> create table t10(id number primary key);
Table created.
SQL> create view vt10 as select * from t10;
View created.
SQL> insert into vt10 values(10);
1 row created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING MUM
20 SALES & MKT DEL
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB NOT NULL VARCHAR2(9)
MGR NUMBER(7)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER
DEPTNO NUMBER(2)
SQL> select empno,job from emp;
EMPNO JOB
---------- ---------
7369 CLERK
7499 SALESMAN
7521 SALESMAN
7566 MANAGER
7654 SALESMAN
7698 MANAGER
7782 MANAGER
7788 ANALYST
7839 PRESIDENT
7844 SALESMAN
7876 CLERK
EMPNO JOB
---------- ---------
7900 CLERK
7902 ANALYST
7934 CLERK
14 rows selected.
SQL> create view mv1 as select d.deptno,e.empno,job,dname from emp e join dept d on e.deptno=d.deptno;
View created.
SQL> desc mv1
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
EMPNO NOT NULL NUMBER(4)
JOB NOT NULL VARCHAR2(9)
DNAME VARCHAR2(14)
SQL> select * from mv1;
DEPTNO EMPNO JOB DNAME
---------- ---------- --------- --------------
10 7369 CLERK ACCOUNTING
20 7499 SALESMAN SALES & MKT
10 7876 CLERK ACCOUNTING
10 7900 CLERK ACCOUNTING
10 7934 CLERK ACCOUNTING
SQL> update mv1 set JOB='SALES' where deptno=20;
1 row updated.
SQL> create or replace view nv1 as select empno,sal*.4 "comm" from emp;
View created.
SQL> desc nv1
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
comm NUMBER
SQL> create or replace view nv1 as select empno,sal*.4 "comm",job from emp;
View created.
SQL> desc nv1
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
comm NUMBER
JOB NOT NULL VARCHAR2(9)
SQL> insert into nv1 values(6777,900,'CLERK');
insert into nv1 values(6777,900,'CLERK')
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB NOT NULL VARCHAR2(9)
MGR NUMBER(7)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER
DEPTNO NUMBER(2)
SQL> create view v1 as select * from emp where sal>3000;
View created.
SQL> select * from v1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
SQL> update v1 set sal=3000 where empno=7839;
1 row updated.
SQL> select * from v1;
no rows selected
SQL> update v1 set sal=4000 where empno=7839;
0 rows updated.
SQL> select * from v1;
no rows selected
SQL> commit;
Commit complete.
SQL> select * from v1;
no rows selected
SQL> select * from emp where empno=7839
2 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 3000
SQL> update v1 set sal=4000 where empno=7839;
0 rows updated.
SQL> create view mv2 as select * from dept with read only;
View created.
SQL> select * from mv2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING MUM
20 SALES & MKT DEL
SQL> insert into mv2 values(30,'Sales','BGL');
insert into mv2 values(30,'Sales','BGL')
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL> create or replace view mv2 as select * from dept;
View created.
SQL> insert into mv2 values(30,'Sales','BGL');
1 row created.
SQL> select * from mv2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING MUM
20 SALES & MKT DEL
30 Sales BGL
SQL> create view mv3 as select * from dept1;
View created.
SQL> select * from mv3
2 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
50 MARKTING TOKYO
SQL> drop view mv3;
View dropped.
SQL> create view mv3 as select * from dept2;
create view mv3 as select * from dept2
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> create force view mv3 as select * from dept2 ;
Warning: View created with compilation errors.
SQL> select * from mv3;
select * from mv3
*
ERROR at line 1:
ORA-04063: view "SCOTT.MV3" has errors
SQL> create table dept2 as select * from dept;
Table created.
SQL> select * from mv3;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING MUM
20 SALES & MKT DEL
30 Sales BGL
SQL> create view mv4 (dno ,dn,l) as select * from dept;
View created.
SQL> select * from mv4;
DNO DN L
---------- -------------- -------------
10 ACCOUNTING MUM
20 SALES & MKT DEL
30 Sales BGL
SQL> create sequence sq1;
Sequence created.
SQL> select sq1.nextval from dual;
NEXTVAL
----------
1
SQL> select sq1.nextval from dual;
NEXTVAL
----------
2
SQL> select sq1.nextval from dual;
NEXTVAL
----------
3
SQL> select * from t1;
EID
----------
101
SQL> insert into t1 values (sq1.nextval);
1 row created.
SQL> select * from t1;
EID
----------
101
4
SQL> insert into t1 values (sq1.nextval);
1 row created.
SQL> select * from t1;
EID
----------
101
4
5
SQL> drop sequence sq1;
Sequence dropped.
SQL> create sequence sq1 start with 100;
Sequence created.
SQL> select sq1.nextval from dual;
NEXTVAL
----------
100
SQL> select sq1.nextval from dual;
NEXTVAL
----------
101
SQL> select sq1.nextval from dual;
NEXTVAL
----------
102
SQL> drop sequence sq1;
Sequence dropped.
SQL> create sequence sq1 start with 100 increment by 10;
Sequence created.
SQL> select sq1.nextval from dual;
NEXTVAL
----------
100
SQL> select sq1.nextval from dual;
NEXTVAL
----------
110
SQL> select sq1.nextval from dual;
NEXTVAL
----------
120
SQL> create synonym e for emp;
create synonym e for emp
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> grant create synonym to scott;
Grant succeeded.
SQL> conn scott/lion
Connected.
SQL> create synonym e for emp;
Synonym created.
SQL> select * from e;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
10
7499 ALLEN SALES 7698 20-FEB-81 1600 300
20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
7698 BLAKE MANAGER 7839 01-MAY-81 2850
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000
7839 KING PRESIDENT 17-NOV-81 3000
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500
7876 ADAMS CLERK 7788 23-MAY-87 1100
10
7900 JAMES CLERK 7698 03-DEC-81 950
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
SQL> conn /as sysdba
Connected.
SQL> create user u1 identified by u1;
User created.
SQL> grant resource, connect to u1;
Grant succeeded.
SQL> conn u1/u1
Connected.
SQL> select * from scott.emp;
select * from scott.emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn scott/lion;
Connected.
SQL> grant select on emp to u1;
Grant succeeded.
SQL> conn u1/u1
Connected.
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
10
7499 ALLEN SALES 7698 20-FEB-81 1600 300
20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
7698 BLAKE MANAGER 7839 01-MAY-81 2850
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000
7839 KING PRESIDENT 17-NOV-81 3000
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500
7876 ADAMS CLERK 7788 23-MAY-87 1100
10
7900 JAMES CLERK 7698 03-DEC-81 950
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
SQL> create synonym e on scott.emp;
create synonym e on scott.emp
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> create synonym e for scott.emp;
create synonym e for scott.emp
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> grant create synonym to u1;
Grant succeeded.
SQL> conn u1/u1
Connected.
SQL> create synonym e for scott.emp;
Synonym created.
SQL> select * from e;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
10
7499 ALLEN SALES 7698 20-FEB-81 1600 300
20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
7698 BLAKE MANAGER 7839 01-MAY-81 2850
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000
7839 KING PRESIDENT 17-NOV-81 3000
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500
7876 ADAMS CLERK 7788 23-MAY-87 1100
10
7900 JAMES CLERK 7698 03-DEC-81 950
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
SQL> create table e (eid number);
create table e (eid number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> select * from e;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
10
7499 ALLEN SALES 7698 20-FEB-81 1600 300
20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
7698 BLAKE MANAGER 7839 01-MAY-81 2850
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000
7839 KING PRESIDENT 17-NOV-81 3000
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500
7876 ADAMS CLERK 7788 23-MAY-87 1100
10
7900 JAMES CLERK 7698 03-DEC-81 950
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
SQL> desc user_synonym;
ERROR:
ORA-04043: object user_synonym does not exist
SQL> desc user_synonyms;
Name Null? Type
----------------------------------------- -------- ----------------------------
SYNONYM_NAME NOT NULL VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
DB_LINK VARCHAR2(128)
SQL> select * from user_synonyms;
SYNONYM_NAME TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
DB_LINK
--------------------------------------------------------------------------------
E SCOTT
EMP
SQL> create public synonym ee1 for scott.emp;
create public synonym ee1 for scott.emp
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> grant create public synonym to u1;
Grant succeeded.
SQL> conn u1/u1;
Connected.
SQL> create public synonym ee1 for scott.emp;
Synonym created.
SQL> conn scott/lion
Connected.
SQL> select * from ee1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
10
7499 ALLEN SALES 7698 20-FEB-81 1600 300
20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
7698 BLAKE MANAGER 7839 01-MAY-81 2850
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
7788 SCOTT ANALYST 7566 19-APR-87 3000
7839 KING PRESIDENT 17-NOV-81 3000
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500
7876 ADAMS CLERK 7788 23-MAY-87 1100
10
7900 JAMES CLERK 7698 03-DEC-81 950
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.
No comments:
Post a Comment