oracle_advanced

Oracle , Comments

References

http://otn.oracle.com/

http://technet.oracle.com

http://www.ioug.org

http://www.doug.org

http://www.oracle.com/openworld/archive

https://community.oracle.com/welcome

http://www.dba.ant.lviv.ua/

https://oracle-base.com/

http://www.dba-oracle.com/

http://www.dbasupport.com/forums/

https://www.orapub.com/

http://www.orafaq.com/

http://dbazine.com/

https://www.rittman.net/

https://www.databasejournal.com/

https://asktom.oracle.com/

hedu.pjwstk.edu.pl/wyklady/szb/scb/wyklad14/01L.ppt

https://www.2cto.com/database/Oracle/

格式化输出

  • col column_name_xxx for a10/a45
  • set linesize 9999 pagesize 9999

Oracle数据库 表

  • 动态视图
  • 动态性能视图
  • 内部表
  • 数据字典表

内部表

  • x$

数据字典

  • 基表

  • sys.ts$

  • sys.user$

  • 数据字典视图

  • dba_xxx

  • dba_synonyms;

  • dba_users;

  • dba_sys_privs;

  • dba_tab_privs;

  • dba_col_privs;

  • dba_role_privs;

  • system_privilege_map;

  • role_sys_privs

  • dba_tablespaces;

  • dba_data_files;

  • dba_temp_files;

  • all_xxx

  • all_synonyms;

  • user_xxx

  • user_synonyms;

  • user_tables;

  • user_tab_comments;

  • user_tab_columns;

动态视图

动态性能视图( v/ v_)

  • v$fixed_table;

  • v$sga

  • v$sgainfo

  • v$sga_dynamic_components

  • v$sga_dynamic_free_memory

  • v$instance

  • v$database

  • v$datafile

  • v$parameter

  • v$spparameter

  • v$controlfile

  • v$tablespace

  • v$instance;

  • v$database;

  • v$datafile;

  • v$tempfile;

  • v$sga_dynamic_components;

  • v#db_cache_advice;//查看Database buffer cache的统计信息

  • v$session;

  • v$archived_log;

  • v$archive_dest;

  • v$archive_processes;

  • v$archive_dest_status;

  • v$archive_gap;

  • v$archive;

  • vlog;//select group#, thread#, sequence#,status from vlog

  • vlogfile;//select∗fromvlogfile;//select * from vlogfile

  • v$log_history;

  • dict;

    select * from vparameterwherename=′backgrounddumpdest′;selectinstancename,hostname,status,instancerole,activestatefromvparameter where name='background_dump_dest'; select instance_name,host_name,status,instance_role,active_state from vinstance
    select name,open_mode,database_role,platform_name,current_scn from v$database

  • show parameter o7;

alter system set o7=true scope=both;
alter system set fast_start_mttr_target=600 scope=both;
– show-related

  • show parameter sql_tace;

数据库管理员的职责

DBA的主要工作

1）安装和升级oracle数据库
2）建库,表空间，表，视图，索引
3）指定并实施备份与恢复计划
4）数据库权限管理，调优，故障排除
5）对于高级dba，要求能参与项目开发，会编写sql语句、存储过程、触发器、规则、约束、包

oraenv

环境变量设置

1
2
3
4
5
6
7
8
9
10
11
12
[oracle@rhel750 ~]$ diff oracle.file oraenv.file 
11c11,12
< WINDOWID=41946019
---
> WINDOWID=41943046
> OLDPWD=/tmp
13a15
> LD_LIBRARY_PATH=/oracle/app/oracle/product/11.2.0/db/lib
22c24
< PATH=/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/bin:/sbin:/home/oracle/.local/bin:/home/oracle/bin
---
> PATH=/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/bin:/sbin:/home/oracle/.local/bin:/home/oracle/bin:/oracle/app/oracle/product/11.2.0/db/bin

五大进程：

  • DBWn
  • LGWR
  • PMON
  • SMON
  • CKPT

Oracle数据库安装

  • 系统参数调整

    /etc/security/limits.conf
    /etc/pam.d/login

  • 核心参数调整

    /etc/sysctl.conf

数据库监听

Oracle 网络连接(Oracle Net Services)

优化网络带宽，支持高速的InfiniBand网络(无限带宽技术)

ADR

TNS

专用服务器体系

共享服务器体系

SQL Plus 交互

用户登录

local

Net Service

conn user_name/password as sys/system

alter user hr identified by “hr” account unlock;

帮助文件

  1. help index

  2. col col_name_xxx for[mat] axx_size_width

交互式

&table_feild

连接(listener服务必须启动)

tnsnames.ora

sqlplus scott/tiger@orcl

listener.ora(/etc/hosts)–>GLOBAL_DBNAME
tnsnames.ora–>SERVICE_NAME

