oracle入门篇

[TOC]

基本操作

登入与登出

#.使用sys管理员登录
su - oracle -c "sqlplus /nolog"
SQL> conn sys/oracle as sysdba;

#.使用sys管理员登录
su - oracle -c "sqlplus / as sysdba"

--.创建新用户
SQL> create user dba_backup identified by Admin_147;
SQL> grant connect,resource,dba to dba_backup;

#.普通登录 - 用户:密码
su - oracle -c "sqlplus dba_backup/Admin_147"

#.普通登录 - 用户:密码@service_name
su - oracle -c "sqlplus dba_backup/Admin_147@orcl"

#.普通登录 - 用户:密码@host:port/sid
su - oracle -c "sqlplus dba_backup/Admin_147@192.168.31.101:1521/orcl"

--.切换登录用户
SQL> conn sys as sysdba;
SQL> conn dba_backup/Admin_147;

--.查看版本
SQL> select * from v$version;

--.退出交互式界面
SQL> exit
SQL> quit

实例的管理

• 实例的管理

#.登陆实例
su - oracle -c "sqlplus / as sysdba"

--.查询当前实例的状态
SQL> select instance_name,status from v$instance;

#.启动数据库实例(包含监听器)
su - oracle -c "dbstart"

#.oracle服务的启停
echo "startup;" | su - oracle -c "sqlplus / as sysdba" 
echo "shutdown;" | su - oracle -c "sqlplus / as sysdba" 

#.oracle服务的启停
su - oracle -c "sqlplus / as sysdba"
SQL> startup;
SQL> shutdown;

#.监听器的启停
su - oracle -c "lsnrctl stop"
su - oracle -c "lsnrctl start"
su - oracle -c "lsnrctl status"

#.启动emctl
su - oracle -c "emctl start dbconsole"

• 启动与停止的几种模式

启停的模式	状态	备注
startup nomount;	关闭状态，实例未打开	无法访问数据文件，可以执行恢复操作,比如恢复控制文件或恢复归档日志等
startup mount;	装载阶段，实例已打开	数据文件尚未读取，可以执行只读操作,比如查询静态信息或重建索引等
startup open;	正常状态，实例已打开	数据文件可以读取，可以执行读写操作,比如增删改查
startup;	-	等于nomount、mount、open
shutdown normal;	-	正常方式关闭数据库
shutdown immediate;	-	立即方式关闭数据库
shutdown abort;	-	直接关闭数据库
shutdown;	-	-

• 切换启动模式

SQL> alter database mount;                --.从NOMOUNT模式切换到MOUNT模式：
SQL> alter database open resetlogs;        --.从MOUNT模式切换到OPEN模式：
SQL> alter database mount exclusive;    --.从OPEN模式切换到MOUNT模式：
SQL> alter database close;                --.从MOUNT模式切换到NOMOUNT模式：

数据库的管理

• oracle的数据库不同于mysql的数据库，oracle只有一个库（不像MySQL的多库结构），包括dbf数据文件、ctl控制文件、log日志文件等3类

--.查看有哪些库
SELECT name,open_mode,cdb FROM V$DATABASE;

表空间的管理

• oracle可以创建多个表空间用来存放表，每个表空间对应一个数据文件，oracle表空间的概念就相当于mysql中的数据库
• 表空间名称默认为大写，如果使用小写，则授权给用户的时候可能会找不到

#.创建表空间的存放目录
mkdir -p /data/oracle_1521/tablespace
chown -R oracle:oinstall /data/oracle_1521/tablespace

--.创建表空间
create tablespace tbs_aaa datafile '/data/oracle_1521/tablespace/tbs_aaa.dbf' size 512M autoextend on next 100m maxsize unlimited;

--.创建临时表空间，最大2G
create temporary tablespace tbs_tmp tempfile '/data/oracle_1521/tablespace/tbs_tmp.dbf' size 200M AUTOEXTEND ON MAXSIZE 2G;

--.查看所有表空间
select * from v$tablespace;

