Oracle DBA学习日记笔记

来源：互联网发布：西安婚纱摄影知乎编辑：程序博客网时间：2024/04/28 18:38

数据库管理工具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

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 )

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

索引已创建。

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

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

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;

索引已删除。