Oracle 学习笔记(1)

1.如何查询每个用户权限？

select from dba_sys_privs;

2.oracle如何产生随机数的函数？

dbms_random.random

3.查询当前用户对象

select * from user_objects;select * from dba_segments;

4.如何错误信息

select * from user_errors;

5.查看字符状况

select * from nls_database_parameters;SELECT * from V$NLS_PARAMETERS；

6.查询表空间信息

select * from dba_data_files;

7.如何给表、列加注释？

SQL>comment on table 表 is '表注释'； 注释已创建。 SQL>comment on column 表.列 is '列注释'； 注释已创建。 SQL> select * from user_tab_comments where comments is not null； 

8.将表t_test放入keep池中

alter table t_test storage(buffer_pool keep);

9.使select查询结果自动生成序号？

select rownum,num1 from t_test;

10.快速创建一个备份表

create table t_testnew as(select * from t_test);

11.如何在PL/SQL中读写文件？

UTL_FILE包允许用户通过PL/SQL读写操作系统文件。
12.如何查看现有回滚段及其状态?

SQL> col segment format a30 SQL> SELECT SEGMENT_NAME，OWNER，TABLESPACE_NAME，SEGMENT_ID，FILE_ID，STATUS FROM DBA_ROLLBACK_SEGS 

13.Oracle常用系统文件有哪些？
通过以下视图显示这些文件信息：vdatabase，vdatafile，vlogfile,vcontrolfile v$parameter；
14.如何测试SQL语句执行所用的时间?

SQL>set timing on； SQL>select * from tablename； 

15.字符串的连接

SELECT CONCAT(COL1，COL2) FROM TABLE； SELECT COL1||COL2 FROM TABLE； 

16.怎么把select出来的结果导到一个文本文件中？

SQL>SPOOL C：\ABCD.TXT； SQL>select * from table； SQL >spool off； 

17.怎样估算SQL执行的I/O数 ?

SQL>SET AUTOTRACE ON ； SQL>SELECT * FROM TABLE； 或 SQL>SELECT * FROM v$filestat；可以查看IO数 

18.如何知道用户拥有的权限?

SELECT * FROM dba_sys_privs ；

19.如何统计两个表的记录总数?

select (select count(id) from aa)+(select count(id) from bb) 总数 from dual； 

20.返回当前月的最后一天?

SELECT LAST_DAY(SYSDATE) FROM DUAL； 

21.结果集互加（union）结果集互减（minus）。

---

日期的各部分的常用的的写法
22. 取时间点的年份的写法：

SELECT TO_CHAR(SYSDATE，'YYYY') FROM DUAL； 

1. 取时间点的月份的写法：

SELECT TO_CHAR(SYSDATE，'MM') FROM DUAL； 

1. 取时间点的日的写法：

SELECT TO_CHAR(SYSDATE，'DD') FROM DUAL； 

1. 取时间点的时的写法：

SELECT TO_CHAR(SYSDATE，'HH24') FROM DUAL； 

1. 取时间点的分的写法：

SELECT TO_CHAR(SYSDATE，'MI') FROM DUAL； 

1. 取时间点的秒的写法：

SELECT TO_CHAR(SYSDATE，'SS') FROM DUAL； 

1. 取时间点的日期的写法：

SELECT TRUNC(SYSDATE) FROM DUAL； 

1. 取时间点的时间的写法：

SELECT TO_CHAR(SYSDATE，'HH24：MI：SS') FROM DUAL； 

1. 日期，时间形态变为字符形态：

SELECT TO_CHAR(SYSDATE) FROM DUAL； 

1. 将字符串转换成日期或时间形态：

SELECT TO_DATE('2003/08/01') FROM DUAL； 

1. 返回参数的星期几的写法：

SELECT TO_CHAR(SYSDATE，'D') FROM DUAL； 

1. 返回参数一年中的第几天的写法：

SELECT TO_CHAR(SYSDATE，'DDD') FROM DUAL； 

1. 返回午夜和参数中指定的时间值之间的秒数的写法：

SELECT TO_CHAR(SYSDATE，'SSSSS') FROM DUAL； 

1. 返回参数中一年的第几周的写法：

SELECT TO_CHAR(SYSDATE，'WW') FROM DUAL；

23.rowid
返回行的物理地址
24.如何查找重复记录？

SELECT * FROM TABLE_NAME WHERE ROWID!=(SELECT MAX(ROWID) FROM TABLE_NAME D WHERE TABLE_NAME.COL1=D.COL1 AND TABLE_NAME.COL2=D.COL2)；

这里可以使用rowid来判断是否有重复，因为不同行的rowid是不同的。
删除操作类似。

25.SGA?

    系统全局区又称SGA （System Global Area）是Oracle Instance的 基本组成部分，在实例启动时分配。是一组包含一个Oracle实例的数据和控制信息的共享内存结构。主要是用于存储数据库信息的内存区，该信息为数据库进程所共享（PGA不能共享的）。它包含Oracle 服务器的数据和控制信息，它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。

26.如何显示所有数据库对象的类别和大小？

select count(name) num_instances ,type ,sum(source_size) source_size , sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size, sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required from dba_object_size group by type order by 2； 

27.如何监控当前数据库谁在运行什么SQL语句？

SELECT osuser, username, sql_text from v$session a, v$sqltext b where a.sql_address =b.address order by address, piece； 

28.监控MTS

    MTS = Multi-Threaded Server    MTS是ORACLE SERVER的一个可选的配置选择，是相对DEDICATE方式而言，它最大的优点是在以不用增加物理资源（内存）的前提下支持更多的并发的连接。

---

SQL语句的优化方法：

---

