About Oracle's NLS_DATE_FORMAT

NLS_DATE_FORMAT is used if a date format mask is not specified in application code. The effective NLS_DATE_FORMAT is determined by the following (in order of precedence):

1. Session NLS_DATE_FORMAT (via ALTER SESSION command)
2. Client side NLS_DATE_FORMAT (from client environment variables/registry settings)
3. Instance NLS_DATE_FORMAT (from init.ora file)
4. Database NLS_DATE_FORMAT

Session NLS_DATE_FORMAT is set to client side NLS_DATE_FORMAT (explicit or implicit) ONLY if NLS_LANG is set. Another words, if NLS_LANG is set, then session NLS_DATE_FORMAT will be taken from the client. If clent NLS_LANG is set and client NLS_DATE_FORMAT is not, session NLS_DATE_FORMAT will default to DD-MON-YY (and that was exactly what you experienced). If NLS_LANG is not specified on the client side, NLS_DATE_FORMAT will be taken from instance NLS_DATE_FORMAT which is NLS_DATE_FORMAT from INIT.ORA. If NLS_DATE_FORMAT is not set in INIT.ORA, session NLS_DATE_FORMAT will default to DD-MON-YY.

Get the SQL using ORA_SQL_TXT via Trigger

From 9i，you can using the system event ORA_SQL_TXT to get the SQL by Trigger.

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

SQL> CREATE TABLE T_LOG (EXECUTE_DATE DATE, SQL_STATMENT CLOB);

SQL> CREATE OR REPLACE TRIGGER T
2 BEFORE INSERT OR UPDATE OR DELETE ON T
3 FOR EACH ROW
4 DECLARE
5 V_SQL_OUT ORA_NAME_LIST_T;
6 V_NUM NUMBER;
7 V_SQL_STATMENT VARCHAR2(32767);
8 BEGIN
9 V_NUM := ORA_SQL_TXT(V_SQL_OUT);
10 FOR I IN 1 .. V_NUM LOOP
11 V_SQL_STATMENT := V_SQL_STATMENT || V_SQL_OUT(I);
12 END LOOP;
13 INSERT INTO T_LOG (EXECUTE_DATE, SQL_STATMENT)
14 VALUES (SYSDATE, V_SQL_STATMENT);
15 END;
16 /

SQL> INSERT INTO T VALUES (1, 'A');

SQL> INSERT INTO T (ID) VALUES (2);

SQL> UPDATE T SET NAME = 'B' WHERE ID = 2;

SQL> DELETE T;

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

SQL> SELECT * FROM T_LOG;

EXECUTE_DATE SQL_STATMENT
------------------- -----------------------------------------------
2007-09-13 22:55:35 INSERT INTO T VALUES (1, 'A')
2007-09-13 22:55:46 INSERT INTO T (ID) VALUES (2)
2007-09-13 22:55:54 UPDATE T SET NAME = 'B' WHERE ID = 2
2007-09-13 22:56:00 DELETE T
2007-09-13 22:56:00 DELETE T

After 9206，Oracle no more support this function in trigger for DML.just only support in system event.

--DML触发器中使用ORA_SQL_TXT函数，这个函数返回NULL，而且传入的OUT变量将不会被初始化。

From yangtinkun

A Perl script used to load a remote file by FTP.

sub FTP_Get_File {
  
    my ($hostname,$remote_file)=@_;
    my $filename = basename($remote_file);
    my $ftp;

    $ftp = Net::FTP->new($hostname, Debug => 0)
        or die "Cannot connect to $hostname: $@";
    #Please modify the login 
    $ftp->login("ora9i",'ora9i') or die "Cannot login ", $ftp->message;
   
    #$ftp->cwd("$remote_file") or die "Cannot change working directory ", $ftp->message;
    $ftp->get($remote_file,$filename)
        or die "get the romote file $remote_file failed ", $ftp->message;
    $ftp->quit;
}

 

How to do a full database backup automatically by RMAN -- shell script show

#!/bin/ksh
# function: backup database(cdmaetl) or archive with rman
# usage: crontab on AIX

#env
export ORACLE_SID=cdmaetl
#export SHELL=/usr/bin/ksh
export ORACLE_HOME=/ora9i/OraHome1
export NLS_LANG=american_america.zhs16gbk
export PATH=$PATH:$ORACLE_HOME/bin
#variable
RMAN=$ORACLE_HOME/bin/rman
SQLPLUS=$ORACLE_HOME/bin/sqlplus
TEE=/usr/bin/tee

LOGFILE=/ora9i/logs/rmanback.log

#dictionary structure
DBDEST=/oradata/backup/rmanbak/${ORACLE_SID}/datafiles
CTLFILE=/oradata/backup/rmanbak/${ORACLE_SID}/ctlfiles/${ORACLE_SID}_ctl_`date +%Y%m%d%H%M`.ctl

#start running RMAN to backup database and redo log, also have a log for those actions
echo "----------------backup start----------------------" > $LOGFILE
date
$RMAN <<EOF $TEE -a $LOGFILE
connect target

#allocate channel for backup, and only bak database
Run{
allocate channel c1 type disk format '$DBDEST/%U_%s.bak' maxpiecesize = 2000M;
backup database filesperset = 4;
backup archive log delete input;

}

#save just only one bak
delete noprompt obsolete redundancy = 1 device type disk;
exit;
EOF

#start bak control file
$SQLPLUS "/ as sysdba" <<EOF $TEE -a $LOGFILE
alter database backup controlfile to '$CTLFILE';
exit
EOF
date
echo "-----------------backup end-----------------------" >> $LOGFILE

How to switch the Autotrace and Explain Plan functions of Oracle

$cd $ORACLE_HOME/sqlplus/admin
$ls
$sqlplus /nolog
sql> @connect sys/passwd as sysdba
sys@ora9i> @plustrce.sql
sys@ora9i> grant plustrace to public;
sys@ora9i> exit
$ cd $ORACLE_HOME/rdbms/admin
sql> @connect sys/passwd as sysdba
sys@ora9i> @utlxplan.sql
sys@ora9i> exit

 

SQL> set autotrace on
SQL> select * from all_users where rownum<3;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 28-SEP-08
SYSTEM                                  5 28-SEP-08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COUNT (STOPKEY)
   2    1     NESTED LOOPS
   3    2       NESTED LOOPS
   4    3         TABLE ACCESS (FULL) OF 'USER$'
   5    3         TABLE ACCESS (CLUSTER) OF 'TS$'
   6    5           INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)
   7    2       TABLE ACCESS (CLUSTER) OF 'TS$'
   8    7         INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE)

 

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        717  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> explain plan for select * from all_objects where rownum<3;

 

         Explained.

SQL> select * from table(dbms_xplan.display);

...