oracle 常用命令
常用数据字典
可以使用desc 查看结构
dba_users
dba_tablespaces
user_users
user_tablespaces
dba_data_files #文件数据，可查看表空间的实际存储位置
USER_CONSTRAINTS; #查询数据字典可以看到某个表中约束的信息
#查看表
user_tables#当前用户表
user_tab_comments;#
all_tab_comments #所有表
#视图字典视图
dba_views——DBA视图描述数据库中的所有视图
all_views——ALL视图描述用户“可访问的”视图
user_views——USER视图描述“用户拥有的”视图
dba_tab_columns——DBA视图描述数据库中的所有视图的列(或表的列)
all_tab_columns——ALL视图描述用户“可访问的”视图的列(或表的列)
user_tab_columns——USER视图描述“用户拥有的”视图的列(或表的列)
##权限
dba_role_privs
dba_sys_privs
role_sys_privs
登录和基本操作
使用sqlplus 登录
### 登录和基本操作
connect sysdba/manager
### 查看当前用户
show user;
###权限
select * from dba_role_privs;
select * from dba_sys_privs;
select * from role_sys_privs;
###查看当前用户对象的表
select table_name from user_tables;
select * from user_tab_comments; # all_tab_comments
### 查看数据字典（表）的结构
desc dba_tablespaces; # 系统管理员
desc user_tablespaces; #普通用户
### 查看用户字典
desc dba_users; #系统管理员
desc user_users; #普通用户
###查询数据字典
select * from dba_users; #系统管理员才能查询，注意 非管理员用户登录查询该字典会提示，表或者视图不存在，因为没有权限
select * from user_users; #普通用户查询
### 解锁scott用户
select account_status from dba_users where username='SCOTT'; # 查看scott用户的状态
alter user scott account unlock; #将scott用户解锁，默认是锁状态
alter user scott identified by tiger; #修改scott 用户密码
表空间
### 查询
select * from dba_tablespaces; # 查看dba用户表空间
select * from dba_tablespaces; # 查看dba用户表空间
select default_tablespace from user_users where username='SCOTT';#查看SCOTT用户的默认表空间
### 设置用户的默认或者临时表空间
alter user username default/temporary tablespace tablespacename; #语法
alter user scott default tablespace SYSTEM; #设置SCOTT用户默认表空间是system,注意需要dba用户来修改，普通用户权限不足
#修改之后使用scott 用户登录，查询数据字典
select * from user_users; #可以看到默认的用户表空间已经变更
###通过数据字典查看表空间数据
desc dba_data_files; #数据字典
select * from dba_data_files; #查看数据文件存储
select * from dba_tablespaces; #查看表空间的状态
### 创建表空间
create [temporary] tablespace tablespacename datafile 'xxx.dbf' size 10m ; 语法
create TABLESPACE fore_tablespace DATAFILE 'fore_tablespace.dbf' SIZE 10m; #默认是永久表空间，使用dba用户创建表空间，大小10m
create temporary TABLESPACE temp_tablespace DATAFILE 'temp_tablespace.dbf' SIZE 10m; #创建临时表空间，大小10m
### 设置表空间的联机或者脱机状态
alter tablespace tablespacename online|offline; #语法
alter tablespace fore_tablespace offline； #默认创建的时候是联机
select * from dba_tablespaces;#查看表空间状态
### 修改表空间的状态（只有在联机状态下可以修改）
alter TABLESPACE tablespacename read ONLY| read write;#语法，只读和可读写，默认是可读写的
alter TABLESPACE fore_tablespace read ONLY；
select * from dba_tablespaces; #查看表空间的状态
### 修改数据文件（表空间需要在联机状态下）
select * from dba_tablespaces; #查看表空间状态
alter tablespace tablespacename add DATAFILE '/home/oracle/xx/df1.dbf' size 10m; # 语法
alter tablespace fore_tablespace add DATAFILE 'df1.dbf' size 10m; #若不指定路径，则会oracle存放在oracle的默认位置
select * from dba_data_files where TABLESPACE_NAME='TEST_USER_TABLESPACE1'; #查看表空间对应的数据文件
###删除表空间的数据文件，注意不能删除创建表空间的第一个数据文件，若想删除，需要先删除表空间
--强行删除会出现该错误
--------------------------------------------------------------------------
-----03261. 00000 - "the tablespace %s has only one file"---------------
-----*Cause: Dropping file from ts which has a single file extent.-----
-----*Action: Cannot make a tablespace fileless-------------------------
-------------------------------------------------------------------------
alter tablespace tablespacename drop DATAFILE 'df1.dbf'; #语法，指定文件名
###删除表空间
drop TABLESPACE tablespacename[INCLUDING CONTENTS] ; #语法,[INCLUDING CONTENTS]包含对应的数据文件
drop TABLESPACE TEST_USER_TABLESPACE1 INCLUDING CONTENTS ; #删除表空间以及表空间对应的数据文件
表
###创建表
CREATE TABLE TABLE_NAME (
COLUMN_NAME DATATYPE ,...
) #语法
CREATE TABLE USERINFO (
ID NUMBER(6,0) ,
USERNAME VARCHAR2(20),
EMAIL VARCHAR2(20),
BIRTHDAY DATE
); #创建用户信息表
DESC USERINFO; #查看表结构
###修改表
--添加字段
ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATEtYPE;#语法
ALTER TABLE USERINFO ADD AGE NUMBER(3,0); #添加字段
--修改字段名字
ALTER TABLE TABLE_NAME RENAME COLUMN COLUMN_NAME TO NEW_COLUMN_NAME;#语法
ALTER TABLE USERINFO RENAME COLUMN AGE TO AGE1; #将AGE重命名为AGE1
--修改表名
RENAME TABLE_NAME TO NEW_TABLENAME;#语法
RENAME USERINFO TO NEW_USERINFO; #修改USERINFO 为 NEW_USERINFO
--修改字段的数据类型
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DATETYPE; #语法
ALTER TABLE USERINFO MODIFY AGE DATE; #修改字段的数据类型
--删除字段
ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME; #语法
ALTER TABLE USERINFO DROP COLUMN AGE; #删除字段AGE
--删除表
DROP TABLE TABLE_NAME ; #语法
DROP TABLE USERINFO ; #删除表
--删除表中所有的数据，截断表，比delete效率高
TRUNCATE TABLE TABLE_NAME; #语法
TRUNCATE TABLE USERINFO;
表约束
主键约束
非空约束
唯一约束
检查约束
1.主键约束（PRIMARY KEY）
- 主键用于唯一地标识表中的每一条记录，可以定义一列或多列为主键。
- 是不可能（或很难）更新．
- 主键列上没有任何两行具有相同值（即重复值），不允许空（NULL）．
- 主健可作外健，唯一索引不可；
2.唯一性约束（UNIQUE）
- 唯一性约束用来限制不受主键约束的列上的数据的唯一性，用于作为访问某行的可选手段，一个表上可以放置多个唯一性约束．
- 只要唯一就可以更新．
- 即表中任意两行在 指定列上都不允许有相同的值，允许空（NULL）．
- 一个表上可以放置多个唯一性约束
### 添加非空约束(创建表时)
CREATE TABLE TABLE_NAME (
COLUMN_NAME DATE_TYPE [NOT NULL]|[缺省]|[NULL]); #语法，添加表的时候添加非空约束,默认是可以为空
###修改
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME DATE_TYPE [NOT NULL]|[缺省]|[NULL]; #语法
###添加主键约束（创建表时）
CREATE TABALE TABLE_NAME(
ID NUMBER(11,0) PRIMARY KEY); # PRIMARY KEY 主键约束
--多个字段联合约束,联合主键，复合主键
CREATE TABALE TABLE_NAME(
ID NUMBER(11,0),
NAME VARCHAR2(20) ,
CONSTRAINT CONSTRAINT_NAME PRIMARY KEY (COLUMN_NAME1,COLUMN_NAME2)
); # PRIMARY KEY 主键约束,语法 CONSTRAINT(约束)
---例子
CREATE TABLE HELLO(
ID NUMBER(11,0) ,
NAME VARCHAR2(20) ,
CONSTRAINT PK_ID_NAME PRIMARY KEY (ID,NAME)
);
--查询数据字典可以看到某个表中约束的信息
DESC USER_CONSTRAINTS;
SELECT * FROM USER_CONSTRAINTS;#查看所有约束
###后为表添加约束
ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINTS_NAME PRIMARY KEY (COLUMN_NAME1,COLUMN_NAME2，...）；
--修改约束的名字
ALTER TABLE TABLE_NAME RENAME CONSTRAINT PK_NAME TO NEW_PK_NAME;
SELECT * FROM USER_CONSTRAINTS;#查看所有约束
--启用禁用约束
ALTER TABLE TABLE_NAME DISABLE | ENABLE CONSTRAINT CONSTRAINT_NAME;
SELECT STATUS FROM USER_CONSTRAINTS;#可以查看约束的状态 status
--删除主键约束
ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTAINT_NAME ;
ALTER TABLE TABLE_NAME DROP PRIMARY KEY [casacade] #级联删除，在有外键的时候使用
###外键约束
--创建表的时候添加，列级约束，在新建字段的时候添加
CREATE TABLE TABLE_NAME(
COLUMN DATE_TYPE REFERENCES OTHER_TABLE (COLUMN_NAME),OTHER_TABLE (COLUMN_NAME)...); #语法
--在新建表时，最后添加
CREATE TABLE TABLE_NAME(
CONSTRAINT CONSTRAINT_NAME FOREIGN KEY(COLUMN_NAME(本表中的字段))
REFERENCES OTHER_TABLE (COLUMN_NAME),OTHER_TABLE (COLUMN_NAME) [ON DELETE CASCADE]); #语法 [ON DELETE CASCADE]是否级联删除，当主表中的删除时，从表中的一行会被删除
--在修改表的时候添加外键约束
ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME FOREIGN KEY (COLUMN_NAME) REFERENCES OTHER_TABLE(COLUMN_NAME) [ON DELETE CASCADE];
--禁用（启用） 外键约束
SELECT * FROM USER_CONSTRAINTS; #找到对应表的约束名称 CONSTRAINT_NAME
ALTER TABLE TABLE_NAME DISABLE|ENABLE CONSTRAINT CONSTRAINT_NAME; #禁用约束
--删除外键约束
ALTER TABLE TABLE_NAME DROP CONTRAINT CONSTRAINT_NAME;
###唯一约束
--列级别
CREATE TABLE TABLE_NAME (
COLUMN DATE_TYPE UNIQUE ) #UNIQUE唯一约束
--表级别
CREATE TABLE TABLE_NAME (
COLUMN DATE_TYPE ，
CONSTRAINT CONSTRAINT_NAME UNIQUE(COLUMN_NAME)) ;#UNIQUE唯一约束
--在修改表的时候添加唯一约束
ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME unique (COLUMN_NAME);
--禁用（启用）唯一约束
SELECT * FROM USER_CONSTRAINTS; #找到对应表的约束名称 CONSTRAINT_NAME
ALTER TABLE TABLE_NAME DISABLE|ENABLE CONSTRAINT CONSTRAINT_NAME; #禁用约束
--删除唯一约束
ALTER TABLE TABLE_NAME DROP CONTRAINT CONSTRAINT_NAME;
###检查约束
--列级别
CREATE TABLE TABLE_NAME (
COLUMN DATE_TYPE CHECK(EXPRESSIONS)) #检查约束 (EXPRESSIONS) => COLUMN >0 表达式
--表级别
CREATE TABLE TABLE_NAME (
COLUMN DATE_TYPE ，
CONSTRAINT CONSTRAINT_NAME CHECK(EXPRESSIONS)) ;
--在修改表的时候添加检查约束
ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME CHACK(EXPRESSIONS);
--禁用（启用）检查约束
SELECT * FROM USER_CONSTRAINTS; #找到对应表的约束名称 CONSTRAINT_NAME
ALTER TABLE TABLE_NAME DISABLE|ENABLE CONSTRAINT CONSTRAINT_NAME; #禁用约束
--删除检查约束
ALTER TABLE TABLE_NAME DROP CONTRAINT CONSTRAINT_NAME;
查询
###字段命名
COL COLUMN_NAME HEADING 别名;
###CASE WHEN 语句
SELECT COLUMN_NAME ,CASE CLOUMN_NAME
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
ELSE '其他'
END AS 结果列名
FROM TABLE_NAME
WHERE XXX #第一种
###第二种方式
SELECT age ,CASE WHEN 0<age<18 THEN '未成年人'
WHEN AGE>18 THEN '成年人'
END AS REMARK
FROM PERSON
WHERE XXX #第二种方式，（字段>10）可以填写条件
序列
###创建队列
CREATE SEQUENCE seqTest
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXvalue -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10; --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置
--队列测试
CREATE SEQUENCE seqTest
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXvalue -- 不设置最大值
NOCYCLE -- 一直累加，不循环
NOCACHE --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE
--下一个值
select seqTest.nextval from dual;
--当前的值
select seqTest.currval from dual;
--设置Shenqingdh队列
CREATE SEQUENCE seqShenqingdh
INCREMENT BY 1 -- 每次加几个
START WITH 100000001 -- 从100000001开始计数
NOMAXvalue -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10 --设置缓存cache个序列，如果系统down掉了或者其它情况将会导致序列不连续，也可以设置为---------NOCACHE
--查询 (第一次要先查nextVal 值,否则直接查currval 值会报错)
select seqShenqingdh.currval from dual
select seqShenqingdh.nextVal from dual
--得到Sequence值
--定义好sequence后，你就可以用currVal，nextVal取得值。
-- CurrVal：返回 sequence的当前值
-- NextVal：增加sequence的值，然后返回 增加后sequence值
--SELECT [定义Sequence名称].CurrVal FROM DUAL;
--如得到上边创建Sequence值的语句为：
select seqShenqingdh.nextVal from dual
--删除序列
DROP SEQUENCE LAB.TEST_ITEM_NO;
--查询序列名，用户
SELECT t.* from dba_SEQUENCES t where t.sequence_name like '%TEST%';
注：
1、如果想要改变start的值，必须 drop sequence 再重建一个序列
2、如果想要改变minvalue的值，必须删除序列后再重新建立序列化。不可以修改序列化的minvalue。
视图
###创建视图
create [or replace ] view view_name
[column1,column2,column3...] --和查询字句的返回列数匹配
as
select xxx from xxx
[WITH CHECK OPTION]
[with read only];
###实例 scott 用户，得有创建视图的权限
connect /as system/manager
grant create view to scott，xxx;
--创建只读视图
--查询目前每个岗位姓名，平均工资（保留两位小数）、工资总和、最高工资和最低工资。分组，然后连接查名字
CREATE OR REPLACE VIEW v_dept_sal AS
SELECT
d.dname ，avg_sal,
sum_sal,
max_sal,
min_sal
FROM
(
SELECT
deptno,
to_number(TRIM(to_char(AVG(sal), '99999999999999.99'))) avg_sal,
SUM(sal) sum_sal,
MAX(sal) max_sal,
MIN(sal) min_sal
FROM
emp
GROUP BY
deptno
) s
LEFT JOIN dept d ON s.deptno = d.deptno
WITH READ ONLY;
###查询视图
SELECT * FROM V_DEPT_SAL;
###修改基表中数据，发现视图中的数据也会同时更新
UPDATE DEPT SET DNAME='SALES1' WHERE DEPTNO=30;
###查看快照
--可以通过这种方法查出最近某个时间的快照
select * from 表 as of timestamp to_timestamp('20151021 09:00:00','yyyymmdd hh24:mi:ss')
where 字段名=''
查询表空间占用
SELECT DD.TABLESPACE_NAME,
DD.AUTOEXTENSIBLE,
DD.ONLINE_STATUS,
DD.INCREMENT_BY * 8 / 1024 "next(M)",
A.*
FROM (SELECT D.FILE_ID AS FID,
MAX(D.BYTES) / 1024 / 1024 "total(M)",
NVL(SUM(F.BYTES), 0) / 1024 / 1024 "free(M)",
(1 - ((NVL(SUM(F.BYTES), 0) / 1024 / 1024) /
(MAX(D.BYTES) / 1024 / 1024))) * 100 "usage(%)",
D.FILE_NAME,
MAX(D.BYTES) - NVL(SUM(F.BYTES), 0) USED_BYTES,
ROUND(SQRT(MAX(F.BLOCKS) / SUM(F.BLOCKS)) *
(100 / SQRT(SQRT(COUNT(F.BLOCKS)))),
2) FRAG_IDX
FROM DBA_FREE_SPACE F, DBA_DATA_FILES D
WHERE F.TABLESPACE_NAME(+) = D.TABLESPACE_NAME
AND F.FILE_ID(+) = D.FILE_ID
GROUP BY D.FILE_NAME, D.FILE_ID) A
LEFT JOIN DBA_DATA_FILES DD
ON DD.FILE_ID = A.FID
ORDER BY DD.TABLESPACE_NAME ASC, A.FILE_NAME ASC;
数据库允许的最大连接数
SELECT COUNT(*) FROM V$SESSION;
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'processes'; --数据库允许的最大连接数
Session
SELECT COUNT() FROM V$SESSION; --当前的session连接数
SELECT COUNT() FROM V$PROCESS; --当前的数据库连接数
查看SQL执行记录
SELECT * FROM V$SESSION T WHERE T.MACHINE='AA6IYGQ8GYQUKZF';
SELECT T.CPU_TIME,T.ELAPSED_TIME,t.* FROM V$SQL T WHERE T.ADDRESS='000000008DB81038';
