Category Archives: 12c

Get the secondmax, again

Just bouncing on 2008/07/secondmax.

Another way of getting secondmax would be with an ordered collection. While collection methods like (n), first, last, count are not in SQL, I used PLSQL (within SQL)


WITH FUNCTION f (c sys.odcinumberlist, n NUMBER) RETURN number
IS BEGIN RETURN c (n); END;
SELECT 
  f(
    CAST(
      COLLECT(
        CAST(
          sal AS NUMBER
        ) ORDER BY sal DESC
      ) 
      AS SYS.odcinumberlist
    ), 
    2
  ) as secondmax
FROM emp; 

 SECONDMAX
----------
      3000

Another 12c syntax would be


SELECT sal secondmax
FROM emp
ORDER BY sal DESC
OFFSET 1 ROW
FETCH FIRST 1 ROW ONLY;

 SECONDMAX
----------
      3000

strings larger than 4000 in 12c

Back in Oracle 7, the maximum length for VARCHAR2 was 2000. In 11gR2, it is still 4000 for varchar2/char columns and for literals. Any attempt to use something larger will produce an infamous ora-910, ora-1704 or ora-1489 error.


SQL> create table t(x varchar2(5000));
create table t(x varchar2(5000))
                           *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> select 'x-
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
... 100 times 
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
' from dual;

select 'x-
       *
ERROR at line 1:
ORA-01704: string literal too long

SQL> select 'x'||
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 100 times 
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
'x' from dual;

select 'x'||
       *
ERROR at line 1:
ORA-01489: result of string concatenation is too long

In 12c it is possible to extends the varchar2 to 32k.

This is not the default and it is controlled by max_string_size


SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade quiet
ORACLE instance started.
Database mounted.
Database opened.
SQL> alter system set max_string_size=extended;
System altered.
SQL> @?/rdbms/admin/utl32k
SQL> shu immediate
Database closed.
Database dismounted.
SQL> startup quiet
ORACLE instance started.
Database mounted.
Database opened.

Now we can create, insert and select longer strings.


SQL> create table t(x varchar2(6000));
Table created.
SQL> desc t
 Name                  Null?    Type
 --------------------- -------- ----------------
 X                              VARCHAR2(6000)
