Category Archives: SQL

Oracle auditing insert, update, and delete on a table

July 3, 2009 Larry Catt Leave a comment

Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. Care should be taken on what you wish to audit, due to the amount of disk space required to store all of this information. In this article we will discuss the auditing of insert, update, and deletes by user account access.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jul 03 19:18:21 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Ensure that audit_trail is set to DB_EXTENDED and audit_sys_operations parameter is set to TRUE. Note: It is important to realize that setting the AUDIT_TRAIL to DB_EXTENDED will dramatically increase storage, so monitor this closely.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE

SQL>

System altered.

SQL> alter system set audit_sys_operations= TRUE scope=spfile;

System altered.

SQL>

3. Restart the database if you had to edit the init parameters.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 209718148 bytes
Database Buffers 394264576 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

4. Ensure that the new values took effect.

SQL> show parameter audit

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /U01/ORACLE/PRODUCT/10.2.0/ADMIN
/ORCL10G/ADUMP
audit_sys_operations boolean TRUE
audit_trail string TRUE
SQL>

5. Execute the audit command below to begin monitoring changes to the table SCOTT.DEPT.

SQL> audit insert, update, delete on scott.dept by session;

Audit succeeded.

SQL>

6. Connect to the database as the user LJCATT and perform a insert, update and delete operation on the table scott.dept.

SQL> connect ljcatt/ljcatt
Connected.
SQL> insert into scott.dept(deptno, dname, loc) values(50,’PARTNERS’,’NEW YORK’)
;

1 row created.

SQL> commit;

Commit complete.

SQL> update scott.dept set loc=’WASHINGTON’ where deptno=50;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from scott.dept where deptno=50;

1 row deleted.

SQL> commit;

Commit complete.

SQL>

7. Login as sysdba again.

SQL> connect sys as sysdba
SQL>

8. Execute the following SQL to extract the statements executed by LJCATT.
â€œselect timestamp, sql_text from dba_audit_object where username=’LJCATT’;â€

SQL> select timestamp, sql_text from dba_audit_object where username=’LJCATT’;

TIMESTAMP SQL_TEXT
——— ——————————————————-
27-AUG-09 update scott.dept set loc=’WASHINGTON’ where deptno=50
27-AUG-09 insert into scott.dept(deptno, dname, loc) values(50,’PARTNERS’,’NEW YORK’)
27-AUG-09 delete from scott.dept where deptno=50

SQL>

9. That completes the use of Oracle Auditing to monitor the changes to the Oracle database by users.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Disabling and Enabling Oracle Jobs with DBMS_JOB

May 25, 2009 Larry Catt Leave a comment

Oracle provides the ability to schedule predetermined jobs via the package DBMS_JOB, just as you would in a UNIX environment with CRON or the Windows environment with SCHEDULER. Normally, a production database will have several dozen pre-scheduled jobs executing at various times of the day. There are times when you will need to disable a JOB or all jobs due to various events. This article explains the use of procedure DBMS_JOB.BROKEN which allows you to disable a job and re-enable it once you are finished. This procedure will work in Oracle 8, 8i, 9i, 10g, and 11g.

NOTE: only the user whom own a job can modify with the package DBMS_JOB, if you logon to the database as an administrator and want to disable jobs you must use the package DBMS_IJOB.

1. Logon to SQL*PLUS as sysdba

Mylinux> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.3.0 – Production on Tue May 25 10:19:38 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options

SQL>

2. To disable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(‘||job||’,true);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(‘||job||’,true);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(‘||JOB||’,TRUE);’
———————————————————————
exec dbms_ijob.broken(1,true);
exec dbms_ijob.broken(2,true);
exec dbms_ijob.broken(4,true);
exec dbms_ijob.broken(5,true);
exec dbms_ijob.broken(41,true);
exec dbms_ijob.broken(42,true);
exec dbms_ijob.broken(370,true);
exec dbms_ijob.broken(371,true);
exec dbms_ijob.broken(1605,true);
exec dbms_ijob.broken(2204,true);
exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);

13 rows selected.

SQL>

3. Execute the SQL generated by the step above to disable all jobs.

exec dbms_ijob.broken(2224,true);
exec dbms_ijob.broken(2225,true);
exec dbms_ijob.broken(2245,true);
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.
…
…
…

4. To re-enable all jobs on the system use the following SQL statement to generate your SQL:
“select ‘exec dbms_ijob.broken(‘||job||’,false);’ from dba_jobs;”

SQL> select ‘exec dbms_ijob.broken(‘||job||’,false);’ from dba_jobs;

‘EXECDBMS_IJOB.BROKEN(‘||JOB||’,FALSE);’
———————————————————————-
exec dbms_ijob.broken(1,false);
exec dbms_ijob.broken(2,false);
exec dbms_ijob.broken(4,false);
exec dbms_ijob.broken(5,false);
exec dbms_ijob.broken(41,false);
exec dbms_ijob.broken(42,false);
exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
exec dbms_ijob.broken(2224,false);
exec dbms_ijob.broken(2225,false);
exec dbms_ijob.broken(2245,false);

