查看数据文件位置

select name from v$datafile;

查看控制文件位置

SELECT NAME FROM v$controlfile;

查看日志文件位置

SELECT MEMBER FROM v$logfile;

查看处理undotbs1表空间满，无法拓展问题

先用show parameter undo_retention显示回滚段的保留时间，显示的时间单位是“秒”。

oracle里对undo_retention默认时间是900秒。

如果要修改 undo_retention的默认时间，用下面一句话修改：

ALTER SYSTEM SET undo_retention=100 SCOPE=BOTH;

-- 建立表空间

CREATE TABLESPACE ts01 

DATAFILE 'D:\DataBase\Oracle11g\oradata\orcl\test_db01.dbf'SIZE 100M

AUTOEXTEND ON NEXT 100M MAXSIZE 1024M

DEFAULT STORAGE(INITIAL 10m NEXT 1M)

PERMANENT

ONLINE

LOGGING;

-- 查看表空间数据文件路径

select t.file_name from dba_data_files t

-- 修改表空间

ALTER TABLESPACE ts01

NOLOGGING;

-- 表空间增加数据文件

ALTER TABLESPACE ts01 

ADD DATAFILE 'D:\DataBase\Oracle11g\oradata\orcl\test_db02.dbf'SIZE 100M REUSE

AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

-- 删除表空间

DROP TABLESPACE ts01;

-- 删除表空间同时删除数据文件

DROP TABLESPACE ts01 INCLUDING CONTENTS AND DATAFILES;

-- 表空间中建表

CREATE TABLE student(

student_id VARCHAR2(10),

student_name VARCHAR2(20)

)TABLESPACE ts01;

-- 查看表所属表空间

SELECT TABLE_NAME, TABLESPACE_NAME FROM tabs WHERE TABLE_NAME = 'STUDENT';

-- 查看表结构

DESCRIBE student;

DESC student;

-- 增加表注释

COMMENT ON TABLE student IS '学生信息表';

-- 查看表注释

SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME = 'STUDENT';

SELECT * FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = 'STUDENT';

-- 表字段增加注释

COMMENT ON COLUMN STUDENT.STUDENT_ID IS '学生编号';

-- 查看表字段注释

SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = 'STUDENT';

SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME = 'STUDENT';

-- 查看用户所有表

SELECT * FROM User_Tables;

-- 查看用户拥有的所有对象

SELECT * FROM User_Objects;

-- 查看用户拥有的 表 试图 序列

SELECT * FROM User_Catalog;

-- 表字段修改

ALTER TABLE student MODIFY(student_id CHAR(15));

-- 表字段增加

ALTER TABLE STUDENT ADD(AGE NUMBER(2));

-- 删除表字段

ALTER TABLE STUDENT DROP COLUMN student_name;

-- 修改表名称

RENAME STUDENT TO STU;

-- 删除表

DROP TABLE STUDENT;

CREATE TABLE student(

s_id Varchar2(10),

s_name varchar2(20),

s_age Number(3),

s_birthday DATE

)TABLESPACE ts01;

-- 增加一条记录

INSERT INTO

student (s_id, s_name, s_age, s_birthday) 

VALUES ('S000000001', 'Tarring01', 10, to_date('1982-10-06','yyyy-mm-dd'));

INSERT INTO

student (s_id, s_name, s_age, s_birthday) 

VALUES ('S000000002', 'Tarring02', 10, Sysdate);

-- 使用替代变量时，输入字符串字段时一样要写上引号

INSERT INTO

student (s_id, s_name, s_age, s_birthday) 

VALUES (&s_id, &s_name, 10, Sysdate);

-- 修改记录

UPDATE student SET s_name = '陶川', s_age = 20 WHERE s_id = 'S000000002';

-- 删除记录

DELETE FROM student WHERE s_id = 'S000000002';

-- 截断表

TRUNCATE TABLE student;

-- 事务处理

COMMIT; -- 提交事务

INSERT INTO student (s_id, s_name) VALUES ('S001', 'tarring1');

ROLLBACK; -- 回滚，回滚到上一次提交过后的点

-- 带恢复点的事务

COMMIT;

