My favorites -

General concepts -

1. Why there is implicit commit before and after executing DDL Statements

2. Implicit commit with DDLs – AskTom

3. Why not use Redo log for consistent read

4. Difference between redo logs and undo tablespace

5. What does redo log files hold?

6. Why Hash Join goes slower?

 SQL and PL/SQL – 

1.

2.

How did you learn… Cary Millsap ?

One not-so-fine evening, while at work, I was stuck with a SQL performance issue. Over the internet, I found an article by Cary Millsap titled Why You Should Focus on LIOs Instead of PIOs. Going through the content, I got some further doubts. Finally, as always, I resorted to the OTN forums and posted my doubts.

For those who are not aware about OTN, it stands for Oracle Technology Network. A technical discussion forum where technologists and enthusiasts meet and discuss their concerns.

Back to the topic…

In no time, I was helped by some of the most renowned database experts including the author himself, Cary. :)

After my doubts were cleared, overwhelmed by his knowledge and efforts in helping me, I contacted him. Then I asked him few further technical doubts and finally, a peculiar (silly!) question: “How did you learn so much stuff about Oracle”. :P

And, here was his reply – http://carymillsap.blogspot.in/2014/02/how-did-you-learn-so-much-stuff-about.html

A complete post on his blog. :) Thanks a lot, Cary!!!

Learning about ASH and AWR -

Lately, I have become a l’il more serious and concerned about SQL (and database) performance tuning, which at some point or the other, becomes an absolute necessity.
So, to take the tuning activity to the next higher level, beyond the execution plans, I thought of delving into the concepts of AWR (Automatic Workload Repository) and ASH (Active Session History).

I believe, this is the point, where a Developer meets a DBA course of activity.

So, my quest for some good starter material, halted on this amazing video tutorial on YouTube “Performance Tuning with ASH and AWR data” by Kellyn Pot’vin, a former Oracle ACE Director.

Towrads the end, she advised to go through some nice resources/blogs, and I have noted the same here for my future reference purpose:

1) Karl Arao — http://karlarao.wordpress.com

2) Tyler Muth — http://tylermuth.wordpress.com

3) Kyle Hailey, John Beresniewicz, Graham Wood — http://ashmasters.com

4) Kellyn Pot’vin — http://dbakevlar.com/2011/02/for-the-love-of-awr-and-ash/

Really appreciate Kellyn for the wonderful video tutorial. Thanks!!!

What is SYS_CONTEXT ??

SYS_CONTEXT is an Oracle function which can be used to retrieve useful information about the Oracle environment. This can be used both in SQL and PL/SQL.