5. Execute the SQL generated by the step above to enable all jobs.

exec dbms_ijob.broken(370,false);
exec dbms_ijob.broken(371,false);
exec dbms_ijob.broken(1605,false);
exec dbms_ijob.broken(2204,false);
SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

6. This completes the disablement and enablement of jobs with DBMS_JOB package.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle RESOURCE role

May 20, 2009 Larry Catt Leave a comment

Oracle provides several predefined roles for various functions within the database. Roles are defined as a grouping of system and object privileges which can be granted to a user instead of assigning individual privileges. This functionality can be extremely helpful when you have to assign hundreds of privileges to a large number of users, which is a typical function in most systems. Additionally, changes to privileges for groups of users can easily be accomplished by altering the definition of the role instead of changing each individual user account. In this article we will discuss the use of the Oracle defined role RESOURCE.

The RESOURCE role is used to give an individual the right to create any object within his/her own schema. This role is normally used to define a developerâ€™s privilege within a test and development system. It allows a developer to build objects necessary to create a system without impacting other developers on the same system. The following instructions define how to create a user account, assign the RESOURCE role to that user and lists the privileges granted by RESOURCE role.

1. Logon to you Oracle database as sysdba.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Wed May 20 17:16:49 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Create a permanent tablespace for your new user.

SQL> create tablespace devl datafile ‘/U01/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL10G/d
evl01.dbf’ size 5000m;

Tablespace created.

SQL>

3. Create a temporary tablespace for you new user.

SQL> create temporary tablespace devl_temp tempfile ‘/U01/ORACLE/PRODUCT/10.2.0/OR
ADATA/ORCL10G/devl_temp01.dbf’ size 2500m;

Tablespace created.

SQL>

4. Create you development user.

SQL> Create user developer01 identified by developer
2 Default tablespace devl
3 Quota unlimited on devl
4 Temporary tablespace devl_temp;

User created.

SQL>

5. You have to grant create session directly to the user developer01. NOTE: â€œcreate sessionâ€ is not part of the RESOURCE role.

SQL> grant create session to developer01;

Grant succeeded.

SQL>

6. Grant the role RESOURCE to the user developer01.

SQL> grant resource to developer01;

Grant succeeded.

SQL>

7. Now you can logon and test the new user account by creating an object.

SQL> connect developer01/developer
Connected.
SQL> create table test1(testing varchar2(30));

Table created.

SQL> select tablespace_name from user_tables where table_name=’TEST1′;

TABLESPACE_NAME
——————————
DEVL

SQL>

8. The following PL/SQL block gives you all of the privileges granted to a specific role where the variable V_USER defines the role.

set serveroutput on

declare

v_ct number;
v_user varchar2(30):=’RESOURCE’;

begin

for role in(select * from dba_role_privs where grantee=v_user)
loop

if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

for sys_priv in(select * from dba_sys_privs where grantee=v_user)
loop

if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

for tab_priv in(select * from dba_tab_privs where grantee=v_user)
loop

if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/

SQL> set serveroutput on

declare

v_ct number;
v_user varchar2(30):=’RESOURCE’;

begin

for role in(select * from dba_role_privs where grantee=v_user)
loop

if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

for sys_priv in(select * from dba_sys_privs where grantee=v_user)
loop

if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

for tab_priv in(select * from dba_tab_privs where grantee=v_user)
loop

if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/
SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
31 32 33 34 35 36 37 38 39 40 41 42 grant CREATE TYPE to
RESOURCE;
grant CREATE TABLE to RESOURCE;
grant CREATE CLUSTER to RESOURCE;
grant CREATE TRIGGER to RESOURCE;
grant CREATE OPERATOR to RESOURCE;
grant CREATE SEQUENCE to RESOURCE;
grant CREATE INDEXTYPE to RESOURCE;
grant CREATE PROCEDURE to RESOURCE;
grant CREATE TYPE to RESOURCE;
grant CREATE TABLE to RESOURCE;
grant CREATE CLUSTER to RESOURCE;
grant CREATE TRIGGER to RESOURCE;
grant CREATE OPERATOR to RESOURCE;
grant CREATE SEQUENCE to RESOURCE;
grant CREATE INDEXTYPE to RESOURCE;
grant CREATE PROCEDURE to RESOURCE;

PL/SQL procedure successfully completed.

SQL>

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Oracle, Oracle 10g, oracle 9i, SQL

Recreate the Oracle DDL for a PROFILE

May 19, 2009 Larry Catt Leave a comment

Oracle uses PROFILES to limit the access and resources a user has within an Oracle database. In a properly secured Oracle environment care must be taken in defining resource limits of your users. This article covers the creation of the DDL to define an Oracle profile already established in one database and recreate it in another.

1. Connect to your Oracle database server and logon to SQL*PLUS.

MyLinux:>sqlplus ‘\ as sysdba’
SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 19 13:30:13 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