Oracle的内部函数：
1.字符串第一个字符的ASCII值
ASCII(”);
2.得到数值N的指定字符
CHR(11);
3.连接两个字符串
CONCAT(‘1’,’2’);
4. 字符串第一个大写，其余小写
INITCAP(CHAR);
5.字符串的长度
LENGTH(CHAR);
6.全部变为小写、全部变为大写
LOWER(CHAR);UPPER(CHAR);
7.把CHAR2左填CHAR1，长度为N
LPAD(CHAR1,N,CHAR2);
8.右填
RPAD(CHAR1,N,CHAR2);
9.截取字符串
SUBSTR(CHAR1,开始截取位置，截取后位置)
10.将CHAR1中的CHAR2的部分用CHAR3代替。
TRANSLATE(CHAR1，CHAR2，CHAR3)
11.char转换为fmt日期
to_date(char,fmt);
12.大于或等于n的最大整数
CEIL(N);
13.小于或等于n的最小整数
FLOOR(N);
14.字符转换数值（number）
TO_NUMBER(CHAR);//其中的CHAR是字符串类型的数字

---

遗留问题：
1.MTS？专用模式？(43)

MTS（Multi-Threaded Server）是ORACLE SERVER的一个可选的配置选择，是相对DEDICATE方式而言，它最大的优点是在以不用增加物理资源（内存）的前提下支持更多的并发的连接。换句话 说，如果你只有2G的物理内存，而你又想支持2000个连接，在获取最好性能的前提下，你就应该选择MTS了。专用模式，就是专用服务器模式，就是一个客户端链接一个服务进程，是一个专用通道。

2.系统当前的SCN号？(44)

    SCN是当Oracle数据库更新后，由DBMS自动维护去累积递增的一个数字。Oracle数据库中一共有4种SCN分别为    系统检查点SCN： 系统检查点SCN位于控制文件中，当检查点进程启动时(ckpt)，Oracle就把系统检查点的SCN存储到控制文件中。该SCN是全局范围的，当发生文件级别的SCN时，例如将表空间置于只读状态，则不会更新系统检查点SCN。      查询系统检查点SCN的命令如下：

 select CHECKPOINT_CHANGE# from v$database;

    数据文件scn：当ckpt进程启动时，包括全局范围的（比如日志切换）以及文件级别的检查点（将表空间置为只读、begin backup或将某个数据文件设置为offline等），这时会在控制文件中记录的scn。    查询数据文件SCN的命令如下：

alter tablespace users read only;select file#,checkpoint_change# from v$datafile;

    结束scn：每个数据文件都有一个结束scn，在数据库的正常运行中，只要数据文件在线且是可读写的，结束scn为null。否则则存在具体的scn值。结束scn也记录在控制文件中。

SQL>select TABLESPACE_NAME,STATUS from dba_tablespaces

3.patch?(52)

    Oracle patch也即是Oracle补丁。Oracle补丁又包含好几个种类，小的补丁简直是难以数计，难免让人眼花缭乱。尽管如此，Oracle patch还是有序可循的。而且Oracle提供的opatch工具非常方便的用于安装oracle patch，以及查看当前系统已经安装的patch。本文列出了patch的几种类型，以及主要描述通过opatch工具查看当前数据库的patch应用的情况。对于如何apply patch可参考Oracle官方文档。

4.如何修改Oracle数据库的最大连接数？（63）

alter system set processes = 300 scope = spfile;

（要以sys身份登录才能进行修改，不然会提示权限不足）

修改initSID.ora，将process加大，重启数据库。

5.归档模式？非归档模式？

    归档模式有效防止instance和disk的故障。在数据库故障恢复中是不可或缺的。初始默认为非归档模式，需要手动打开归档模式。

6.如何加密Oracle的存储过程？（144）

create or replace procedure testCCB(i in number) as begin dbms_output.put_line('输入参数是'||to_char(i))； end； SQL>wrap iname=a.sql； PL/SQL Wrapper： Release 8.1.7.0.0 - Production on Tue Nov 27 22：26：48 2001 Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved。 Processing AA.sql to AA.plb 运行AA.plb SQL> @AA.plb ； 

7.回滚？回滚段的争用情况？（146）

    一般执行了DDL语句就无法回滚。（create、alert、drop）    conmmit之后就无法rollback；

如何回滚段的争用情况？

select name, waits, gets, waits/gets "Ratio" from v$rollstat C, v$rollname D where C.usn = D.usn；

8.如何监控SGA中的命中率？监控其缓冲区的命中率？监控其重做缓存区的命中率？（150~154）

    内存分配（SGA和PGA） --system global area    SGA：是用于存储数据库信息的内存区，该信息为数据库进程所共享。它包含Oracle 服务器的数据和控制信息,它是在Oracle服务器所驻留的计算机的实际内存中得以分配，如果实际内存不够再往虚拟内存中写。

*如何监控 SGA 的命中率？

select a.value + b.value "logical_reads", c.value "phys_reads", round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat c where a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40；

*如何监控 SGA 中字典缓冲区的命中率？

select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio", (1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio" from v$rowcache where gets+getmisses <>0 group by parameter, gets, getmisses； 

*如何监控 SGA 中共享缓存区的命中率，应该小于1% ？

select sum(pins) "Total Pins", sum(reloads) "Total Reloads", sum(reloads)/sum(pins) *100 libcache from v$librarycache； select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent" from v$librarycache； 

*监控 SGA 中重做日志缓存区的命中率，应该小于1%

SELECT name, gets, misses, immediate_gets, immediate_misses, Decode(gets,0,0,misses/gets*100) ratio1, Decode(immediate_gets+immediate_misses,0,0, immediate_misses/(immediate_gets+immediate_misses)*100) ratio2 FROM v$latch WHERE name IN ('redo allocation', 'redo copy')；