--.查看所有表空间及状态
select tablespace_name,status from dba_tablespaces;

--.查看非系统的表空间
select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','USERS','UNDOTBS1','TEMP');

--.查询表空间和物理文件的对应关系
select file_name,tablespace_name,bytes from dba_data_files order by tablespace_name,file_name;

--.查看表空间的的大小、路径等信息
select tablespace_name, bytes/1024/1024, autoextensible, file_name from dba_data_files;

--.查看表空间文件的剩余空间
select tablespace_name, bytes/1024/1024 from dba_free_space;

--.修改表空间的大小
alter database datafile '/data/oracle_1521/tablespace/tbs_aaa.dbf' AUTOEXTEND ON MAXSIZE 10G;

--.修改表空间及数据文件名字（oracle 11g）
alter tablespace tbs_aaa rename to tbs_bbb;
alter tablespace tbs_bbb offline;
!mv /data/oracle_1521/tablespace/tbs_aaa.dbf /data/oracle_1521/tablespace/tbs_bbb.dbf
alter database rename file '/data/oracle_1521/tablespace/tbs_aaa.dbf' to '/data/oracle_1521/tablespace/tbs_bbb.dbf';
alter tablespace tbs_bbb online;

--.修改表空间及数据文件名字（oracle 12C及以后）
alter tablespace tbs_aaa rename to tbs_bbb;
alter tablespace tbs_bbb offline;
alter tablespace tbs_bbb rename datafile '/data/oracle_1521/tablespace/tbs_aaa.dbf' to '/data/oracle_1521/tablespace/tbs_bbb.dbf';
alter tablespace tbs_bbb online;

--.查看某用户用到了哪些表空间
select distinct owner,tablespace_name from dba_extents where owner like 'SYS%';

--.查看用户和其默认表空间
select username,default_tablespace,temporary_tablespace from dba_users where username='SYS';

--.查看表和表所在表空间
select table_name,tablespace_name from dba_tables where tablespace_name = 'SYSTEM';

--.给某表空间添加数据文件
alter tablespace tbs_bbb add datafile '/data/oracle_1521/tablespace/tbs_bbb.2.dbf' size 100M autoextend on next 100M maxsize unlimited;

--.查看表空间使用情况
select f.tablespace_name, a.total "total (M)", f.free "free (M)", round((f.free / a.total) * 100) "% Free"
from (select tablespace_name, sum(bytes / (1024 * 1024)) total
from dba_data_files
group by tablespace_name) a, (select tablespace_name, round(sum(bytes / (1024 * 1024))) free from dba_free_space group by tablespace_name) f
where a.tablespace_name = f.tablespace_name(+)
order by "% Free";

--.删除表空间和数据文件
drop tablespace tbs_bbb including contents and datafiles;

--.删除临时表空间和数据文件
drop tablespace tbs_tmp including contents and datafiles;

数据表的管理

• oracle 不支持 drop table if exists 语法
• oracle 对表名大小写不敏感，在创建表时，表名会自动转换大写。若表名含有小写字母或特殊符号，则需要在表名两侧添加双引号

--.不加双引号，则新表名称为 TAB01
create table tab01(id1 int);

--.添加双引号，则新表名称为 tab01
create table "tab01"(id2 int);

• 出于安全考虑，强大的sys用户不允许使用 alter table 表名 drop column 列名; 来删除字段

--.创建超管账号并切换，否则在sys用户下删除字段会报错
create user dba_admin identified by cf_rB1NKCzbaQuPH;
grant connect,resource,dba to dba_admin;
conn dba_admin/cf_rB1NKCzbaQuPH;

--.准备测试表
drop table tab01;
CREATE TABLE tab01(id number(6,0) primary key,name varchar2(10),city varchar(10));
INSERT INTO tab01 VALUES(1,'sam','beijing');

--.创建会话级临时表，在整个会话期间存在
CREATE GLOBAL TEMPORARY TABLE temp_table01 (id number, name varchar2 (20)) ON COMMIT PRESERVE ROWS;