2. Create the profile TEST_PROFILE to demonstrate the recreation of the DDL to generate the profile in another database.

SQL> create profile test_profile limit IDLE_TIME 15
2 CONNECT_TIME 30
3 FAILED_LOGIN_ATTEMPTS 3
4 PASSWORD_LIFE_TIME 5
5 PASSWORD_REUSE_TIME 3
6 PASSWORD_REUSE_MAX 3
7 PASSWORD_LOCK_TIME 30
8 PASSWORD_GRACE_TIME 30
9 SESSIONS_PER_USER 2;

Profile created.

SQL>

3. Execute the following PL/SQL block to recreate the DDL for your profile.

set serveroutput on
spool ./create_profile.lst

declare

v_ct number;
v_profile varchar2(30):=’TEST_PROFILE’;
begin

dbms_output.put_line(‘create profile ‘||v_profile||’ limit ‘);

for v_limit in(select resource_name, limit from dba_profiles
where profile = v_profile)
loop

dbms_output.put_line(v_limit.resource_name||’ ‘||v_limit.limit);

end loop;
end;
/

SQL> set serveroutput on
SQL>
SQL> spool ./create_profile.lst
SQL>
SQL> declare
2
3 v_ct number;
4 v_profile varchar2(30):=’TEST_PROFILE’;
5 begin
6
7 dbms_output.put_line(‘create profile ‘||v_profile||’ limit ‘);
8
9 for v_limit in(select resource_name, limit from dba_profiles
10 where profile = v_profile)
11 loop
12
13 dbms_output.put_line(v_limit.resource_name||’ ‘||v_limit.limit);
14
15 end loop;
16 end;
17 /
create profile TEST_PROFILE limit
COMPOSITE_LIMIT DEFAULT
SESSIONS_PER_USER 2
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
IDLE_TIME 15
CONNECT_TIME 30
PRIVATE_SGA DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 5
PASSWORD_REUSE_TIME 3
PASSWORD_REUSE_MAX 3
PASSWORD_VERIFY_FUNCTION DEFAULT
PASSWORD_LOCK_TIME 30
PASSWORD_GRACE_TIME 30

PL/SQL procedure successfully completed.

SQL>

4. With the DDL generated you can recreate the TEST_PROFILE in any other Oracle database environment.

Larry J Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, linux, Oracle 10g, oracle 9i, SQL

Oracle DBA role

May 4, 2009 Larry Catt Leave a comment

The DBA role is used to give an individual user the right to administrator an oracle database. This role is normally only granted to users who have a need to view dictionary level views and administrate other user accounts. NOTE: Great care has to be used when assigning the DBA role, because users who have it can perform almost every action in the database short of actually shutting down the system. The following instructions define how to create a user account, assign the DBA role to that user and lists the privileges granted by DBA role.

1. Logon the you Oracle database as sysdba.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon May 4 21:56:18 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

2. Create a permanent tablespace for your new user.

SQL> create tablespace admin datafile ‘/U01/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL10G/admin01.dbf’ size 5m;

Tablespace created.

SQL>

3. Create a temporary tablespace for you new user.

SQL> create temporary tablespace admin_temp tempfile ‘/U01/ORACLE/PRODUCT/10.2.0/O
RADATA/ORCL10G/admin_temp01.dbf’ size 5m;

Tablespace created.

SQL>

4. Create you administrative user.

SQL> Create user admin01 identified by admin
2 Default tablespace admin
3 Quota unlimited on admin
4 Temporary tablespace admin_temp;

User created.

SQL>

5. Grant the role DBA to the user developer01.

SQL> grant dba to admin01;

Grant succeeded.

SQL>

6. Connect to the system as the admin01 user and perform a select from a system view.

SQL> connect admin01/admin
Connected.
SQL> select count(*) from v$session;

COUNT(*)
———-
18

SQL>

7. The PL/SQL block below will list all privileges contained in the role DBA, where the variable V_USER defines the roleâ€™s privileges to be displayed.
set serveroutput on

declare

v_ct number;
v_user varchar2(30):=’DBA’;

begin

for role in(select * from dba_role_privs where grantee=v_user)
loop

if role.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’ with
admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||role.granted_role||’ to ‘||role.grantee||’;’);
end if;
end loop;

for sys_priv in(select * from dba_sys_privs where grantee=v_user)
loop

if sys_priv.admin_option = ‘YES’
then
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to ‘||sys_priv.grantee||’
with admin option’||’;’);
else
dbms_output.put_line(‘grant ‘||sys_priv.privilege||’ to
‘||sys_priv.grantee||’;’);
end if;
end loop;

for tab_priv in(select * from dba_tab_privs where grantee=v_user)
loop

if tab_priv.grantable = ‘YES’
then
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’ with
grant option;’);
else
dbms_output.put_line(‘grant ‘||tab_priv.privilege||’ on
‘||tab_priv.owner||’.’||tab_priv.table_name||’ to ‘||tab_priv.grantee||’;’);
end if;
end loop;
end;
/

