Script to list the users who haven't changed the password.

There are at times , where you might be know the list of users who have not changed the password since their account creation. 

Accessing sys.user$ will provide this information very easily . 

Here is the SQL that will list all the users who have not changed the password.You may want to add a filter to exclude the users who are authenticated by other means ( external / global ).

SELECT u.username ,
  u.account_status ,
  u.created ,
  to_char(u1.ctime , 'dd-mon-yyyy hh:mi:ss') as CreatedTime ,
  to_char(u1.ptime ,'dd-mon-yyyy hh:mi:ss') as  ChangedTime  
FROM dba_users u ,
  sys.user$ u1
WHERE u1.name = u.username
AND u1.ctime  = u1.ptime 

Here , ctime is the timestamp at which the user was created 
and ptime is the timestamp at which the user was modified. 


Let us create two users user1 , user2 and change the password for user1 after 90 seconds. 

SQL> grant create session to user1 identified by u1;

Grant succeeded.

SQL> grant create session to user2 identified by u2;

Grant succeeded.

SQL> exec dbms_lock.sleep(90);

PL/SQL procedure successfully completed.

SQL> alter user user1 identified by u11;

User altered.

Note : The above grant statements are shortcut to create users and assign privilege.

Let us examine the value of ctime and ptime in "USER$" table. 
As you can see , it reflects the updated time of the password  . 

SQL> set linesize 1000
SQL> col username format a10
SQL> SELECT u.username ,
  2    u.account_status ,
  3    u.created ,
  4    to_char(u1.ctime , 'dd-mon-yyyy hh:mi:ss') as CreatedTime ,
  5    to_char(u1.ptime ,'dd-mon-yyyy hh:mi:ss') as  ChangedTime
  6  FROM dba_users u ,
  7    sys.user$ u1
  8  WHERE u1.name = u.username
  9  AND u.username in ( 'USER1', 'USER2') ;

USERNAME   ACCOUNT_STATUS                   CREATED   CREATEDTIME          CHANGEDTIME
---------- -------------------------------- --------- -------------------- --------------------
USER2      OPEN                             16-DEC-15 16-dec-2015 03:12:43 16-dec-2015 03:12:43
USER1      OPEN                             16-DEC-15 16-dec-2015 03:12:43 16-dec-2015 03:14:13


Comments Welcome. 

Creating Common User in Oracle 12c

Oracle has introduced multi-tenant architecture in 12c. With this , we will have an instance ( Single Instance) , multiple instances ( RAC) will be hosting multiple pluggable databases ( PDBs) with in a  container database  ( CDB). 

Please note that the  CDB/PDB is extra licensing option . With the regular license , we can have a CDB with only one PDB. 

Accordingly , there are changes to how users are maintained in 12c.  There are two type of users. 

a) Common User  

b) Local User 

In short , common user is for all PDBs and Local user is local to a PDB. 


There are few rules / restrictions in creating a common user. 
One of them being , the username has to be prefixed with ( c##) . 
However  , this can be circumvented . But I would recommend against against this practice, as this is not ( likely ) supported by oracle . 

Here is an example . 


SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create user c##zahir identified by ZahirPassword container=all;

User created.


As you can see, common user ( c##zahir) was created sucessfully . 

Let us try , by removing c##. 

SQL> create user zahirm identified by ZahirPassword container=all;
create user zahirm identified by ZahirPassword container=all
            *
ERROR at line 1:
ORA-65096: invalid common user or role name


Let us set the UNDOCUMENTED parameter to true . 
Being this is undocumented parameter ; as like many Oracle professionals , 
I would not recommend with meddling  with these  parameters unless otherwise directed by Oracle support. 

Note : This parameter also needed , when you need to create a CDB from sqlplus. 

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> create user zahirm identified by ZahirPassword container=all;

User created.

Voila . The user was created . 


Comments Welcome. 

Impact of LEFT or RIGHT in SQL Server Partition Function's Definition

SQL Server ( since 2005 ) has introduced partitioning to effectively manage very large databases ( VLDBs) . 
Partitioning helps the DBA in 

a) maintaining the statistics at the partition level . 
b) easily load/unload  the data ( via sliding the partition ) 
c) optimal utilizing the storage for the datasets 
   (i.e., Current Year's Data can be housed in the faster storage ; archived data can be stored in cheaper / slower storage ) . 


Here are the steps to implement partitioning 

a) Define the partition function ( PF). 
   This step defines how the data should be partitioned . 
   The option of "RIGHT" or "LEFT" dictates how the upper boundary is treated by SQL Server. 
   
b) Define the partition schema ( PS) that uses the partition function created above
   This steps associates the physical storage ( aka filegroup) that corresponds to the partitions defined in the PF

c) Create a table that utilizes the PS  created above. 
  