--.创建事务级临时表，在事务结束时自动删除
CREATE GLOBAL TEMPORARY TABLE temp_table02 (id number, name varchar2 (20)) ON COMMIT DELETE ROWS;

--.查看表结构
desc tab01;

--.查询表
select table_name from all_tables where table_name like 'TAB0%';
select table_name,owner,status from all_tables where owner='DBA_ADMIN';

--.查看表所占空间
Select 
  a.owner,a.table_name,
  Sum(Bytes) / 1024 / 1024 As Total_SIZE_MB,
  sum(Case When Type = 'TABLE' Then Bytes Else 0 End) / 1024 / 1024 As TABLE_SIZE_MB,
  sum(Case When Type = 'INDEX' Then Bytes Else 0 End) / 1024 / 1024 As INDEX_SIZE_MB,
  sum(Case When Type = 'LOB' Then Bytes Else 0 End) / 1024 / 1024 As LOB_SIZE_MB,
  sum(Case When Type = 'RECYCLE' Then Bytes Else 0 End) / 1024 / 1024 As RECYCLE_SIZE_MB,
  Max(Num_rows) As Num_rows 
From dba_segments b 
Left Join ( 
  select owner,table_name,segment_name,'LOB' Type,0 as Num_rows 
  from dba_lobs 
  union all select table_owner,table_name,index_name,'INDEX',0 From dba_indexes 
  union all select owner,table_name,table_name,'TABLE',Num_rows From dba_tables 
  union all Select owner,original_name,object_name,'RECYCLE',0 from dba_recyclebin ) a 
on a.segment_name = b.segment_name and a.owner = b.owner 
Where Segment_Type <> 'TYPE2 UNDO' 
Group by a.owner,a.table_name 
Order by INDEX_SIZE_MB desc;

--.修改表-添加字段
ALTER TABLE tab01 ADD (remark varchar(20), outdate date);

--.修改表-修改字段类型
ALTER TABLE tab01 MODIFY (remark varchar(30), outdate timestamp);

--.修改表-修改字段名称
ALTER TABLE tab01 RENAME COLUMN outdate TO exitdate;

--.修改表-删除字段,注意不允许sys用户删除字段
ALTER TABLE tab01 drop column remark;

--.复制表结构和数据
CREATE TABLE tab02 AS SELECT * FROM tab01 WHERE city = 'beijing';

--.复制表结构
CREATE TABLE tab03 AS SELECT * FROM tab01 WHERE 1=0;

--.重命名表
alter table tab02 rename to tab02_bak;

--.清空表
truncate table tab03;

--.删除表
drop table tab03;

增删改查

• oracle 不支持mysql中limit功能，但可以通过 rownum 来限制返回的结果集的行数，rownum并不是用户添加的字段，而是oracle系统自动添加的

--.准备测试表
drop table t_user;
create table t_user(id number primary key, name varchar2(10), city varchar(10), rmb number(6,2), adddate date);

--.插入数据
insert into t_user VALUES(1,'sam','beijing',999.12,sysdate);
insert into t_user VALUES(2,'joy','shanghai',9999.99,sysdate);
commit;

--.修改数据
update t_user set adddate=adddate-3 where id=1;
commit;

--.查询数据
select * from t_user where rownum <= 100;
select id,name,city,to_char(adddate,'yyyy-mm-dd') from t_user where UPPER(city) like 'SH%' and rmb > 100 order by id desc; 

--.删除数据
delete from t_user where id=2;
commit;

账号的管理

• 关于默认账号，sys相当于qq群主，system相当于qq群管理员，scott相当于Guest

用户名称	权限	备注
SYS	权限最高的超级用户，具有DBA权限，并具有sysdba权限	只能以sysdba或sysoper登陆，不能用normal
SYSTEM	内置的一个普通管理员，拥有普通DBA权限，但没有sysdba权限	该帐号用于管管理数据库，只能以Normal登陆
PDBADMIN	用于管理可插拔数据库(PDB)的用户	可以执行与PDB相关的操作,如创建、删除PDB等
SYSMAN	管理员级别的用户	用于操作企业管理器	-
SCOTT	-	示范帐户,默认锁定不可登录

