Oracle常用命令-基本命令

常用命令

// 增删改查

// 登陆SQL*Plus（以管理员用户登陆）
# sysdba登录orcl实例
sqlplus / as sysdba
# jczltyfb 远程登入某地址 orcl实例（注意高版本不登入低版本）
sqlplus jczltyfb/passwd@10.255.33.xx:1521/orcl

//关闭数据库
SQL>shutdown
//启动数据库
SQL>startup
重启数据库
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open;

//查询实例状态                                           
SQL> select status from v$instance;

//查看数据库版本
SQL> select * from v$version;
 
 // 解锁SYSTEM,scott用户，可以用远程工具连接
alter user SYSTEM account unlock;
alter user scott account unlock;

// 激活scott用户
SQL> alter user scott identified by tiger;

User altered.

// 修改SYSTEM用户密码
alter user system identified by 新密码
;

// 授权scott用户，否则不能登录
SQL> grant dba to scott;

 
// 查看oracle所有用户 ，查询Oracle中所有用户信息
SQL> select username,account_status from dba_users; 
SQL> select * from all_users;

 **查看数据文件位置**
  SELECT NAME FROM v$datafile;
- **查看控制文件位置**
  SELECT NAME FROM v$controlfile;
- **查看日志文件位置**
  SELECT MEMBER FROM v$logfile;
  
//创建需要的表空间
mkdir /data/app/oracle/oradata/DB_DATA

//表空间大小2G，每次变动100M，设置数据文件的自动扩展。
 create tablespace DB_DATA logging datafile'/data/app/oracle/oradata/DB_DATA/DB_DATA.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

// 创建新用户指定表空间
create user ogg identified by ogg default tablespace DB_DATA;

// grant新用户权限
//清屏
clear scr

//DDL DMLsql语句操作

• 修改表空间
  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 (&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;
• 查看非空约束
  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;
• 被引用的记录是不能删除的
  nsert 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);

系统权限	说明
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 all on user1 from wangkai;
• 收回所有用户对一个表的所有权限
  revoke all on user1 to public;