INSERT INTO student (s_id, s_name) VALUES ('S001', 'tarring1');

SAVEPOINT firstdate;

INSERT INTO student (s_id, s_name) VALUES ('S002', 'tarring2');

SAVEPOINT seconddate;

DELETE FROM student;

ROLLBACK TO firstdate;

SELECT * FROM student;

--  约束条件            说明

--------------------------------------------------------------------

--  UNIQUE              指定字段的值，必须是唯一的

--  PRIMARY KEY         主键，会为指定的字段作索引，并且也是唯一的值

--  NOT NULL            不可以是空值【'' NULL】或0（零）

--  CHECK               检查，必须符合指定的条件

--  FOREIGN KEY         外键，用来创建一个参考表之间的关系

-- 建表同时建立唯一约束

CREATE TABLE student(

s_id Varchar2(10),

s_name varchar2(20),

s_age Number(3),

s_birthday DATE,

CONSTRAINT s_name_uk UNIQUE(s_name)

)TABLESPACE ts01;

-- 查看唯一约束

SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHERE table_name = 'STUDENT';

-- 作业：数据字典【分类 常用】

-- 建表同时建立主键

CREATE TABLE student(

s_id Varchar2(10),

s_name varchar2(20),

s_age Number(3),

s_birthday DATE,

CONSTRAINT s_id_pk PRIMARY KEY (s_id)

)TABLESPACE ts01;

-- 查看主键约束

SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHERE table_name = 'STUDENT';

-- 建表同时建立非空字段

CREATE TABLE student(

s_id Varchar2(10),

s_name varchar2(20) NOT NULL,

s_age Number(3),

s_birthday DATE,

CONSTRAINT s_id_pk PRIMARY KEY (s_id)

)TABLESPACE ts01;

INSERT INTO student (s_id, s_name) VALUES ('S001', NULL); -- 插入一个null

-- 查看非空约束

SELECT table_name, constraint_name, constraint_type, search_condition FROM User_Constraints WHERE table_name = 'STUDENT';

-- 建表同时建立检查

CREATE TABLE student(

s_id Varchar2(10),

s_name varchar2(20),

s_age Number(3),

s_birthday DATE,

CONSTRAINT s_age_ck CHECK (s_age BETWEEN 1 AND 100) -- 端点值可以使用

)TABLESPACE ts01;

-- 查看检查约束

SELECT table_name, constraint_name, constraint_type, search_condition FROM User_Constraints WHERE table_name = 'STUDENT';

-- 外键的使用

CREATE TABLE team(

t_id Varchar2(10),

t_name Varchar2(20),

CONSTRAINT t_id_pk PRIMARY KEY (t_id)

)TABLESPACE ts01;

CREATE TABLE student(

s_id Varchar2(10),

team_id VARCHAR2(10),

s_name varchar2(20),

CONSTRAINT s_id_pk PRIMARY KEY (s_id),

CONSTRAINT s_team_id_fk FOREIGN KEY (team_id) REFERENCES team(t_id)

)TABLESPACE ts01;

-- 查看表的外键约束

SELECT table_name, constraint_name, constraint_type FROM User_Constraints WHERE table_name = 'STUDENT';

drop table team;  -- 被引用表是不能删除的

insert into team (t_id, t_name) values ('t001', 'lansene');

insert into student(s_id, s_name, team_id) values ('s001','tarring', 't001');

delete from team; -- 被引用的记录是不能删除的

-- 关闭一个约束

ALTER TABLE student DISABLE CONSTRAINT s_team_id_fk;

-- 启用一个约束

ALTER TABLE student ENABLE CONSTRAINT s_team_id_fk;

-- 删除一个约束

ALTER TABLE student DROP CONSTRAINT s_team_id_fk;

-- 已创建的表增加一个约束

ALTER TABLE student ADD CONSTRAINT s_team_id_fk FOREIGN KEY (team_id) REFERENCES team(t_id);

