1. How to check the session time zone ?

=======================================

Answer

——

The new SESSIONTIMEZONE built-in SQL function returns the value of the current session’s time zone.

SQL> SELECT SESSIONTIMEZONE FROM DUAL;

SESSIONTIMEZONE

—————

+01:00

2. How to set the session time zone ?

=====================================

Answer

——

The session time zone can be set to:

– O/S local time zone

– Database time zone

– An absolute offset

– A named region

1. The first method consists to use one of the following ALTER SESSION SET TIME_ZONE statements:

SQL> ALTER SESSION SET TIME_ZONE = local;

SQL> ALTER SESSION SET TIME_ZONE = dbtimezone;

SQL> ALTER SESSION SET TIME_ZONE = ‘-05:00’;

SQL> ALTER SESSION SET TIME_ZONE = ‘Europe/London’;

2. As an alternative, the operating system environment variable ORA_SDTZ can also be used to set the session time zone:

ORA_SDTZ = ‘OS_TZ’ | ‘DB_TZ’ | ‘[+ | -] hh:mm’ | ‘timezone_region’

Example:

$ ORA_SDTZ=’OS_TZ’

$ export ORA_SDTZ

$ ORA_SDTZ=’-05:00′

$ export ORA_SDTZ

3. How to check the database time zone ?

========================================

Answer

——

The DBTIMEZONE SQL function returns the value of the database time zone.

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIMEZONE

————–

Europe/Lisbon

Note that the database timezone is also included in both database_properties and props$ views:

SQL> SELECT property_name, property_value

FROM database_properties

WHERE property_name=’DBTIMEZONE’;

and

SQL> SELECT name, value$

FROM props$

WHERE name=’DBTIMEZONE’;

Be aware that you should not rely on these views because in case of db time zone change, these views reflect the new db time zone too early: they should reflect

it only after database shutdown and restart.

4. How to set the database time zone ?

======================================

Answer

——

Note that the database time zone is only relevant for TIMESTAMP WITH LOCAL TIME ZONE columns.

1. At creation time

SQL> CREATE DATABASE …

SET TIME_ZONE=’Europe/London’;

If not specified with the CREATE DATABASE statement, the database time zone defaults to the server抯 O/S timezone offset.

2. After database creation, use the ALTER DATABASE SET TIME_ZONE statement and then shut down and restart the database.

SQL> ALTER DATABASE SET TIME_ZONE = ‘-05:00’;

SQL> ALTER DATABASE SET TIME_ZONE = ‘Europe/Lisbon’;

The change will not take effect until the database is bounced.

5. How to list the valid time zone regions ?

============================================

Answer

——

SQL> SELECT * FROM v$timezone_names;

TZNAME TZABBREV

—————————— ———-

Pacific/Tahiti LMT

Poland LMT

US/Pacific PST

Europe/Zurich CET

…

The result will depend on which timezone file is currently in use.

See Q&A 7-8 for further details.

6. How to retrieve the time zone offset corresponding to a time zone region ?

======================================================

The new 9i TZ_OFFSET() SQL function returns the time zone offset displacement to the input time zone region.

SQL> SELECT TZ_OFFSET(‘Europe/London’) FROM DUAL;

TZ_OFFS

——-

+01:00

The returned offset depends on the date this statement is executed. For example, in the ‘US/Pacific’ time zone, it may return ‘-07:00’ or ‘-08:00’ whether daylight saving is in effect or not.

SQL> SELECT TZ_OFFSET(SESSIONTIMEZONE), TZ_OFFSET(DBTIMEZONE) FROM DUAL;

returns the time zone offset corresponding to the time zone set for session and database.

TZ_OFFS TZ_OFFS

——- ——-

+01:00 +00:00

7. How to use a larger set of defined time zones ?

==================================================

2 different time zone files contain for each zone the offset from UTC,the transition times for daylight savings and abbreviation :

– ORACLE_HOME/oracore/zoneinfo/timezone.dat

This is the default and contains the most commonly used time zones.