34
35 36 37 38 39 40 41 42
grant XDBADMIN to DBA;
grant JAVA_ADMIN to DBA;
grant JAVA_DEPLOY to DBA;
grant WM_ADMIN_ROLE to DBA;
grant SCHEDULER_ADMIN to DBA with admin option;
grant EXP_FULL_DATABASE to DBA;
grant IMP_FULL_DATABASE to DBA;
grant DELETE_CATALOG_ROLE to DBA with admin option;
grant SELECT_CATALOG_ROLE to DBA with admin option;
grant EXECUTE_CATALOG_ROLE to DBA with admin option;
grant GATHER_SYSTEM_STATISTICS to DBA;
grant ADVISOR to DBA with admin option;
grant AUDIT ANY to DBA with admin option;
grant DROP USER to DBA with admin option;
grant RESUMABLE to DBA with admin option;
grant ALTER USER to DBA with admin option;
grant CREATE JOB to DBA with admin option;
grant ANALYZE ANY to DBA with admin option;
grant BECOME USER to DBA with admin option;
grant CREATE ROLE to DBA with admin option;
grant CREATE RULE to DBA with admin option;
grant CREATE TYPE to DBA with admin option;
grant CREATE USER to DBA with admin option;
grant CREATE VIEW to DBA with admin option;
grant ALTER SYSTEM to DBA with admin option;
grant AUDIT SYSTEM to DBA with admin option;
grant CREATE TABLE to DBA with admin option;
grant DROP PROFILE to DBA with admin option;
grant ALTER PROFILE to DBA with admin option;
grant ALTER SESSION to DBA with admin option;
grant DROP ANY ROLE to DBA with admin option;
grant DROP ANY RULE to DBA with admin option;
grant DROP ANY TYPE to DBA with admin option;
grant DROP ANY VIEW to DBA with admin option;
grant QUERY REWRITE to DBA with admin option;
grant ALTER ANY ROLE to DBA with admin option;
grant ALTER ANY RULE to DBA with admin option;
grant ALTER ANY TYPE to DBA with admin option;
grant ALTER DATABASE to DBA with admin option;
grant CREATE ANY JOB to DBA with admin option;
grant CREATE CLUSTER to DBA with admin option;
grant CREATE LIBRARY to DBA with admin option;
grant CREATE PROFILE to DBA with admin option;
grant CREATE SESSION to DBA with admin option;
grant CREATE SYNONYM to DBA with admin option;
grant CREATE TRIGGER to DBA with admin option;
grant DROP ANY INDEX to DBA with admin option;
grant DROP ANY TABLE to DBA with admin option;
grant GRANT ANY ROLE to DBA with admin option;
grant LOCK ANY TABLE to DBA with admin option;
grant MERGE ANY VIEW to DBA with admin option;
grant UNDER ANY TYPE to DBA with admin option;
grant UNDER ANY VIEW to DBA with admin option;
grant ALTER ANY INDEX to DBA with admin option;
grant ALTER ANY TABLE to DBA with admin option;
grant CREATE ANY RULE to DBA with admin option;
grant CREATE ANY TYPE to DBA with admin option;
grant CREATE ANY VIEW to DBA with admin option;
grant CREATE OPERATOR to DBA with admin option;
grant CREATE RULE SET to DBA with admin option;
grant CREATE SEQUENCE to DBA with admin option;
grant DROP TABLESPACE to DBA with admin option;
grant UNDER ANY TABLE to DBA with admin option;
grant ALTER TABLESPACE to DBA with admin option;
grant BACKUP ANY TABLE to DBA with admin option;
grant CREATE ANY INDEX to DBA with admin option;
grant CREATE ANY TABLE to DBA with admin option;
grant CREATE DIMENSION to DBA with admin option;
grant CREATE INDEXTYPE to DBA with admin option;
grant CREATE PROCEDURE to DBA with admin option;
grant DELETE ANY TABLE to DBA with admin option;
grant DROP ANY CLUSTER to DBA with admin option;
grant DROP ANY CONTEXT to DBA with admin option;
grant DROP ANY LIBRARY to DBA with admin option;
grant DROP ANY OUTLINE to DBA with admin option;
grant DROP ANY SYNONYM to DBA with admin option;
grant DROP ANY TRIGGER to DBA with admin option;
grant EXECUTE ANY RULE to DBA with admin option;
grant EXECUTE ANY TYPE to DBA with admin option;
grant INSERT ANY TABLE to DBA with admin option;
grant MANAGE ANY QUEUE to DBA with admin option;
grant MANAGE SCHEDULER to DBA with admin option;
grant SELECT ANY TABLE to DBA with admin option;
grant UPDATE ANY TABLE to DBA with admin option;
grant ALTER ANY CLUSTER to DBA with admin option;
grant ALTER ANY LIBRARY to DBA with admin option;
grant ALTER ANY OUTLINE to DBA with admin option;
grant ALTER ANY TRIGGER to DBA with admin option;
grant COMMENT ANY TABLE to DBA with admin option;
grant CREATE TABLESPACE to DBA with admin option;
grant DEQUEUE ANY QUEUE to DBA with admin option;
grant DROP ANY OPERATOR to DBA with admin option;
grant DROP ANY RULE SET to DBA with admin option;
grant DROP ANY SEQUENCE to DBA with admin option;
grant ENQUEUE ANY QUEUE to DBA with admin option;
grant EXECUTE ANY CLASS to DBA with admin option;
grant FORCE TRANSACTION to DBA with admin option;
grant MANAGE FILE GROUP to DBA with admin option;
grant MANAGE TABLESPACE to DBA with admin option;
grant ON COMMIT REFRESH to DBA with admin option;
grant ALTER ANY RULE SET to DBA with admin option;
grant ALTER ANY SEQUENCE to DBA with admin option;
grant CREATE ANY CLUSTER to DBA with admin option;
grant CREATE ANY CONTEXT to DBA with admin option;
grant CREATE ANY LIBRARY to DBA with admin option;
grant CREATE ANY OUTLINE to DBA with admin option;
grant CREATE ANY SYNONYM to DBA with admin option;
grant CREATE ANY TRIGGER to DBA with admin option;
grant DROP ANY DIMENSION to DBA with admin option;
grant DROP ANY DIRECTORY to DBA with admin option;
grant DROP ANY INDEXTYPE to DBA with admin option;
grant DROP ANY PROCEDURE to DBA with admin option;
grant RESTRICTED SESSION to DBA with admin option;
grant ALTER ANY DIMENSION to DBA with admin option;
grant ALTER ANY INDEXTYPE to DBA with admin option;
grant ALTER ANY PROCEDURE to DBA with admin option;
grant ALTER RESOURCE COST to DBA with admin option;
grant CHANGE NOTIFICATION to DBA with admin option;
grant CREATE ANY OPERATOR to DBA with admin option;
grant CREATE ANY RULE SET to DBA with admin option;
grant CREATE ANY SEQUENCE to DBA with admin option;
grant CREATE EXTERNAL JOB to DBA with admin option;
grant DEBUG ANY PROCEDURE to DBA with admin option;
grant DROP PUBLIC SYNONYM to DBA with admin option;
grant EXECUTE ANY LIBRARY to DBA with admin option;
grant EXECUTE ANY PROGRAM to DBA with admin option;
grant FLASHBACK ANY TABLE to DBA with admin option;
grant GRANT ANY PRIVILEGE to DBA with admin option;
grant READ ANY FILE GROUP to DBA with admin option;
grant SELECT ANY SEQUENCE to DBA with admin option;
grant CREATE ANY DIMENSION to DBA with admin option;
grant CREATE ANY DIRECTORY to DBA with admin option;
grant CREATE ANY INDEXTYPE to DBA with admin option;
grant CREATE ANY PROCEDURE to DBA with admin option;
grant CREATE DATABASE LINK to DBA with admin option;
grant DROP ANY SQL PROFILE to DBA with admin option;
grant EXECUTE ANY OPERATOR to DBA with admin option;
grant EXECUTE ANY RULE SET to DBA with admin option;
grant EXPORT FULL DATABASE to DBA with admin option;
grant GLOBAL QUERY REWRITE to DBA with admin option;
grant IMPORT FULL DATABASE to DBA with admin option;
grant ALTER ANY SQL PROFILE to DBA with admin option;
grant CREATE PUBLIC SYNONYM to DBA with admin option;
grant DEBUG CONNECT SESSION to DBA with admin option;
grant DROP ROLLBACK SEGMENT to DBA with admin option;
grant EXECUTE ANY INDEXTYPE to DBA with admin option;
grant EXECUTE ANY PROCEDURE to DBA with admin option;
grant FORCE ANY TRANSACTION to DBA with admin option;
grant MANAGE ANY FILE GROUP to DBA with admin option;
grant SELECT ANY DICTIONARY to DBA with admin option;
grant ALTER ROLLBACK SEGMENT to DBA with admin option;
grant ANALYZE ANY DICTIONARY to DBA with admin option;
grant CREATE ANY SQL PROFILE to DBA with admin option;
grant SELECT ANY TRANSACTION to DBA with admin option;
grant CREATE ROLLBACK SEGMENT to DBA with admin option;
grant CREATE MATERIALIZED VIEW to DBA with admin option;
grant ADMINISTER SQL TUNING SET to DBA with admin option;
grant CREATE EVALUATION CONTEXT to DBA with admin option;
grant DROP PUBLIC DATABASE LINK to DBA with admin option;
grant DROP ANY MATERIALIZED VIEW to DBA with admin option;
grant GRANT ANY OBJECT PRIVILEGE to DBA with admin option;
grant ADMINISTER DATABASE TRIGGER to DBA with admin option;
grant ADMINISTER RESOURCE MANAGER to DBA with admin option;
grant ALTER ANY MATERIALIZED VIEW to DBA with admin option;
grant CREATE PUBLIC DATABASE LINK to DBA with admin option;
grant DROP ANY EVALUATION CONTEXT to DBA with admin option;
grant ALTER ANY EVALUATION CONTEXT to DBA with admin option;
grant CREATE ANY MATERIALIZED VIEW to DBA with admin option;
grant ADMINISTER ANY SQL TUNING SET to DBA with admin option;
grant CREATE ANY EVALUATION CONTEXT to DBA with admin option;
grant EXECUTE ANY EVALUATION CONTEXT to DBA with admin option;
grant ALTER on SYS.MAP_OBJECT to DBA;
grant DELETE on SYS.MAP_OBJECT to DBA;
grant INSERT on SYS.MAP_OBJECT to DBA;
grant SELECT on SYS.MAP_OBJECT to DBA;
grant UPDATE on SYS.MAP_OBJECT to DBA;
grant ON COMMIT REFRESH on SYS.MAP_OBJECT to DBA;
grant QUERY REWRITE on SYS.MAP_OBJECT to DBA;
grant DEBUG on SYS.MAP_OBJECT to DBA;
grant FLASHBACK on SYS.MAP_OBJECT to DBA;
grant EXECUTE on SYS.DBMS_FLASHBACK to DBA;
grant EXECUTE on SYS.OUTLN_PKG to DBA;
grant EXECUTE on SYS.OUTLN_EDIT_PKG to DBA;
grant EXECUTE on SYS.DBMS_RESUMABLE to DBA;
grant EXECUTE on SYS.DBMS_DEFER_QUERY to DBA;
grant EXECUTE on SYS.DBMS_DEFER_SYS to DBA;
grant EXECUTE on SYS.DBMS_STORAGE_MAP to DBA;
grant UPDATE on WKSYS.WK$SYS_CONFIG to DBA;
grant EXECUTE on PORTAL.WWV_EXPORT to DBA;
grant EXECUTE on SYS.DBMS_LOGSTDBY to DBA;
grant EXECUTE on SYS.DBMS_INTERNAL_LOGSTDBY to DBA;
grant EXECUTE on SYS.DBMS_SERVER_ALERT to DBA;
grant EXECUTE on SYS.DBMS_TDB to DBA;
grant ALTER on SYS.AWSEQ$ to DBA;
grant SELECT on SYS.AWSEQ$ to DBA;
grant SELECT on SYS.AW$ to DBA;
grant DEBUG on SYS.AW$ to DBA;
grant SELECT on SYS.PS$ to DBA;
grant DEBUG on SYS.PS$ to DBA;
grant SELECT on SYS.AW_PROP$ to DBA;
grant DEBUG on SYS.AW_PROP$ to DBA;
grant SELECT on SYS.AW_OBJ$ to DBA;
grant DEBUG on SYS.AW_OBJ$ to DBA;
grant EXECUTE on SYS.DBMS_SERVER_TRACE to DBA;
grant EXECUTE on SYS.DBMS_SERVICE to DBA;
grant EXECUTE on SYS.DBMS_MONITOR to DBA;
grant EXECUTE on SYS.DBMS_WORKLOAD_REPOSITORY to DBA;
grant EXECUTE on SYS.DBMS_UADV_ARR to DBA;
grant EXECUTE on SYS.DBMS_UNDO_ADV to DBA;
grant EXECUTE on SYS.LOAD_UNDO_STAT to DBA;
grant EXECUTE on SYS.RESET_UNDO_STAT to DBA;
grant EXECUTE on SYS.DBMS_FEATURE_USAGE_REPORT to DBA;
grant EXECUTE on SYS.DBMS_WORKLOAD_CAPTURE to DBA;