In this blog post , I will show the differences between LEFT and RIGHT option definied in the PF. 
Rest of the partitioning 


Let us dive into a simple example . 

C:\>sqlcmd -W
1> use my2014db
2> go
Changed database context to 'my2014db'.

Step a) 
We will create a PF with LEFT range for three values ( 10 , 20 , 30) . 


1> Create Partition Function pf_seqno_l ( int) as range left for values ( 10 , 20 , 30 ) ;
2> go

This will create 
i) parition for any values less than or equal to 10 
ii) parition for any values greater than 10 and less than or equal to 20 
iii) parition for any values greater than 20 and less than or equal to 30 
iv) parition for any values greater than 30 

Step b) 
We will create a PS that will associate filegroup to the partitions . 
Here in this example , I have assigned ALL the paritions to go into PRIMARY filegroup; typically in production environments , it will be associated with multiple filegroups. 

1> Create Partition Scheme ps_seqno_l as partition pf_seqno_l ALL to ([PRIMARY]) ;
2> go
Partition scheme 'ps_seqno_l' has been created successfully. 'PRIMARY' is marked as the next used filegroup in partition scheme 'ps_seqno_l'.

Step c) 

We will create a table that will use the PS. 

1> Create table t_seqno_l
2> (   cid int identity ,
3>     seqno int )
4>      on ps_seqno_l(seqno) ;
5> go

Let us check , if the object does all the partitions . As we can see, it has 4 partitions . 

1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go

partition_number rows
---------------- ----
1 0
2 0
3 0
4 0

Let us insert a record and look at the partitions to confirm the number of records in the partitions . As the first record's value is 2 ( that <= 10 ) , that record is placed in the first partition. 


1> insert into t_seqno_l(seqno) values ( 2) ;
2> go

(1 rows affected)


1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go

partition_number rows
---------------- ----
1 1
2 0
3 0
4 0

(4 rows affected)

Second record is also placed in the first partition as the values is <= 10. 

1> insert into t_seqno_l(seqno) values ( 10) ;
2> go

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go
partition_number rows
---------------- ----
1 2
2 0
3 0
4 0

(4 rows affected)


Let us insert two more records. 

1> insert into t_seqno_l(seqno) values ( 20) ;
2> insert into t_seqno_l(seqno) values ( 50) ;
3> go

(1 rows affected)

(1 rows affected)

1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go
partition_number rows
---------------- ----
1 2
2 1
3 0
4 1

1> select * from t_seqno_l;
2> go
cid seqno
--- -----
3 2
4 10
5 20
6 50

(4 rows affected)

As you can see from the above , 
- Partition 1 has two records ( seqno with the value of 2 and 10) 
- Partition 2 has one record ( seqno with the value of 20) 
- Partition 3 has no record 
- Partition 4 has one record ( seqno with the value of 50 ; which is >= 30 ) 


Now , let us try PF with right option . 
Let us repeat the steps for creating PF , PS and the table. 

This will create 
i) partition for any values less than  10 
ii) partition for any values greater than or equal to  10 and less than  20 
iii) partition for any values greater than or equal to  20 and less than  30 
iv) partition for any values greater than or equal to  30 


1> Create Partition Function pf_seqno_r ( int) as range right for values ( 10 , 20 , 30 ) ;
2> go

1> Create Partition Scheme ps_seqno_r as partition pf_seqno_r ALL to ([PRIMARY]) ;
2> go
Partition scheme 'ps_seqno_r' has been created successfully. 'PRIMARY' is marked as the next used filegroup in partition scheme 'ps_seqno_r'.

1> Create table t_seqno_r
2> (   cid int identity ,
3>     seqno int )
4>      on ps_seqno_r(seqno) ;
5> go

1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 0
2 0
3 0
4 0

(4 rows affected)

Let us insert a record. Let us insert a record and look at the partitions to confirm the number of records in the partitions . As the first record's value is 2 ( that <= 10 ) , that record is placed in the first partition. 

1> insert into t_seqno_r(seqno) values ( 2) ;
2> go

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 1
2 0
3 0
4 0

(4 rows affected)

Let us second record . As you can see now , this record is placed in the second partition. Here the partition layout differ.  

1> insert into t_seqno_r(seqno) values ( 10) ;
2> go

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 1
2 1
3 0
4 0

(4 rows affected)

1> insert into t_seqno_r(seqno) values ( 20) ;
2> insert into t_seqno_r(seqno) values ( 50) ;
3> go

(1 rows affected)

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 1
2 1
3 1
4 1


In short , LEFT or RIGHT dictates how the upper boundary  is placed with in the partitioned table. 

In the next few posts , we will discuss other features of partitioning. 

Thanks for reading. 

External table Enhancements in Oracle 12c.