• 从 oracle 12c 开始，在默认cdb容器中创建用户必须以C##或者c##开头，通过设置 "_ORACLE_SCRIPT"=true 可临时绕过CDB中创建用户必须以 C## 开头的限制。请注意，此设置只在当前会话中有效，新会话仍然会遵循默认规则

--.创建表空间
create tablespace TBS_PRD datafile '/data/oracle/tablespace/TBS_PRD.dbf' size 200m autoextend on next 100m maxsize unlimited;

--.创建用户
--.语法：create user <用户> identified by <密码> default tablespace <默认表空间> temporary tablespace <临时表空间> profile <profile> account <状态>;
drop user user_test01;
create user user_test01 identified by 123456 default tablespace SYSTEM;

--.临时绕过CDB中创建用户必须以C##开头的限制-针对12c及以上版本
alter session set "_ORACLE_SCRIPT"=true;

--.给该用户在USERS表空间分配配额-针对12c及以上版本
ALTER USER user_test01 QUOTA UNLIMITED ON USERS;

--.1.查看所有用户
select username,user_id,account_status,created,expiry_date from dba_users;
select * from all_users;
select * from user_users;

--.2.查看用户系统权限
select * from dba_sys_privs;
--select * from all_sys_privs;
select * from user_sys_privs;

--.3.查看用户对象权限
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

--.4.查看所有角色
select * from dba_roles;

--.5.查看用户所拥有的角色
select * from dba_role_privs;
select * from user_role_privs;


--.查看当前连接的用户
show user;

--.查看当前用户所拥有的权限
select * from session_privs;

--.查看当前用户所有对象和数量统计
select object_type,count(*) from user_objects group by object_type;

--.查看当前用户下有多少表
select count(*) from user_tables;

--.锁定与解锁
alter user user_test01 account lock;
alter user user_test01 account unlock;

--.修改用户的表空间
alter user user_test01 default tablespace TBS_PRD;

--.调整用户在特定表空间上的配额
alter user user_test01 quota unlimited on TBS_PRD;

--.修改用户密码
alter user user_test01 identified by newpassword;

--.删除用户,若用户拥有对象则删除会报错,可指定关键字cascade连同对象一并删除
drop user user_test01;
drop user user_test01 cascade;

授权与撤销

权限名称	权限介绍	常见的命令
系统权限	对服务器或数据库的权限	create session, create table等
对象权限	对数据库或表的增删改查等操作	select, insert, update, delete等
connect 角色	可以与Oracle服务器建立连接，并对表有访问权限	select, insert, update, delete等
resource 角色	可以创建自己的对象，包括：表、存储过程、索引等	-
dba 角色	管理员角色，它拥有系统的所有权限，和给其他用户授权的权限	-

--.准备测试账号
drop user user_test02;
create user user_test02 identified by password;

--.授予权限
--.grant select,insert,update,delete on 表名 to 用户1,用户2;
grant connect,resource to user_test02;
grant select,insert,update,delete on tab01 to user_test02;

--.Revoke 对象权限 on 对象 from 用户 
REVOKE delete ON tab01 FROM user_test02;

--.收回所有权限可以用 ALL 代替
REVOKE ALL ON tab01 FROM user_test02;

--.Revoke 系统权限 from 用户
revoke SELECT ANY TABLE from user_test02;

--.Revoke 角色 from 用户
revoke connect,resource from user_test02;

• [推荐] 使用 with admin option，被授权用户可将所获得的权限再次授予其它用户或角色，而且取消授权时不级联

--.首先管理员授权 user_01 拥有 create session 权限
conn dba_admin/cf_rB1NKCzbaQuPH;
grant create session to user_01 with admin option;

--.然后用户 user_01 可以给 user_02 授予同样的权限
conn user_01/password_a;
grant create session to user_02;

