1创建用户 SQL> CREATE USER xiaoxc IDENTIFIED BY kusch 2 DEFAULT TABLESPACE myDataTS 3 TEMPORARY TABLESPACE myTempTS; User created. 2创建临时表空间 SQL> CREATE TEMPORARY TABLESPACE myTempTS 2 TEMPFILE 'D:/oracle/product/10.1.0/oradata/STUDY/myTempTS.DBF' SIZE 10M 3 AUTOEXTEND ON NEXT 1M MAXSIZE 20M; Tablespace created. 3创建永久表空间 SQL> create tablespace myDataTS 2 DATAFILE 'D:/oracle/product/10.1.0/oradata/STUDY/myDataTS.DBF' SIZE 10M 3 AUTOEXTEND ON NEXT 1M MAXSIZE 100M; Tablespace created. 4解锁数据库的某一用户 SQL> conn sys/kusch@study as sysdba Connected. SQL> alter user scott identified by kusch account unlock; User altered. 5查看某一角设的所拥有的权限 my.....>select * from dba_sys_privs where grantee='MYROLE'; GRANTEE PRIVILEGE ADM ------------------------------ ---------------------------------------- --- MYROLE CREATE TABLE NO MYROLE CREATE TABLESPACE NO 6创建一个用户后，如果用该用户创建表时，一定得先给这个用户分配表空间的使用配额，否则无法创建表成功。 MY...>CONN SYS/KUSCH@STUDY AS SYSDBA Connected. MY...>ALTER USER XIAOXC QUOTA UNLIMITED ON DATA_INFOR_TS 7修改数据库表的表名 MY...>RENAME DEPT_LOCAIONS TO DEPT_LOCATIONS; Table renamed. 8为表添加check约束 MY...>ALTER TABLE DEPENDENT 2 ADD CONSTRAINT CK_DEPENDENT_SEX CHECK(SEX IN ('F','M')); Table altered. 9为表添加主键的两种脚本实现方法 法一:创建数据表的时候直接添加,脚本如下: MY...>create table PROJECT 2 (PNAME VARCHAR2(20), 3 PNUMBER NUMBER PRIMARY KEY, 4 PLOCATION VARCHAR2(20), 5 DNUM NUMBER REFERENCES DEPARTMENT); Table created. 法二:数据表创建好后,修改数据表,脚本如下: MY...>create table DEPARTMENT(DNAME VARCHAR2(20), 2 DNUMBER NUMBER, 3 MGRSSN VARCHAR2(20), 4 MGRSTARTDATE DATE); Table created. MY...>alter table DEPARTMENT 2 ADD CONSTRAINT PK_DEPTARTMENT PRIMARY KEY (DNUMBER); Table altered. 10为表添加外键的两种脚本实现方法 法一:创建数据表的时候直接添加,脚本如下 : MY...>CREATE TABLE WORKS_ON( ESSN VARCHAR2(20) REFERENCES EMPLOYEE, 2 PNO NUMBER REFERENCES PROJECT, 3 HOURS NUMBER(4,1)); Table created. 法二:数据表创建好后,修改数据表 实现,脚本如下: MY...>create table DEPARTMENT(DNAME VARCHAR2(20), 2 DNUMBER NUMBER, 3 MGRSSN VARCHAR2(20), 4 MGRSTARTDATE DATE); Table created. MY...>ALTER TABLE DEPARTMENT 2 ADD CONSTRAINT FK_DEPT_EMP FOREIGN KEY (MGRSSN) REFERENCES EMPLOYEE(SSN); Table altered.