One of the External tables' enhancements in Oracle 12c is the ability to use wildcard characters in defining the data sources.  We can have either ? ( to denote single character substitution ) or * ( multiple character substitution ) in denoting the location of the data files . 

Here is an simple example . 

Let us create  few CSV files. 

C:\extdir>dir

 Volume in drive C has no label.

 Volume Serial Number is 8280-8864

 Directory of C:\extdir

07/06/2015  01:45 PM              .

07/06/2015  01:45 PM              ..

07/06/2015  01:37 PM                27 emp1.txt

07/06/2015  01:38 PM                26 emp2.txt

07/06/2015  01:44 PM                52 emp500.txt

               3 File(s)            105 bytes

               2 Dir(s)  168,809,664,512 bytes free

C:\extdir>type emp1.txt

101,zahir,F

102,mohamed,R

C:\extdir>type emp2.txt

201,Hameed,A

252,Abdul,R

C:\extdir>type emp500.txt

511,Balasubramaniam,A

577,Yesudas,A

501,Janaki,A

Let us create an external table with Single character in the wild card .

CREATE TABLE EMP_EXT

  (

    "EMP_ID"   VARCHAR2(1000 BYTE),

    "EMP_NAME" VARCHAR2(1000 BYTE),

    "EMP_TYPE" VARCHAR2(1000 BYTE)

  )

  ORGANIZATION EXTERNAL

  (

    TYPE ORACLE_LOADER DEFAULT DIRECTORY "EX_TAB_DIR"

    ACCESS PARAMETERS

    ( debug = 3

     RECORDS DELIMITED BY NEWLINE

     CHARACTERSET we8mswin1252

     BADFILE EX_TAB_DIR:'emp_ext.bad_xt'

     LOGFILE 'emp_ext.log_xt'

     FIELDS TERMINATED BY ","

     LDRTRIM MISSING FIELD VALUES ARE NULL

     REJECT ROWS WITH ALL NULL FIELDS

    ) LOCATION ( 'emp?.txt')

  )

REJECT LIMIT UNLIMITED ;

SQL> Select * from emp_ext ;

EMP_I EMP_NAME                  EMP_T

----- ------------------------- -----

101   zahir                     F

102   mohamed                   R

201   Hameed                    A

252   Abdul                     R

As we have given '?' in the wildcard , the external table created above did not take in the records from emp500.txt. 

Let us create another table with '*' in the wildcard as below. 

CREATE TABLE EMP_EXT_MULTI

  (

    "EMP_ID"   VARCHAR2(1000 BYTE),

    "EMP_NAME" VARCHAR2(1000 BYTE),

    "EMP_TYPE" VARCHAR2(1000 BYTE)

  )

  ORGANIZATION EXTERNAL

  (

    TYPE ORACLE_LOADER DEFAULT DIRECTORY "EX_TAB_DIR" 

    ACCESS PARAMETERS 

    ( debug = 3 

     RECORDS DELIMITED BY NEWLINE 

     CHARACTERSET we8mswin1252 

     BADFILE EX_TAB_DIR:'emp_m_ext.bad_xt' 

     LOGFILE 'emp_m_ext.log_xt' 

     FIELDS TERMINATED BY "," 

     LDRTRIM MISSING FIELD VALUES ARE NULL 

     REJECT ROWS WITH ALL NULL FIELDS 

    ) LOCATION ( 'emp*.txt')

  ) 

REJECT LIMIT UNLIMITED ;

SQL> Select * from EMP_EXT_MULTI ;

EMP_I EMP_NAME                  EMP_T

----- ------------------------- -----

101   zahir                     F

102   mohamed                   R

201   Hameed                    A

252   Abdul                     R

511   Balasubramaniam           A

577   Yesudas                   A

501   Janaki                    A

As can be seen from the above example , the external table did bring in the contents of all files starting with the filenames 'emp.....txt'.

- Zahir 

ORA-01720 ... Error while recreating a view in 12c

We were testing some of my scripts for 12c upgrade . There were few changes to few views . So , we changed the script and deploy it . 

The script was similar to the following 

Create or replace view v_emp_access .... 

When we execute the view , we got the following error .

Error at Command Line : 24 Column : 8

Error report -

SQL Error: ORA-01720: grant option does not exist for 'SCOTT.EMP'

01720. 00000 -  "grant option does not exist for '%s.%s'"

*Cause:    A grant was being performed on a view and the grant option was

           not present for an underlying object.

*Action:   Obtain the grant option on all underlying objects of the view.

All the grants have been provisioned and everything works in lower versions. 

 

After few minutes of R&D , found that the behavior was changed by Oracle from 11.2.0.4 onwards. The resolution is to either drop all the grants and recreate the view or drop the view ( thus dropping the grants )  and recreate it again.  

Per Oracle , this is not a bug ; works as designed. 

You can refer to MOS article (Doc ID 1628033.1) for additional information .

