Monday, July 08, 2013

12c - SQL Plus new things....

SQL Plus is still my typical tool of choice to "talk" to Oracle - 25+ years and going...  Oracle Database 12c has introduced a few new things in this venerable old tool

Last Login Time

$ sqlplus /

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 7 13:53:15 2013

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

Last Successful login time: Wed Jul 03 2013 14:30:14 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

ops$tkyte%ORA12CR1>

By default, SQL Plus will display your last login time - if you don't want that, just use -nologintime.

More on invisible columns

I recently wrote about invisible columns and how they wouldn't be displayed via a DESCRIBE command.  That is - SQL Plus won't show them by default.  However, there is a SET command that will display them:

ops$tkyte%ORA12CR1> create table t
  2  ( x int,
  3    y int invisible
  4  );

Table created.

ops$tkyte%ORA12CR1> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)

ops$tkyte%ORA12CR1> show colinvisible
colinvisible OFF
ops$tkyte%ORA12CR1> set colinvisible ON
ops$tkyte%ORA12CR1> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)
 Y (INVISIBLE)                                     NUMBER(38)


Support for Pluggable Databases

There is support for starting pluggable databases from a container database, as well as three new SHOW commands to see what pluggable databases there are and information about the current pluggable database you are connected to:

sys%ORCL> startup pluggable database pdborcl;
Pluggable Database opened.

sys%ORCL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        READ WRITE NO



sys%ORCL> connect scott/tiger@pdborcl
Connected.

scott%PDBORCL> show con_id

CON_ID
------------------------------
3

scott%PDBORCL> show con_name

CON_NAME
------------------------------
PDBORCL

For more details...


For more details - check out the SQL Plus guide.  It is always good to look through the existing commands anyway - to remind you of something you forgot you already knew (I do that all a lot - forget things I knew once upon  a time.  Just looking at the table of contents for SQL Plus could remind you of many forgotten SHOW and SET commands!)

POST A COMMENT

10 Comments:

Anonymous Anonymous said....


sokrates@12.1 > create table t(
2 x int invisible,
3 "X (INVISIBLE)" int
4 );

Table created.

sokrates@12.1 > set colinvisible on
sokrates@12.1 > desc t
Name Null? Type
----------------------------------------- -------- ----------------------------
X (INVISIBLE) NUMBER(38)
X (INVISIBLE) NUMBER(38)



haha !

Mon Jul 08, 01:45:00 PM EDT  

Blogger Thomas Kyte said....

@Sokrates,

you think in an evil way ;)

Mon Jul 08, 01:49:00 PM EDT  

Blogger Jason said....

2 errors here.
PS C:\Users\majian> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on 星期二 7月 16 00:58:52 2013

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


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

no login time shows.

2. SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
SQL> startup pluggable databae pdborcl;

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBORCL MOUNTED
SQL> startup pluggable databae pdborcl;
SP2-0714: 无效的 STARTUP 选项组合

SQL> conn /@pdborcl as sysdba
已连接。
SQL> show con_name;

CON_NAME
------------------------------
PDBORCL
SQL> startup pluggable database pdborcl;
SP2-1546: 插接式数据库的选项无效。

I tried both in the CDB and PDB, both failed.

Mon Jul 15, 01:13:00 PM EDT  

Blogger Jason said....

This comment has been removed by the author.

Mon Jul 15, 01:14:00 PM EDT  

Blogger Thomas Kyte said....

@Jason,

sys is special. sys is unique. many things work differently for sys. sys (sysdba) is not something you should be using day to day - it should be very very very rare.

I don't ever do anything as sys/sysdba unless I really have to and I certainly do not test out little things like this with sys/sysdba.

Just do not use sys/sysdba - please - do not.

many things - MANY THINGS (eg; go ahead and set transaction readonly as sys and report back how "read only" you are. Or do a 'consistent' export and report back on how consistent it is) - work differently for sys/sysdba.

just do not use it.




you spelled database wrong in your first start.

and you use startup pluggable database in a container, not a plug.

so in the plug you should just type "startup" and nothing else.

Mon Jul 15, 01:20:00 PM EDT  

Anonymous Oracle Connections said....

That's quite a wise thinking came from you.. keep it up ;)

Tue Jul 30, 04:38:00 AM EDT  

Anonymous Anonymous said....

sir, could you show the script used to create the user ops$tkyte, seems vary cool...In 12c, have to use C## to create a common user, and your ops$tkyte doesn't seem to be a local user

Thu Sep 12, 11:46:00 AM EDT  

Blogger Thomas Kyte said....

@Anonymous

ops$tkyte is not a common user - it was a 'regular' user in a 'regular' database - non-container database.

Thu Sep 12, 12:41:00 PM EDT  

Blogger Malar Curran said....

Tom how do I connect an identified external account ops$oracle to a pluggable Database using sqlplus. It is a single instance, I can't set the SID as it is a pluggable DB. Thanks for your response.

Fri Jan 10, 09:55:00 AM EST  

Blogger Thomas Kyte said....

@Malar,

you have to use sqlnet and a service to connect to a pluggable database. No "ops$"

You'd have to "ops$" into the root and then set the container - meaning you'd need a global user to do this.

Fri Jan 10, 10:37:00 AM EST  

POST A COMMENT

<< Home