/********************************************************************************************|

|             SQL语句5大类型                    |  命令     | 说明                           |

|********************************************************************************************|

| Data Retrieval数据检索                        | select    | 查询记录                       |

|********************************************************************************************|

| Date Manipulation Language【DML】数据操纵语言 | insert    | 添加记录                       |

|                                               | update    | 修改记录                       |

|                                               | delete    | 删除记录                       |

|********************************************************************************************|

| Data Definition Language【DDL】数据定义语言   | create    | 创建                           |

|                                               | alter     | 修改                           |

|                                               | drop      | 丢弃【删除】                   |

|                                               | rename    | 重命名                         |

|                                               | truncate  | 截断                           |

|********************************************************************************************|

| Transaction Control事务控制                   | commit    | 确认命令                       |

|                                               | rollback  | 回退至前一次确认的命令或保存点 |

|                                               | savepoint | 设置保存点                     |

|********************************************************************************************|

| Data Control Language【DCL】数据控制语言      | grant     | 授予权限                       |

|                                               | revoke    | 撤消权限                       |

|********************************************************************************************/

/*************************************|

|       系统权限       |     说明     |

|*************************************|

|   create session     |  连接数据库  |

|*************************************|

|   create table       |  创建表      |

|*************************************|

|   create sequence    |  创建序列    |

|*************************************|

|   create view        |  创建视图    |

|*************************************|

|   create proceduer   |  创建程序    |

|*************************************/

/*******************************************************************|

|      \  对象 |  表【table】 |  视图【view】 |  程序【procedure】  |

| 权限  \      |              |               |                     |

|*******************************************************************|

|    insert    |      Y       |       Y       |                     |

|*******************************************************************|

|    alter     |      Y       |               |                     |

|*******************************************************************|

|    update    |      Y       |       Y       |                     |

|*******************************************************************|

|    delete    |      Y       |       Y       |                     |

|*******************************************************************|

|    select    |      Y       |       Y       |                     |

|*******************************************************************|

|    index     |      Y       |               |                     |

|*******************************************************************|

|    execute   |              |               |          Y          |

|*******************************************************************/

-- 创建用户

CREATE USER u01 IDENTIFIED BY p01;

-- 创建用户并制定默认表空间

CREATE USER u01 IDENTIFIED BY p01 DEFAULT TABLESPACE QUOTA 2M ON ts01; --quota 表空间中可使用的配额

-- 修改用户密码

ALTER USER u01 IDENTIFIED BY p001;

-- 修改用户表空间配额

ALTER USER u01 QUOTA 20M ON ts01;

ALTER USER u01 QUOTA UNLIMITED ON ts01; -- 用户对表空间没有配额限制

-- 回收unlimited tablespace权限

REVOKE UNLIMITED TABLESPACE FROM ts01; 

-- 删除用户

DROP USER u01;

-- 切换连接数据库的用户

CONNECT u01/p01;

conn u01/p01;

-- 授权用户连接数据库的权限

GRANT CREATE SESSION TO u01;

-- 授权用户创建序列(sequence)的权限

GRANT CREATE sequence TO u01;

-- 授权用户创建表的权限

GRANT CREATE TABLE TO u01;

--授权用户查表的权限

grant select on ts01.user1 to wangkai；

--授权用户修改表的权限

grant alter on user1 to  wangkai；

--授权用户删除表的权限(没有此权限)

grant drop on user1 to  wangkai；(错误)

--授权用户对一个表的所有权限

grant all on user1 to wangkai; 

--授权所有用户对一个表的所有权限

grant all on user1 to public;

-- 收回用户创建表的权限

REVOKE CREATE TABLE FROM u01;

-- 收回用户查表的权限

REVOKE select on user1 FROM u01;

-- 收回用户修改的权限

REVOKE select on user1 FROM u01;

-- 收回用户对一个表的所有权限

revoke all on user1 from wangkai;

--收回所有用户对一个表的所有权限

revoke all on user1 to public; 

-- 创建角色

CREATE ROLE r01;

-- 角色授权

GRANT CREATE SESSION, CREATE TABLE TO r01;

-- 收回角色权限

REVOKE CREATE TABLE FROM r01;

-- 查看角色权限

SELECT ROLE,PRIVILEGE FROM role_sys_privs WHERE ROLE='R01'

-- 角色赋给用户

GRANT r01 TO u01;

-- 查看当前用户角色

SELECT * FROM user_role_privs;

-- 删除角色

DROP ROLE r01;