Synonyms and Privileges

Usually synonyms are used to abstract the location / schema dependency of the underlying object. In other words , if I have a table "emp" in a remote database "Sales" ( in Oracle / other DBMS such as SQL Server , DB2 via transparent gateway) , 

It is easier to create synonym as "semp" in our local database to make our SQLs a lot more readable and also to make it portable ( across DEV , QA regions) . 

For an example 

Create synonym s_emp for scott.emp@salesdb;

Here s_emp is the name of the synonym and it references "emp" table/view in "Scott" schema in "SalesDB" ( via dblink) . 

The SQL (  'select ename from scott.emp@salesdb ') can be rewritten as 
'select ename from s_emp' , hiding/abstracting  all the location details .

In the development phase , the remote database(DBlink)  could be a development remote database ; and when the application is deployed to LIVE ,only the synonyms needs to be changed to point to LIVE database , thus your code is  lot more portable than harcoding the remote database names in the code. 


However , we need to be careful with how the privileges are being applied . Let us look at a simple example . 

In the following example , we will create a synonym is SCOTT schema and grant the  "SELECT" privilege to "APPUSR".


SQL> show user
USER is "SCOTT"
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> Create synonym s_emp for scott.emp;

Synonym created.

SQL> grant select on s_emp to appusr ;

Grant succeeded.


In another session , let us connect as "APPUSR" and view the data in the synonym as well as the base table. Yes  , we can see the data. 


SQL> show user
USER is "APPUSR"
SQL>
SQL> Select empno , ename from scott.s_emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL> select empno , ename from scott.emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.



Now , let us go back to SCOTT schema and drop the synonym .

SQL> show user
USER is "SCOTT"

SQL> Drop synonym s_emp;

Synonym dropped.

You would think APPUSR will not be able to view the data . 
Not really. As an APPUSR  , I can look at the data from the underlying object as shown below. 

SQL> show user
USER is "APPUSR"
SQL>


SQL> Select ename , empno  from scott.s_emp;
Select ename , empno  from scott.s_emp
                                 *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> Select ename , empno  from scott.emp;

ENAME           EMPNO
---------- ----------
SMITH            7369
ALLEN            7499
WARD             7521
JONES            7566
MARTIN           7654
BLAKE            7698
CLARK            7782
SCOTT            7788
KING             7839
TURNER           7844
ADAMS            7876
JAMES            7900
FORD             7902
MILLER           7934

14 rows selected.


So , as always , better to understand on how the privileges works .

Comments Welcome. 

Renaming PDB

Oracle 12c has introduced CDB/ PDB architecture . Prior to 12c , Only one database can  be serviced by an instance ( in a Single Instance ) / by instances ( in RAC) . In other words , no more than one databases can be serviced by an instance(s). 

In Oracle 12c, we can have ( pluggable) databases that are  contained with in a  (container) database. Container database will not have user data , where as the pluggable database will have the user data. This is almost synonymous with Sybase / MS SQL Server or DB2.  Loosely speaking , we can equate Container database as to Combination of System (master , msdb , model and resource ) databases in Sybase / MS-SQL Server. 

Note : With the standard license , we can one CDB and one PDB . We would need additional license , if we are planning to have more than one PDB. 

Let us say , we created a database with DBCA and asked for 2 PDBs with the pdb_docs, then we will have a CDB ( whatever we have given ) , PDB seed  and two pdbs with pdb_docs1 and pdb_docs2. 

As you can see , OXYZ12DB is a the containter database .

C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 22 13:37:02 2015

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


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

SQL> select name , CDB  from v$database;

NAME      CDB
--------- ---
OXYZ12DB  YES

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB$SEED                       READ ONLY  NO
PDB_DOCS1                      READ WRITE YES

PDB_DOCS2                      READ WRITE NO

Now , let us see how we can change the name of pdb_docs2 to pdb_noncredit .
We need to be connected the correct PDB and it needs to be in restricted mode. 

C:\>sqlplus sys@pdb_docs2 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 22 13:50:16 2015

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

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics

and Real Application Testing options

SQL> select name , open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------

PDB_DOCS2                      READ WRITE


We need to open the PDB in restricted mode , so we can its change its name. 

SQL>  alter pluggable database pdb_docs2 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb_docs2 open restricted;

Pluggable database altered.

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB_DOCS2                      READ WRITE YES



SQL> alter pluggable database pdb_docs2  rename global_name to pdb_noncredit;

Pluggable database altered.

SQL> select name , open_mode , restricted from v$pdbs;

NAME                           OPEN_MODE  RES
------------------------------ ---------- ---
PDB_NONCREDIT                  READ WRITE YES

Here we go. Now the PDB's name is changed to PDB_NONCREDIT.

Make sure that you change your tnsnames.ora to reflect the new name . 

Comments welcome.