SQL> insert into t values ('-
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-
... 100 times 
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1 row created.
SQL> insert into t values (
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 100 times 
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
1 row created.

Oracle 8i came with a bunch of issue (cannot index a varchar2(4000) column) because the datatype was really increased to 4000. In 12c, however, it is silently using clob.


SQL> select TABLE_NAME,COLUMN_NAME,SEGMENT_NAME from user_lobs where table_name='T';

TABLE_NAME COLUMN_NAM SEGMENT_NAME
---------- ---------- ------------------------------
T          X          SYS_LOB0000022083C00001$$

It also works for NVARCHAR2 and RAW (who wasn’t increased to 4000). But not for CHAR.

This is a smart move to provide larger text fields to developers. On the other hand, why limit it to 32k if it is a clob? VARCHAR(MAX) in SQL Server does not have such a limitation.


SQL> create table t(x varchar2(32768));
create table t(x varchar2(32768))
                           *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> select 'x'||
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
... 999 times 
'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'||
'x' from dual;
select
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long

old-hash, SHA-1, SHA-2/512

Until pretty recently, only the dubious unsalted proprietary algorithm was available to store Oracle passwords. A bunch of tool where at the time able to decode any 6-8 characters in no time, and the rainbow approach was to precalculate all possibles passwords for a specific user.

Those time are not really for away, only starting at Oracle 11g, you could have salted/case sensitive passwords. Salted means that Scott may have many different passwords keys for tiger.


 SQL> select spare4 from user$ where name='SCOTT';
SPARE4
----------------------------------------------------------------
S:96A5FF65BFF84D7AAC6F8F00879881E8506FE57F555E5BA2927B606DC4F1

SQL> alter user scott identified by tiger;

User altered.

SQL> select spare4 from user$ where name='SCOTT';
SPARE4
----------------------------------------------------------------
S:AE23FB94A462C44A75040CE3BA731E3EF08C4A270F5940491045CBCEF63C

Some users may have only the 10g version (password not changed after migrating to 11g), some may have the 11g version of both, and -who knows- some may have already have SHA-2/512 passwords. SHA2 has many advantages. The chance that 2 passwords provides exactly the same string are much lower than in SHA1 (collision) and it performs twice faster on 64 bits servers.


SQL> select username, password_versions from dba_users where username like 'U_;
USERNAME                       PASSWORD
------------------------------ --------
U1                             10G
U2                             11G
U3                             10G 11G
U4                             12C

Probably you never saw this unless you are in beta 12. But actually it is documented in the 11gR2 Documentation.

12C if a new SHA-2 based SHA-512 hash exists

Enterprise Manager command line interface

emcli has been around for a while, but in 12c the installation has never been easier

do not search on otn for the jar, go to

download with : Setup –>My Preferences –>Command line interface –> download

install with : java -jar emclikit.jar client -install_dir=/u01/app/oracle/emcli

configure with : emcli setup -url=https://precision.example.com:4901/em -username=sysman -password=sysmanpw -dir=/u01/app/oracle/emcli.

That’s it.

Let’s try


$ emcli get_targets
Status  Status           Target Type           Target Name                        
 ID                                                                               
1       Up               host                  precision.example.com              
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/e
                                               mgc                                
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/O
                                               CMRepeater                         
1       Up               j2ee_application      /EMGC_GCDomain/GCDomain/EMGC_OMS1/e
                                               mpbs                               
-9      n/a              metadata_repository   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
                                               ERVER/mds-sysman_mds               
-9      n/a              metadata_repository   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
                                               ERVER/mds-owsm                     
1       Up               oracle_apache         /EMGC_GCDomain/instance1/ohs1      
1       Up               oracle_apm            /EMGC_GCDomain/GCDomain/EMGC_OMS1/o
                                               racle.security.apm(11.1.1.3.0)     
1       Up               oracle_beacon         EM Management Beacon               
1       Up               oracle_database       LSC01                              
1       Up               oracle_database       LSC02                              
1       Up               oracle_database       LSC03                              
1       Up               oracle_database       LSC05                              
1       Up               oracle_database       LSC04                              
1       Up               oracle_dbsys          LSC01_sys                          
1       Up               oracle_dbsys          LSC03_sys                          
1       Up               oracle_dbsys          LSC04_sys                          
1       Up               oracle_dbsys          LSC02_sys                          
1       Up               oracle_em_service     EM Console Service                 
1       Up               oracle_em_service     EM Jobs Service                    
1       Up               oracle_emd            precision.example.com:1830         
1       Up               oracle_emrep          Management Services and Repository 
-9      n/a              oracle_home           oms12g1_8_precision                
-9      n/a              oracle_home           WebLogicServer10_3_5_0_0_precision 
-9      n/a              oracle_home           OraDb10g_home1_5_precision         
-9      n/a              oracle_home           OraDb11g_home1_1_precision         
-9      n/a              oracle_home           agent12g1_13_precision             
-9      n/a              oracle_home           webtier12g1_24_precision           
-9      n/a              oracle_ias_farm       EMGC_GCDomain                      
1       Up               oracle_listener       LISTENER_precision.example.com     
1       Up               oracle_oms            precision.example.com:4890_Manageme
                                               nt_Service                         
1       Up               oracle_oms_console    precision.example.com:4890_Manageme
                                               nt_Service_CONSOLE                 
1       Up               oracle_oms_pbs        precision.example.com:4890_Manageme
                                               nt_Service_PBS                     
-9      n/a              weblogic_domain       /EMGC_GCDomain/GCDomain            
1       Up               weblogic_j2eeserver   /EMGC_GCDomain/GCDomain/EMGC_OMS1  
1       Up               weblogic_j2eeserver   /EMGC_GCDomain/GCDomain/EMGC_ADMINS
                                               ERVER 

All green (one could argue command line has no color)