PL/SQL procedure successfully completed.

SQL>

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, script, security, SQL, sqlplus, unix

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

January 29, 2009 Larry Catt Leave a comment

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

Oracle provides the procedure UTL_FILE to allow for the reading of external OS layer files from within the Oracle RDBMS. This article will give an example of how to read the contents of an external OS file and display it in the Oracle database.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called read.txt with the following text.

This is line one.

3. Logon to SQL*PLUS with an account which has DBA privileges. In this example we are using the account LJCATT.

mylinux:> sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jan 29 23:28:23 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

4. Create the procedure read_file as detailed below.

create or replace procedure read_file
is

v_record varchar2(50);
v_file varchar2(30) := ‘read.txt’;
v_dir varchar2(512) := ‘/home/lufsora’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘r’, 2000);

utl_file.get_line(v_write,v_record, v_record);

dbms_output.put_line(v_record);

utl_file.fclose(v_write);

end read_file;
/

5. Execute the procedure read_file with the command execute and the test within the OS file will be display.

SQL> execute read_file;
This is line one.

PL/SQL procedure successfully completed.

SQL>

This completes the use of UTL_FILE to read OS level files into your Oracle RDBMS.

Larry J. Catt, OCP9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i, privileges, script, SQL, sqlplus

Oracle – ORA-00604: error occurred at recursive SQL level 1 and revoking a user privileges.