This is the smallest file.

– ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

This file contains the larger set of defined time zones and can be used

if you require time zones that are not defined in the default time zone file.

To enable the use of this larger time zone data file :

1. Shutdown the database

2.1 On UNIX platforms :

set the ORA_TZFILE environment variable to the full pathname of

the location for the timezlrg.dat. By default, this should be

$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat:

$ setenv ORA_TZFILE $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

2.2 On Windows systems :

You should add an ORA_TZFILE registry subkey for each of your Oracle Homes

with the Windows Registry Editor:

Start -> Run…

Type “regedit”, and click “ok”

Add or edit the following registry entry:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\ORA_TZFILE

where ID is the unique number identifying the Oracle home.

Set the ORA_TZFILE to the full pathname of the location for the timezlrg.dat: By default, the value should be %ORACLE_HOME%\oracore\zoneinfo\timezlrg.dat

3. Restart the database

Once the larger timezlrg.dat is used, it must continue to be used unless the user is sure that none of the non-default zones are used for data that is stored in the database.

Also, all databases that share information should use the same time zone data file.

Be aware that you can neither create nore alter these timezone definition files !

8. Which timezone-set is currently used ?

=========================================

Beside checking the UNIX ORA_TZFILE parameter or the Windows registry subkey,you can easily check whether you are using the large or the default timezone file with the following query:

SQL> SELECT COUNT(*) FROM v$timezone_names;

If the default-smallest timezone region file is in use, it will return:

COUNT(*)

———-

616 (in 9.0.1.x and 9.2.x)

or

COUNT(*)

———-

1250 (in 9.0.1.x and 9.2.x)

if the largest file is in use.

9. Can customers rely on Oracle time zones ?

============================================

Timezone.dat and timezlrg.dat are based on information maintained by the U.S. Navy Observatory.

今天下午，在一天RHEL as 5.4的PC server上，安装完Oracle软件之后，利用DBCA创建oracle 10g数据库的时候，报错：

触发ORA-00600：[keltnfy-ldmInit]，[46]类似这样的错误。

以前，在红帽Linux系统上创建Oracle数据库向来都是轻车熟路的。今天，怎么回事儿？

不得已，Google，很快定位到问题的根源:

我在安装Oracle软件之后，创建数据库之前，修改了主机的/etc/hosts配置文件中的关于hostname的配置信息。

于是，修改/etc/hosts文件与/etc/sysconfig/network同步更新hostname。

然后，不管是执行Net Configuration Assistant配置网络，还是利用Database Configuration Assistant来建库，均无报错！

千万记得，在配置主机IP，或者修改主机名的时候，稍有不慎，往往会给Oracle数据库带来麻烦！

不可否认SQL语句的功能非常强大，但是，在大多数的时候，我们还是会在使用SQL语句的过程中，调用数据库内嵌的函数，通过函数来强化我们SQL语句的功能，以达到我们预期的目的，尤其是在满足一些应用程序对统计报表的需求情况下。

首先，简单说说单行函数（Single-Row Functions）。在Oracle数据库里，单行函数大概可以细分为Character function，Number function，Date function,Conversion function,General function。其实，Oracle数据库支持的函数足够多了，在这里不一一列举具体用法和使用规则了，我们应该清晰的一个概念就是：单行函数可以多次嵌套调用。

另外，大概说一下分组函数（Group Functions）。所谓分组函数跟单行函数的区别就是：单行函数是对数据源中的记录逐条分别作为函数的输入参数来处理，最终得到函数处理的结果。而分组函数就是把数据源中的记录按照某种特定的需求以分组的形式来作为函数的输入参数处理，最终得到处理的结果自然就是以组的形式区分开来。

来简单看一下，使用分组函数的语法规则：

SELECT    column, group_function(column)
FROM      table
[WHERE    condition]
[GROUP BY group_by_expression]
[ORDER BY column];

我们在使用分组函数的时候需要注意的几个地方就是：

