1
2
3
4
5
|
SQL>
alter
user
scott account unlock;
alter
user
scott account unlock
*
ERROR
at
line 1:
ORA-01918:
user
'SCOTT'
does
not
exist
|
1
2
3
4
5
|
SQL>
create
user
scott identified
by
oracle;
create
user
scott identified
by
oracle
*
ERROR
at
line 1:
ORA-65096: invalid common
user
or
role
name
|
1
2
3
4
5
6
7
8
9
|
SQL> !oerr ora 65096
65096, 00000,
"invalid common user or role name"
// *Cause: An attempt was made
to
create
a common
user
or
role
with
a
name
// that wass
not
valid
for
common users
or
roles.
In
addition
to
// the usual rules
for
user
and
role names, common
user
and
role
// names must start
with
C##
or
c##
and
consist
only
of
ASCII
// characters.
// *
Action
: Specify a valid common
user
or
role
name
.
//
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
SET
TERMOUT
OFF
SET
ECHO
OFF
DROP
USER
C##SCOTT
CASCADE
;
create
user
c##scott identified
by
tiger;
grant
connect
,resource,unlimited tablespace
to
c##scott container=
all
;
alter
user
c##scott
default
tablespace users;
alter
user
c##scott
temporary
tablespace
temp
;
CONNECT
C##SCOTT/tiger
CREATE
TABLE
DEPT
(DEPTNO NUMBER(2)
CONSTRAINT
PK_DEPT
PRIMARY
KEY
,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;
CREATE
TABLE
EMP
(EMPNO NUMBER(4)
CONSTRAINT
PK_EMP
PRIMARY
KEY
,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE
DATE
,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
CONSTRAINT
FK_DEPTNO
REFERENCES
DEPT);
INSERT
INTO
DEPT
VALUES
(10,
'ACCOUNTING'
,
'NEW YORK'
);
INSERT
INTO
DEPT
VALUES
(20,
'RESEARCH'
,
'DALLAS'
);
INSERT
INTO
DEPT
VALUES
(30,
'SALES'
,
'CHICAGO'
);
INSERT
INTO
DEPT
VALUES
(40,
'OPERATIONS'
,
'BOSTON'
);
INSERT
INTO
EMP
VALUES
(7369,
'SMITH'
,
'CLERK'
,7902,to_date(
'17-12-1980'
,
'dd-mm-yyyy'
),800,
NULL
,20);
INSERT
INTO
EMP
VALUES
(7499,
'ALLEN'
,
'SALESMAN'
,7698,to_date(
'20-2-1981'
,
'dd-mm-yyyy'
),1600,300,30);
INSERT
INTO
EMP
VALUES
(7521,
'WARD'
,
'SALESMAN'
,7698,to_date(
'22-2-1981'
,
'dd-mm-yyyy'
),1250,500,30);
INSERT
INTO
EMP
VALUES
(7566,
'JONES'
,
'MANAGER'
,7839,to_date(
'2-4-1981'
,
'dd-mm-yyyy'
),2975,
NULL
,20);
INSERT
INTO
EMP
VALUES
(7654,
'MARTIN'
,
'SALESMAN'
,7698,to_date(
'28-9-1981'
,
'dd-mm-yyyy'
),1250,1400,30);
INSERT
INTO
EMP
VALUES
(7698,
'BLAKE'
,
'MANAGER'
,7839,to_date(
'1-5-1981'
,
'dd-mm-yyyy'
),2850,
NULL
,30);
INSERT
INTO
EMP
VALUES
(7782,
'CLARK'
,
'MANAGER'
,7839,to_date(
'9-6-1981'
,
'dd-mm-yyyy'
),2450,
NULL
,10);
INSERT
INTO
EMP
VALUES
(7788,
'SCOTT'
,
'ANALYST'
,7566,to_date(
'13-JUL-87'
,
'dd-mm-rr'
)-85,3000,
NULL
,20);
INSERT
INTO
EMP
VALUES
(7839,
'KING'
,
'PRESIDENT'
,
NULL
,to_date(
'17-11-1981'
,
'dd-mm-yyyy'
),5000,
NULL
,10);
INSERT
INTO
EMP
VALUES
(7844,
'TURNER'
,
'SALESMAN'
,7698,to_date(
'8-9-1981'
,
'dd-mm-yyyy'
),1500,0,30);
INSERT
INTO
EMP
VALUES
(7876,
'ADAMS'
,
'CLERK'
,7788,to_date(
'13-JUL-87'
,
'dd-mm-rr'
)-51,1100,
NULL
,20);
INSERT
INTO
EMP
VALUES
(7900,
'JAMES'
,
'CLERK'
,7698,to_date(
'3-12-1981'
,
'dd-mm-yyyy'
),950,
NULL
,30);
INSERT
INTO
EMP
VALUES
(7902,
'FORD'
,
'ANALYST'
,7566,to_date(
'3-12-1981'
,
'dd-mm-yyyy'
),3000,
NULL
,20);
INSERT
INTO
EMP
VALUES
(7934,
'MILLER'
,
'CLERK'
,7782,to_date(
'23-1-1982'
,
'dd-mm-yyyy'
),1300,
NULL
,10);
CREATE
TABLE
BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;
CREATE
TABLE
SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
INSERT
INTO
SALGRADE
VALUES
(1,700,1200);
INSERT
INTO
SALGRADE
VALUES
(2,1201,1400);
INSERT
INTO
SALGRADE
VALUES
(3,1401,2000);
INSERT
INTO
SALGRADE
VALUES
(4,2001,3000);
INSERT
INTO
SALGRADE
VALUES
(5,3001,9999);
COMMIT
;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
sqlplus /
as
sysdba
SQL> @/home/oracle/scott.sql;
SQL> conn c##scott/tiger
Connected.
SQL>
select
table_name
from
user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
SALGRADE
BONUS
DEPT
EMP
SQL>
select
*
from
SALGRADE;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
|