Taking Things For Granted

"Sometimes I find the best way of getting from one place to another is simply to erase everything and begin again."
-- Norton Juster, The Phantom Tollbooth

In one of the Oracle forums a question was asked regarding revoking selected privileges from the DBA role. Unfortunately for the person posting the question the answer is a resounding “No”; granting a role grants all privileges assigned to that role and there is no “picking and choosing” as if you were in a cafeteria. Roles are designed (or should be, at least) to grant all necessary privileges a user would need to access objects that role will use. And roles supplied by Oracle are designed for the jobs they are named after, such as DBA. Changing the role affects functionality and can seriously impact those granted that role. Let’s look at that in a bit more detail.

Oracle provides pre-configured roles with every installation of the database, and the list can vary based on the options you choose to install. A partial list of these roles from 12.1.0.2 is shown below (remember this is not a complete and exhaustive list):

ROLE                           O
------------------------------ -
CONNECT                        Y
RESOURCE                       Y
DBA                            Y
AUDIT_ADMIN                    Y
AUDIT_VIEWER                   Y
SELECT_CATALOG_ROLE            Y
EXECUTE_CATALOG_ROLE           Y
DELETE_CATALOG_ROLE            Y
CAPTURE_ADMIN                  Y
EXP_FULL_DATABASE              Y
IMP_FULL_DATABASE              Y
CDB_DBA                        Y
PDB_DBA                        Y
RECOVERY_CATALOG_OWNER         Y
LOGSTDBY_ADMINISTRATOR         Y
DBFS_ROLE                      Y
GSMUSER_ROLE                   Y
AQ_ADMINISTRATOR_ROLE          Y
AQ_USER_ROLE                   Y
DATAPUMP_EXP_FULL_DATABASE     Y
DATAPUMP_IMP_FULL_DATABASE     Y
ADM_PARALLEL_EXECUTE_TASK      Y
PROVISIONER                    Y
XS_RESOURCE                    Y
XS_SESSION_ADMIN               Y
XS_NAMESPACE_ADMIN             Y
XS_CACHE_ADMIN                 Y
GATHER_SYSTEM_STATISTICS       Y
OPTIMIZER_PROCESSING_RATE      Y
GSMADMIN_ROLE                  Y
RECOVERY_CATALOG_USER          Y
EM_EXPRESS_BASIC               Y
EM_EXPRESS_ALL                 Y
SCHEDULER_ADMIN                Y
HS_ADMIN_SELECT_ROLE           Y
HS_ADMIN_EXECUTE_ROLE          Y
HS_ADMIN_ROLE                  Y
GLOBAL_AQ_USER_ROLE            Y
OEM_ADVISOR                    Y
OEM_MONITOR                    Y
XDBADMIN                       Y
XDB_SET_INVOKER                Y
AUTHENTICATEDUSER              Y
XDB_WEBSERVICES                Y
XDB_WEBSERVICES_WITH_PUBLIC    Y
XDB_WEBSERVICES_OVER_HTTP      Y
GSM_POOLADMIN_ROLE             Y
GDS_CATALOG_SELECT             Y
WM_ADMIN_ROLE                  Y
JAVAUSERPRIV                   Y
JAVAIDPRIV                     Y
JAVASYSPRIV                    Y
JAVADEBUGPRIV                  Y
EJBCLIENT                      Y
JMXSERVER                      Y
JAVA_ADMIN                     Y
JAVA_DEPLOY                    Y
CTXAPP                         Y
ORDADMIN                       Y
OLAP_XS_ADMIN                  Y
OLAP_DBA                       Y
OLAP_USER                      Y
SPATIAL_WFS_ADMIN              Y
WFS_USR_ROLE                   Y
SPATIAL_CSW_ADMIN              Y
CSW_USR_ROLE                   Y
LBAC_DBA                       Y
APEX_ADMINISTRATOR_ROLE        Y
APEX_GRANTS_FOR_NEW_USERS_ROLE Y
DV_SECANALYST                  Y
DV_MONITOR                     Y
DV_ADMIN                       Y
DV_OWNER                       Y
DV_ACCTMGR                     Y
DV_PUBLIC                      Y
DV_PATCH_ADMIN                 Y
DV_STREAMS_ADMIN               Y
DV_GOLDENGATE_ADMIN            Y
DV_XSTREAM_ADMIN               Y
DV_GOLDENGATE_REDO_ACCESS      Y
DV_AUDIT_CLEANUP               Y
DV_DATAPUMP_NETWORK_LINK       Y
DV_REALM_RESOURCE              Y
DV_REALM_OWNER                 Y