①All columns in the SELECT list that are not in group functions must be in the GROUP BY clause.凡是出现在查询列表中的没有应用分组函数的字段必须要出现在GROUP BY子句中，否则报错。

②The GROUP BY column does not have to be in the SELECT list.反之，出现在GROUP BY从句中的字段并非一定要出现在查询列表中。并且，GROUP BY从句中可以出现排序列表，即可以对多列进行分组排序。

③You cannot use the WHERE clause to restrict groups.You cannot use group functions in the WHERE clause.You use the HAVING clause to restrict groups.我们不能在WHERE从句中对分组做限制，也不能在WHERE从句使用组函数，而应该通过在HAVING子句中使用分组函数来实现这一目的。

④ORDER BY从句同样可以使用分组函数，而且该分组函数同查询列表中使用的分组函数以及HAVING从句中的三类分组函数，均可以不同。

⑤分组函数只能嵌套调用一次。

在上一篇日志里：我们了解了怎么使用LogMiner工具的第一种使用方法。接着，我们来学习下LogMiner工具的第二种使用方式：
在使用LogMiner之前，我们要确定相关的参数，也就是简单配置一下LogMiner工具使用的“环境”：
①配置一个用于将来存放LogMiner分析日志文件（online redo logs,archived logs）的结果路径和文件，这个路径和文件是映射在文件系统上的。这个文件可以认为是一个字典文件，但是这个字典文件跟数据库的数据字典文件完全是两码子事儿。对应的初始化参数是utl_file_dir，默认情况下，该参数没有配置。
②在调用LogMiner工具之前，在文件系统上生成这个字典文件。
③接下来调用LogMiner工具的方法跟第一种方法就完全类似了。
接下来，进入正式调用流程：
I 首先：以SYS用户登录数据库，检查utl_file_dir初始化参数是否设置，或者设置是否正确，捎带看下数据库版本，以及操作系统版本：

SQL> show user;
USER is "SYS"
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> !uname -a
Linux Oracle10g 2.6.9-55.ELsmp #1 SMP Wed May 2 14:28:44 EDT 2007 i686 i686 i386 GNU/Linux

确认初始化参数文件，utl_file_dir,并修改之，重启数据库使之生效，因为该参数不能直接动态修改：

SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string
SQL> !mkdir -p /u01/app/logmnr
SQL> alter system set utl_file_dir='/u01/app/logmnr/' scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area  343932928 bytes
Fixed Size                  1219304 bytes
Variable Size             125830424 bytes
Database Buffers          209715200 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SQL> show parameter utl_file_dir
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /u01/app/logmnr/

II 好了，前期准备工作就绪。其次，开始调用dbms_logmnr_d函数包在操作系统生生成”字典文件”:

SQL> exec dbms_logmnr_d.build(dictionary_location=>'/u01/app/logmnr/',
dictionary_filename=>'dictionary.ora');
PL/SQL procedure successfully completed.
SQL>

至此，可以到操作系统上对应的路径上去瞅两眼，验证一下：

SQL> !
[oracle@Oracle10g ~]$ cd /u01/app/logmnr/ && ll
total 23724
-rw-r--r--  1 oracle oinstall 24257361 May 24 23:52 dictionary.ora
[oracle@Oracle10g logmnr]$

看到，达到预期目的。
III 然后，查看数据库当前日志文件情况，并开始调用LogMiner工具：

[oracle@Oracle10g logmnr]$ exit
exit
SQL> set linesize 120
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          8   52428800          1 YES INACTIVE                738316 24-MAY-10
         2          1          7   52428800          1 YES INACTIVE                718138 24-MAY-10
         3          1          9   52428800          1 NO  CURRENT                 758625 24-MAY-10
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
         1          1          8   52428800          1 YES INACTIVE                738316 24-MAY-10
         2          1         10   52428800          1 NO  CURRENT                 759039 24-MAY-10
         3          1          9   52428800          1 YES ACTIVE                  758625 24-MAY-10
