oracle 12cr2 plugable database glogin.sql

参考文献
http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2013/07/oracle-database.html

COLUMN OTHER_PLUS_EXP FORMAT A200
COLUMN PLAN_PLUS_EXP  FORMAT A200
SET NUMWIDTH 13
SET LINESIZE 1000
SET LONG 40000
SET LONGCHUNKSIZE 40000
SET PAGESIZE 50000
SET SERVEROUTPUT ON
SET TAB OFF
SET TRIMSPOOL ON
set feed on
--set autotrace traceonly
set timing on
--set hist on
DEFINE_EDITOR=nano
set sqlp "_USER'@'_CONNECT_IDENTIFIER> "
[oracle@centos7 ~]$ docker exec -it orcl_12cr2 /bin/bash
[oracle@609a69bc0b21 ~]$ vim
[oracle@609a69bc0b21 ~]$ find / -name *login*.sql -type f 2>/dev/null
/mnt/11gr2/share/login/glogin.sql
/opt/oracle/product/12.2.0.1/dbhome_1/sqlplus/admin/glogin.sql
[oracle@609a69bc0b21 ~]$ vim /opt/oracle/product/12.2.0.1/dbhome_1/sqlplus/admin/glogin.sql
[oracle@609a69bc0b21 ~]$ sqlplus sys/ORACLE_PWD as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Dec 23 14:49:53 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SYS@ORCL> 
SYS@ORCL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@609a69bc0b21 ~]$ sqlplus sys/ORACLE_PWD@pdb1 as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Dec 23 14:50:22 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SYS@pdb1> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

sql oracle test


SELECT
    *
FROM
    (SELECT LEVEL AS rn FROM dual CONNECT BY LEVEL <=10)
MODEL
DIMENSION BY (rn)
MEASURES(
            1 AS val_asc
            ,CAST(NULL AS VARCHAR2(4000)) AS ope_asc
            ,row_number () OVER (ORDER BY rn DESC) AS rnn
            ,MAX(rn) OVER () AS val_desc
            ,CAST(NULL AS VARCHAR(4000)) AS ope_desc
            )
RULES(
        val_asc[rn] ORDER BY rn ASC = nvl(val_asc[cv() - 1],1) * cv(rn)
        ,ope_asc[rn] ORDER BY rn ASC = to_char(nvl(ope_asc[cv() - 1],NULL)) || CASE WHEN to_char(nvl(ope_asc[cv() - 1],NULL)) IS NULL THEN NULL ELSE ' * ' END || to_char(cv(rn))
        ,val_desc[rn] ORDER BY rnn DESC = nvl(val_desc[cv() - 1],1) * rnn[cv()]
        ,ope_desc[rn] ORDER BY rnn DESC = to_char(nvl(ope_desc[cv() - 1],NULL)) || CASE WHEN to_char(nvl(ope_desc[cv() - 1],NULL)) IS NULL THEN NULL ELSE ' * ' END || to_char(rnn[cv()])
        )
ORDER BY rn
;