其实Oracle的管理没传说中那么难，刚到地税局搞运维，我基本就不会Oracle，仅仅熟悉MysqL，这东西听听课，只要笔记做得好，根本不用往脑子里记，用的时候翻出来照着颜色往上敲，就这么简单，又不是搞开发，大家真别被这破东西吓住了，不信往下看，绝对看一遍半小时全部掌握，不用记，用的时候看颜色往上敲就行了。本人blog全部位原创，转载请注明源地址。

用户管理

oracle安装会自动胜场sys用户和system用户

（1）sys用户是超级用户，具有最高权限，具有sysdba角色，有create database权限，该用户默认密码manager

（2）system用户是管理操作员，权限也很大，具有sysoper角色，没有create database权限，该用户默认密码是change_on_install

（3）一般讲，对数据库维护，使用system用户登录就可以了。

oracle的数据对象：表、视图、触发器、存储过程、同义词

启动数据库分两步：1、启动实例 2、启动监听，不启动监听无法使用jdbc进行连接

权限：

1、系统权限：用户对数据库的相关权限

2、对象权限：用户对其他用户的数据对象操作的权限，如：select insert update delete create all 等

角色：对各个权限叠加形成的一个具有某种权限集合的功能

1、预定义角色

2、自定义角色

如conn角色，具有给新用户授予登陆的角色

dba角色 最大权限

resource 让某用户在任何一个表空间建表

格式：

linesize 行

pagesize 页

赋予权限：grant某权限on某表to某个用户

收回权限：revoke某权限from某个用户

对象权限的传递：to某个用户with grant option

系统权限的传递：with admin option

？如果某个人的权限被回收，那么传递的用户全部被回收（诛连制）

创建用户：

create user用户名identified by密码

创建完成的用户是没有任何权限和任何表的，需要有管理权限的用户如sys或system进行附权才能操作，比如conn权限可以连接。

使用profile管理用户口令

创建profile文件（创建一种规则，这个规则放在这个文件里）：create profile规则名称（名字随便起）limit Failed_login_attempts几次password_lock_time几天；

alter user某用户profile规则名称；

解锁：

account unlock;

用户密码生命周期

create profile规则名称limit password_life_time天数（生命周期）password_grace_time天数（宽限期）password_reuse_time天数（指定口令可重用时间，即多少天后就可以重用）;

删除profile，当不需要某个profile文件时，可以删除该文件，那么该文件所约束的所有用户的约束将删除

drop profile apssword_history[cascade]（级联删除）

表的管理

对表进行创建、维护、修改

1、表名必须以字符开头 2、长度不能超过30个字符 3、不能使用oracle的保留字 ——只能使用A-Z a-z 0-9$ #

字符型

char 定长 最大2000字符

varchar2 变长 最大 4000字符

clob 字符型大对象 最大4G

数字类型

number 范围10的正负38次方

number(5,2) 范围-999.99~+999.99

number(5) -99999~+99999

日期类型

date年月日时分秒

datestamp 毫秒级

二进制数据 图片、声音等类型

blob

表空间：对表管理的一种形式

建表

create table表名 (字段 类型(?),字段 类型(?),...字段 类型(?));

修改表

alter table表名add/modify(字段 类型(?)); --修改类型时，该列不能有数据

--删除字段

drop column列名;

修改表名

rename表名to表名;

删除表

drop table表名;

向表添加数据

insert into 表名 values ('?','?','?');

oracle的日期是：dd-mm月-yyyy 即：DD-MON-YYYY

修改oracle的日期格式：alter session set nls_date_format='yyyy-mm-dd';

添加一部分字段：

--但是有可能不成功，因为表有可能有一些约束

insert into表名 (列名,列名,...)values('?',...);

关于空值：如果查表中某个字段为空的值不能用=null或=''，而是要使用 is null

修改一个字段

update表名set字段=？where字段=？

删除数据

delete from表名;

在删除前插入一个回滚点：savepoint保存点， 可以将数据回滚回来 rollback to 保存点;

--不写日志，速度极快

tracate from表名;

--删除一条记录

delete from 表名 where 列名=？;

查看表结构

desc表名;

打开操作时间开关

--操作执行的时间

set timeing on;

查询

--oracle对字段内的内容是大小写区分的

取消重复行

select distinct列名from表名

--oracle的查询字段可以参与算数运算和逻辑运算

nvl(列,某值)函数，如果某列查出为空，就用某值代替

查询多个条件 用 and