WITH user_environment(param, param_value) AS
(
select 'AUDITED_CURSORID', SYS_CONTEXT('USERENV','AUDITED_CURSORID') from dual UNION ALL
select 'AUTHENTICATED_IDENTITY', SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY') from dual UNION ALL
select 'AUTHENTICATION_DATA', SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') from dual UNION ALL
select 'AUTHENTICATION_METHOD', SYS_CONTEXT('USERENV','AUTHENTICATION_METHOD') from dual UNION ALL
select 'BG_JOB_ID', SYS_CONTEXT('USERENV','BG_JOB_ID') from dual UNION ALL
select 'CLIENT_IDENTIFIER', SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') from dual UNION ALL
select 'CLIENT_INFO', SYS_CONTEXT('USERENV','CLIENT_INFO') from dual UNION ALL
select 'CURRENT_BIND', SYS_CONTEXT('USERENV','CURRENT_BIND') from dual UNION ALL
select 'CURRENT_EDITION_ID', SYS_CONTEXT('USERENV','CURRENT_EDITION_ID') from dual UNION ALL
select 'CURRENT_EDITION_NAME', SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME') from dual UNION ALL
select 'CURRENT_SCHEMA', SYS_CONTEXT('USERENV','CURRENT_SCHEMA') from dual UNION ALL
select 'CURRENT_SCHEMAID', SYS_CONTEXT('USERENV','CURRENT_SCHEMAID') from dual UNION ALL
select 'CURRENT_SQL', SYS_CONTEXT('USERENV','CURRENT_SQL') from dual UNION ALL
select 'CURRENT_SQLn', SYS_CONTEXT('USERENV','CURRENT_SQLn') from dual UNION ALL
select 'CURRENT_SQL_LENGTH', SYS_CONTEXT('USERENV','CURRENT_SQL_LENGTH') from dual UNION ALL
select 'CURRENT_USER', SYS_CONTEXT('USERENV','CURRENT_USER') from dual UNION ALL
select 'CURRENT_USERID', SYS_CONTEXT('USERENV','CURRENT_USERID') from dual UNION ALL
select 'DATABASE_ROLE', SYS_CONTEXT('USERENV','DATABASE_ROLE') from dual UNION ALL
select 'DB_DOMAIN', SYS_CONTEXT('USERENV','DB_DOMAIN') from dual UNION ALL
select 'DB_NAME', SYS_CONTEXT('USERENV','DB_NAME') from dual UNION ALL
select 'DB_UNIQUE_NAME', SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') from dual UNION ALL
select 'DBLINK_INFO', SYS_CONTEXT('USERENV','DBLINK_INFO') from dual UNION ALL
select 'ENTRYID', SYS_CONTEXT('USERENV','ENTRYID') from dual UNION ALL
select 'ENTERPRISE_IDENTITY', SYS_CONTEXT('USERENV','ENTERPRISE_IDENTITY') from dual UNION ALL
select 'FG_JOB_ID', SYS_CONTEXT('USERENV','FG_JOB_ID') from dual UNION ALL
select 'GLOBAL_CONTEXT_MEMORY', SYS_CONTEXT('USERENV','GLOBAL_CONTEXT_MEMORY') from dual UNION ALL
select 'GLOBAL_UID', SYS_CONTEXT('USERENV','GLOBAL_UID') from dual UNION ALL
select 'HOST', SYS_CONTEXT('USERENV','HOST') from dual UNION ALL
select 'IDENTIFICATION_TYPE', SYS_CONTEXT('USERENV','IDENTIFICATION_TYPE') from dual UNION ALL
select 'INSTANCE', SYS_CONTEXT('USERENV','INSTANCE') from dual UNION ALL
select 'INSTANCE_NAME', SYS_CONTEXT('USERENV','INSTANCE_NAME') from dual UNION ALL
select 'IP_ADDRESS', SYS_CONTEXT('USERENV','IP_ADDRESS') from dual UNION ALL
select 'ISDBA', SYS_CONTEXT('USERENV','ISDBA') from dual UNION ALL
select 'LANG', SYS_CONTEXT('USERENV','LANG') from dual UNION ALL
select 'LANGUAGE', SYS_CONTEXT('USERENV','LANGUAGE') from dual UNION ALL
select 'MODULE', SYS_CONTEXT('USERENV','MODULE') from dual UNION ALL
select 'NETWORK_PROTOCOL', SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') from dual UNION ALL
select 'NLS_CALENDAR', SYS_CONTEXT('USERENV','NLS_CALENDAR') from dual UNION ALL
select 'NLS_CURRENCY', SYS_CONTEXT('USERENV','NLS_CURRENCY') from dual UNION ALL
select 'NLS_DATE_FORMAT', SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') from dual UNION ALL
select 'NLS_DATE_LANGUAGE', SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') from dual UNION ALL
select 'NLS_SORT', SYS_CONTEXT('USERENV','NLS_SORT') from dual UNION ALL
select 'NLS_TERRITORY', SYS_CONTEXT('USERENV','NLS_TERRITORY') from dual UNION ALL
select 'OS_USER', SYS_CONTEXT('USERENV','OS_USER') from dual UNION ALL
select 'POLICY_INVOKER', SYS_CONTEXT('USERENV','POLICY_INVOKER') from dual UNION ALL
select 'PROXY_ENTERPRISE_IDENTITY',SYS_CONTEXT('USERENV','PROXY_ENTERPRISE_IDENTITY') from dual UNION ALL
select 'PROXY_USER', SYS_CONTEXT('USERENV','PROXY_USER') from dual UNION ALL
select 'PROXY_USERID', SYS_CONTEXT('USERENV','PROXY_USERID') from dual UNION ALL
select 'SERVER_HOST', SYS_CONTEXT('USERENV','SERVER_HOST') from dual UNION ALL
select 'SERVER_NAME', SYS_CONTEXT('USERENV','SERVICE_NAME') from dual UNION ALL
select 'SESSION_EDITION_ID', SYS_CONTEXT('USERENV','SESSION_EDITION_ID') from dual UNION ALL
select 'SESSION_EDITION_NAME', SYS_CONTEXT('USERENV','SESSION_EDITION_NAME') from dual UNION ALL
select 'SESSION_USER', SYS_CONTEXT('USERENV','SESSION_USER') from dual UNION ALL
select 'SESSION_USERID', SYS_CONTEXT('USERENV','SESSION_USERID') from dual UNION ALL
select 'SESSIONID', SYS_CONTEXT('USERENV','SESSIONID') from dual UNION ALL
select 'SID', SYS_CONTEXT('USERENV','SID') from dual UNION ALL
select 'STATEMENTID', SYS_CONTEXT('USERENV','STATEMENTID') from dual UNION ALL
select 'TERMINAL', SYS_CONTEXT('USERENV','TERMINAL') from dual
)
---------
SELECT 
   param, 
   param_value