SQL> col member for a50
SQL> select * from v$logfile;
    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         3         ONLINE  /u01/app/oradata/helen/redo03.log                  NO
         2         ONLINE  /u01/app/oradata/helen/redo02.log                  NO
         1 STALE   ONLINE  /u01/app/oradata/helen/redo01.log                  NO
SQL>

上述操作中，发现当前日志组为第三组，强制日志切换到第二组，并验证日志成员为/u01/app/oradata/helen/redo02.log，这个信息时尤为重要的，因为在下面的操作中，我们要在数据库里另起一个会话，做DML操作，数据库会把日志记录到该日志文件里。这些信息供我们将来分析用。
IV 新开会话，做DML操作，产生日志信息，添加当前日志文件，并开始调用LogMiner分析日志文件：
新开回话：

SQL> show user;
USER is "HR"
SQL> select * from test2;
        ID
----------
         1
         2
SQL> delete from test2 where id=2;
1 row deleted.
SQL> insert into test2 values(3);
1 row created.
SQL>

返回原来的会话，执行下述操作：

SQL> exec dbms_logmnr.add_logfile
(logfilename=>'/u01/app/oradata/helen/redo02.log',
options=>dbms_logmnr.removefile);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.add_logfile
(logfilename=>'/u01/app/oradata/helen/redo02.log',
options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
SQL> exec dbms_logmnr.start_logmnr
(dictfilename=>'/u01/app/logmnr/dictionary.ora');
PL/SQL procedure successfully completed.
SQL> set linesize 160
SQL> col sql_redo for a40
SQL> col sql_undo for a40
SQL> col operation for a10
SQL> col table_name for a5
SQL> select table_name,sql_redo,sql_undo,operation from v$logmnr_contents where username='HR';

TABLE SQL_REDO                                 SQL_UNDO                                 OPERATION
----- ---------------------------------------- ---------------------------------------- ----------
      set transaction read write;                                                       START
TEST2 delete from "HR"."TEST2" where "ID" = '2 insert into "HR"."TEST2"("ID") values (' DELETE
      ' and ROWID = 'AAANB9AAEAAAAGHAAB';      2');

TEST2 insert into "HR"."TEST2"("ID") values (' delete from "HR"."TEST2" where "ID" = '3 INSERT
      3');                                     ' and ROWID = 'AAANB9AAEAAAAGFAAA';

SQL>

至此，达到最终目的。

在我们写SQL语句的时候，会经常用到order by子句来对查询结果进行排序。在这里对order by子句的使用，作一个简单的总结：

1 SQL语句中，order by从句永远并且一定是放在所有从句的最后位置处，也就是说，一个复杂的SQL语句中，可能会包含诸如group by子句,Where 子句等等。但是，不管有多少个从句跟在FROM关键字后面，order by从句一定是出现在所有子句的最后端，因为order by是对所有查询结果集的排序。如：

SQL> SHOW USER;
USER is "HR"
SQL> SELECT employee_id,last_name
  2  FROM employees
  3  WHERE employee_id>200
  4  ORDER BY employee_id
  5  ;
EMPLOYEE_ID LAST_NAME
----------- -------------------------
        201 Hartstein
        202 Fay
        203 Mavris
        204 Baer
        205 Higgins
        206 Gietz
6 rows selected.
SQL>

可以看到：ORDER BY 子句是出现在整个SQL语句的最后位置处。否则，ORDER BY子句不放在该SQL语句的最后，就会报错！
2  在oracle 数据库中，order by从句默认是升序（ASCend)排序的，如果想做降序排序，那么就需要显示声明关键字（DESCend).在升序排序的时候，Oracle对于NULL值的处理是放在最后的。因为Oracle认为NULL值是一个未知的，未分配的值，作为无穷大∞ 对待，自然升序的时候就放在最后，反之，降序排列时就放在首位。如：

SQL> SELECT employee_id,last_name,manager_id
  2  FROM employees
  3  ORDER BY manager_id DESC
  4  ;
EMPLOYEE_ID LAST_NAME                 MANAGER_ID
----------- ------------------------- ----------
        100 King
        206 Gietz                            205
        202 Fay                              201
