1 -- 01 创建表空间
2 -- 注意表空间的路径 根据实际安装环境进行调整
3
4 CREATE TABLESPACE ts_myscott
5 LOGGING
6 DATAFILE ‘/home/oracle_11/app/oradata/orcl/ts_myscott.dbf‘ SIZE 10M
7 EXTENT MANAGEMENT LOCAL;
8
9 CREATE TABLESPACE ts_myscott2
10 LOGGING
11 DATAFILE ‘/home/oracle_11/app/oradata/orcl/ts_myscott2.dbf‘ SIZE 20M
12 EXTENT MANAGEMENT LOCAL;
13
14 ALTER DATABASE DATAFILE ‘/home/oracle_11/app/oradata/orcl/ts_myscott.dbf‘ AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;
15 ALTER DATABASE DATAFILE ‘/home/oracle_11/app/oradata/orcl/ts_myscott2.dbf‘ AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED;
16
17 commit;
18
19 -- 02 创建方案 (创建用户)
20 CREATE USER MYSCOTT PROFILE DEFAULT
21 IDENTIFIED BY MYSCOTT DEFAULT TABLESPACE USERS
22 ACCOUNT UNLOCK;
23
24 -- 资源和登录权限
25 GRANT RESOURCE TO MYSCOTT;
26 GRANT create session TO MYSCOTT;
27
28
29 -- 03 创建表
30 -- 创建部门表 并赋值
31 CREATE TABLE MYSCOTT.DEPT(
32 DEPTNO NUMBER(2) PRIMARY KEY, 33 DNAME VARCHAR2(14) NOT NULL, 34 LOC VARCHAR2(13)
35 )TABLESPACE ts_myscott;
36
37 INSERT INTO MYSCOTT.dept VALUES(10,‘ACCOUNTING‘,‘NEW YORK‘);
38 INSERT INTO MYSCOTT.dept VALUES(20,‘RESEARCH‘,‘DALLAS‘);
39 INSERT INTO MYSCOTT.dept VALUES(30,‘SALES‘,‘CHICAGO‘);
40 INSERT INTO MYSCOTT.dept VALUES(40,‘OPERATIONS‘,‘BOSTON‘);
41
42 commit;
43
44 -- 创建员工表 并赋值
45 CREATE TABLE MYSCOTT.EMP(
46 EMPNO NUMBER(4) constraint emp_empno_pk PRIMARY KEY, 47 ENAME VARCHAR2(10) constraint emp_ename_notnull NOT NULL, 48 JOB VARCHAR2(9), 49 MGR NUMBER(4), 50 HIREDATE DATE, 51 SAL NUMBER(7,2) constraint emp_sal_check check (SAL>0), 52 COMM NUMBER(7,2), 53 DEPTNO NUMBER(2) constraint emp_deptno_fk references MYSCOTT.dept(deptno)
54 )TABLESPACE ts_myscott;
55
56 --创建索引 在新的表空间上
57 CREATE INDEX MYSCOTT.IX_CAtbAuditOperInfo_OT ON MYSCOTT.EMP(ENAME) TABLESPACE ts_myscott2;
58
59
60 INSERT INTO MYSCOTT.EMP VALUES(7369,‘SMITH‘,‘CLERK‘,7902,‘17-12月-80‘,800,NULL,20);
61 INSERT INTO MYSCOTT.EMP VALUES(7499,‘ALLEN‘,‘SALESMAN‘,7698,‘20-2月 -81‘,1600,300,30);
62 INSERT INTO MYSCOTT.EMP VALUES(7521,‘WARD‘,‘22-2月 -81‘,1250,500,30);
63
64 INSERT INTO MYSCOTT.EMP VALUES(7566,‘JONES‘,‘MANAGER‘,7839,‘02-4月 -81‘,2975,20);
65 INSERT INTO MYSCOTT.EMP VALUES(7654,‘MARTIN‘,‘28-9月 -81‘,1400,30);
66 INSERT INTO MYSCOTT.EMP VALUES(7698,‘BLAKE‘,‘01-5月 -81‘,2850,30);
67
68 INSERT INTO MYSCOTT.EMP VALUES(7782,‘CLARK‘,‘09-6月 -81‘,2450,10);
69 INSERT INTO MYSCOTT.EMP VALUES(7788,‘SCOTT‘,‘ANALYST‘,7566,‘19-4月 -87‘,3000,20);
70 INSERT INTO MYSCOTT.EMP VALUES(7839,‘KING‘,‘PRESIDENT‘,‘17-11月-81‘,5000,10);
71
72 INSERT INTO MYSCOTT.EMP VALUES(7844,‘TURNER‘,‘08-9月 -81‘,1500,0,30);
73 INSERT INTO MYSCOTT.EMP VALUES(7876,‘ADAMS‘,7788,‘23-5月 -87‘,1100,20);
74 INSERT INTO MYSCOTT.EMP VALUES(7900,‘JAMES‘,‘03-12月-81‘,950,30);
75
76 INSERT INTO MYSCOTT.EMP VALUES(7902,‘FORD‘,20);
77 INSERT INTO MYSCOTT.EMP VALUES(7934,‘MILLER‘,7782,‘23-1月 -82‘,1300,10);
78
79 commit;
80
81 -- 创建工资级别表 并赋值
82 CREATE TABLE MYSCOTT.SALGRADE(
83 GRADE NUMBER, 84 LOSAL NUMBER, 85 HISAL NUMBER
86 )TABLESPACE ts_myscott;
87
88 INSERT INTO MYSCOTT.SALGRADE VALUES(1,700,1200);
89 INSERT INTO MYSCOTT.SALGRADE VALUES(2,1201,1400);
90 INSERT INTO MYSCOTT.SALGRADE VALUES(3,1401,2000);
91 INSERT INTO MYSCOTT.SALGRADE VALUES(4,2001,3000);
92 INSERT INTO MYSCOTT.SALGRADE VALUES(5,3001,9999);
93 commit;
94
95 --创建奖金表
96 CREATE TABLE MYSCOTT.BONUS(
97 ENAME VARCHAR2(10), 98 JOB VARCHAR2(9), 99 SAL NUMBER,100 COMM NUMBER
101 )TABLESPACE ts_myscott;
102
103 ------停止-----
104 -- 04创建新用户方案 通过MYSCOTTUSER1来访问数据库,权限配置演示
105 CREATE USER "MYSCOTTUSER1" PROFILE "DEFAULT" IDENTIFIED BY "123456" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;
106 GRANT "CONNECT" TO "MYSCOTTUSER1";
107 GRANT SELECT ANY TABLE TO "MYSCOTTUSER1";
108
109 GRANT DELETE ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
110 GRANT INSERT ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
111 GRANT UPDATE ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
112
113 GRANT DELETE ON MYSCOTT.EMP TO "MYSCOTTUSER1";
114 GRANT INSERT ON MYSCOTT.EMP TO "MYSCOTTUSER1";
115 GRANT UPDATE ON MYSCOTT.EMP TO "MYSCOTTUSER1";
116
117 commit;
118
119