sqlplus scott/tiger@//192.168.1.3:1521/orcl.oracle.com

基本数据类型

  • varchar2
  • num
  • date
  • timestamp
  • clob
  • blob

用户管理

  • create user user_name identified by pwd_xxx default tablespace xxx;

  • grant role_name to user_name;

  • grant unlimited tablespace to username_XXX

  • alter user username_XXX account unlock;

  • drop user_name_XXX [cascade];

  • select username,account_status from dba_users where account_status=‘OPEN’;

权限

系统权限

对象权限

权限的授予与回收

grant privilege_xxx on tb_xx to user_name

revoke privilege_xxx on tb_xx from user_name

权限的维护
系统权限：不级联回收
对象权限：级联回收

sys-privs:with admin option

obj-privs:with grant option

角色

预定义角色
connect
dba
resource

方案

自定义角色

profile文件管理用户口令

create profile
drop profile profile_xxx [cascade]

表管理

表新建

dual 虚拟表

表删除

  • delete table tb_name
  • drop table tb_name
  • truncate tb_name
1
2
3
4
5
6
7
8
9
10
11
12
13
select tablespace_name,status from dba_tablespaces;

alter tablespace tb_xxx offline/online;

select t.ts#,t.name,f.name from v$datafile f,v$tablespace t
where f.ts#=t.ts#;

select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;



表查询

  • 基本查询

  • 算数表达式

  • 聚合函数

  • WHERE

  • like操作符

  • GROUP by

  • having

  • ORDER by

多表查询

  • 自连接

  • 子查询

    单行子查询

    多行子查询

  • from子句中使用子查询(内嵌视图)

  • 分页查询
    rownum

  • 合并查询(对大表操作速度快)

    1. union
    2. union all
    3. intersect
    4. minus

  • 多行插入
    insert into tb_name(xxx,xx,xxx) select xxx,xxx,xxx from tb_name

  • 更新数据
    直接修改
    子查询修改

|| 合并

存储对象

过程

函数

SQL函数
字符函数
数学函数
转换函数

select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual;

包

索引

表空间

表空间配额

同义词

用户

用户：sys,system
角色：sysdba sysoper dba

默认环境变量

角色

  • 预定义角色

  • 自定义角色

角色被删除，该角色下的用户再也不具有该角色所拥有的权限。

精细访问控制

创建数据库

  • Database Configuration Assistant(dbca)

数据库管理

  • OEM Grid Control(emca)

事务

  • 设置保存点
  • 取消部分事务
  • 取消全部事务

只读事务

set transaction read only

锁

导入导出

逻辑备份
物理备份
导出表
导出方案
导出数据库
导入表
导入结构
导入方案
导入数据库

表

  • global_name

数据字典

查看当前用户下的所有表

SQL> SELECT t.table_name FROM USER_TABLES t

某个角色所具有的系统权限

select * from system_privilege_map oredr by name;
select * from dba_sys_privs where role=‘xxx’;
select * from role_sys_privs where role=‘xxx’;

某个角色所拥有的对象权限

select distinct privilege from dba_tab_privs;

所有角色

select * from dba_roles;

角色下的权限

dba_roles;
dba_tablespaces;

某个用户具有哪些角色

select * from dba_role_privs where grantee=‘xx’;

显示当前数据库全称

select * from global_name;

显示当前用户可以访问的所有数据字典视图

select * from dict where comments like ‘%grant%’

查看数据库物理文件

select t.file_id, t.file_name,t.tablespace_name from dba_data_files t

管理表空间和数据文件

数据文件路径/临时文件路径/重做日志文件路径/控制文件路径：$ORACLE_BASE/oradata/orcl/

参数文件路径/密码文件路径：$ORACLE_HOME/dbs

Oracle监听文件路径:$ORACLE_HOME/network/admin

告警日志路径：$ORACLE_BASE/diag/rdbms/leo1/LEO1/trace

表空间

表空间，段，区，块

创建表空间

管理表空间的状态（online/offline/read only/read write）

管理表空间容量

扩容表空间的方法：

  • 增加数据文件
  • 增加数据文件的大小
  • 设置文件的自动增长

删除表空间

迁移表空间，迁移数据库:移动数据文件

  • 索引表空间
  • undo表空间
  • 临时表空间
  • 非标准块的表空间

维护数据完整性

user_constraint
user_cons_columns

数据完整性实现方法
* 约束
not null
unique
primary key
foreign key
check
* 触发器
* 应用程序(过程，函数)

列级定义

表级定义

索引

dba_indexs
user_indexs
user_ind_columns

加速数据存取
降低io次数
提高数据访问性能

单列索引

复合索引

数据库的筛选SQL语句扫描是从后往前的

素丶素丶丶wodetian

忍一时越想越气，退一步越想越亏。