like 操作符 %：0到多个字符 _：任意的单个字符

in(值,值,.....) 替代or的用法，批量处理查询到in里这些值的列

排序：

order by字段,字段,... [asc]（默认从低到高）

列的别名：

某列 [as] 别名 as可以省略，别名如果是中文需要加双引号

数据分组-max(列名)，min(列名)，avg(列名)，sum(列名)，count(列名)

group by和having 子句

group by 用于对查询的结果分组统计，分组必须出现在查询列表中

group by列名,.....

having 用于先知分组显示结果

having 列名，或数据分组+条件 如： having arv(列名)>数字

1、分组函数只能出现在选择列表、having、order by 子句中

2、如果select语句中同时包含group by、having、order by，那么他们的顺序是group by、having、order by（顺序不能错！）

3、在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必须有一个出现在group by子句中，否则出错

笛卡尔积：多表查询时，要排除笛卡尔积，条件必须是表的个数减一，但即使这样，也不一定排除笛卡尔积。

原则：多表查询的条件是：至少不能少于表的个数减一。

讲到表查询2 第9讲

10、11讲跳过

Oracle的权限

管理数据库的用户主要是sys和system，sys就像董事长，system就像总经理

sys：所有oracle的数据字典的基表和视图都存放在sys用户中，这些基表和视图对于oracle至关重要，由数据库自行维护，任何用户都不能手动变更。sys用户拥有dba，sysdba，sysoper角色，是oracle权限最高的用户

system：用于存放次一级内部数据，如oracle的一些特性或工具的管理信息，system用户拥有dba，sysdba角色或权限

sys用户必须以as sysdba或as sysoper形式登陆，不能以normal方式登陆数据库

system如果正常登录，它其实就是一个普通的dba用户，但如果以as sysdba登陆，其结果实际上是作为sys用户登录的，从登录信息中就可看出。

物理备份既可以在数据库open的状态下进行也可以在关闭数据库后进行，但是逻辑备份和恢复只能在open的环境下进行

导出分三种：1、导出表 2、导出方案 3、导出数据

导出使用exp命令来完成

1、表的导出

（1）导出自己的表：expuserid=用户名/密码@数据库实例名tables=(表名,表名,......) file=路径/文件名.dmp 在oracle主目录的bin下找exp

