--创建永久表空间
CREATE TABLESPACE "58SPACE"
LOGGING
DATAFILE 'D:\ORACLE\ORADATA\MYDB\57SPACE.ora' SIZE 5M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
--创建临时表空间
CREATE
TEMPORARY TABLESPACE "58TMPSPACE" TEMPFILE
'D:\ORACLE\ORADATA\MYDB\58TMPSPACE.ora' SIZE 5M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 2M

--创建用户
CREATE USER "58USER"
IDENTIFIED BY "123456"--指定口令
DEFAULT TABLESPACE "58SPACE"--分配永久表空间
TEMPORARY TABLESPACE "58TMPSPACE"--分配临时表空间
QUOTA UNLIMITED ON "58SPACE";--分配使用表空间的权限
--SQL COMMAND
--用户从客户端登录数据库
CONN 58USER/123456@MYDB;
CONN system/system@MYDB;
--创建角色
CREATE ROLE "58ROLE";
CREATE ROLE "TESTROLE";
--授权
GRANT ALTER ANY TABLE TO "58ROLE";
GRANT ALTER ANY TABLE TO "58ROLE" WITH ADMIN OPTION;
REVOKE ALTER ANY TABLE FROM "58ROLE";
GRANT CONNECT TO "58USER" WITH ADMIN OPTION;
REVOKE CONNECT FROM "58USER";
GRANT RESOURCE TO "58USER";
GRANT CONNECT TO "TESTROLE";


--创建永久表空间
CREATE TABLESPACE "62SPACE"
LOGGING
DATAFILE 'D:\ORACLE\ORADATA\MYDB\62SPACE.ora' SIZE 5M EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
--创建临时表空间
CREATE
TEMPORARY TABLESPACE "62TMPSPACE" TEMPFILE
'D:\ORACLE\ORADATA\MYDB\62TMPSPACE.ora' SIZE 5M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 2M

--创建用户
CREATE USER "62USER"
IDENTIFIED BY "123456"--指定口令
DEFAULT TABLESPACE "62SPACE"--分配永久表空间
TEMPORARY TABLESPACE "62TMPSPACE"--分配临时表空间
QUOTA UNLIMITED ON "62SPACE";--分配使用表空间的权限
--SQL COMMAND
--用户从客户端登录数据库
CONNECT 62USER/123456@MYDB;
CONN system/system@MYDB;
--创建角色
CREATE ROLE "62ROLE";
CREATE ROLE "TESTROLE";
--授权
GRANT ALTER ANY TABLE TO "62ROLE";
GRANT ALTER ANY TABLE TO "62ROLE" WITH ADMIN OPTION;
REVOKE ALTER ANY TABLE FROM "62ROLE";
GRANT CONNECT TO "62USER" WITH ADMIN OPTION;
GRANT CONNECT TO "58USER";
--取消权限或角色
REVOKE CONNECT FROM "62USER";
GRANT RESOURCE TO "62USER";
GRANT CONNECT TO "TESTROLE";
--查看系统视图
SELECT * FROM USER_TABLESPACES;
SELECT * FROM USER_OBJECTS;
SELECT * FROM USER_CONSTRAINTS;
--创建表
CREATE TABLE STUDENT(
ID CHAR(10),
NAME VARCHAR2(40) NOT NULL,
SEX CHAR(1) CONSTRAINTS STU_SEX_NN NOT NULL,
BIRTHDAY DATE,
CONSTRAINTS STU_ID_PK PRIMARY KEY (ID)
);

CREATE TABLE DEGREEdd (
ID CHAR(10),
CID CHAR(4),
SCORE NUMBER(4, 1)
);

--删除表
DROP TABLE DEGREE;
--添加约束
ALTER TABLE DEGREE
ADD CONSTRAINTS DEG_IDCID_PK PRIMARY KEY (ID, CID);
--外键约束
ALTER TABLE DEGREE
ADD CONSTRAINTS DEG_ID_FK FOREIGN KEY (ID) REFERENCES STUDENT(ID);
--CHECK 约束
ALTER TABLE DEGREE
ADD CONSTRAINTS DEG_SC_C CHECK (SCORE >= 60);

--UNIQUE
ALTER TABLE DEGREE
ADD CONSTRAINTS DEG_CID_UK UNIQUE (CID);
--添加 NOT NULL
ALTER TABLE DEGREE
MODIFY CID NOT NULL;

ALTER TABLE DEGREE
MODIFY CID CONSTRAINTS DEG_CID_NN NOT NULL;
--删除约束
ALTER TABLE DEGREE
DROP CONSTRAINTS SYS_C003040;

--添加列
ALTER TABLE STUDENT
ADD ROOMID CHAR(4);

--SQL COMMAND
DESC STUDENT;
--删除列
ALTER TABLE STUDENT
DROP COLUMN ROOMID;

--修改列的类型
ALTER TABLE DEGREE
MODIFY CID VARCHAR2(4);

--启用/禁用约束
ALTER TABLE DEGREE
ENABLE CONSTRAINTS DEG_CID_NN;
ALTER TABLE DEGREE
DISABLE CONSTRAINTS DEG_CID_NN;

--修改列名
ALTER TABLE DEGREE
RENAME COLUMN CID TO COURSE;
--修改约束名
ALTER TABLE DEGREE
RENAME CONSTRAINTS DEG_CID_NN TO DEG_COU_NN;

--修改表名
RENAME STUDENT TO STU;
--截断表,删除数据的同时释放该表所占用的内存空间
TRUNCATE TABLE STU;

本文出自 “Enthusiasm 10年” 博客