January 17, 2009 Larry Catt Leave a comment

A schema is considered all of the objects owned by a single user within the Oracle database. When a user has a privilege revoked in the database, but owns a particular object which requires that privilege, errors will result. This article will recover an error which will result from a user having a privilege revoked which is require by one of the objects he owns.

1. Logon to your Oracle database server as the Oracle software owner.

2. Logon to SQLPLUS with SYSDBA privileges.

mylinux:> sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 17 11:00:09 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. Create the user LJCATT and grant him the DBA role and create procedure privileges.

SQL> create user ljcatt identified by password10;

User created.

SQL> grant dba to ljcatt;

Grant succeeded.

SQL> grant execute on utl_file to ljcatt;

Grant succeeded.

4. Connect as the user ljcatt and create a procedure named test_file.

SQL> connect ljcatt
Enter password:
Connected.

SQL> create or replace procedure test_file
is
2 3 v_record varchar2(50) := ‘Testing file creation’;
4 v_file varchar2(30) := ‘testfile’;
5 v_dir varchar2(100) := ‘/home/lcatt’;
6 v_write utl_file.file_type;
7 begin
8
9 v_write := utl_file.fopen(v_dir, v_file, ‘w’, 4000);

10 11 –utl_file.fopen(v_dir, v_file, ‘w’, 100);
12
13 utl_file.put_line(v_write, v_record);

