Oracle常用操作【自己的练习】
Oracle查询的时候条件要用单引号包裹，不能用双引号;Oracle的in子查询里面的值最多有1000个。。。。。。。。
连接orcl数据库
C:\Windows\system32>sqlplus sys/123456@orcl as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 2 14:32:34 2018 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER 为 "SYS" SQL> conn sbgl/sbgl@orcl 已连接。 SQL> show user USER 为 "SBGL" SQL> show parameter instance_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ instance_name string orcl
登录用户切换用户:
C:\Windows\system32>sqlplus /@orcl as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 2 17:31:52 2018 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user USER 为 "SYS" SQL> connect sbgl/sbgl@orcl as sysdba 已连接。 SQL> show user USER 为 "SYS" SQL> connect sbgl/sbgl@orcl 已连接。 SQL> show user USER 为 "SBGL" SQL>
注意:以sysdba登录实际上登录的sys用户，因此登录用户的时候不要以sysdba登录。
/**1创建表空间**/
create tablespace sbgl datafile 'F:\Oracle\sbgl.dbf'size 200M
autoextend on next 50M force logging;
/**2.创建用户***/
create user sbgl identified by sbgl default tablespace sbgl;
/**3.授予sbgl操作权限**/
grant connect,resource,dba to sbgl;
/***4.创建导出目录(之后手动创建目录)**/
create or replace directory pump_dir as 'F:\expdp';
/********5.向新创的用户授权******/
grant read,write on directory pump_dir to sbgl;
/*******6.查看管理理员目录****/
select * from dba_directories;
导入导出:
*导入数据* 1、将SBGL20180129.EXPDP文件拷贝到d:\expdp； 2、在CMD窗口下执行以下命名 impdp sbgl/sbgl@orcl dumpfile=pump_dir:SBGL20180129.EXPDP table_exists_action=replace nologfile=y *导出数据：* expdp sbgl/sbgl@orcl dumpfile=pump_dir:SBGL20180129.expdp schemas=sbgl nologfile=y
/****查看用户的角色***/
select * from USER_SYS_PRIVS where USERNAME='sbgl';
select * from dba_sys_privs;
/**查看当前登录用户信息(用户名、创建时间、表空间)*****/
select * from user_users;
/**查看所有用户信息****/
select * from dba_users; /*查看数据库里面所有用户,前提是有dba权限的帐号,如sys,system*/
select username from dba_users; //查看所有有dba权限用户名称
select * from all_users; /*查看能管理的所有用户*/
/****查看用户与表空间的关系***/
select default_tablespace, temporary_tablespace, d.username from dba_users d where username='SBGL';
/***查看所有的表空间**/
select tablespace_name from dba_tablespaces;
/*******删除表空间*********/
drop tablespace 表空间名称 including contents and datafiles;
/*****查看所有的表空间与其对应的数据文件的位置*********/
select * from dba_data_files;
/*查看所有用户**/
select * from dba_users;
/*查看一个表空间下面的所有表**/
select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='SBGL';
/***查看一个用户有哪些表***/
select * from all_tables where owner='SBGL';
/***********查看当前用户有哪些表****/
select table_name from user_tables;
/**********查看当前用户有哪些试图**********/
select * from user_views;
select VIEW_NAME from user_views;
/*****查看dba_tablespaces表结构*****/
desc dba_tablespaces;
/***查看当前用户***/
show user;
select username from user_users;
/*****查看当前用户所有的权限***/
select * from session_privs;
/***查看当前连接数据库***/
show parameter instance_name;
/****查看建表语句******/
select dbms_metadata.get_ddl('TABLE','a') from dual 只需要将表名的小写改为大写即可；; //其中a为表名； 如果有PLSQL的话可以直接在可视化界面查看表的建表语句；
/*****oracle查看版本**/
select * from v$version;
/*查看数据库名*/
select name from v$database;
/*查看sid*/
select instance_name from v$instance;
/**根据约束名称查询对应的表名(有一列是tablename)**/
select * from dba_constraints where constraint_name='xxx外键名' and constraint_type = 'R';
/********删除用户************/
drop user username;
drop user username cascade;//级联删除用户的数据
/************添加一列 *******/
alter table A add( columnname varchar2(20));
/*********修改列名称 ******/
alter table A rename column coluName to newName;
/************修改列的类型：*******/
alter table A modify coluName varchar(200);
/****删除一列 ***********/
alter table A drop column coluName
/******字符串拼接 (||)***/
select 1||'23' from dual;
结果:
/**************序列简单使用*********/
1. 创建 CREATE SEQUENCE SEQ_TEST_1 INCREMENT BY 1 NOCYCLE CACHE 10; 2.获取序列的下一值 select "SEQ_TEST_1".nextval from dual 3.删除序列 DROP SEQUENCE SEQ_TEST_1
有时候序列会发生错误，导致数据插入报错，解决办法：重新设置自增的步长，然后自增一下到指定的值，然后重置自增的步长为1。比如下面想在目前错误的基础上+400变为正确的：
alter sequence SEQ_TEST_1 increment BY 400; select "SEQ_TEST_1".nextval from dual alter sequence SEQ_TEST_1 increment BY 1
插入的时候直接使用序列：
insert into table (id) values (seq_name.nextval)
补充:oracle中多个用户共用一个表空间也是不同的表，表空间、表、用户的关系如下:
用户=商家
表=商品
表空间=仓库
1. 1个商家能有很多商品，1个商品只能属于一个商家
2. 1个商品可以放到仓库A，也可以放到仓库B，但不能同时放入A和B
3. 仓库不属于任何商家
4. 商家都有一个默认的仓库，如果不指定具体仓库，商品则放到默认的仓库中
oracle中用户的所有数据都是存放在表空间中的，很多个用户可以共用一个表空间，也可以指定一个用户只用某一个表空间。
表空间：创建表空间会在物理磁盘上建立一个数据文件，作为数据库对象（用户、表、存储过程等等）的物理存储空间；
用户：创建用户必须为其指定表空间，如果没有显性指定默认表空间，则指定为users表空间；创建用户后，可以在用户上，创建表、存储过程等等其他数据库对象；
表：是数据记录的集合；
创建过程： 表空间--->用户--->表;
所属关系： 表空间 包含 用户 包含 表；
分页查询相关:(对原始的查询进行两层封装)
/*****普通的查询(30数据)****/ select * from ENVIRONMENT_APPLY_AUDIT; /*******查询带行号的数据**/ select ROWNUM ru,AAA.* from ( select * from ENVIRONMENT_APPLY_AUDIT ) AAA; /*******查询第一页 每页5条数据***/ select * from (select AAA.*,ROWNUM ru from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<6); select * from (select ROWNUM ru,AAA.* from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<6) where ru>0; /**********查询第二页数据********************/ select * from (select ROWNUM ru,AAA.* from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<11) where ru>5; /**********查询第三页数据********************/ select * from (select ROWNUM ru,AAA.* from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<16) where ru>10;
/********************oracle表分区相关*************/
1.概念
表空间：是一个或多个数据文件的集合，所有的数据对象都存在指定的表空间中。
分区表：逻辑上分区表仍然是一个完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，可以提高查询效率，不至于每次都扫描整张表。
查询oracle版本是否支持分区
select * from v$option where parameter = 'Partitioning';
结果为true 的时候表示当前oracle版本支持分区。如果不支持需要更换oracle版本为支持分区的版本。XE不支持，ORCL支持。
对现有表table_8进行分区有两种方式,第一种是创建与现有表一样结构的分区表table_9，然后将原来table_8表的数据插入到table_9，然后删掉表table_8，将table_9命名为table_8；第二种是使用oracle自带的线上重定义功能进行分区，重定义会将基础表table_8和table_9进行交换，包括索引、触发器、外键等也会交换,重定义之后table_8变为分区表，table_9变为普通表，table_9会保留table_8的原有的数据。(两种方式都需要确保基础表table_8和中间表table_9的表结构一致，包括索引、触发器等)
2.练习
====================从无到有创建表分区(散列分区)======================== 在列值上使用散列分区。当列的值没有指定条件时，建议使用散列分区。散列分区为同通过指定分区编号来均匀分布数据的一种分区类型。 1.创建两个表空间 create tablespace part1 datafile 'D:\oraclexe\tablespace\part1.dbf'size 1024M autoextend on next 500m force logging; create tablespace part2 datafile 'D:\oraclexe\tablespace\part2.dbf'size 1024M autoextend on next 500m force logging; 2.创建用户 create user sa identified by 111222 default tablespace part1; 3.授权 grant connect,resource,dba to sa; 4. 查看是否支持分区 select * from v$option where parameter = 'Partitioning'; 5.创建一个测试表 create table table_1(ID NUMBER PRIMARY KEY, name varchar2(200)); 插入数据 insert into table_1 values(1, 'name1'); insert into table_1 values(2, 'name1'); 创建分区表(哈希分区) CREATE TABLE table_2 ( ID NUMBER PRIMARY KEY, name VARCHAR2(200) ) partition BY hash ( ID ) ( partition part1 TABLESPACE part1, partition part2 TABLESPACE part2 ); 查看表是否已经分区： SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME = 'TABLE_2'; 查看表table_2的分区以及分区所在的表空间： select * from user_tab_partitions where table_name = 'TABLE_2' 测试将表1的数据插入表2： insert into table_2 select * from table_1 查看分区1的数据： select * from table_2 partition(part1) 结果： 2 name1 5 name1 6 name1 8 name1 查看分区2的数据： select * from table_2 partition(part2) 1 name1 3 name1 4 name1 7 name1 9 name1 10 name1 ==============在线重定义将普通表变为分区表========================= 测试能否对表进行在线重定义： EXEC SYS.dbms_redefinition.can_redef_table('SA', 'table_1'); // 参数是用户, 表名 创建中间分区表table_3: CREATE TABLE table_3 ( ID NUMBER PRIMARY KEY, name VARCHAR2(200) ) partition BY hash ( ID ) ( partition part1 TABLESPACE part1, partition part2 TABLESPACE part2 ); 开始重建： BEGIN SYS.dbms_redefinition.start_redef_table('SA', 'TABLE_1', 'TABLE_3'); END; 把中间表的内容和数据源表进行同步 EXECUTE SYS.dbms_redefinition.sync_interim_table('sa', 'TABLE_1', 'TABLE_3') 开始重定义之后，完成重定义之前，需要在中间表上创建与源表对应的索引、外键、触发器等，而中间表与源表对应的主键，如果需要按照主键重定义，需要在开始redefine之前创建。 结束在线重定义 EXEC SYS.dbms_redefinition.finish_redef_table('sa', 'TABLE_1', 'TABLE_3') 如果执行在线重定义的过程中出错： 可以在start_redef_table之后，finish_redef_table之前执行：EXEC SYS.dbms_redefinition.abort_redef_table('sa', 'TABLE_1', 'TABLE_3') 测试表table_1是否已经分区 SELECT * FROM user_tab_partitions WHERE table_name = 'TABLE_1' ==================================分区的其他操作=================================================== 分区创建： 1.再开始建表的时候就指定分区 2.有数据的表table_1变为分区表的思路： (1)建一个分区中间表table_2,表结构与表table_1一样，然后将表table_1的数据插入table_2,删除表table_1之后将table_2重命名为table_1 (2)在线重定义，也是用中间表进行分区 ===============================创建其他方式的分区================================ ======范围分区==== 1.基于ID进行划分 (1)小于10的一个分区，小于20的一个分区 CREATE TABLE table_4 ( ID NUMBER PRIMARY KEY, name VARCHAR2(200) ) partition BY range ( ID ) ( partition part41 VALUES less than (10) TABLESPACE part1, partition part42 VALUES less than (20) TABLESPACE part2 ); 插入20的时候会报错，未映射到分区：SQL 错误: ORA-14400: 插入的分区关键字未映射到任何分区。 解决办法：第二个分区建立的时候使用Maxvalue CREATE TABLE table_4 ( ID NUMBER PRIMARY KEY, name VARCHAR2(200) ) partition BY range ( ID ) ( partition part41 VALUES less than (10) TABLESPACE part1, partition part42 VALUES less than (maxvalue) TABLESPACE part2 ); 按日期进行分区也是上面的语句:只不过值改为日期比较-> partition part42 VALUES less than (to_date('01-apr-1998','dd-mon-yyyy')) TABLESPACE part2 ======列表分区(适用于一个列的值只有几个值)==== 1.创建列表分区 CREATE TABLE table_5 ( ID NUMBER PRIMARY KEY, name VARCHAR2(200) ) partition BY list ( name ) ( partition part51 VALUES ( 'zs' ) TABLESPACE part1, partition part52 VALUES ( 'lisi', 'ww', 'zl', 'tq' ) TABLESPACE part2 ); 如果插入的值不在上面分区允许的范围之内也是报错：SQL 错误: ORA-14400: 插入的分区关键字未映射到任何分区。 ======组合范围散列分区==== 基于范围分区和散列分区，表首先按照某列进行范围分区，然后按照某列进行列表分区，分区之中的分区称为子分区 CREATE TABLE table_6 ( ID NUMBER PRIMARY KEY, name VARCHAR2(200) ) partition BY range ( ID ) subpartition BY list ( name ) ( partition part61 VALUES less than (10) TABLESPACE part1 ( subpartition part61sub1 VALUES ( 'zs' ) TABLESPACE part1, subpartition part61sub2 VALUES ( 'lisi' ) TABLESPACE part2 ) , partition part62 VALUES less than (maxvalue) TABLESPACE part2 ( subpartition part62sub1 VALUES ( 'zs' ) TABLESPACE part1, subpartition part62sub2 VALUES ( 'lisi' ) TABLESPACE part2 ) ); ==================================分区查询=========================== 1.查看当前用户可以访问的所有分区表的信息 select * from all_part_TABLES; 2.显示当前用户所有分区表的信息： select * from user_part_tables; 3.查看所有的组合分区表的子分区信息： select * from user_tab_subpartitions ============================带索引的表的重建(在线重定义分区)================== 1.创建带索引的表8 CREATE TABLE table_8 ( ID NUMBER PRIMARY KEY, name VARCHAR2(200) ); 创建索引： create index t8_name_index on table_8(name); 插入数据： insert into table_8 select * from table_1; 2.查看是否支持在线重定义 EXEC SYS.dbms_redefinition.can_redef_table('SA', 'table_8'); 3.创建与表8结构一样的分区表9 CREATE TABLE table_9 ( ID NUMBER PRIMARY KEY, name VARCHAR2(200) ) partition BY hash ( ID ) ( partition part1 TABLESPACE part1, partition part2 TABLESPACE part2 ); 4.开始重定义 BEGIN SYS.dbms_redefinition.start_redef_table('SA', 'TABLE_8', 'TABLE_9'); END; 5.进行数据同步 EXECUTE SYS.dbms_redefinition.sync_interim_table('sa', 'TABLE_8', 'TABLE_9') 6.结束在线重定义 EXEC SYS.dbms_redefinition.finish_redef_table('sa', 'TABLE_8', 'TABLE_9') 7.查看表8是否已经分区 SELECT * FROM user_tab_partitions WHERE table_name = 'TABLE_8' 8.查看表8中每个分区的数据： select * from table_8 partition(part1) select * from table_8 partition(part2)
/**************Oracle 事务相关********************/
DDL(数据定义语言) - Create、Alter、Drop 这些语句自动提交，无需用Commit提交。
DQL（数据查询语言）- Select查询语句不存在提交问题。
DML(数据操纵语言) - Insert、Update、Delete 这些语句需要Commit才能提交。
DTL(事务控制语言) - Commit、Rollback 事务提交与回滚语句。
DCL(数据控制语言) - Grant、Revoke 授予权限与回收权限语句。
执行完DML语句，若没有commit再执行DDL语句，也会自动commit未被commit的数据。
如果打开自动提交，DML操作后也不需要手动提交：SET AUTOCOMMIT ON;
补充：Oracle如果字段有默认值，在插入或者修改的时候设为null的时候默认值不会生效。
补充：注意not in里包含的子查询是否包含null值。另外记住 null = 2 或者 null != 2 都是false，不满足匹配的条件。
1.使用in做条件时时始终查不到目标列包含NULL值的行，比如：
select * from t_in where id in (1,3,null); 等价于id = 1 or id = 3 or id = null，id = NULL的结果是UNKNOWN，也相当于FALSE。(判断为NULL只能用is)
2.如果not in条件中包含null值，则不会返回任何结果，包含in中含有子查询。比如：
（1）select * from t_in where id not in (1,3,null); 等价于id!=1 and id!=3 and id!=null。(NULL与其他值做=或!=比较结果都是UNKNOWN，所以整个条件就相当于FALSE的，最终没有查出数据。)
（2）select * from t_in where id not in (1,3); 上面查询的where条件等价于id != 1 and id !=3，即使t_in表中有一行为null，它虽然满足!=1和!=3但根据上面的规则，NULL与其他值做=或!=比较结果都是UNKNOWN，所以NULL也不会出现在结果。也就是NOT IN 不能查出为null的元素。
参考： https://www.cnblogs.com/DreamDrive/p/7608815.html
