Oracle DBA学习日记笔记
数据库管理工具sql*Plus
启动SQL*PLUS工具
Cmd ->sqlplus /nolog
Conn system/manager
Desc 表名 查看表结构
Column 列名 for 9999 格式化列
Column 列名 for a99 格式化
List查看缓存区的指令；
Del 清除缓存区最后一行
Run 或者/查看运行缓存区指令。
Set line 120 设置整个命令行宽度为120个字符
SET PAGESIZE 20 设置每页显示20行
Save d:\select _emp.sql 保存脚本文件
@ d:\select _emp.sql 运行脚本文件
Edit d:\select _emp.sql 编辑脚本文件
get d:\select _emp.sql 查看脚本文件
spool d：\d.sql
spool spool off 把命令输出指定的文件中
sql语言概述
Structured Query Language
分类
1. 数据查询
2. 数据操作 DML
3. 数据定义 DDL
CREATE ALTER DROP RENAME TRUNCATE
4. 事务控制 COMMIT ROLLBACK
5. 数据控制 DCL grant revoke
条件表达式
CASE 表达式用于逻辑判断
DECODE 函数跟CASE表达式具有相同的功能
SQL>SELECT ename，job，sal, DECODE(JOB,’SALESMAN’,1.20*sal,
‘MANAGER’,1.30*sal,
‘ANALYST’,1.40*sal,
sal
)
LAST_Salary
FROM emp
ORDER BY job;
SQL字符型单行函数
LOWER() 把字符变为小写
Upper()大写转换
INITCAP 把首个字母大写
CONCAT 连接字符串
INSTR(‘MY NAME IAS NS’,’MY’)搜索字符的位置
LPAD() RPAD() 查询结构后用字符补充
Substr(‘my nasdf is sdfsdf’,12)截取字符串
Length（）字符串的长度
Replace() 替换函数
TRIM()
Round() 四舍五入的规则
Mod() 求余数
日期函数
ALTER SESSION SET NLS_date_lanauage=’amercan’;
Sysdate函数
MONTHS_BETWEEN()
ADD_MONTHS
NEXT_DAY(date,string)
LAST_DAY(date)
分组函数
AVG，SUM，MAX， MIN，count ,distinct
NVL(COL,0)
NVL2(EXPR1,EXPR2,EXP3)
第五章创建数据库
内存：内存是否满足Oracle实例SGA的要求
磁盘分配对数据文件控制文件和重做日志文件的大小评估
数据库文件分布 对于存在竞争的数据文件放在不同的磁盘上，以免I、O竞争，如重做日志文件和归档日志文件就不应该放在同一个磁盘对于控制文件要进行多路复用Oracle要求将多个控制文件放在不通的磁盘设备上
创建数据库 DBCA
Sqlplus /nolog
Conn /as sysdba
startup
Create database 指令
在安装数据库软件的时候创建
第六章管理和维护表
Varchar2(size) 存储变长的字符数据，大小不固定的
Nvarchar2() 不同之处在于支持全球化数据类型支持定长和变长的字符集
Char(size) 定长字符类型
Rowid（）前6位 AAAQ +h表示数据对象号
接着3位 AAE表示相对文件号
接着6位AAAAAO为块号
最后3位为行号
SQL>select owner ,table_name,tablespace_name
From dba_tables
Where owner=’SCOTT’
创建临时表
Create global temporary table
Emp_temporary
On commit preserve rows
As
Select * from emp
Where job=’MANAGER’;
临时表别的用户是无法访问的。
维护列
SQL>ALTER TABLE SCOTT.EMPLOYEES
ADD (
DEGREE VARCHAR2(10)
);
修改列
SQL>ALTER TABLE scott.employees
Modify(
Degree varchar2(10) not null
);
删除列
SQL>ALTER TABLE scott.employees drop column degree;
删除表
SQL> TRUNCATE TABLE TEST;
表被截断。
SQL> drop table test
2 ;
表已删除。
分区表操作
对于一个很大的表而言，每次搜索时对全表扫描很耗时间，Oracle允许对一个表进行分区。把达标分解为更容易管理的区分块。按照不通的规则将表分布在不同的磁盘上。
1． 范围分区 range partitioning
SQL> conn system/manager
已连接。
SQL> create table Sales_range(
2 Salesman_id number(5),
3 Salesman_name varchar2(30),
4 Sales_amount number(10),
5 Sales_date date)
6 partition by Range(Sales_date)(
7 partition sales_jan2008 values less than(to_date('02/01/2008','MM/DD/YY')),
8 partition sales_feb2008 values less than(to_date('03/01/2008','MM/DD/YY')),
9 partition sales_mar2008 values less than(to_date('04/01/2008','MM/DD/YYYY')
),
10 partition sales_apr2008 values less than(to_date('05/01/2008','MM/DD/YYYY')
))
11 ;
表已创建。
按照Sales_date字段分区。 Values less than指定什么样的数据放在什么样的分区中。
2.列表分区
显示的把数据行映射到各个分区，这些分区的定义中指定了一个由分区键离散值的列表。
SQL> create table sales_list(
2 Salesman_id number(5),
3 Salesman_name varchar2(30),
4 Sales_state varchar2(20),
5 Sales_amount number(10),
6 Sales_date date)
7 partition by list(sales_state)(
8 partition sales_west values('California','Hawaii'),
9 partition sales_east values('New Yor','Virginia','Florida'),
10 partition sales_centralt values('Texas','Tllinois'),
11 partition sales_other values(default)
12 )
13 ;
表已创建。
该分区表有4个分区块，键值sales_state用来把不通的数据行映射到相应的分区表中。
第七章Oracle 数据字典
静态动态数据字典表
查询数据库的名字和创建时间
SQL> select name ,created from v$database;
NAME CREATED
--------- --------------
ORCL 20-6月 -12
查询主机名和实例名
SQL> col host_name for a20;
SQL> run;
1* select host_name,instance_name,version from v$instance
HOST_NAME INSTANCE_NAME VERSION
-------------------- ---------------- -----------------
CSSTSTEP-ZHOU orcl 11.1.0.6.0
查看控制文件的配置
SQL> run;
1* select status,name from v$controlfile
STATUS NAME
---------- --------------------------------------------------
D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL
D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL02.CTL
D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL03.CTL
查询重做日志配置
SQL> select group#,members,status,archived from v$log;
GROUP# MEMBERS STATUS ARC
---------- ---------- ---------- ---
1 1 INACTIVE NO
2 1 CURRENT NO
3 1 INACTIVE NO
查询数据文件
SQL> run;
1* select file_id,file_name,tablespace_name from dba_data_files
FILE_ID FILE_NAME TABLESPACE
---------- -------------------------------------------------- ----------
4 D:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF USERS
3 D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF UNDOTBS1
2 D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSAUX01.DBF SYSAUX
1 D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF SYSTEM
5 D:\APP\ADMINISTRATOR\ORADATA\ORCL\EXAMPLE01.DBF EXAMPLE
查询表空间信息
SQL> select tablespace_name,block_size,contents from dba_tablespaces;
TABLESPACE BLOCK_SIZE CONTENTS
---------- ---------- ---------
SYSTEM 8192 PERMANENT
SYSAUX 8192 PERMANENT
UNDOTBS1 8192 UNDO
TEMP 8192 TEMPORARY
USERS 8192 PERMANENT
EXAMPLE 8192 PERMANENT
已选择6行。
第八章 Oracle视图
SQL> conn /as sysdba;
已连接。
SQL> grant create view to scott;
SQL> run;
1 create view accounting_view as
2 select e.ename "employee_name",e.job "job",e.hiredate "hiredate",
3 e.sal "salary" ,d.dname "dep_name"
4 from dept d,emp e
5 where e.deptno=d.deptno
6 and
7* d.deptno<20
视图已创建。
查询视图信息
SQL> select view_name from user_views;
VIEW_NAME
------------------------------
ACCOUNTING_VIEW
SQL> select text from user_views where view_name='ACCOUNTING_VIEW';
TEXT
------------------------------------------
select e.ename "employee_name",e.job "job",e.hiredate "hiredate",
e.sal "salary"
WITH READ ONLY 子句
设置只读视图不能修改和增加视图数据
WITH CHECK OPTION
插入数据的视图约束
事务
事务是一个逻辑工作单元。ACID特性
COMMIT显示事务控制
ROLLBACK实现事务控制
异常退出对事务的影响
AUTOCOMMIT实现自动提交
SQL> set autocommit on;
SQL> set autocommit off;
第十章数据查询
SQL> run;
1 select ename ||'的雇佣日期是：' || hiredate
2* from emp
ENAME||'的雇佣日期是：'||HIREDATE
--------------------------------------
SMITH的雇佣日期是：17-12月-80
ALLEN的雇佣日期是：20-2月 -81
WARD的雇佣日期是：22-2月 -81
JONES的雇佣日期是：02-4月 -81
MARTIN的雇佣日期是：28-9月 -81
BLAKE的雇佣日期是：01-5月 -81
CLARK的雇佣日期是：09-6月 -81
SCOTT的雇佣日期是：19-4月 -87
KING的雇佣日期是：17-11月-81
TURNER的雇佣日期是：08-9月 -81
ADAMS的雇佣日期是：23-5月 -87
JAMES的雇佣日期是：03-12月-81
FORD的雇佣日期是：03-12月-81
MILLER的雇佣日期是：23-1月 -82
查询加别名
SQL> run;
1 select ename ||'的雇佣日期是：' || hiredate "员工雇佣日期查询"
2* from emp
员工雇佣日期查询
--------------------------------------
SMITH的雇佣日期是：17-12月-80
ALLEN的雇佣日期是：20-2月 -81
WARD的雇佣日期是：22-2月 -81
JONES的雇佣日期是：02-4月 -81
MARTIN的雇佣日期是：28-9月 -81
BLAKE的雇佣日期是：01-5月 -81
CLARK的雇佣日期是：09-6月 -81
SCOTT的雇佣日期是：19-4月 -87
KING的雇佣日期是：17-11月-81
TURNER的雇佣日期是：08-9月 -81
ADAMS的雇佣日期是：23-5月 -87
JAMES的雇佣日期是：03-12月-81
FORD的雇佣日期是：03-12月-81
MILLER的雇佣日期是：23-1月 -82
已选择14行。
DISTINCT 该列的输入是唯一的。
连接查询
乘机连接
相等连接
自连接
不等连接
外连接
子查询
Where 条件语句
HAVING子句单行子查询
用来限制分组函数
SQL> run;
1 select job,min(sal),avg(sal),max(sal)
2 from emp
3 group by job
4* having avg(sal)>2000
JOB MIN(SAL) AVG(SAL) MAX(SAL)
--------- ---------- ---------- ----------
PRESIDENT 5000 5000 5000
MANAGER 2450 2758.33333 2975
ANALYST 3000 3000 3000
ALL子查询
SQL> run;
1 select ename,job,sal
2 from emp
3 where sal >=all(
4 select avg(sal)
5 from emp
6 group by job
7 )
8*
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
Any子查询
SQL> select ename ,job,sal
2 from emp
3 where sal >any(
4 select avg(sal)
5 from emp
6 group by job
7 );
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
FORD ANALYST 3000
SCOTT ANALYST 3000
JONES MANAGER 2975
BLAKE MANAGER 2850
CLARK MANAGER 2450
ALLEN SALESMAN 1600
TURNER SALESMAN 1500
MILLER CLERK 1300
WARD SALESMAN 1250
MARTIN SALESMAN 1250
ADAMS CLERK 1100
已选择12行。
第十一章索引
建立索引不用全表扫描，可以适当的减少磁盘I/O
对于具有只读特性或者较少的插入，更新或者删除操作的大表通常可以提高查询速度
可以对表的一列或者多列建立索引
建立索引的数量没有限制
索引需要磁盘存储。由Oracel自动维护
索引对用户透明
创建索引的语法：
CREATE [UNIQUE|BITMAP] INDEX [SCHEMA.] INDEX_NAME UNIQUE说明该索引是唯一的索引 BITMAP：创建位图索引
ON [SCHEMA.] TABLE_NAME
(COLUMN_NAME [DESC]ASC[,COLUMN_NAME[DESC]ASC])….) --说明创建的索引为降序或者升序排序
[REVERSE] REVERSE 说明创建反向键索引
[TABLESPACE TABLESPACE_NAME]说明要创建的索引所存储的表空间
[PCTFREE N]—索引块中预先保留的空间比例
[INITRANS N]—每一个索引块分配的事务数
[MAXTRANS N]---每一个索引块分配的最多事务数
[INSTORAGE STATE]—说明索引中区段EXTENT如何分配
[LOGGING|NOLOGGING] 说明要记录|不记录索引相关的操作，并保存联机重做日志中
[NOSORT] 不需要在创建索引时再按键值进行排序
创建索引 INDEX exp：
SQL> create index emp_enam_idx
2 on emp(ename)
3 ;
索引已创建。
查询索引
SQL> select index_name,index_type,tablespace_name from user_indexes
SQL> ;
INDEX_NAME INDEX_TYPE TABLESPACE_NAME
-------------------- ---------- --------------------
PK_EMP NORMAL USERS
EMP_ENAM_IDX NORMAL USERS
PK_DEPT NORMAL USERS
用户必须建立单独的表空间来存储索引
SQL> create tablespace index_tbs
2 datafile 'd:/index_tbs'
3 size 100m
4 autoextend on ;
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
--------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
INDEX_TBS
已选择7行。
创建符合索引（多列索引）
SQL> run;
1 create index emp_ename_sal_idx
2 on scott.emp(ename,sal)
3 tablespace index_tbs
4*
索引已创建。
SQL> select index_name ,table_name,tablespace_name from user_indexes
2 where index_name like 'EM%'
3 ;
INDEX_NAME TABLE_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
EMP_ENAME_SAL_IDX EMP INDEX_TBS
查询列上的索引信息 user_ind_columns
SQL> run;
1 select index_name,table_name,column_name
2 from user_ind_columns
3* where index_name like 'EMP%'
INDEX_NAME TABLE_NAME COLUMN_NAME
-------------------- ---------- --------------------
EMP_ENAME_SAL_IDX EMP ENAME
EMP_ENAME_SAL_IDX EMP SAL
B树索引
创建位图索引
SQL> create bitmap index emp_job_bitmap_idx
2 on emp(job);
SQL> select index_name,index_type,table_name,status from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME STATUS
-------------------- --------------------------- ---------- --------
PK_EMP NORMAL EMP VALID
EMP_ENAM_IDX NORMAL EMP VALID
EMP_JOB_BITMAP_IDX BITMAP EMP VALID
PK_DEPT NORMAL DEPT VALID
反向键索引
SQL> create index emp_sal_reverse_index
2 on emp(sal)
3 reverse;
索引已创建。
SQL> select index_name,index_type,table_name from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME
-------------------- --------------------------- ----------
PK_EMP NORMAL EMP
EMP_ENAM_IDX NORMAL EMP
EMP_JOB_BITMAP_IDX BITMAP EMP
EMP_SAL_REVERSE_INDE NORMAL/REV EMP
X
PK_DEPT NORMAL DEPT
创建UPPER函数索引
SQL> create index dept_dname_idx
2 on dept(upper(dname))
3 ;
索引已创建。
对索引的监控
SQL> alter index EMP_JOB_BITMAP_IDX
2 monitoring usage; --启动监控
SQL> select job from emp; --查询使用到了索引
JOB
---------
ANALYST
ANALYST
CLERK
CLERK
CLERK
CLERK
SQL> alter index EMP_JOB_BITMAP_IDX --关闭监控
2 nomonitoring usage;
索引已更改。
SQL> select index_name ,table_name,monitoring,used from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
-------------------- ---------- --- ---
EMP_ENAM_IDX EMP YES NO
EMP_JOB_BITMAP_IDX EMP NO YES --说明索引有使用到
重建索引
SQL> alter index emp_enam_idx
2 rebuild
3 ;
索引已更改。
重建并迁移其表空间
SQL> alter index emp_enam_idx
2 rebuild
3 tablespace index_tbs1;
索引的维护
通过rebuild修改索引参数
SQL>alter index emp_job_bitmap_idx
Rebuild
Pctfree 30
Storage (next 100k) ;
手工增加磁盘空间
SQL>alter index emp_job_bitmap_idx
Allocate extent;
合并索引碎片
SQL>alter index emp_job_bitmap_idx coalesce;
删除索引
SQL> drop index emp_enam_idx;
索引已删除。