The ‘O’ header is for the ORACLE_MAINTAINED column which indicates the role is supplied by Oracle. [This is a new column in the DBA_ROLES view for 12.1; earlier releases do not have this column in the view definition.] That list has 84 different roles all created when your database was created. What privileges do these roles have? That’s a question answered by the ROLE_SYS_PRIVS and ROLE_TAB_PRIVS views; let’s look at the DBA role and see what Oracle deems as necessary system privileges to be an effective DBA:

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
DROP TABLESPACE
BECOME USER
DROP ROLLBACK SEGMENT
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DROP ANY INDEX
SELECT ANY SEQUENCE
CREATE ROLE
EXECUTE ANY PROCEDURE
ALTER PROFILE
CREATE ANY DIRECTORY
CREATE ANY LIBRARY
EXECUTE ANY LIBRARY
ALTER ANY INDEXTYPE
DROP ANY INDEXTYPE
DEQUEUE ANY QUEUE
EXECUTE ANY EVALUATION CONTEXT
EXPORT FULL DATABASE
CREATE RULE
ALTER ANY SQL PROFILE
ADMINISTER ANY SQL TUNING SET
CHANGE NOTIFICATION
DROP ANY EDITION
DROP ANY MINING MODEL
ALTER ANY MINING MODEL
ALTER ANY CUBE DIMENSION
CREATE CUBE
DROP ANY CUBE BUILD PROCESS
USE ANY SQL TRANSLATION PROFILE
CREATE PLUGGABLE DATABASE
ALTER ROLLBACK SEGMENT
DELETE ANY TABLE
ALTER DATABASE
FORCE ANY TRANSACTION
ALTER ANY PROCEDURE
DROP ANY TRIGGER
DROP ANY MATERIALIZED VIEW
UNDER ANY TYPE
ALTER ANY LIBRARY
CREATE DIMENSION
DEBUG ANY PROCEDURE
CREATE RULE SET
ALTER ANY RULE SET
ANALYZE ANY DICTIONARY
ALTER ANY EDITION
CREATE ANY ASSEMBLY
ALTER ANY CUBE
SELECT ANY CUBE
DROP ANY MEASURE FOLDER
RESTRICTED SESSION
CREATE TABLESPACE
ALTER TABLESPACE
CREATE USER
ALTER USER
LOCK ANY TABLE
CREATE VIEW
DROP ANY VIEW
GRANT ANY ROLE
CREATE TRIGGER
CREATE TYPE
EXECUTE ANY OPERATOR
CREATE ANY DIMENSION
ALTER ANY DIMENSION
CREATE ANY OUTLINE
ADMINISTER DATABASE TRIGGER
RESUMABLE
FLASHBACK ANY TABLE
CREATE ANY RULE SET
EXECUTE ANY RULE SET
IMPORT FULL DATABASE
EXECUTE ANY RULE
EXECUTE ANY PROGRAM
CREATE ANY EDITION
CREATE ASSEMBLY
ALTER ANY ASSEMBLY
CREATE CUBE DIMENSION
CREATE ANY CUBE BUILD PROCESS
UPDATE ANY CUBE DIMENSION
EM EXPRESS CONNECT
SET CONTAINER
ALTER ANY MEASURE FOLDER
CREATE ANY TABLE
CREATE ANY INDEX
CREATE ANY SEQUENCE
ALTER ANY ROLE
ANALYZE ANY
DROP ANY LIBRARY
CREATE ANY OPERATOR
CREATE INDEXTYPE
UNDER ANY TABLE
DROP ANY DIMENSION
SELECT ANY DICTIONARY
GRANT ANY OBJECT PRIVILEGE
CREATE EVALUATION CONTEXT
CREATE ANY EVALUATION CONTEXT
DROP ANY EVALUATION CONTEXT
CREATE ANY RULE
CREATE JOB
CREATE ANY JOB
CREATE MINING MODEL
INSERT ANY CUBE DIMENSION
DROP ANY CUBE
UPDATE ANY CUBE BUILD PROCESS
EXEMPT DML REDACTION POLICY
READ ANY TABLE
ALTER SYSTEM
AUDIT SYSTEM
CREATE ROLLBACK SEGMENT
DROP ANY TABLE
COMMENT ANY TABLE
REDEFINE ANY TABLE
CREATE CLUSTER
ALTER ANY INDEX
DROP PUBLIC DATABASE LINK
CREATE PROFILE
ALTER ANY MATERIALIZED VIEW
ALTER ANY TYPE
DROP ANY TYPE
UNDER ANY VIEW
EXECUTE ANY INDEXTYPE
DROP ANY CONTEXT
ALTER ANY OUTLINE
ADMINISTER RESOURCE MANAGER
MANAGE SCHEDULER
MANAGE FILE GROUP
CREATE ANY MINING MODEL
SELECT ANY MINING MODEL
CREATE ANY MEASURE FOLDER
DELETE ANY MEASURE FOLDER
CREATE ANY SQL TRANSLATION PROFILE
CREATE ANY CREDENTIAL
EXEMPT DDL REDACTION POLICY
SELECT ANY MEASURE FOLDER
SELECT ANY CUBE BUILD PROCESS
ALTER ANY CUBE BUILD PROCESS
CREATE TABLE
BACKUP ANY TABLE
CREATE ANY CLUSTER
DROP ANY SYNONYM
DROP PUBLIC SYNONYM
CREATE ANY VIEW
CREATE SEQUENCE
ALTER ANY SEQUENCE
FORCE TRANSACTION
CREATE PROCEDURE
CREATE ANY PROCEDURE
ALTER RESOURCE COST
DROP ANY DIRECTORY
CREATE ANY TYPE
ALTER ANY OPERATOR
CREATE ANY INDEXTYPE
ENQUEUE ANY QUEUE
ON COMMIT REFRESH
DEBUG CONNECT SESSION
DROP ANY RULE SET
EXECUTE ANY CLASS
MANAGE ANY FILE GROUP
EXECUTE ANY ASSEMBLY
EXECUTE ASSEMBLY
COMMENT ANY MINING MODEL
CREATE ANY CUBE DIMENSION
DELETE ANY CUBE DIMENSION
SELECT ANY CUBE DIMENSION
DROP ANY SQL TRANSLATION PROFILE
CREATE CREDENTIAL
ALTER ANY TABLE
DROP ANY CLUSTER
CREATE SYNONYM
CREATE PUBLIC SYNONYM
DROP ANY SEQUENCE
DROP ANY ROLE
AUDIT ANY
DROP ANY PROCEDURE
CREATE ANY TRIGGER
ALTER ANY TRIGGER
DROP PROFILE
GRANT ANY PRIVILEGE
CREATE LIBRARY
CREATE OPERATOR
DROP ANY OUTLINE
MERGE ANY VIEW
ADMINISTER SQL TUNING SET
UPDATE ANY CUBE
INSERT ANY MEASURE FOLDER
ADMINISTER SQL MANAGEMENT OBJECT
CREATE SQL TRANSLATION PROFILE
LOGMINING
MANAGE TABLESPACE
DROP USER
ALTER ANY CLUSTER
CREATE ANY SYNONYM
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK
CREATE MATERIALIZED VIEW
CREATE ANY MATERIALIZED VIEW
EXECUTE ANY TYPE
DROP ANY OPERATOR
QUERY REWRITE
GLOBAL QUERY REWRITE
MANAGE ANY QUEUE
CREATE ANY CONTEXT
ALTER ANY EVALUATION CONTEXT
ALTER ANY RULE
DROP ANY RULE
ADVISOR
SELECT ANY TRANSACTION
DROP ANY SQL PROFILE
CREATE ANY SQL PROFILE
READ ANY FILE GROUP
CREATE EXTERNAL JOB
DROP ANY ASSEMBLY
DROP ANY CUBE DIMENSION
CREATE ANY CUBE
CREATE MEASURE FOLDER
CREATE CUBE BUILD PROCESS
ALTER ANY SQL TRANSLATION PROFILE
FLASHBACK ARCHIVE ADMINISTER