--.当管理员回收 user_01 的权限时，默认 user_02 的权限不会被回收
conn dba_admin/cf_rB1NKCzbaQuPH;
revoke create session from user_01;

--.除非显式回收 user_02 的权限
conn dba_admin/cf_rB1NKCzbaQuPH;
revoke create session from user_02;

• 使用 with grant option，被授权用户可将所获得的权限再次授予其它用户或角色，并且权限的取消是级联的

--.首先管理员授权 user_01 拥有 create session 权限
conn dba_admin/cf_rB1NKCzbaQuPH;
grant create session to user_01 with grant option;

--.然后用户 user_01 可以给 user_02 授予同样的权限
conn user_01/password_a;
grant create session to user_02;

--.当管理员回收 user_01 的权限时，则 user_02 的权限也会被回收
conn dba_admin/cf_rB1NKCzbaQuPH;
revoke create session from user_01;

三类示范账号

• 创建常用的三种账号：管理员、业务读写账号、只读账号

--.创建超管账号
create user dba_admin identified by cf_rB1NKCzbaQuPH;
grant connect,resource,dba to dba_admin with admin option;

--.创建业务账号
create user user_pay identified by Aa_123456;
grant connect to user_pay;
grant select,insert,update,delete on tab01 to user_pay;

--.创建只读账号
create user user_bigdata_readonly identified by Aa_123456;
grant select any table to user_bigdata_readonly;

连接数与会话

• 通过 profile 限制某个用户的连接数

--.准备测试账号
create user user_test02 identified by 123456;


--.设置 RESOURCE_LIMIT=TRUE 开启资源限制（对密码资源无效）
alter system set resource_limit =TRUE;
show parameter resource_limit;

--.创建PROFILE（最大连接数限制为20），并将PROFILE指定给用户
create profile SESSION_NUM limit sessions_per_user 20;
alter user user_test02 profile SESSION_NUM;

--.查看用户所属的proifle，默认是default
select username,profile from dba_users;

--.查看并修改指定PROFILE（如 DEFAULT）的密码有效期设置
select * from dba_profiles where PROFILE='DEFAULT' and RESOURCE_NAME='PASSWORD_LIFE_TIME';
alter profile DEFAULT limit password_life_time unlimited;

--.查看并修改指定PROFILE（如 SESSION_NUM）的最大连接数改成“无限制”（无需重启,立即生效）
select * from dba_profiles where PROFILE='SESSION_NUM' and RESOURCE_NAME='SESSIONS_PER_USER';
alter profile SESSION_NUM limit SESSIONS_PER_USER unlimited;

--.当某个profile被删除时，加上cascade可将该profile所分配的用户重置为default profile
drop profile SESSION_NUM cascade;

• 连接数的管理

--.当前进程的连接数
select count(*) from v$process;

--.当前会话的连接数
select count(*) from v$session;

--.查看数据库的并发连接数
select count(*) from v$session where status='ACTIVE';

--.查询数据库允许的最大连接数
select value from v$parameter where name = 'processes';

--.修改数据库允许的最大连接数(需要重启数据库才能实现连接数的修改)
alter system set processes = 300 scope = spfile;

--.查看当前数据库建立的会话情况
select count(1),username from v$session group by username

--.查看当前连接的用户
show user;

--.查看会话
select SID,SERIAL#,USERNAME,STATUS from v$session where rownum<=100;
select SID,SERIAL#,USERNAME,STATUS from v$session where username = 'SYS';

--.查看当前的会话id
select sid from v$mystat where rownum=1;
select userenv('sid') from dual;

• 杀掉会话的多种方式，推荐使用 DISCONNECT SESSION IMMEDIATE

--.杀掉会话 - KILL SESSION
--.比如杀掉sid为68且serial#为479的会话
--.它实际上不是真正的杀死会话,它只是将会话标记为终止,等待PMON进程来清除会话
ALTER SYSTEM KILL SESSION '68,479';

