Oracle 使用技巧

1、查看Oracle 版本

通过对存放版本信息的视图V$VERSION 进行检索操作，返回Oracle 版本信息。

2、查看表所占用的空间

对DBA_TABLES 表进行检索操作，返回该表中的表名（TABLE_NAME）、记录数（NUM_ROWS）、和所占空间（BLOCKS）。进行BLOCKS*8/1024/1024运算，得到以MB为单位的输出值。8表示当前Oracle 的块大小。

补充：查询当前Oracle块大小

           //查询实例的db_block_size值（为8192 bytes）

           SELECTVALUE FROM V$PARAMETER WHERE NAME=‘db_block_size’;

3、查看Oracle 最大连接数

SHOW PARAMETER PROCESSES;

SELECT SESSIONS_MAX, SESSIONS_CURRENT, SESSIONS_HIGHWATER,USERS_MAX

FROM V$LINCENSE;

说明：SESSIONS_HIGHTWATER 的值即为当前Oracle 系统曾经到达的最大连接数

4、查看并统计当前活动用户

SELECT USERNAME, COUNT(USERNAME) FROM V$SESSION GROUP BYUSERNAME;

说明：V$SESSION 视图储存当前正在连接库的信息。

5、查看表空间占用空间情况

将DBA_FREE_SPACE和 DBA_DATA_FILES 进行连接查询

SELECT B.TABLESPACE_NAME, B.BYTES, SUM(A.BYTES)

FROM DBA_FREE_SP­­ACE A, DBA_DATA_FIES B WHEREA.FILE_ID = B.FILE_ID

GROUP BY B.TALBESPACE_NAME, B.FILE_ID, B.BYTES

ORDER BY B.FILE_ID;

6、按中文拼音/笔画/部首排序输出

Oracle 9i之前，支持的中文是按照二进制编码进行排序的。Oracle 10g中提供了按照拼音、部首、笔画排序功能。

         NLSSORT(SNAME, ‘NLS_SORT=SCHINESS_STROKE_M’)

l  SCHINESS_RADICAL_M 按照部首（第一顺序）、笔画（第二顺序）排序

l  SCHINESS_STROKE_M 按照笔画（第一顺序）、部首（第二顺序）排序

l  SCHINESS_PINYIN_M 按照拼音排序

7、修改数据库默认日期格式

ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD HH24:MI:SS’

8、自动生成查询结果序号（ROWNUM）

9、查看数据文件放置的路径

COL FILE_NAME FORMAT A50;

SELECT FIFLE_ID, TALBESPACE_NAME, BATES/1024/1024 FSIZE,FILE_NAME

FROM DBA_DATA_FILES ORDER BY FILE_ID

说明：COL FILE_NAME FORMAT A50 用于设置输出格式，即将列FILE_NAME的最大输出限制在50个字符以内。

10、将查询结果导出到文本文件

使用SPOOL 语句

11、跟踪SQL 语句执行

SET AUTOTRACE ON

补充：使用 SET AUTOTRACE ON 选项需要一定的权限。

12、插入全年日期

CREATE TABLE BSYEAR(D DATE);

INSERT INTO BSYEAR

SELECT TO_CHAR(‘20140101’,‘YYYYMMDD’) + ROWNUM – 1

FROM ALL_OBJECTS

WHERE ROWNUM <=TO_CHAR(TO_DATE(‘20121231’, ‘YYYYMMDD’), ‘DDD’)

13、输出表中某列第N大的记录

SELECT * FROM

(SELECT S.NO, S.NAME, S.AGE, S.BIRTH, S.DPT, DENSE_RANK()

OVER (ORDER BY AGE DESC) RANK)

FROM STUDENTS WHERE RANK = 3

补充：DENSE_RANK() 函数生成的序列从1开始，往后累加，每次ORDER BY 表达式的值发生变化时，该序列也随之增加。

14、返回记录行的物理地址（ROWID）

说明：前6位表示数据对象编号，第7~9 位表示相对表中间的数据文件号，第10~15位表示这条记录在文件中的第几个BLOCK（块）中。

15、查找列存在的重复值得记录

16、删除重复值得记录

17、加密存储过程

使用WRAP 工具，加密后文件后缀”pld”

18、移动数据库文件

a)        SELECT NAME FROM V$DATEFILE; 查询数据文件位置

b)        以管理员身份登录

c)        SHUTDOWN IMMEDIATE 关闭数据库

d)        复制数据库文件

e)        STARTUP MOUNT 重新启动数据

f)         ALTER DATABASE

RENAME FILE ‘原数据库文件路径’ TO ‘新数据库文件路径’

 

本内容摘自《Oracle 数据库编程经典300例》