......

可以验证：查询结果集中，员工编号为100的员工是BOSS，没有manager，即其MANAGER_ID为NULL，降序排列的时候，自然会作为第一条返回记录出现在整个结果集中。
3 order by可以用于查询列表中的字段名，也可用于字段别名、表达式、字段名在列表中出现的顺序，如：

SQL> SELECT employee_id,last_name,salary*12 "Annual_Salary"
  2  FROM employees
  3  where employee_id>200
  4  order by salary*12
  5  ;
EMPLOYEE_ID LAST_NAME                 Annual_Salary
----------- ------------------------- -------------
        202 Fay                               72000
        203 Mavris                            78000
        206 Gietz                             99600
        204 Baer                             120000
        205 Higgins                          144000
        201 Hartstein                        156000
6 rows selected.

上例是按表达式排序，看一个按字段别名排序的例子：

SQL> SELECT employee_id,last_name,salary*12 Annual_Salary
  2  FROM employees
  3  WHERE employee_id>200
  4  ORDER BY Annual_Salary
  5  ;
EMPLOYEE_ID LAST_NAME                 ANNUAL_SALARY
----------- ------------------------- -------------
        202 Fay                               72000
        203 Mavris                            78000
        206 Gietz                             99600
        204 Baer                             120000
        205 Higgins                          144000
        201 Hartstein                        156000
6 rows selected.
SQL>

注：在该示例SQL语句中，如果salary*12的别名Annual_Salary，加了双引号”Annual_Salary”,那么按别名排序的时候也必须带有双引号：
order by “Annual_Salary”
接下来，再看一个按字段在查询列表中出现的先后顺序排列的例子：

SQL> SELECT employee_id,last_name,salary*12 Annual_Salary
  2  FROM employees
  3  WHERE employee_id>200
  4  ORDER BY 3;
EMPLOYEE_ID LAST_NAME                 ANNUAL_SALARY
----------- ------------------------- -------------
        202 Fay                               72000
        203 Mavris                            78000
        206 Gietz                             99600
        204 Baer                             120000
        205 Higgins                          144000
        201 Hartstein                        156000
6 rows selected.
SQL>

4 order by 子句甚至可以是对那些没有出现在查询列表中出现的字段进行排序：ORDER BY子句只对查询结果进行排序，而不关心按排序的字段是否出现在查询列表中。如：

SQL> SELECT last_name,salary*12 Annual_Salary
  2  FROM employees
  3  WHERE salary*12>150000
  4  ORDER BY employee_id
  5  ;
LAST_NAME                 ANNUAL_SALARY
------------------------- -------------
King                             288000
Kochhar                          204000
De Haan                          204000
Russell                          168000
Partners                         162000
Hartstein                        156000
6 rows selected.
SQL>
----------------------------------------------
SQL> SELECT employee_id,last_name,salary*12 Annual_Salary
  2  FROM employees
  3  WHERE salary*12>150000
  4  ORDER BY employee_id
  5  ;
EMPLOYEE_ID LAST_NAME                 ANNUAL_SALARY
----------- ------------------------- -------------
        100 King                             288000
        101 Kochhar                          204000
        102 De Haan                          204000
        145 Russell                          168000
        146 Partners                         162000
        201 Hartstein                        156000
6 rows selected.
SQL>

可以验证，其实，这两个SQL语句的排序效果是同样的！
5 ORDER BY子句中，同样支持对多个字段排序，如果需要对不同字段进行排序的方式（升序或者降序）不同，则需要显示声明。如：

SQL> SELECT employee_id,manager_id,department_id
  2  FROM employees
  3  WHERE employee_id>200
  4  ORDER BY manager_id ASC,department_id DESC
  5  ;
EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
        201        100            20
        205        101           110
        204        101            70
        203        101            40
        202        201            20
        206        205           110
6 rows selected.
SQL>

ORDER BY 子句的用法很灵活，我们可以根据不同的应用场景，写出满足不同需求的排序SQL语句。