--.杀掉会话 - DISCONNECT SESSION
--.杀掉专用服务器(DEDICATED SERVER)或共享服务器的连接会话,它等价于从操作系统杀掉进程
--.它有两个选项 POST_TRANSACTION(等待事务完成后断开会话)、IMMEDIATE(中断会话,立即回滚事务)
ALTER SYSTEM DISCONNECT SESSION '68,479' POST_TRANSACTION;
ALTER SYSTEM DISCONNECT SESSION '68,479' IMMEDIATE;

cdb与pdb管理

--.查看当前数据库container中包含有多少个pdb
show pdbs;
select name,open_mode from v$pdbs;

--.查看当前属于哪个容器
select sys_context('USERENV','CON_NAME') from dual;
show con_name;

字符集

• Oracle 定义的字符集包括：数据库字符集、国家字符集

数据库字符集(nls_character)：用于CHAR, VARCHAR2, LONG和CLOB数据类型中的数据
数据库国家字符集(nls_nchar_character)：用于存储在NCHAR, NVARCHAR2和NCLOB数据类型中的数据

• NLS_LANG 是一个环境变量，用于定义语言、地域、字符集属性，其参数组成如下：

参数组成：NLS_LANG=LANGUAGE.CHARACTERSET，即 语言.字符集
示例返回：NLS_LANG=AMERICAN_AMERICA.AL32UTF8

• 查看os层面的字符集设置

#.先切换到oracle用户下
su - oracle

#.查看 NLS_LANG
echo $NLS_LANG
env | grep NLS_LANG

#.设置 unicode字符集
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

#.设置 中文字符集
export NLS_LANG=SimplifiedChinese_china.ZHS16GBK

#.修改 ~/.bash_profile 永久生效
sed -i 's/^NLS_LANG=.*/NLS_LANG=AMERICAN_AMERICA.AL32UTF8/' ~/.bash_profile
cat ~/.bash_profile | grep "^NLS_LANG"
source ~/.bash_profile

• 查看db层面的字符集设置

#.使用sys管理员登录
su - oracle -c "sqlplus / as sysdba"

--.查看db层面 NLS_LANGUAGE
select userenv('language') from dual;

--.查看db层面 NLS_LANGUAGE、NLS_CHARACTERSET、NLS_NCHAR_CHARACTERSET
select * from v$nls_parameters where PARAMETER in ('NLS_LANGUAGE','NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

--.查看数据库可用的字符集
SELECT * FROM v$nls_valid_values;

--.查看 数据库字符集 NLS_CHARACTERSET、数据库国家字符集 NLS_NCHAR_CHARACTERSET
select * from nls_database_parameters where PARAMETER in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

parameter参数

• 通过 show parameter 及动态视图 查看oracle系统参数

--.查看所有参数及其值
show parameter;
select * from v$parameter;

--.查看指定参数的值，比如 db_name
show parameter db_name;
SELECT * FROM v$parameter WHERE NAME ='db_name';

索引与调优

索引的创建与删除

--.准备测试表
drop table test04;
create table test04(id number,name varchar(10),age number(3),city varchar(10),email varchar(30));

--.主键约束-添加与删除
alter table test04 add constraint pk_id_test04 primary key(id,name);
alter table test04 drop primary key;

--.唯一约束-添加与删除
alter table test04 add constraint uq_email_test04 unique(email);
alter table test04 drop constraint uq_email_test04;

--.检查约束-添加与删除
alter table test04 add constraint chk_age_test04 check(age between 1 and 150);
alter table test04 drop constraint chk_age_test04;

--.默认值约束-添加与删除
alter table test04 modify city varchar(10) default 'beijing';
alter table test04 modify city varchar(10) default null;

--.创建索引
create index idx_test04_name on test04(name);

--.唯一索引
--.CREATE UNIQUE INDEX udx_test04_name ON test04(name) TABLESPACE TBS_INDEX;
CREATE UNIQUE INDEX udx_test04_name ON test04(name);

--.删除索引
drop index idx_test04_name;