Select from cdb_* views

There is no privileges strong enough for you to view all objects in all databases Let’s try as sys: SQL> select con_id, count(*) from cdb_objects group by con_id; CON_ID COUNT(*) ———- ———- 1 22749 3 22721 as non-sys SQL> create user c##u identified by ***; User created. SQL> grant create session, select any dictionary to… Continue reading Select from cdb_* views

changing container in plsql

One of the today’s challenge, since Oracle 12c deprecated non-cdb, is to make the dba scripts CDB-aware. If you are lucky enough to have no 11g around, you can mostly replace DBA_* by CDB_* OLD: SQL> select count(*) from dba_users; COUNT(*) ———- 121 NEW: non-cdb SQL> select con_id, count(*) from cdb_users group by con_id; CON_ID… Continue reading changing container in plsql

Audit pluggable database

In the old now-deprecated maybe-soon-desupported non-cdb infrastructure, AUDIT’ing was done right after connect / as sysdba. In single-tenant (or multi-tenant), things get complicated. Once again, the doc must be read at least twice 😉 If you issue an audit statement in the root, then the database performs auditing across the entire CDB, that is, in… Continue reading Audit pluggable database

Single Tenant duplicates

I recently reported an issue regarding single tenant. In old-time non-cdb, the SID used to be unique on a server. If you connect to srv01:port:sid, then you know where you connect. Unfortunately, this is no longer true. If for instance you have two database sid’s S01 and S02 with a pluggable P01, and both run… Continue reading Single Tenant duplicates

Published
Categorized as 12c Tagged

old jdbc driver generates ORA-28040

I read on note 401934.1 that Oracle 10gR2 jdbc clients are still supported with Oracle 12c. I have an application using an oracle10gr2 jdbc, and connection to 12c generates ORA-28040. Connection to 11gR2 works like a charm. O12.java import java.util.Properties; import java.sql.*; public class O12 { public static void main(String argv[]) throws SQLException { Properties… Continue reading old jdbc driver generates ORA-28040

Published
Categorized as 12c, java Tagged

Restricted sqlplus and sql_script

Yesterday I wrote about execute sql script from plsql. Connor then posted a 12c new feature that I overviewed https://twitter.com/connor_mc_d/status/811563990346186752 If you are still considering the 11g approach with sqlplus, you should read about restricted sqlplus If you run sqlplus -L -s -R 3 scott/tiger@db01 lot’s of OS-command are disabled SQL> get /etc/passwd SP2-0738: Restricted… Continue reading Restricted sqlplus and sql_script

column width change in 12c

In 11g I used to have 30 characters width in my dictionary SQL> select table_name, column_name from user_tab_columns; TABLE_NAME COLUMN_NAME —————————— —————————— BONUS COMM BONUS SAL BONUS JOB BONUS ENAME DEPT LOC DEPT DNAME DEPT DEPTNO EMP DEPTNO EMP COMM EMP SAL EMP HIREDATE TABLE_NAME COLUMN_NAME —————————— —————————— EMP MGR EMP JOB EMP ENAME EMP… Continue reading column width change in 12c

anonymous cypher suites for SSL (and a 12c pitfall)

If you configure your listener for encryption only, you do not really need authentication. It works pretty fine until 11.2.0.2, I wrote multiple posts on ssl. You add SSL_CLIENT_AUTHENTICATION=FALSE to your server sqlnet.ora and listener.ora and specify an “anon” cipher suite in your client. You do not need to validate the certificate, so a default… Continue reading anonymous cypher suites for SSL (and a 12c pitfall)

KeepAlive socket in 12c listener

A not uncommon issue with firewalls and listeners are timeouts. Your production database may be behind a firewall, you may connect from a remote location, even your Windows workstation may have some firewall activated, possibly you use ssh tunnels or TCPS. All those occasionally lead to timeouts and connection abortion, for instance ORA-03113 end-of-file on… Continue reading KeepAlive socket in 12c listener

12.1.0.2 on AIX

just released today http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html only in Enterprise Edition at the moment, and now available on HPUX, zLinux and AIX #oracle12c #db12102 is out for a bunch of platform #aix #os390 #hpux #zlinux — laurentsch (@laurentsch) November 17, 2014 This is the first and last patchset for 12cR1 #oracle 12.1.0.2 is the last patch set for… Continue reading 12.1.0.2 on AIX

to R1 or to R2

In the past, most of my customers skipped R1 releases. That is, 8.1.7 -> 9.2 -> 10.2 -> 11.2. SAP does the same. For the very first time SAP plans to go to 12.1.0.2 + some PSU in spring 2015. But only to avoid running out of support and without any fancy feature like Multitenant… Continue reading to R1 or to R2

select a.b.c.d.e from t a

I just learnt a new syntax. With 12.1.0.2, you can dive into your JSON document within sql, it looks a bit unusual to me SQL> CREATE TABLE t (b CLOB CHECK (b IS JSON)); Table created. SQL> insert into t(b) values (‘{“c”:{“d”:{“e”:1}}}’); 1 row created. SQL> select a.b.c.d.e from t a; C ———————- 1 Largely… Continue reading select a.b.c.d.e from t a

Published
Categorized as 12c Tagged

ssl version

I wrote about ssl version in jdbc thin yesterday The default version also no longer works for the thick client with 12c client and 11g Server. With 11gR2 : C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521))) TNS Ping Utility for 64-bit Windows: Version 11.2.0.4.0 OK (100 msec) with 12cR1 : C:> tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=SRV01)(PORT=1521))) TNS Ping Utility for 64-bit Windows:… Continue reading ssl version