FROM user_environment;

Indexes on any table -

This is a small query I always keep handy, given the table name as input, it lists down 
all the Indexes and its constituent column(s).


WITH indx AS(
	SELECT
		index_owner,
		index_name,
		table_name,
		LISTAGG(column_name, ', ') WITHIN GROUP (
			ORDER BY column_position ASC
		) lst 
	FROM
		all_ind_columns
	WHERE
		table_name IN ('TABLE_1','TABLE_2') -->>-- input: Table name(s)
	GROUP BY
		index_owner,
		index_name,
		table_name
)
SELECT
	x.*,
	DECODE(REGEXP_COUNT(x.lst,','),
		0,'Normal Index',
		'Composite Index'
	) idx_type -->>-- identifies the Index is a Normal or Composite index
FROM indx x
ORDER BY x.table_name;

Utility to generate DB Object scripts -

Hi,

Caution: Work-in-Progress

Trying to develop an utility to generate DB Object scripts customized for my personal use.

This might fall in the category called *reinventing-the-wheel* as DBMS_METADATA package does a very similar work, but not same.

Let’s see how it goes:

DECLARE
	TYPE curtyp IS REF CURSOR;

	--------------------------------------- 
	--	declaring all Nested Table types 
	---------------------------------------
	TYPE colname_ntt IS TABLE OF all_tab_columns.column_name%TYPE;
	TYPE tabname_ntt IS TABLE OF all_tab_columns.table_name%TYPE;
	TYPE data_typ_len_ntt IS TABLE OF VARCHAR2(20);

	v_tab_name VARCHAR2(50 CHAR) := 'TABLE_1,TABLE_2,ranit_biswas';

	table_not_found EXCEPTION;
	---------------------------------------
	curs curtyp;
	cols colname_ntt;
	tabs tabname_ntt;
	data_typ_len data_typ_len_ntt; 
	---------------------------------------
	v_table_exists NUMBER := 0;
	

