-- 使用超级管理员登录
CONN sys/change_on_install AS SYSDBA ;
-- 创建c##scott用户
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; -- 使用c##scott用户登录 CONNECT c##scott/tiger -- 删除数据表(删除表结构) DROP TABLE emp PURGE ; DROP TABLE dept PURGE ; DROP TABLE bonus PURGE ; DROP TABLE salgrade PURGE ; -- 创建数据表 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 ); -- 插入测试数据 —— dept INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK'); -- 插入测试数据 —— emp INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); -- 事务提交 COMMIT;