14 15 utl_file.fclose(v_write);
16
17 end test_file;
/ 18

Procedure created.

5. Reconnect to the database with SYSDBA privileges and revoke the DBA from ljcatt and grant the create session privilege. Then attempt to reconnect as the user LJCATT.

SQL> connect / as sysdba
Connected.
SQL> revoke dba from ljcatt;

Revoke succeeded.

SQL> grant create session to ljcatt;

Grant succeeded.

SQL> connect ljcatt
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-00900: invalid SQL statement
ORA-06512: at line 8

Warning: You are no longer connected to ORACLE.

6. NOTE: The error is received due to this user owning objects which he does not have privileges to create or access. This is shown below by reconnecting as SYSDBA and displaying objects owned by the user LJCATT.

SQL> connect / as sysdba
Connected.

SQL> select owner, object_name from dba_objects where object_name like ‘TEST_F%’
and owner=’LJCATT’

OWNER OBJECT_NAME
————————- ————————–
LJCATT TEST_FILE

7. As SYSDBA, re-grant the DBA role to LJCATT and attempt to reconnect.

SQL> grant dba to ljcatt;

Grant succeeded.

SQL> connect ljcatt
Enter password:
Connected.
SQL>

Now that the user LJCATT has the specific privileges required by the objects he owns, he is able to connect without error.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, script, SQL, sqlplus, unix

Oracle – ORA-29280: invalid directory path when using UTL_FILE

January 10, 2009 Larry Catt Leave a comment

Oracle provides the package UTL_FILE to produce OS layer files from within the Oracle RDBMS. This article will describe the common error of ORA-29280 which maybe received when using the UTL_FILE procedure and how to resolve it.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called create_file.sql with the following SQL.

create or replace procedure test_file
is

v_record varchar2(50) := ‘Testing file creation’;
v_file varchar2(30) := ‘test_file.txt’;
v_dir varchar2(512) := ‘/home/lcatt’;
v_write utl_file.file_type;
begin

v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);

utl_file.put_line(v_write, v_record);

utl_file.fclose(v_write);

end test_file;
/

3. Enter SQL*PLUS, set ECHO and FEEDBACK to on, and execute the file
create_file.sql as a user with DBA privileges. In this example we are using the account LJCATT.

Mylinux:/home/lcatt:>sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 10 10:43:17 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set feedback on
SQL> set echo on
SQL>@./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Procedure created.

SQL>

4. Use the SQL*PLUS command show to display the initialization parameter utl_file_dir and ensure that the directory /home/lcatt is not present.

SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string /home/lcatt/output
SQL>

5. If the directory is present, remove it with the following command:

SQL> alter system set utl_file_dir=” scope=spfile;

System altered.

SQL>

6. Logon to the database as sysdba and restart your oracle instance.

SQL> connect / as sysdba
Connected.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 2068728 bytes
Variable Size 654315272 bytes
Database Buffers 503316480 bytes
Redo Buffers 48259072 bytes
Database mounted.
Database opened.
SQL>

7. Now logon as the user LJCATT and execute the package test_file with the command execute.

SQL> execute test_file
BEGIN test_file; END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at “SYS.UTL_FILE”, line 29
ORA-06512: at “SYS.UTL_FILE”, line 448
ORA-06512: at “LJCATT.TEST_FILE”, line 10
ORA-06512: at line 1

SQL>

8. NOTE: The error produced is a result of no reference to the defined directory structure in the initialization parameter UTL_FILE_DIR. This parameter is used to allow the database access to OS layer directories. To resolve this error, connect to the database as sysdba and place the directory path in the initialization parameter UTL_FILE_DIR with the command alter system and restart the database.