dotNet transaction guard

also with ODP in 12c, you can check the commit outcome as in jdbc let’s create a table with a deferred primary key create table t (x number primary key deferrable initially deferred); Here an interactive Powershell Demo PS> [Reflection.Assembly]::LoadFile(“C:\oracle\product\12.1.0\dbhome_1\ODP.NET\bin\4\Oracle.DataAccess.dll”) GAC Version Location — ——- ——– True v4.0.30319 C:\Windows\Microsoft.Net\assembly\GAC_64\Oracle.DataAccess\v4.0_4.121.1.0__89b483f429c47342\Oracle.DataAccess.dll I first load the assembly. Some… Continue reading dotNet transaction guard

Transaction guard

Getting the logical transaction id in 12c will greatly simplify your error handling and enhance your business continuity in your application. In 11g and below, your java code use to look like try { insert into… } catch () { error_handling() } but one probably assumed the insert failed when it was committed (e.g. database… Continue reading Transaction guard

in memory option

Oracle 12cR1 patchset 1 is due this month and there is a new parameter that you can set to boost your performance. It is a bit of a SET “_FAST”=true parameter. The in memory parameter is part of the sga. It is not mandatory to size it correctly, even if you do not have enough… Continue reading in memory option

Published
Categorized as 12c Tagged

distinct listagg

One limitation in listagg, you cannot use DISTINCT. Okay, there are plenty of distinct-capable listagg workarounds, but to get the real listagg working, it is a bit of an headache With one listagg SELECT DEPTNO, LISTAGG (JOB, ‘,’) WITHIN GROUP (ORDER BY JOB) JOBS FROM ( SELECT DISTINCT DEPTNO, JOB FROM EMP) GROUP BY DEPTNO;… Continue reading distinct listagg

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… Continue reading Get the secondmax, again

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… Continue reading strings larger than 4000 in 12c

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… Continue reading old-hash, SHA-1, SHA-2/512

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… Continue reading Enterprise Manager command line interface

Ow yes it is a “c” !

Rumours about what would come after 8i (internet) 10g (grid) were around, but now it is official, there will be an Oracle Enterprise Manager 12c Read more : http://www.oracle.com/us/products/enterprise-manager/index.html And in the blogosphere http://orana.info