220 system privileges are required to be an effective DBA for an Oracle database, an impressive list, indeed. Of course once a user is granted the DBA role he or she gets ALL of those system privileges and since the role is the only direct grant that gives those privileges that list cannot be modified by selectively revoking one or more of those privileges:

SQL> grant DBA to blorpo identified by gussyflorp;

Grant succeeded.

SQL> revoke select any transaction from blorpo;
revoke select any transaction from blorpo
*
ERROR at line 1:
ORA-01952: system privileges not granted to 'BLORPO'


SQL>

Yes, the user DOES have that privilege, albeit indirectly. It’s indirect because it’s the ROLE that was granted that privilege, among others, and no attempt was made to revoke the role from the user. It’s a ‘package deal’; you grant a role to a user and it’s all or nothing, and even though it behaves like the user has the privileges granted directly that’s not the case.

You could, of course, get all of the privileges the DBA role has (both system and table) and create a script to grant each individual privilege to the desired user. It would be a LONG script and such grants require attention from the DBA granting them to ensure they are both current and not being abused. In that case individual privileges can be revoked which would be a maintenance nightmare for the DBA having keep track of which user has which set of privileges. Another option presents itself, creating a new role with only the privileges the DBA wants to assign to a user. The privilege list for DBA could be shortened to create, say, a DB_OPERATOR or DBO role. Such privileges would depend upon the job description; creating such a role would make granting such access easier and make maintenance simpler since when the role grants are changed those who are granted that role have there privileges adjusted the next time they login.

Roles make granting privileges very easy and straightforward, provided the role is properly created and maintained. Roles also make it impossible to “pick and choose” privileges a user should have. It’s an “all or nothing” proposition and there’s no way around that when using a pre-defined role.

Sometimes you just need to begin again.

“That STILL Ain’t Right”

"If you want sense, you'll have to make it yourself."
- Norton Juster, The Phantom Tollbooth

