Thursday, July 14, 2016

INTERVAL conversion functions

›
There are 4 convertions functions that deal with INTERVAL datatypes, as listed below. The two first converts strings, the later two convert...
Wednesday, July 13, 2016

SYSTIMESTAMP and SYSDATE VS. CURRENT_TIMESTAMP and CURRENT_DAY

›
Most DBAs I've spoken to are familiar with the function SYSDATE, but there are some other functions, too, that may prove to be useful, e...
Tuesday, July 12, 2016

Overview of role privileges

›
The following UNION can be handy to get an overview of what system privileges, object privileges and other roles a specific role may contain...

How does Oracle decide which roles should be enabled in a session?

›
Oracle uses the concept of default roles to decide whether or not a role should be enabled when a user establishes a session. CREATE USER ...
Wednesday, July 6, 2016

A workaround for ORA-02287: sequence number not allowed here when using GROUP BY in a subquery

›
When you use GROUP BY in the subquery of an INSERT statement, Oracle will throw ORA-02287: sequence number not allowed here if you try t...
1 comment:
Thursday, June 30, 2016

How to extract the base tables used by a view

›
To extract the tables being used in a view, you can query the USER_DEPENDENCIES view, like this: SELECT NAME, TYPE, REFERENCED_NAME,REFERE...
Monday, June 20, 2016

How to list all users and their number of owned objects

›
Join dba_users with dba_objects using a correlated subquery, and you'll get all the schemas, both those which own objects and those who...