（2）导出其他方案的表 如果用户要导出其他方案的表，则需要dba角色或是exp_full_database的权限 exp userid=用户名/密码@数据库实例名 tables=(用户名.表名,file=路径/文件名.dmp

（3）导出表结构 expuserid=用户名/密码@数据库实例名file=路径/文件名.dmp rows=n

（4）使用直接导出方式 导出速度快file=路径/文件名.dmp direct=y

2、方案导出

（1）导出自己的方案expuserid=用户名/密码@数据库实例名 owner=用户名 file=路径/文件名.dmp

3、导出数据库

导出数据库是利用export导出所有数据库中的对象及数据，要求该用户具有dba的角色或者exp_full_database权限 expuserid=full=y inctype=complete file=路径/文件名.dmp

对应的，导入也分三种：1、导入表 2、导入方案 3、导入数据

1、表的导入

（1）导入自己的表 imp userid=file=路径/文件名.dmp

（2）导入表到其他用户 谁连接到数据库就倒到谁的用户下

（3）导入数据 不帮你建表仅导入数据（表已经存在的情况）tables=(表名,......) file=路径/文件名.dmpignore=y

2、导入方案 如果要导入其他方案，要求该用户有dba权限

（1）导入自身方案

（2）导入其他方案impuserid=file=路径/文件名.dmpfromuser=某用户 touser=某用户

3、导入数据库（全库导入）

full=yfile=路径/文件名.dmp

数据字典和动态性能视图

数据字典：存放静态信息，所有者sys用户，数据字典=数据基表+数据视图，记录了数据库的系统信息，它是只读表和视图的集合

数据字典的视图主要包括 user_xxx all_xxx dba_xxx

动态性能视图：例程启动后的相关信息

user_tables:显示当前用户所有有的表，只返回用户所对应方案的所有表

all_tables:显示当前用户可以访问的所有的表

dba_tables:显示所有方案拥有的数据库表，要求用户是dba角色或具有select any table系统权限

dba_users:显示所有数据库用户的详细信息

dba_sys_privs:显示用户所具有的系统权限

dba_tab_prvs:显示用户所具有的对象权限

dba_col_privs:显示用户具有的列权限

dba_role_prvs:显示用户所具有的角色

dba_roles:dba角色视图，包含了oralce所有的预定义角色

动态性能试图就是数据库运行时的一些动态的运行参数，是以$v开头的

建立表空间

create tablespace 表空间名 datafile '路径名/文件名.dbf' size xxm uniform size xxk 创建表空间，大小多少兆，每个段多少K

使表空间脱机

alter tablespace 表空间名 offline;

使表空间联机

alter tablespace 表空间名 online;

只读表空间

alter tablespace 表空间名 read only;

可读写表空间

alter tablespace 表空间名 read write;

查询某表空间包括的所有的表

select * from all_tables where tablespace_name='表空间名';

删除表空间

drop tablespace '表空间' including contents and datafiles;

扩展表空间 三种方式

（1）扩展表空间 alter tablespace 表空间名 add datafile '路径/文件名.dbf' size xxM

（2）增加数据文件大小 alter tablespace表空间名 datafile'路径/文件名.dbf' resize xxM

（3）设置文件自动增长 alter tablespace表空间名 datafile'路径/文件名.dbf' autoextend on next xxM maxsize xxM ;

数据迁移（故障处理）

1 确定数据文件所在表空间 select tablespace_name from dba_data_files where file_name='路径/文件名.dbf' ;

2 使表空间脱机alter tablespace 表空间名 offline;

3 将文件移动到其他路径下

4 移动数据文件alter tablespace表空间名rename datafile'路径/文件名.dbf' to'路径/文件名.dbf'

5 是表空间联机alter tablespace 表空间名 online;

数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在oracle中，数据完整性可以使用约束、触发器、应用程序（过程、函数）三种方法来实现。

约束5种：not null （不能为空），unique（唯一。必须唯一，可以为空），primary key（不能重复、不能为空），foreign key ，check（强制行数据必须满足的条件）

索引的建立

create index索引名on表名(列名,列名...)对某个表某个列建立一个索引 建议：把一下就可以把数据筛选的很少的列放在后面，sql语句的扫描是从后往前扫。

索引使用原则：

1、大表里建立索引才有意义

2、在where子句或是连接条件上经常引用的列上建立索引

3、索引层次不宜超过4层

索引最怕的就是表经常变化，在改变表的时候，数据库同时要维护索引。

不宜建索引的情况

1、很少或从不引用的字段

2、逻辑性字段，如男或女

索引信息查询：通过数据字典 dba_indexs（数据库所有索引信息）和user_indexs（当前用户索引信息）查询

select index_name,index_type from user_indexs/dba_indexs where table_name='??'

显示索引列：user_ind_columns

建立角色 可以常见数据库验证密码，也可以不创建

create role 角色名 [not] identified [by xxxx];

给角色授权：刚建立角色时，他没有任何权限

grant 权限 to 角色名 [with admin option] --权限可以由该角色传递

删除角色

drop role 角色名

显示所有角色

select * from dba_roles;

显示角色具有的系统权限

select privilege,admin_option from role_sys_privs where role='角色名'

pl/sql是什么：procedural language/sql 过程化语言 在sql上扩展的语言

过程、函数、触发器就是放到数据库里的一种对象

1、过程、函数、触发器是pl/sql编写的

2、过程、函数、触发器实在oracle中

3、pl/sql是非常强大的数据库过程语言

4、过程、函数可以在java程序中调用

缺点：移植性不好

create[or replace]procedure存储过程名as --replace 如果有此过程名就替换

begin

--执行过程

sql 语句;

exception

异常处理语句段;

end;

/--创建存储过程

如果编译错误：show error; 查看错误

如何调用该过程

1、exec过程名(参数值1,参数值2...);

2、call过程名(参数值1,参数值2...);

导出用户和表空间

--导出用户
set echo off
set heading off
set Feedback off
set term off
set long 51200
set linesize 300
SPOOL OFF
SPOOL /home/oracle/user.sql
SELECT DBMS_MetaDATA.GET_DDL('USER',USERNAME) || ';'
FROM DBA_USERS;
SPOOL OFF

--导出表空间 set echo off set heading off set Feedback off set term off set long 51200 set linesize 300 SPOOL OFF SPOOL C:\ORACD\tablespace.sql SELECT DBMS_MetaDATA.GET_DDL('TABLESPACE',TABLESPACE_NAME) || ';' FROM DBA_TABLESPACES; SPOOL OFF

原文链接：https://www.f2er.com/oracle/210836.html