In a recent post to an Oracle forum a query was presented and a tuning request was made. It appears that the query was taking 20 hours to complete. Through further interrogation and responses it was discovered that the dates were being stored in a VARCHAR2 column and implicit date conversion was being used. To show how much of a problem this can cause the following example was created; notice the results returned and the execution plans generated for each run of the query, once with the table defined in the manner the original poster described and one with the date column using the DATE datatype. We begin:

SQL>
SQL> create table datetst(
  2  myid    number,
  3  res_value	varchar2(3),
  4  mydt    varchar2(20));

Table created.

SQL>
SQL> create index datetest_idx on datetst(res_value);

Index created.

SQL>
SQL> begin
  2  	     for i in 1..10000 loop
  3  		     if mod(i,23) = 0 then
  4  		     insert into datetst
  5  		     values(i, 'PUR', to_char(sysdate+i, 'MM/DD/RRRR'));
  6  		     else
  7  		     insert into datetst
  8  		     values(i, 'BID', to_char(sysdate+i, 'MM/DD/RRRR'));
  9  		     end if;
 10  	     end loop;
 11
 12  	     commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>

Let’s now run a query using conditions similar to the posted query and see what Oracle returns:

SQL>
SQL>
SQL> select *
  2  from datetst
  3  where mydt <= sysdate +230;
where mydt <= sysdate +230
      *
Error at line 4:
ORA-01843: not a valid month

As expected the implicit date conversion failed; modifying the query to explicitly convert the strings to dates produces ‘interesting’ results:

SQL>
SQL> select *
  2  from datetst
  3  where res_value = 'PUR'
  4  and mydt <= to_char(sys date +230)

      MYID RES MYDT
---------- --- --------------------
        23 PUR 01/21/2022
        46 PUR 02/13/2022
        69 PUR 03/08/2022
        92 PUR 03/31/2022
       115 PUR 04/23/2022
       138 PUR 05/16/2022
       161 PUR 06/08/2022
       184 PUR 07/01/2022
       207 PUR 07/24/2022
       230 PUR 08/16/2022
       253 PUR 09/08/2022
       276 PUR 10/01/2022
       299 PUR 10/24/2022
...
      9913 PUR 02/18/2049
      9936 PUR 03/13/2049
      9959 PUR 04/05/2049

434 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3255216368
---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |   434 | 12152 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATETST      |   434 | 12152 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DATETEST_IDX |   434 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("MYDT"<=TO_CHAR(SYSDATE@!+230))
   2 - access("RES_VALUE"='PUR')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

The query should have returned no more than 10 rows that met the criteria, and it returned 434. Looking at the plan we see:

SQL>
SQL> select * From table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------- 
SQL_ID  6qatrtphp5wjt, child number 0
-------------------------------------
select * from datetst where res_value = 'PUR' and mydt <=
to_char(sysdate +230, 'MM/DD/RRRR')

Plan hash value: 3255216368

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATETST      |   222 |  6216 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DATETEST_IDX |   434 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MYDT"

Oracle found 434 rows that ‘matched’ the conditions, illustrating a problem of using an incorrect datatype; Oracle can’t know these are dates and compares them as ASCII strings creating a result set that is much larger than it should be. We drop the table and start over:

SQL>
SQL> drop table datetst purge;

Table dropped.

SQL>
SQL> create table datetst(
  2  myid    number,
  3  res_value varchar2(3),
  4  mydt    date);

Table created.

SQL>
SQL> create index datetest_idx on datetst(res_value);

Index created.

SQL>
SQL> begin
  2  	     for i in 1..10000 loop
  3  		     if mod(i,23) = 0 then
  4  		     insert into datetst
  5  		     values(i, 'PUR', sysdate+i);
  6  		     else
  7  		     insert into datetst
  8  		     values(i, 'BID', sysdate+i);
  9  		     end if;
 10  	     end loop;
 11
 12  	     commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>

We now run the original query (that didn’t have explicit date conversion, since we no longer need it) and examine the results:

SQL>
SQL> select *
  2  from datetst
  3  where res_value = 'PUR'
  4  and mydt <= sys date + 230;

      MYID RES MYDT
---------- --- ---------
        23 PUR 21-JAN-22
        46 PUR 13-FEB-22
        69 PUR 08-MAR-22
        92 PUR 31-MAR-22
       115 PUR 23-APR-22
       138 PUR 16-MAY-22
       161 PUR 08-JUN-22
       184 PUR 01-JUL-22
       207 PUR 24-JUL-22
       230 PUR 16-AUG-22

10 rows selected.

SQL> select * From table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  0m2c2mv7zhx49, child number 0
-------------------------------------
select * from datetst where res_value = 'PUR' and mydt <= sysdate +230

Plan hash value: 3255216368

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATETST      |    10 |   250 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DATETEST_IDX |   434 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("MYDT"<=SYSDATE@!+230)
  2 - access("RES_VALUE"='PUR')

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

