用来做个人记录的
1、用户管理
(1)创建用户
Create user myuser identified by testuserpwd default tablespace MAIN;
(2)修改用户密码
Alter user myuser identified by newpwd;
(3)修改默认表空间
Alter user myuser default tablespace mydata;
(4)授权
grant "DBA","PUBLIC","RESOURCE","SOI","VTI" to " myuser" with admin option;
(5)查看
select * from dba_users;
2、表空间管理
(1)创建表空间
create tablespace "TEST" datafile '/dmdata/DAMENG/TEST01.dbf' size 10240
(2)表空间扩容(扩容数据文件大小或者新增)
alter tablespace "MAIN" resize datafile 'MAIN.DBF' to 10240;
alter tablespace "MAIN" add datafile '/dmdata/DAMENG/MAIN01.DBF' size 10240;
(3)查看表空间信息
select * from dba_tablespaces;
(4)查看数据文件信息
select * from dba_data_files;
3、表创建
CREATE TABLE EMPLOYEE (
EMPNO INT CLUSTER PRIMARY KEY,
ENAME VARCHAR(15) NOT NULL,
JOB VARCHAR(10),
MGR INT
CONSTRAINT EMP_FKEY REFERENCES EMPLOYEE(EMPNO), ---外键
HIREDATE DATE DEFAULT (CURDATE),
SALARY FLOAT,
DEPTNO TINYINT NOT NULL
CONSTRAINT DEPT_FKEY REFERENCES DEPT(DEPTNO))
STORAGE (
INITIAL 2,
NEXT 2,
MINEXTENTS 10,
FILLFACTOR 90,
ON USERS, CLUSTERBTR);
创建的表默认是聚簇表(也就是索引组织表)
(1)INITIAL 表段初始簇数,默认是1
(2)MINEXTENTS 表段保留最少簇数,默认1
(3)NEXT 表段下次扩展簇数,默认1
(4)FILLFACTOR 插入数据时填充比例,达到比例停止插入数据留作update,默认100
(5)ON tablespace_name 指定表空间存放
(6)CLUSTERBTR 创建聚簇表
(7)CLUSTER PRIMARY KEY 指定为聚簇索引