BEGIN

	FOR x IN (
	  SELECT RTRIM(REGEXP_SUBSTR(v_tab_name,'(,?)([^,]*)(,?)',1,LEVEL),',') v_tab_name1
	  FROM dual
	  CONNECT BY LEVEL <= (LENGTH(v_tab_name)-LENGTH(REPLACE(v_tab_name,',')))+1
	)
	LOOP
		BEGIN
			SELECT COUNT(1) INTO v_table_exists
			FROM USER_TABLES
			WHERE table_name = x.v_tab_name1;

				IF(v_table_exists  0) THEN
					OPEN curs FOR
					SELECT 
						table_name, 
						column_name,
						data_type||'('||data_length||')' 
					FROM all_tab_columns
					WHERE table_name = x.v_tab_name1;

					FETCH curs 
					BULK COLLECT INTO tabs, cols, data_typ_len;
					CLOSE curs;

					DBMS_OUTPUT.PUT_LINE('PROMPT CREATE TABLE '||LOWER(x.v_tab_name1));
					DBMS_OUTPUT.PUT_LINE('CREATE TABLE '||LOWER(x.v_tab_name1)||'(');

					FOR i IN tabs.first .. tabs.last
					LOOP
						DBMS_OUTPUT.PUT_LINE(
							CHR(9)||CHR(9)||(CASE WHEN i  tabs.FIRST THEN ', ' END)||LOWER(cols(i))||
							CHR(9)||(
                CASE 
                  WHEN (data_typ_len(i) LIKE '%DATE%') THEN 'DATE' 
                  WHEN (data_typ_len(i) LIKE '%NUMBER%') THEN 'NUMBER'
                  ELSE data_typ_len(i) 
                END
              ) 
						);
					END LOOP;

					DBMS_OUTPUT.PUT_LINE(');'||CHR(10)||CHR(10));
				
				ELSE
					RAISE table_not_found;
				END IF;
		EXCEPTION
		WHEN table_not_found THEN
			DBMS_OUTPUT.PUT_LINE(
'
---+----------------------------------------------------------------------------------------------------------+
---+--'||CHR(9)||'Table: ['||UPPER(x.v_tab_name1)||'] is not found.
---+----------------------------------------------------------------------------------------------------------+');
		END;
	END LOOP;

END;

END;

But there are several pending action items which makes it *Work-in-Progress*.
– Have to merge the “Index Creation” logic and “Alter Table” to add Primary Keys.
– Foreign Key relationship

-----------------------------------------------------------
-- PRIMARY KEY
-----------------------------------------------------------
WITH pkey AS(
    SELECT 
      LOWER(table_name) tbl_name,
      LOWER(index_name) idx_name,
      LISTAGG(CHR(9)||LOWER(column_name),','||CHR(10)) WITHIN GROUP (ORDER BY column_position) lst
    FROM user_ind_columns uic
    WHERE 
      EXISTS(
        SELECT 1 FROM user_constraints uc
        WHERE 
          uic.index_name = uc.constraint_name
          AND uc.constraint_type = 'P'
      )
    GROUP BY table_name,index_name
)
SELECT 
'
ALTER TABLE '||tbl_name||'
ADD CONSTRAINT '||idx_name||' PRIMARY KEY('
||CHR(10)||LST||CHR(10)||
');'
FROM pkey
WHERE UPPER(tbl_name) = ''; 



-----------------------------------------------------------
-- INDEXES // to be re-structured
-----------------------------------------------------------
WITH pkey AS(
    SELECT 
      LOWER(table_name) tbl_name,
      LOWER(index_name) idx_name,
      LISTAGG(CHR(9)||LOWER(column_name),','||CHR(10)) WITHIN GROUP (ORDER BY column_position) lst
    FROM user_ind_columns uic
    WHERE 
      NOT EXISTS(
        SELECT 1 FROM user_constraints uc
        WHERE 
          uic.index_name = uc.constraint_name
          AND uc.constraint_type = 'P'
      )
    GROUP BY table_name,index_name
)
SELECT 
'
CREATE INDEX '||idx_name||CHR(10)||' ON '||tbl_name||' ( 
  '||lst||'
);'
FROM pkey
WHERE UPPER(tbl_name) = ''; 



-----------------------------------------------------------
-- FOREIGN KEYS // yet to start
-----------------------------------------------------------

Thanks and Regards,
— Ranit

GRANT ‘All Privileges’ -

Hi,

This is regarding an issue faced on creating a new User (or Schema) and then trying to create a table there. And this happened even after giving CREATE TABLE and CREATE SESSION grants to the new user.

Here goes a small demonstration :

/* as SYSDBA role */
ranit@XE11GR2>> grant create session, create table, create procedure to rb1;
Grant succeeded.

And then, when tried to create a table in User ‘rb1′

ranit@XE11GR2>> create table x as
2 select 1 c from dual;
select 1 c from dual
*
ERROR at line 2:
ORA-01950: no privileges on tablespace 'SYSTEM'

After spending some time with this error, a grant UNLIMITED TABLESPACE seemed to fix this.
So, just for future reference, below I’m listing all the Grants covered when ALL PRIVILEGES is issued to an user.

ranit@XE11GR2>> select * from
2 user_sys_privs;

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

200 rows selected.

The above workout is on Oracle Database 11g Express Edition Release 11.2.0.2.0 – Production

Follow

Get every new post delivered to your Inbox.