Oracle概念
基本概念
数据库
Oracle 数据库是数据的物理存储。这就包括（数据文件 ORA 或者 DBF、控制文件、联机日
志、参数文件）。其实 Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统
只有一个库。可以看作是 Oracle 就只有一个大数据库。
实例
一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构
（Memory Structures)组成。一个数据库可以有 n 个实例。
用户
用户是在实例下建立的。不同实例可以建相同名字的用户。
表空间
表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数
据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每
个数据库至少有一个表空间(称之为 system 表空间)。
每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件
只能属于一个表空间。
数据库---表空间 一个表空间可以与多个数据文件（物理结构）关联一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表
数据文件（dbf、ora）
数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个
或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于
一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数
据文件，只能删除其所属于的表空间才行。
注： 表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到
一个或者多个数据文件中。
由于 oracle 的数据库不是普通的概念，oracle 是有用户和表空间对数据进行管理和存放的。
但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同
一个名字的表！这里区分就是用户了！
SERVICE_NAMES，INSTANCE_NAME，SID
SID即是INSTANCE_NAME，SERVICE_NAMES主要用在监听器中
1. Oracle的服务名(ServiceName)查询
show parameter service_name;
2. Oracle的SID查询命令：
select instance_name from v$instance;
3. 查看Oracle版本
select version from v$instance
数据库名
SELECT * FROM V$DATABASE;
SELECT t.NAME FROM V$DATABASE t;
实例名
SELECT * FROM V$INSTANCE;
SELECT t.INSTANCE_NAME FROM V$INSTANCE t;
SID
select * from v$thread;
select t.INSTANCE from v$thread t;
域名
select * from v$parameter t where t.name = 'db_domain';
select t.VALUE from v$parameter t where t.name = 'db_domain';
服务名
select * from v$parameter t where t.name = 'service_names';
select t.VALUE from v$parameter t where t.name = 'service_names';
架构
Oracle数据库可以创建多个实例，每个实例可以创建多个表空间，每个表空间下可以创建多个用户（同时用户也属于表空间对应的实例）和数据库文件，用户可以创建多个表（每个表随机存储在一个或多个数据库文件中），如下图：
实例下有用户和表空间，用户授权访问表空间，是管理的，用户经授权在表空间中创建表，随机存储到不同的文件中。如下图所示
一、数据库名
1.数据库名
在oracle中，数据库名是一个数据库的标识，使用参数“DB_NAME”表示，数据库名是在安装数据库时指定的，存储在“dbhome_1/dbs/init.ora”中；数据库名常使用于：安装数据库、创建新的数据库、创建数据库控制文件
格式如下：
DB_NAME=myorcl
...
在 创建数据库时就应考虑好数据库名，并且在创建完数据库之后，数据库名不宜修改，即使要修改也会很麻烦。因为，数据库名还被写入控制文件中，控制文件是以二 进制形式存储的，用户无法修改控制文件的内容。假设用户修改了参数文件中的数据库名，即修改DB_NAME的值。但是在Oracle启动时，由于参数文件 中的DB_NAME与控制文件中的数据库名不一致，导致数据库启动失败，将返回ORA-01103错误。
DB_NAME和instance_name在\oracle\admin\szcg\pfile\下的init.ora文件中，其中szcg是数据库名
2.查询当前数据名
方法一:select name from v$database;
方法二：show parameter db
方法三：查看参数文件。
select global_name from global_name;
3.修改数据库名
建议应在创建数据库时就确定好数据库名，数据库名不应作修改，因为修改数据库名是一件比较复杂的事情。那么现在就来说明一下，如何在已创建数据之后，修改数据库名。步骤如下：
1.关闭数据库。
2.修改数据库参数文件中的DB_NAME参数的值为新的数据库名。
3.以NOMOUNT方式启动实例，修建控制文件(有关创建控制文件的命令语法，请参考oracle文档)
二、数据库实例名
1.数据库实例名
数据库实例名是用于和操作系统进行联系的标识，就是说数据库和操作系统之间的交互用的是数据库实例名。实例名也被写入参数文件中，该参数为instance_name，在winnt平台中，实例名同时也被写入注册表。
数据库名和实例名可以相同也可以不同。
在一般情况下，数据库名和实例名是一对一的关系，但如果在oracle并行服务器架构(即oracle实时应用集群)中，数据库名和实例名是一对多的关系。这一点在第一篇中已有图例说明。
2.查询当前数据库实例名
方法一：select instance_name from v$instance;
方法二：show parameter instance
方法三：在参数文件中查询。
Unix下可以这样看：
$ ps -ef|grep ora_
进程的最后几个字母就是sid
在oracle的安装目录下,如D:/oracle/product/10.2.0/db_1/dbs,看文件名如SPFILEORCL.ORA,那么就是orcl,也就是说文件名字是 "SPFILE**.ORA ",那种的 " "就是SID
D:/oracle/product/10.2.0/oradata/SID文件夹...
或D:/oracle/product/10.2.0/db_1/dbs/SPFILESID名称.ORA
三、数据库域名
分布式数据库系统中，不同版本的数据库服务器之间，不论运行的操作系统是unix或是windows,各服务器之间都可以通过数据库链路进行远程复制。
数据库域名主要用于oracle分布式环境中的复制。数据库域名被写入参数文件中，该参数为db_domain。
在分布式数据库系统中，不同版本的数据库服务器之间，不论运行的操作系统是unix或是windows，各服务器之间都可以通过数据库链路进行远程复制，数据库域名主要用于oracle分布式环境中的复制。举例说明如：
全国交通运政系统的分布式数据库，其中：
福建节点： fj.jtyz
福建厦门节点： xm.fj.jtyz
江西： jx.jtyz
江西上饶：sr.jx.jtyz
这就是数据库域名。
数据库域名在存在于参数文件中，他的参数是db_domain.
2.查询数据库域名
方法一：select * from v$parameter where name = 'db_domain';
方法二：show parameter domain
方法三：在参数文件中查询。
全局数据库名
全局数据库名=数据库名+数据库域名，如前述福建节点的全局数据库名是：oradb.fj.jtyz
四、数据库服务名
1、数据库服务名？
从oracle9i版本开始，引入了一个新的参数，即数据库服务名。参数名是SERVICE_NAME。
如果数据库有域名，则数据库服务名就是全局数据库名；否则，数据库服务名与数据库名相同。
2. 查询数据库服务名
show parameter service_name
select * from v$parameter t where t.name = 'service_names';
select t.VALUE from v$parameter t where t.name = 'service_names';
数据库服务名与网络连接
从oracle8i开始的oracle网络组件，数据库与客户端的连接主机串使用数据库服务名。之前用的是ORACLE_SID,即数据库实例名。
服务端默认的端口号一般是389，客户端默认的端口号一般都是1521
五、概念区别
1.Service_name服务名 和Sid的区别
SERVICE_NAME指的是listener中的全局数据库名：这个名字是由listener.ora中GLOBAL_DBNAME参数决定的。
SERVICE_NAME是Oracle8i新引进的，8i之前一个数据库只能有一个实例。8i之后一个数据库可以对应多个实例，例如RAC。为了充分利用所有实例，并且令客户端连接配置简单，ORACLE提出了SERVICE_NAME的概念。该参数直接对应数据库，而不是某个实例。
如果数据库有域名，则 数据库服务名=全局数据库名=数据库名【.】数据库域名
数据库服务名与数据库名相同。
在8i以前，使用SID来表示标识数据库的一个实例，但是在Oracle的并行环境中，一个数据库对应多个实例，这样就需要多个网络服务名，设置繁琐。为了方便并行环境中的设置，引进了Service_name参数，该参数对应一个数据库，而不是一个实例，而且该参数有许多其它的好处。
该参数的缺省值为Db_name. Db_domain，即等于Global_name。一个数据库可以对应多个Service_name，以便实现更灵活的配置。该参数与SID没有直接关系，即不必Service name 必须与SID一样。Sid是数据库实例的名字，每个实例各不相同。
SID:一个数据库可以有多个实例（如RAC），SID是用来标识这个数据库内部每个实例的名字，就好像一个部门里，每个人都有一个自己的名字。
SERVICE_NAME：是这个数据库对外宣称的名字，外面的人要想连接我这个数据库，你就在客户端的连接串里写上service_name。
一句话来说就是：SID是对内的，是实例级别的一个名字，用来内部之间称呼用。SERVICE_name是对外的，是数据库级别的一个名字，用来告诉外面的人，我数据库叫"SERVICE_NAME"。
2.数据库实例名instance_name与ORACLE_SID
虽 然两者都表是oracle实例，但两者是有区别的。instance_name是oracle数据库参数。而ORACLE_SID是操作系统的环境变量。 ORACLD_SID用于与操作系统交互，也就是说，从操作系统的角度访问实例名，必须通过ORACLE_SID。在winnt不 台，ORACLE_SID还需存在于注册表中。
且ORACLE_SID必须与instance_name的值一致，否则，你将会收到一个错误，在unix平台，是“ORACLE not available”,在winnt平台，是“TNS:协议适配器错误”。
数据库实例名与网络连接
数据库实例名除了与操作系统交互外，还用于网络连接的oracle服务器标识。当你配置oracle主机连接串的时候，就需要指定实例名。当然8i以后版本的网络组件要求使用的是服务名SERVICE_NAME。这个概念接下来说明。
Oracle中服务名和sid区别
在Oracle数据库中，服务名和SID是两个不同的概念，它们用于标识数据库实例。
SID（System ID）是每个Oracle实例的唯一标识符，它是由DBA在安装Oracle实例时指定的名称，通常称为实例名。在连接Oracle数据库时，需要指定SID来告诉数据库连接器想连接哪个数据库实例。SID是Oracle的一个传统的、基于网络的方式来标识一个数据库实例，被广泛使用。
服务名（Service Name）是对Oracle数据库实例进行逻辑分组的一种方式。它是一个逻辑名称，类似于域名，可以在Oracle Net服务名注册表中配置，提供多个程序连接同一个数据库实例的不同路径或别名。一个数据库实例可以有多个服务名，每个服务名可以定义不同的连接属性。
在实际应用中，服务名比SID更加灵活，同时也更容易管理。因为一个数据库实例可以有多个服务名，这些服务名可以用于不同的应用程序或者是在不同的网络通讯中使用，这样可以方便的进行管理和维护。同时，使用服务名进行连接时，也可以避免一些SID在网络中的冲突问题。
总之，服务名和SID是Oracle数据库中两个不同概念的标识符。SID是一个实例级别的标识符，服务名是一个逻辑级别的标识符。这两个标识符各有优缺点，可以根据不同的需要选择使用。
Oracle中实例名instance_name和sid区别
在Oracle数据库中，“实例名(instance name)”和“SID(system identifier)”是两个不同概念。 实例名是一个对Oracle数据库实例进行逻辑分组的逻辑名称，即在创建实例时可以通过DB_NAME参数指定的名称。一个服务器上可能会有多个Oracle数据库实例，而每个实例应该具有唯一的实例名。 SID是一个Oracle数据库实例的唯一标识符，与实例名不同，它由Oracle软件在安装时自动生成，一般是DB_NAME + DB_UNIQUE_NAME + DB_DOMAIN这些参数的组合。SID用于标识一个Oracle实例，通常用于客户端连接时指定实例名。 可以认为实例名是对实例的一种高层次抽象，是应用程序连接Oracle实例时的一个逻辑名称，而SID则是Oracle系统内部使用的实例的唯一标识。 总之，实例名和SID在Oracle数据库中都是用来标识Oracle实例的概念，不同的是实例名是应用程序连接Oracle实例时的逻辑名称，而SID是Oracle内部使用的实例标识符。
用户与模式
数据库中的用户，其实就是一个认证登录的东西，用来连接和访问数据库的。
数据库模式是逻辑数据结构（并非我们上面说的逻辑存储结构哈）或模式对象的集合。 数据库模式由一个数据库用户拥有，并与用户名具有相同的名称。
其实就是，我们在创建用户的时候，Oracle会自动创建一个与用户名相同的数据库模式，然后，该用户下所有的对象（也称为模式对象，如表、序列、视图、同义词、存储过程等等等等），都是归属到这个数据库模式。
模式跟用户在oracle 是一对一的关系
简单点可以说，模式是对象的集合，对象（表、存储过程、视图等等）是模式的元素
schema里面包含了各种对象如tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links。
一个用户一般对应一个schema,该用户的schema名等于用户名，并作为该用户缺省schema。这也就是我们在企业管理器的方案下看 到schema名都为数据库用户名的原因。而Oracle数据库中不能新创建一个schema，要想创建一个schema，只能通过创建一个用户的方法解决 (Oracle中虽然有create schema语句，但是它并不是用来创建一个schema的)，在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省 shcema。
即schema的个数同user的个数相同，而且schema名字同user名字一一 对应并且相同，所有我们可以称schema为user的别名，虽然这样说并不准确，但是更容易理解一些。
一个用户有一个缺省的schema，其schema名就等于用户名，当然一个用户还可以使用其他的schema。如果我们访问一个表时，没有指明该 表属于哪一个schema中的，系统就会自动给我们在表上加上缺省的sheman名。比如我们在访问数据库时，访问scott用户下的emp表，通过 select * from emp; 其实，这sql语句的完整写法为select * from scott.emp。
在数据库中一个对象的完整名称为schema.object，而不属user.object。类似如果我们在创建对象时不指定该对象 的schema，在该对象的schema为用户的缺省schema。这就像一个用户有一个缺省的表空间，但是该用户还可以使用其他的表空间，如果我们在创 建对象时不指定表空间，则对象存储在缺省表空间中，要想让对象存储在其他表空间中，我们需要在创建对象时指定该对象的表空间。
jdbc连接Oracle
格式一: Oracle JDBC Thin using a ServiceName:
jdbc:oracle:thin:@//<host>:<port>/<service_name>
Example: jdbc:oracle:thin:@//192.168.2.1:1521/XE
注意这里的格式，@后面有//, 这是与使用SID的主要区别。
这种格式是Oracle 推荐的格式，因为对于集群来说，每个节点的SID 是不一样的，
但是SERVICE_NAME 确可以包含所有节点。
格式二: Oracle JDBC Thin using an SID:
jdbc:oracle:thin:@<host>:<port>:<SID>
Example: jdbc:oracle:thin:@192.168.2.1:1521:X01A
Note:
Support for SID is being phased out. Oracle recommends that users switch over to usingservice names.
格式三：Oracle JDBC Thin using a TNSName:
jdbc:oracle:thin:@<TNSName>
Example: jdbc:oracle:thin:@GL
Note:
Support for TNSNames was added in the driver release 10.2.0.1``
用户
sys和system用户区别
- 最重要的区别，存储的数据的重要性不同
【sys】所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle的运行是至关重要的，由数据库自己维护，任何用户都不能手动更改。sys用户拥有dba，sysdba，sysoper等角色或权限，是oracle权限最高的用户。
【system】用户用于存放次一级的内部数据，如oracle的一些特性或工具的管理信息。system用户拥有普通dba角色权限。
- 权限的不同
【system】用户只能用normal身份登陆em，除非你对它授予了sysdba的系统权限或者syspoer系统权限。
【sys】用户具有“SYSDBA”或者“SYSOPER”系统权限，登陆em也只能用这两个身份，不能用normal。
以sys用户登陆Oracle，执行select * from V_$PWFILE_USERS;可查询到具有sysdba权限的用户，如：
SQL> select fromV_$PWFILE_USERS;
USERNAME SYSDBA SYSOPER
SYS TRUE TRUE
权限
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
-- 授权
grant dba to shixf;
grant create view to shixf;
grant select any table to shixf;
grant select any dictionary to shixf;
--or
grant resource, connect to shixf;
- normal 、sysdba、 sysoper有什么区别
normal 是普通用户
sysdba拥有最高的系统权限，登陆后用户是sys
sysoper主要用来启动、关闭数据库，sysoper 登陆后用户是public
sysdba和sysoper属于systemprivilege，也称为administrative privilege，拥有例如数据库开启关闭之类一些系统管理级别的权限sysdba和sysoper具体的权限可以看下表：
system如果正常登录，它其实就是一个普通的dba用户，但是如果以as sysdba登录，其结果实际上它是作为sys用户登录的，这一点类似Linux里面的sudo的感觉，从登录信息里面我们可以看出来。因此在as sysdba连接数据库后，创建的对象实际上都是生成在sys中的。其他用户也是一样，如果 as sysdba登录，也是作为sys用户登录的
-- 收回权限
revoke read,write on directory dir from student;
基础语法概念
基础语句
-- 创建表空间
create tablespace shixf
datafile 'D:\data.dbf'
size 100m
autoextend on
next 10m
/**
数据库---表空间 一个表空间可以与多个数据文件（物理结构）关联一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表
*/
-- 创建用户
create user shixf identified by shixf default tablespace shixf
grant resource,connect,dba to shixf;
DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。
-- 修改密码
alter user 用户名 identified by 新密码；
-- 锁定用户
alter user shixf account lock;
-- 解说用户
alter user shixf account unlock;
-- 创建表
/**
创建表格时，如果不指定所用的表空间，按照以下顺序使用用户的默认表空间。
1）创建用户时指定的默认表空间。
create user test identified by oracle default tablespace users;
2）创建数据库是指定的默认表空间。可以通过以下语句修改：
alter database default tablespace users;
如果创建用户时，不指定默认的tablespace，
则此用户则会以数据库的默认表空间作为默认表空间。
Oracle创建一个表 如果不指定表空间 默认是当前用户所属的表空间
如果用户没有指定default tablespace
默认是users
*/
create table Student(
StuId NUMBER NOT NULL, --学生ID
StuName VARCHAR2(10) NOT NULL, --名称
Gender VARCHAR2(10)NOT NULL, -- 性别
Age NUMBER(2) NOT NULL, -- 年龄
JoinDate DATE NULL, --入学时间
ClassId NUMBER NOT NULL, --班级ID
Address VARCHAR2(50) NULL --家庭住址
);
-- 添加约束
-- 主键约束
alter table student add constraints pk_student primary key (sno);
-- 唯一约束
alter table student add constraints uk_student_email unique (email);
-- 检查约束
alter table student add constraints ck_student_age check (age between 18 and 30);
alter table student add constraints ck_student_gender check (gender in ('男','女'));
-- 外键约束
alter table student add constraints fk_student_cno foreign key (cno) references clazz (cno);
--创建默认约束
alter table Student add constraint DF_Student_Address default('地址不详');
alter table Student Modify Address varchar(50) default '地址不详';
alter table Student Modify JoinDate Date default sysdate;
DDL
-- 添加列
ALTER TABLE user ADD(phone_number VARCHAR2(20));
-- 修改列名和字段类型
ALTER TABLE user RENAME CLUMN name TO first_name;
ALTER TABLE user MODIFY first_name CHAR(25);
--删除列
ALTER TABLE user DROP(phone_number);
-- 重命名表
rename OLD_TABLE_NAME TO NEW_TABLE_NAME;
--创建视图
CREATE [OR REPLACE] view [(alias[,...])]
AS
Subquery;
--如果不存在视图则创建，如果存在则修改
CREATE OR REPLACE newview (id,name,gender)
AS
SELECT user_id,first_name,gender FROM user;
--删除视图
DROP VIEW view_name;
-- 删除一个表的约束名称
alter table 表 drop constraint 约束名称
--删除一个用户 --cascade级联
drop user 用户名 cascade;
--打开数据库
alter database open;
-- 序列。自增
CREATE SEQUENCE seqpersonid;
序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：
nextval :取得序列的下一个内容
currval :取得序列的当前内容
select seqpersonid.nextval from dual;
select seqpersonid.currval from dual;
在插入数据时需要自增的主键中可以这样使用
insert into person values(seqpersionid.nextval,'zhangsan','男');
-- 创建索引
create index pname_gender_index on person(name, gender);
-- Oracle对primary key和unique约束的列, 会自动创建索引.
-- 删除索引
drop index idx_sname;
触发器
触发器可用于
-
数据确认
-
实施复杂的安全性检查
-
做审计，跟踪表上所做的数据操作等
-
数据的备份和同步
触发器的类型
- 语句级触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响
了多少行 。 - 行级触发器（FOR EACH ROW） ：触发语句作用的每一条记录都被触发。在行级触
发器中使用 old 和 new 伪记录变量, 识别值的状态。
- 语句级触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响
每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。
--语法
CREATE [or REPLACE] TRIGGER 触发器名
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF 列名]}
ON 表名
[FOR EACH ROW [WHEN(条件) ] ]
begin
PLSQL 块
End 触发器名
--例
DELIMITER //
DROP TRIGGER IF EXISTS classinfo_tri//
CREATE TRIGGER classinfo_tri
AFTER INSERT ON classinfo
FOR EACH ROW
BEGIN
insert into studentinfo values(0004,'王五'，‘1’，0001，‘18661617890’);
END//
DELIMITER ;
函数
create or replace function 函数名(Name in type, Name in type, ...) return 数据类型 is
结果变量 数据类型;
begin
return(结果变量);
end 函数名;
-- 例
create or replace function empincome(eno in emp.empno%type) return
number is
psal emp.sal%type;
pcomm emp.comm%type;
begin
select t.sal into psal from emp t where t.empno = eno;
return psal * 12 + nvl(pcomm, 0);
end;
存储过程
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
AS
begin
PLSQL 子程序体；
End;
-- or
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
is
begin
PLSQL 子程序体；
End 过程名;
--例：
create or replace procedure helloworld is
begin
dbms_output.put_line('helloworld');
end helloworld;
函数和存储过程区别
一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。
但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以利用 out 参数，在过程和函数中实
现返回多个值
分页和数据格式转换
-- 分页
select * from (select rownum rm, a.* from (select * from emp) a where rownum < 11) b where b.rm > 5;
-- or
select * from (select rownum r ,emp.* from emp) b
where b.r >5 and b.r <11
--数据格式转化
SELECT to_date('2018-05-25','yyyy-mm-dd'); 25-5月-18
SELECT to_char(sysdate,'yyyy-mm-dd'); --2018-05-25
SELECT to_number('$123.45','$9999.99'); --123.45
常用函数
--to_char()——日期类型转字符串
select to_char(sysdate, ‘YYYY-MM-DD’) from dual;
--2008-04-04 01:10:21
select to_char(sysdate,'YYYY-MM-DD hh:mm:ss') from dual;
--to_date()——字符串转为日期类型
select * from emp where hiredate > to_date(‘1981-03-02’, ‘YYYY-MM-DD’);
--to_number()——字符串转数字
select * from emp where sal > to_number(‘$1,200.00’, ‘$9,999.99’);
--nvl()——空值处理
--第一参数：字段名或表达式，如果这个参数值为null，就返回第二参数值，否则返回第一参数值。
select ename, sal*12+nvl(comm, 0) from emp;
常用查询
-- 查看段
select * from dba_segments
select * from dba_segments where segment_name='"student"';
-- 查看区
select * from dba_extents
select * from dba_extents where segment_name='table_name';
-- 查看表空间
select * from dba_tablespaces;
select * from v$tablespace;
-- 查询表属于哪个表空间
select tablespace_name,table_name from user_tables where table_name='student';
--查看用户的默认表空间
select username,default_tablespace from dba_users;
--查找当前用户下表的默认表空间
select username,default_tablespace from user_users;
-- 查看用户下面的所有的表
select * from user_tables;
-- 查看当前登录用户
select * from user_users;
select user from dual
-- 查看所有用户
select * from all_users;
-- 查看当前用户的角色信息
select * from user_role_privs;
--查看当前用户拥有的角色权限信息
select * from role_sys_privs;
-- 查看当前用户的系统权限
select * from user_sys_privs;
-- 查看当前用户的表级权限
select * from user_tab_privs;
--获取有哪些用户在使用数据库
select username from v$session;
-- 查看建表语句
SELECT DBMS_METADATA.GET_DDL('TABLE','student')FROM DUAL;
--
select * from user_source
select * from v$Session
-- 查看当前用户权限
select * from session_privs
-- 给用户授予一张表的权限
grant select on 表名 to 用户名;
-- 给用户授予表的所有权限
grant all on 表名 to 用户名;
-- server字符集
SELECT * FROM nls_database_parameters
-- client字符集
SELECT * FROM nls_instance_parameters
-- 回话字符集
SELECT * FROM nls_session_parameters
-- 查询当前数据库名
select name from v$database;
show parameter db
-- 查询当前实例名(sid)
select instance_name from v$instance;
show parameter instance
-- 查询版本
select version from v$instance
select * from v$version
--查询Oracle数据库字符集
select userenv('language') from dual
--1.查看所有用户涉及到的表(权限)
select * from dba_users;
select * from all_users;
select * from user_users;
--2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
--3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
select * from role_sys_privs;
select * from dba_role_privs;--查看整个oracle系统的权限角色列表
select * from user_role_privs;--查看当前登录用户自己的权限、角色列表
-- 查询进程数
select count(*) from v$session
-- 查询并发进程
select count(*) from v$session where state='ACTIVE';
-- 显示进程(命令行窗口)
show parameter processes;
-- 普通查询
select * from student where 1=1;
-- 表
grant select on student to test;
--1、查询一个表有多少列
select count(*) from user_tab_columns where table_name=('student') ;
--2、根据列明查找表名，可以查到表、视图、索引(ID为你要查询的字段)
select distinct table_name from user_tab_columns t
where t.column_name like '%ID%' ;
--3、获取表字段
select * from user_tab_columns where Table_Name='student'
order by column_name ;
--4.查询所有的字段名(列名)以**列**的形式输出
select column_name from user_tab_cols where table_name='student';
--5.查询所有的字段名(列名)以**行**的形式输出
select wm_concat(column_name) from user_tab_cols where table_name='student';
-- 查看表创建时间
select object_name,created from user_objects where object_name='student';
DBA_TABLES >= ALL_TABLES >= USER_TABLES
DBA_TABLES：DBA拥有的或者可以访问的所有关系表
ALL_TABLES：某一用户所拥有的或者可以访问的所有关系表
USER_TABLES：某一用户所拥有的所有关系表
由上可知，当某一用户本身就为数据库 DBA 时，DBA_TABLES 与 ALL_TABLES 等价。
-- 查看约束条件
select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = 'student';
select * from user_constraints where table_name= 'student';
-- 查看函数和存储过程状态
select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';
============================
--查询当前日期
select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;
/**
随机UUID
SYS_GUID 以16位RAW类型值形式返回一个全局唯一的标识符
hextoraw()：十六进制字符串转换为raw；
rawtohex()：将raw串转换为十六进制；
*/
select sys_guid(),rawtohex(sys_guid()) from dual;
序列
序列是Oracle中特有的对象, 用于生成一个自动递增的数列. 通常被用来作为主键的值.
1.1 创建序列
a) 语法
create sequence seq_name
[increment by n
start with n
maxvalue n|nomaxvalue // 10^27 or -1
minvalue n|no minvalue
cycle|nocycle
cache n|nocache]
Ø increment by, 代表每次增长的步长, 默认是1, 可以是负数, 表示每次递减;
Ø start with, 从哪个值开始, 默认是1;
Ø maxvalue, 序列能到达的最大值, 默认值是nomaxvalue, 此时正数最大值是10^27, 负数最大值是-1;
Ø minvalue, 序列能到达的最小值, 默认值是nominvalue, 此时正数的最小值是1, 负数的最小值是-10^26;
Ø cycle|nocycle, 表示是否循环. 如果是cycle, 达到最大值是会重新从头开始, 如果是nocycle, 最大值后会报错.
Ø cache n|nocache, 表示高速缓存, 可以优化序列, 缓存的默认值是20. nocache表示没有缓存.
b) 创建学生序列
create sequence seq_student;
1.2 序列的使用
1.2.1 nextval(序列的下一个值)
查看序列的下一个值
select seq_student.nextval from dual;
1.2.2 currval(序列的当前值)
查看序列的当前值
select seq_student.currval from dual;
1.2.3 在插入数据时使用序列
insert into student values (seq_student.nextval, '小红', '女', 19, sysdate, '[email protected]', 102);
1.3 删除序列
drop sequence seq_student;
常用sql
1 查看阻塞和锁
SELECT
T3.Sid,
T3.Serial #,
T1.Object_Name,
T2.Locked_Mode,
T3.Username,
T3.Status,
T3.Machine,
T3.Sql_Id,
T2.Os_User_Name
FROM
Dba_Objects T1,
V$locked_Object T2,
V$session T3
WHERE
T1.Object_Id = T2.Object_Id
AND T2.Session_Id = T3.Sid;
ALTER System Kill SESSION '1583,51746';
2 查看DDL锁定
select * FROM dba_ddl_locks;
3 查看磁盘读最多的SQL
SELECT
*
FROM
( SELECT ROWNUM rn, t.* FROM v$sql t ORDER BY t.disk_reads DESC )
WHERE
rn < 10;
4 查看逻辑读取最多的SQL
SELECT
*
FROM
( SELECT ROWNUM rn, t.* FROM v$sql t ORDER BY t.BUFFER_GETS DESC )
WHERE
rn < 10;
5 查看耗费CPU资源最多的SQL
SELECT
*
FROM
( SELECT ROWNUM rn, t.* FROM v$sql t ORDER BY t.CPU_TIME DESC )
WHERE
rn < 10;
6 显示哪些对象被哪些会话锁住
/*showlock.sql*/ COLUMN o_name FOR a10 COLUMN lock_type FOR a20 COLUMN object_name FOR a15 SELECT
rpad( t1.oracle_username, 10 ) o_name,
t1.session_id sid,
decode(
t1.locked_mode,
0,
'None',
1,
'Null',
2,
'Row Share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive'
) lock_type,
t2.object_name,
t1.xidusn,
t1.xidslot,
t1.xidsqn
FROM
v$locked_object t1
JOIN all_objects t2 ON t1.object_id = t2.object_id;
7 显示当前所有TM和TX锁信息
/*showalllock.sql*/ COLUMN request FOR 9999 SELECT
t1.sid,
t1.TYPE,
t1.id1,
t1.id2,
decode(
t1.lmode,
0,
'None',
1,
'Null',
2,
'Row Share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive'
) lock_type,
t1.request,
t1.ctime,
t1.block
FROM
v$lock t1
WHERE
t1.TYPE IN ( 'TX', 'TM' );
8 与上面的相同，加上排序
/*showlockorder.sql*/
COLUMN SID format 99999 COLUMN RESOURCE_ format a15 COLUMN request format a15 SELECT
t1.TYPE || '-' || t1.id1 || '-' || t1.id2 resource_,
t1.sid,
decode(
t1.lmode,
0,
'None',
1,
'Null',
2,
'Row Share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive'
) lock_type,
decode(
t1.request,
0,
'None',
1,
'Null',
2,
'Row Share',
3,
'Row Exclusive',
4,
'Share',
5,
'Share Row Exclusive',
6,
'Exclusive'
) request,
t1.ctime,
t1.block
FROM
v$lock t1
WHERE
t1.TYPE IN ( 'TX', 'TM' )
ORDER BY
resource_,
t1.ctime DESC;
9 筛选系统CPU高消耗的SQL语句
SELECT
*
FROM
(
SELECT
t1.sql_id,
to_char( t2.begin_interval_time, 'yyyy-mm-dd hh24' ) dt,
SUM( t1.cpu_time_total ) / 1000000 / 60 cpu_time,
SUM( t1.executions_delta ) executes
FROM
dba_hist_sqlstat t1
JOIN dba_hist_snapshot t2 ON t1.snap_id = t2.snap_id
AND t1.instance_number = t2.instance_number
AND t1.instance_number = 1
AND to_char( t2.begin_interval_time, 'yyyy-mm-dd hh24' ) >= '2018-06-05 08'
GROUP BY
t1.sql_id,
to_char( t2.begin_interval_time, 'yyyy-mm-dd hh24' )
ORDER BY
3 DESC
)
WHERE
ROWNUM < 11;
SELECT
*
FROM
v$sql t
WHERE
t.SQL_ID = '6gvch1xu9ca3g';