Oracle now found the 10 rows we sought using the conditions we specified because the date data was correctly stored as a DATE datatype. Using VARCHAR2 made the result set 2,220 percent larger, and that was for a 10000 row table. Let’s re-run the example with 1,000,000 rows and see what numbers Oracle produces:

SQL>
SQL> create table datetst(
  2  myid    number,
  3  res_value	varchar2(3),
  4  mydt    varchar2(20));

Table created.

SQL>
SQL> create index datetest_idx on datetst(res_value);

Index created.

SQL>
SQL> begin
  2  	     for i in 1..1000000 loop
  3  		     if mod(i,23) = 0 then
  4  		     insert into datetst
  5  		     values(i, 'PUR', to_char(sysdate+i, 'MM/DD/RRRR'));
  6  		     else
  7  		     insert into datetst
  8  		     values(i, 'BID', to_char(sysdate+i, 'MM/DD/RRRR'));
  9  		     end if;
 10  	     end loop;
 11
 12  	     commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from datetst
  3  where mydt  select *
  2  from datetst
  3  where res_value = 'PUR'
  4  and mydt <= to_char(sysdate + 230);

      MYID RES MYDT
---------- --- --------------------
        23 PUR 01/21/2022
        46 PUR 02/13/2022
        69 PUR 03/08/2022
        92 PUR 03/31/2022
       115 PUR 04/23/2022
       138 PUR 05/16/2022
       161 PUR 06/08/2022
       184 PUR 07/01/2022
       207 PUR 07/24/2022
       230 PUR 08/16/2022
       253 PUR 09/08/2022
       276 PUR 10/01/2022
...
    998407 PUR 07/17/4755
    998430 PUR 08/09/4755
    998453 PUR 09/01/4755
    998476 PUR 09/24/4755
    998499 PUR 10/17/4755
    998522 PUR 11/09/4755
    998545 PUR 12/02/4755
    998568 PUR 12/25/4755

43478 rows selected.

SQL> select * From table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  6qatrtphp5wjt, child number 0
-------------------------------------
select * from datetst where res_value = 'PUR' and mydt <= to_char(sysdaet + 230)

Execution Plan
----------------------------------------------------------
Plan hash value: 3255216368
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              | 38608 |  1055K|   158   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATETST      | 38608 |  1055K|   158   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DATETEST_IDX | 38608 |       |   158   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("MYDT"<=TO_CHAR(SYSDATE@!+230))
   2 - access("RES_VALUE"='PUR')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

drop table datetst purge;


Table dropped.

SQL>

SQL> create table datetst(
  2  myid    number,
  3  res_value varchar2(3),
  4  mydt    date);

Table created.

SQL>
SQL> create index datetest_idx on datetst(res_value);

Index created.

SQL>
SQL> begin
  2  	     for i in 1..1000000 loop
  3  		     if mod(i,23) = 0 then
  4  		     insert into datetst
  5  		     values(i, 'PUR', sysdate+i);
  6  		     else
  7  		     insert into datetst
  8  		     values(i, 'BID', sysdate+i);
  9  		     end if;
 10  	     end loop;
 11
 12  	     commit;
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from datetst
  3  where res_value = 'PUR'
  4  and mydt <= sysdate + 230;
SQL> select * From table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
SQL_ID  0m2c2mv7zhx49, child number 0
-------------------------------------
select * from datetst where res_value = 'PUR' and mydt <= sysdate +230