SQL> connect / as sysdba
Connected.
SQL> alter system set utl_file_dir=’/home/lcatt’ scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207959552 bytes
Fixed Size 2068728 bytes
Variable Size 654315272 bytes
Database Buffers 503316480 bytes
Redo Buffers 48259072 bytes
Database mounted.
Database opened.
SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————

utl_file_dir string /home/lcatt

9. Connect back to the database as the user LJCATT and re-execute the procedure test_file.

SQL> connect ljcatt
Enter password:
Connected.
SQL> execute test_file

PL/SQL procedure successfully completed.

SQL>

10. Exit out of SQL*PLUS, change directories to the location of your output file, and perform an ls command to display the file generated.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 –
64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Mylinux:/home/lcatt:>cd /home/lcatt
Mylinux:/home/lcatt:>ls testfile
testfile
Mylinux:/home/lcatt:>

The file was produced under the directory defined by the initialization parameter utl_file_dir and the error ORA-29280 has been resolved.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, script, SQL, sqlplus, unix

Creating an external file from within the Oracle RDBMS.

January 3, 2009 Larry Catt Leave a comment

Oracle provides the package UTL_FILE to produce OS layer files from within the Oracle RDBMS. This article will show a basic procedure for the producing an OS layer file from within the Oracle RDBMS.

1. Logon to your Oracle database server as the Oracle software owner.

2. Create a file called create_file.sql with the following SQL.

create or replace procedure test_file
is

end test_file;
/

3. Enter SQL*PLUS, set ECHO and FEEDBACK to on, and execute the file
create_file.sql.

Mylinux:/home/lcatt:>sqlplus ljcatt

SQL*Plus: Release 10.2.0.4.0 – Production on Sat Jan 03 09:43:17 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set feedback on
SQL> set echo on
SQL>@./create_file.sql
SQL> create or replace procedure test_file
2 is
3
4 v_record varchar2(50) := ‘Testing file creation’;
5 v_file varchar2(30) := ‘test_file.txt’;
6 v_dir varchar2(512) := ‘/home/lcatt’;
7 v_write utl_file.file_type;
8 begin
9
10 v_write:=utl_file.fopen(v_dir, v_file, ‘w’, 2000);
11
12 utl_file.put_line(v_write, v_record);
13
14 utl_file.fclose(v_write);
15
16 end test_file;
17 /

Procedure created.

SQL>

4. Ensure that the system initialization parameter for utl_file_dir contains the directory to which utl_file is going to write with the command show utl_file_dir.

SQL> show parameter utl_file_dir

NAME TYPE VALUE
———————————— ———– ——————————
utl_file_dir string

SQL>

5. If it does not contain the directory, as is the case in step 4, logon to your Oracle database as sysdba and add the directory structure and bounce the database, as seen below:

SQL> connect / as sysdba
Connected.
SQL> alter system set utl_file_dir=’/home/lcatt’ scope=spfile;

SQL> execute test_file

PL/SQL procedure successfully completed.

SQL>

7. Exit out of SQL*PLUS and change directory to where you wrote your file.

8. Execute the ls and more command on the file testfile.

Mylinux:/home/lcatt:>ls testfile
testfile
Mylinux:/home/lcatt:>more testfile
Testing file creation
Mylinux:/home/lcatt:>

This completes the creation of external OS layer file from within the Oracle RDBMS.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle 10g, oracle 9i, SQL, sqlplus

Display the time at your SQL*PLUS Prompt

June 25, 2008 Larry Catt Leave a comment

Oracle SQL*PLUS provides the ability to change the default prompt in your session. This allows you to easily determine information about your current session without having to query the database. In this article we will review displaying the current system time at your SQL*PLUS prompt.

1. Connect to your database through SQL*PLUS.

SQL>connect ljcatt/xxxx@mydb
Connected.
SQL>

2. Execute the following SQL*PLUS command: set time on.

SQL>set time on
19:53:56 SQL>
19:53:58 SQL>

3. The time will now be displayed in your SQL*PLUS prompt.
4. To turn off the time execute the following SQL*PLUS command: set time off

19:53:58 SQL>set time off
SQL>
SQL>

5. This completes displaying the time at your Oracle SQL*PLUS prompt.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

Catt Solutions

Category Archives: SQL

Oracle auditing insert, update, and delete on a table

Disabling and Enabling Oracle Jobs with DBMS_JOB

Oracle RESOURCE role

Recreate the Oracle DDL for a PROFILE

Oracle DBA role

Oracle – Use of UTL_FILE to read external OS file into Oracle RDBMS.

Oracle – ORA-00604: error occurred at recursive SQL level 1 and revoking a user privileges.

Oracle – ORA-29280: invalid directory path when using UTL_FILE

Creating an external file from within the Oracle RDBMS.

Display the time at your SQL*PLUS Prompt

Oracle tips and tricks.