Plan hash value: 3255216368

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |       |       |   148 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DATETST      |    35 |   875 |   148   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | DATETEST_IDX | 46482 |       |   148   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("MYDT<=SYSDATE@!+230)

   2 - access("RES_VALUE"='PUR')

Note

-----

   - dynamic statistics used: dynamic sampling (level=2)

With 1,000,000 rows of data Oracle inflated the original 10-row result set to 46,482 rows, a
whopping 464,820 percent increase. As the data volumes increase this result set will increase
to even larger numbers, resulting in extremely long query times and vast numbers of incorrect
results, something I doubt the original poster had counted on.

Using the correct datatype is essential in ensuring Oracle can do its job and do it properly, returning result sets that are reliable. Storing data in formst that doesn’t reflect the actual data type can be disastrous, as illustrated here. It pays when writing or purchasing applications that the proper datatype is in force for the columns being used.

It only makes sense.

“That’s Not What I Wanted.”

"Let me try once more," Milo said in an effort to explain. "In other words--"
"You mean you have other words?" cried the bird happily. "Well, by all means, 
use them. You're certainly not doing very well with the ones you have now."
-- Norton Juster, The Phantom Tollbooth

A recent question in the Oracle database forums has a user wanting to open his physical standby database in READ WRITE mode but still keep it as a physical standby. He found this blog post, followed the instructions and did, indeed, get what used to be his standby database opened in READ WRITE mode. What the blogger failed to mention was that his instructions performed a failover of the standby to primary, and did so in a manner that Oracle does not recommend.

To be honest there isn’t a way to open a physical standby database READ WRITE, keep the primary as a primary and maintain the physical standby as a physical standby. Active Data Guard allows the physical standby to be opened READ ONLY while continuing to apply redo to keep the standby in synch with the primary. (This option has extra cost as it requires additional licensing.) Once a failover has occurred the only recourse is to rebuild the standby database; it can’t be simply ‘brought back’ at the will and whim of the DBA.

What was suggested by the author of that blog post was to use:

alter database activate standby database;

which WILL eventually open the standby READ WRITE, failing it over to be the new primary in the process. Yet Oracle, in the documentation, clearly does not recommend using this option:

ACTIVATE [PHYSICAL|LOGICAL] STANDBY DATABASE [FINISH APPLY]

Performs a failover. The standby database must be mounted before it can be activated with this statement.

Note: Do not use the ALTER DATABASE ACTIVATE STANDBY DATABASE statement to failover because it causes
data loss. Instead, use the following best practices:

For physical standby databases, use the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE statement 
with the FINISH keyword to perform the role transition as quickly as possible with little or no data loss
and without rendering other standby databases unusable.

For logical standby databases, use the ALTER DATABASE PREPARE TO SWITCHOVER and
ALTER DATABASE COMMIT TO SWITCHOVER statements.

Notice the warning given by Oracle:

Note: Do not use the ALTER DATABASE ACTIVATE STANDBY DATABASE statement to failover because it causes
data loss.

A far better option, although not permanent, would be to execute a switchover, where the primary and physical standby switch roles for a period of time before restoring the physical standby to its original role as PHYSICAL STANDBY. This takes more work to accomplish but it is reversible. An example of a switchover is shown below:

Ensure that no gaps exist in the redo apply:

SQL> select status, gap_status
  2  from v$archive_dest_status
  3  where dest_id = 2;
 
STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP
 
SQL>

Make certain that the temp file name or names on the standby match those on the primary.  On the primary execute:

SQL> select file_name from dba_temp_files;
 
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oradata/yumpo/temp01.dbf
 
SQL>

and on the standby execute:

SQL> select name from v$tempfile;
 
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/yumpo/temp01.dbf
 
SQL> 

There may be a delay configured for redo apply on the standby; to determine if a delay is configured execute:

SQL> select delay_mins from v$archive_dest where dest_id = 2; 
 
DELAY_MINS
----------
         0
 
SQL>

(If the DELAY_MINS is greater than 0 the delay can be removed by executing:

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

)

Check the SWITCHOVER_STATUS on the primary:

SQL> select switchover_status from v$database;
 
SWITCHOVER_STATUS
--------------------
TO STANDBY
 
SQL>

Valid values are TO STANDBY and SESSION ACTIVE.  Any other value signals that the redo transport 
isn't functioning and the switchover will fail.  If the status is SESSION ACTIVE query the
V$SESSION view for active sessions:

SQL> select sid, process, program
  2  from v$session where type = 'USER';

       SID PROCESS                  PROGRAM
---------- ------------------------ ---------------------------------------
         6 16952:11396              sqlplus.exe
        17 15728                    ORACLE.EXE (J002)
        18 10668                    ORACLE.EXE (J006)
       131 13196                    ORACLE.EXE (J003)
       134 16576:16772              sqlplus.exe
       135 9720                     ORACLE.EXE (J007)
       244 17404                    ORACLE.EXE (J004)
       248 17844                    ORACLE.EXE (J008)
       254 12992                    ORACLE.EXE (J000)
       364 8280                     ORACLE.EXE (J005)
       366 2320                     ORACLE.EXE (J001)

11 rows selected.

SQL>

When everything looks good execute the switchover; if the status is TO STANDBY the command is:

SQL> alter database commit to switchover to physical standby;
 
Database altered.

SQL>

If the status is SESSION ACTIVE you should modify the command as shown below:

SQL> alter database commit to switchover to physical standby with session shutdown;
 
Database altered.
 
SQL>

Shutdown the 'new' standby and afterwards mount it:

SQL> shutdown immediate
ORA-01507: database not mounted
 
 
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
 
...
Database mounted.
SQL> 

If all has gone according to plan the alert log on the 'new' standby should report:

Successful mount of redo thread 1, with mount id ...
Physical Standby Database mounted.

It's now time to check the 'old' standby for SWITCHOVER_STATUS.  The two valid values are  
TO PRIMARY and SESSIONS ACTIVE:

SQL> select switchover_status from v$database;
 
SWITCHOVER_STATUS
--------------------
TO PRIMARY
 
SQL>

Since no sessions are active the WITH SESSION SHUTDOWN clause will not be necessary:

SQL> alter database commit to switchover to primary;
 
Database altered.
 
SQL>

Open the 'new' primary database:

SQL> alter database open;
 
Database altered.
 
SQL>

Looking in the alert log you should see:

Standby became primary SCN: ...
Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary

Check the LOG_ARCHIVE_DEST_n parameters and set up a new destination, if necessary:


SQL> alter system set log_archive_dest_2='service="yumpo" LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="yumpo" net_timeout=30 valid_for=(all_logfiles,primary_role)' scope = both;

 
System altered.
 
SQL>

Start the redo apply on the 'new' standby:

SQL> alter database recover managed standby database using current logfile disconnect from session;
 
Database altered.
 
SQL>

It's a good idea to verify that the redo transport is working; execute at least two log switches
on the 'new' primary to prove the redo is being transported:

SQL> alter system switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL>/
 
System altered.
 
SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;
 
STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP
 
SQL>

Presuming the status is VALID and there is no gap the switchover is complete.

To reverse the switchover the same steps are applied, starting at the ‘new’ primary and ending with the ‘new’ standby. Should there be no problems the primary database will be returned to the PRIMARY role, likewise the physical standby will return to the PHYSICAL STANDBY role. In between the switchover steps the original physical standby will be temporarily the primary database, opened in READ WRITE mode.

If you have Data Guard Broker configured a switchover is considerably easier to execute; you can read the Oracle documentation for a complete example.

Understanding Data Guard/Standby concepts is essential for knowing what can, and cannot, be done. And blog advice, even from this one, should be investigated before taking any action as the title and text may not accurately reflect what the recommended actions actually accomplish. It would be a shame to put yourself in an unrecoverable situation just because you jumped first and looked later.

In other words …

“If You Change This, Right Here …”

"changes are so frightening."
-- Norton Juster, The Phantom Tollbooth

It isn’t unusual for Oracle to expand functionality with each new release; a short list includes AWR, ADDM, the SYSAUX tablespace, Flashback Query, Flashback Database and Edition-based Redefinition. Oracle 12.1 is no different as it provides an interesting performance enhancement, the in-memory column store, that can increase performance by reducing physical I/O load by making tables memory-resident (to simplify the description somewhat). In addition to the in-memory access to table data Oracle has introduced a new optimization called “Vector Transformation, including Vector Optimization” for in-memory operations. Even when this new optimization isn’t in use there are new code paths to take advantage of this technology. As a result execution plans can change; let’s look at an example originally provided by Jonathan Lewis that illustrates this.

(In-memory is fairly easy to configure. Set inmemory_size to a ‘reasonable’ non-zero value and then restart the database:

SQL> alter system set inmemory_size=500m scope=spfile;

System altered.

SQL>

For a complete reference to in-memory configuration read here.)

Two tables are created, neither one especially noteworthy, an index is created on each table and extended statistics are generated on the (n1,n2) column grouping:

SQL> 
SQL> create table t1
  2  nologging
  3  as
  4  with generator as (
  5  	     select  --+ materialize
  6  		     rownum id
  7  	     from dual
  8  	     connect by
  9  		     level  
SQL> create table t2
  2  nologging
  3  as
  4  with generator as (
  5  	     select  --+ materialize
  6  		     rownum id
  7  	     from dual
  8  	     connect by
  9  		     level 'T1',
  5  		     method_opt       => 'for columns (n1,n2) size 1'
  6  	     );
  7  end;
  8  /
SQL> 

Executing the following query and displaying the execution plan shows no surprises since neither table has been altered to be in-memory:

SQL> 
SQL> select
  2  	     /*+
  3  		     qb_name(main)
  4  	     */
  5  	     count(*)
  6  from    (
  7  	     select
  8  		     /*+ qb_name(inline) */
  9  		     distinct t1.v1, t2.v1
 10  	     from
 11  		     t1,t2
 12  	     where
 13  		     t1.n1 = 50
 14  	     and     t1.n2 = 50
 15  	     and     t2.rand = t1.id
 16  	     )
 17  ;

      9912                                                                                                                                            
SQL> 
SQL> select * from table(dbms_xplan.display_cursor);

SQL_ID  a7gb3ncf922zz, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
select         /*+                 qb_name(main)         */                                                                                           
count(*) from    (         select                 /*+ qb_name(inline)                                                                                 
*/                 distinct t1.v1, t2.v1         from                                                                                                 
t1,t2         where                 t1.n1 = 50         and     t1.n2 =                                                                                
50         and     t2.rand = t1.id         )                                                                                                          
                                                                                                                                                      
Plan hash value: 1718706536                                                                                                                           
                                                                                                                                                      
-------------------------------------------------------------------------------------------------                                                     
| Id  | Operation                               | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                                     
-------------------------------------------------------------------------------------------------                                                     
|   0 | SELECT STATEMENT                        |       |       |       |  5127 (100)|          |                                                     
|   1 |  SORT AGGREGATE                         |       |     1 |       |            |          |                                                     
|   2 |   VIEW                                  |       | 10001 |       |  5127   (1)| 00:00:01 |                                                     
|   3 |    HASH UNIQUE                          |       | 10001 |   351K|  5127   (1)| 00:00:01 |                                                     
|*  4 |     HASH JOIN                           |       | 10001 |   351K|  5125   (1)| 00:00:01 |                                                     
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T1    |   100 |  2100 |     3   (0)| 00:00:01 |                                                     
|*  6 |       INDEX RANGE SCAN                  | T1_N1 |   100 |       |     1   (0)| 00:00:01 |                                                     
|   7 |      TABLE ACCESS FULL                  | T2    |  1000K|    14M|  5113   (1)| 00:00:01 |                                                     
-------------------------------------------------------------------------------------------------                                                     
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   4 - access("T2"."RAND"="T1"."ID")                                                                                                                  
   5 - filter("T1"."N2"=50)                                                                                                                           
   6 - access("T1"."N1"=50)                                                                                                                           
                                                                                                                                                      
SQL> 

Having the extended statistics allowed the optimizer to estimate the correct cardinality between columns n1 and n2, providing a better initial plan. Now things will get a bit interesting; table T2 is altered and put in-memory:

SQL> 
SQL> alter table t2 inmemory;
SQL> 

The same query is executed again and the plan changes because of T2 now being in-memory, even though the new vector transformation has not been applied:

SQL> 
SQL> select
  2  	     /*+
  3  		     qb_name(main)
  4  	     */
  5  	     count(*)
  6  from    (
  7  	     select
  8  		     /*+ qb_name(inline) */
  9  		     distinct t1.v1, t2.v1
 10  	     from
 11  		     t1,t2
 12  	     where
 13  		     t1.n1 = 50
 14  	     and     t1.n2 = 50
 15  	     and     t2.rand = t1.id
 16  	     )
 17  ;

      9912                                                                                                                                            
SQL> 
SQL> select * from table(dbms_xplan.display_cursor);

SQL_ID  a7gb3ncf922zz, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
select         /*+                 qb_name(main)         */                                                                                           
count(*) from    (         select                 /*+ qb_name(inline)                                                                                 
*/                 distinct t1.v1, t2.v1         from                                                                                                 
t1,t2         where                 t1.n1 = 50         and     t1.n2 =                                                                                
50         and     t2.rand = t1.id         )                                                                                                          
                                                                                                                                                      
Plan hash value: 106371239                                                                                                                            
                                                                                                                                                      
----------------------------------------------------------------------------------------------------                                                  
| Id  | Operation                                | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                                  
----------------------------------------------------------------------------------------------------                                                  
|   0 | SELECT STATEMENT                         |         |       |       |   223 (100)|          |                                                  
|   1 |  SORT AGGREGATE                          |         |     1 |       |            |          |                                                  
|   2 |   VIEW                                   |         | 10001 |       |   223  (15)| 00:00:01 |                                                  
|   3 |    HASH UNIQUE                           |         | 10001 |   351K|   223  (15)| 00:00:01 |                                                  
|*  4 |     HASH JOIN                            |         | 10001 |   351K|   222  (14)| 00:00:01 |                                                  
|   5 |      JOIN FILTER CREATE                  | :BF0000 |   100 |  2100 |     3   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T1      |   100 |  2100 |     3   (0)| 00:00:01 |                                                  
|*  7 |        INDEX RANGE SCAN                  | T1_N1   |   100 |       |     1   (0)| 00:00:01 |                                                  
|   8 |      JOIN FILTER USE                     | :BF0000 |  1000K|    14M|   209  (11)| 00:00:01 |
|*  9 |       TABLE ACCESS INMEMORY FULL         | T2      |  1000K|    14M|   209  (11)| 00:00:01 |                                                  
----------------------------------------------------------------------------------------------------                                                  
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   4 - access("T2"."RAND"="T1"."ID")                                                                                                                  
   6 - filter("T1"."N2"=50)                                                                                                                           
   7 - access("T1"."N1"=50)                                                                                                                           
   9 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"T2"."RAND"))
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"T2"."RAND"))
                                                                                                                                                      
SQL> 

The cost of the tablescan was reduced considerably, from 5113 to 209 but that isn’t the most thought-provoking part of the plan, however, as a serial Bloom filter is used for the hash join due to the in-memory code path. With a table stored ‘normally’ (not in-memory) a Bloom filter would not be effective; with the in-memory option a Bloom filter can use enhancements such as simultaneous multiple probe access into table t2 (in this example) in addition to the benefits of avoiding disk access, disk buffer activity and row-by-row data retrieval. All of those avoidances reduce CPU time considerably when compared to a ‘regular’ table, one that is not in-memory.

The in-memory column store is a very nice addition to an already powerful database, made even better by new optimizer transformations that speed data access by providing execution paths not available for tables accessed from disk. ‘Experiment’ with the in-memory option to see what performance improvements it might provide; you might be pleasantly surprised at what you find.

Sometimes unexpected change is good.