Tag Archives: user

11g, hp-ux, installer, itanium, linux, Oracle, Oracle 10g, oracle 9i, RAC, unix

Creation of Oracle OS accounts for 11g RAC install on Linux

Leave a comment

Deployment of Oracle software requires the creation of multiple groups and users on UNIX and Linux servers to properly configure and administrate. This procedure covers the basic creation of Oracle groups and users for a standard 11g RAC implementation.

1. Create group accounts on all nodes: NOTE: id must be exactly the same

/usr/sbin/groupadd -g 501 oinstall
/usr/sbin/groupadd -g 502 dba
/usr/sbin/groupadd -g 504 asmadmin
/usr/sbin/groupadd -g 506 asmdba
/usr/sbin/groupadd -g 507 asmoper

2. Create user accounts on all nodes: NOTE: id must be exactly the same

/usr/sbin/useradd -u 501 -g oinstall -G asmadmin,asmdba,asmoper grid
/usr/sbin/useradd -u 502 -g oinstall -G dba,asmdba oracle

3. Set password for GRID and ORACLE account to be the same on every node.

passwd oracle
Changing password for user oracle.
New UNIX password: password
retype new UNIX password: password
passwd: all authentication tokens updated successfully.
passwd grid
Changing password for user oracle.
New UNIX password: password
retype new UNIX password: password
passwd: all authentication tokens updated successfully.

This completes the standard configuration creation of Oracle groups and users on Linux.

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

11g, Oracle

Auditing the connections for a particular user:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level, providing the administrator the ability to find suspicious activity. In this article we will show how to audit a particular user’s connectivity in an Oracle database. This procedure will work on any OS.

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 Mon Feb 12 04:31:43 2010

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. Check the current setting of the initialization parameter AUDIT_TRAIL with the command: show parameter {initialization_parameter}
The initialization parameter AUDIT_TRAIL controls auditing at the database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure we want the definition to be set to DB.

SQL> show parameter AUDIT_TRAIL

NAME TYPE VALUE
———————————— ———– ———————-
Audit_trail string DB
SQL>

4. If the parameter is not set to DB and you are using a spfile, execute the command: alter system set audit_trail=DB scope=spfile; and bounce the database. If you are using an init file, shutdown the database add the string AUDIT_TRIAL=DB and start the database.
NOTE: If you do not know if you are using an SPFILE or INIT file, search ORACLE SPFILE vs INIT File

SQL> alter system set audit_trail=DB scope=spfile;

System altered.

SQL>

5. Restart your database if you had to change the audit_trail parameter.

6. Execute the following command to begin auditing of connection by a particular user: audit session by {user_name}

SQL> audit session by ljcatt;

Audit succeeded.

SQL>

7. Perform the following select from the view DBA_PRIV_AUDIT_OPTS to see that auditing is enabled: select user_name, privilege from dba_priv_audit_opts;

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME PRIVILEGE
—————————— —————————————-
LJCATT CREATE SESSION

SQL>

8. Connect as the user you are auditing.

SQL> connect ljcatt
Enter password:
Connected.
SQL>

9. Reconnect as sysdba.

SQL> connect / as sysdba
Connected.
SQL>

10 Perform a select against the view dba_audit_trail to determine if the connection was recorded.

SQL> select username, returncode, action_name from dba_audit_trail;

USERNAME RETURNCODE ACTION_NAME
—————————— ———- —————————-
LJCATT 0 SESSION REC
LJCATT 0 LOGOFF
SQL>

As seen in the output above, the session reconnection and then log off where both successfully record with a return code of zero. If either action failed, the return code would show the error number produced. This completes auditing of a particular user account in Oracle RDBMS.

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

11g, audit, Oracle

Removal of Oracle auditing on specific user account:

Leave a comment

Oracle provides the ability to audit your database activities on a multitude of level which provides the administrator the ability to find suspicious activity. In most cases the DBA knows which user account they suspect is causing a problem, thus they only wish to monitor that account. However, in a heavily used system, the auditing process can produce a large amount of data and should be discontinued once it is obsolete. This article covers the removal of audit definitions for a specific user account on an Oracle RDBMS. This procedure will work on any OS.

NOTE: The initialization parameter AUDIT_TRAIL controls auditing at the entire database level and can be set to three definitions: 1. DB – audit trail in the database; 2. OS – audit trail on the OS; and 3. none – no auditing. In this procedure AUDIT_TRAIL must be set to DB or OS and the procedure does not shutdown auditing at the database level.

1. Auditing definitions for user accounts are stored in views:
DBA_OBJ_AUDIT_OPTS, DBA_PRIV_AUDIT_OPTS, and DBA_STMT_AUDIT_OPTS.

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

3. Logon to SQLPLUS with sysdba privileges.

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jan 15 19:18:09 2010

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>

4. In this procedure we will audit the account LJCATT, execute the following command to begin the auditing process for your specified user account: audit session by {user_name};

SQL> audit session by ljcatt;

Audit succeeded.

SQL>

5. Perform a select from the views DBA_PRIV_AUDIT_OPTS to see the audit policies defined by the command in step 4 and as you can see the account is setup for auditing.

SQL> select user_name, privilege from dba_priv_audit_opts;

USER_NAME PRIVILEGE
— —-
LJCATT ALTER SYSTEM
LJCATT AUDIT SYSTEM
LJCATT CREATE SESSION

SQL>

6. Once you have completed your analysis, you want to remove this audit policy, execute the following PL/SQL block.

————————————————————–
Beginning removal of Oracle auditing definitions for a specific user account PL/SQL Block
——————————————————————

set serveroutput on

declare

v_ct number;

begin

–this block removes all auditing from an oracle RDBMS system.

for v_stmt in(select ‘noaudit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
loop
execute immediate(v_stmt.stmt);
end loop;

end;
/

——————————————————————
End removal of Oracle auditing definitions for a specific user account PL/SQL Block
——————————————————————
——————————————————————
OUPUT
——————————————————————

SQL> set serveroutput on
SQL>
SQL> declare
2
3 v_ct number;
4
5 begin
6
7 –this block removes all auditing from an oracle RDBMS system.
8
9 for v_stmt in(select ‘noaudit all by ‘ || user_name as stmt from
sys.dba_priv_audit_opts)
10 loop
11 execute immediate(v_stmt.stmt);
12 end loop;
13
14 end;
15 /

PL/SQL procedure successfully completed.

SQL>
——————————————————————
End of OUPUT
——————————————————————

7. Once completed re-execute the following SQL to verify that the auditing definitions have been removed from the system: select user_name, privilege from dba_priv_audit_opts;

SQL> select user_name, privilege from dba_priv_audit_opts;

no rows selected

SQL>

That completes removal of all Oracle auditing for a specific user account in the RDBMS.

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

linux, Oracle, Oracle 10g, unix

Enable User Tracing on Oracle Listener:

Leave a comment

Most Oracle database connects are made through the use of an Oracle process called the LISTENER which monitors a certain machine and port for connection requests to one or more database instances. There are times when a DBA or SA will want to monitor connects being made through the listener for various reasons. This article covers the setup of USER level tracing of you Oracle Listener and will work on UNIX, Linux, and Windows based machines.

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

2. Open up the LSNRCTL utility.

myhpux:>lsnrctl

LSNRCTL for HPUX: Version 10.2.0.4.0 – Production on 21-OCT-2009 18:17:05

Copyright (c) 1991, 2007, Oracle. All rights reserved.

Welcome to LSNRCTL, type “help” for information.

LSNRCTL>

3. If you are not using the default name of the Oracle listener (LISTENER) then define the Oracle listener name with the following command: set current_listener

LSNRCTL> set current_listener listener_orcl
Current Listener is listener_orcl
LSNRCTL>

4. If you have established a password for your oracle listener set it in the LSNRCTL session with the following command: set password

LSNRCTL> set password oracle
The command completed successfully
LSNRCTL>

5. Turn on user level tracing by specifying 4 or user as the level with the following command: set trc_level

LSNRCTL> set trc_level 4
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to user
The command completed successfully
LSNRCTL>

or

LSNRCTL> set trc_level user
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_level” set to user
The command completed successfully
LSNRCTL>

6. Define the name of the trace file that will be produced by the listener process upon establishment of user connection with the command below: set trc_file

LSNRCTL> set trc_file user_access_trace
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
listener_orcl parameter “trc_file” set to user_access_trace.trc
The command completed successfully
LSNRCTL>

NOTE: You do not have to set a trace directory by default the trace files for the listener are placed under: $ORACLE_HOME/network/trace

7. Save the changes made to the listener.ora file with the following command: save_config

LSNRCTL> save_config
Connecting to
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.110)(PORT=1521)))
Saved listener_orcl configuration parameters.
Listener Parameter File /opt/app/oracle/10.2.0/network/admin/listener.ora
Old Parameter File /opt/app/oracle/10.2.0/network/admin/listener.bak
The command completed successfully
LSNRCTL>

8. Exit out of LSNRCTL utility, change directories to $ORACLE_HOME/network/trace, and list the files present and you will see that the trace has already begun.

LSNRCTL> exit
mylinux:> cd $ORACLE_HOME/network/trace
mylinux:> ls -lrt
-rw-r—– 1 oracle dba 3010 Oct 21 18:26 user_access_trace.trc

9. Logon to the RDBMS as a normal user and exit.

mylinux:> sqlplus ljcatt/ljcatt@orcl

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Oct 21 18:31:26 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> 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:>

10. Perform another listing of the files under directory
$ORACLE_HOME/network/admin and you will see that the file user_access_trace.trc has grown by recording the connection
from user in step 9.

mylinux:> ls -lrt
-rw-r—– 1 oracle dba 6810 Oct 21 18:31 user_access_trace.trc

11 This information can be used to show where connections in you database are coming from and other statistical information about the network connection being established.

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

Oracle, Oracle 10g, oracle 9i, RMAN

Recreate Oracle RDBMS User accounts

Leave a comment

With the advent of Oracle 10g Import and Export data pump utility, the need to recreate a user account from scratch has been greatly reduced. However, there are still situation when you may need the SQL to recreate a specific user within another Oracle environment or just desire to see the creation statements. The script below will generate the statements to create the user defined in the CURR variable from the current Oracle RDBMS environment.

1. First we will create a user named LJCATT with various roles and privileges.

SQL> create user LJCATT identified by LJCATT;
User created.
SQL> alter user LJCATT default tablespace USERS;
User altered.
SQL> alter user LJCATT temporary tablespace TEMP;
User altered.
SQL> alter user LJCATT profile DEFAULT;
User altered.
SQL> grant DBA to LJCATT;
Grant succeeded.
SQL> grant CREATE TABLE to LJCATT with admin option;
Grant succeeded.
SQL> grant UNLIMITED TABLESPACE to LJCATT;
Grant succeeded.
SQL> grant EXECUTE on SYS.UTL_FILE to LJCATT with grant option;
Grant succeeded.
SQL>

2. Now we will execute the following PL/SQL block to regenerate the statements to recreate the user from the data dictionary.

set serveroutput on
spool ./create_user_account.sql

declare

curr varchar2(30):= ‘LJCATT’;
v_ext varchar2(3);

begin

— Create original user definition
for user in(select * from dba_users where username = curr)
loop
dbms_output.put_line(‘create user ‘||user.username||’ identified by
‘||user.username||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ default tablespace
‘||user.default_tablespace||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ temporary tablespace
‘||user.temporary_tablespace||’;’);
dbms_output.put_line(‘alter user ‘||user.username||’ profile
‘||user.profile||’;’);
if user.account_status<>‘OPEN’
then
dbms_output.put_line(‘alter user ‘||user.username||’ account lock;’);
end if;
end loop;

— Grant all roles defined for the user.

for role in(select * from dba_role_privs where grantee=curr)
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;

— Grant all system privileges for the user.

for sys_priv in(select * from dba_sys_privs where grantee=curr)
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;

— Grant all object privileges for the user.

for tab_priv in(select * from dba_tab_privs where grantee=curr)
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;
/

spool off

Sample Output results:

47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
create user LJCATT identified by LJCATT;
alter user LJCATT default tablespace USERS;
alter user LJCATT temporary tablespace TEMP;
alter user LJCATT profile DEFAULT;
grant DBA to LJCATT;
grant CREATE TABLE to LJCATT with admin option;
grant UNLIMITED TABLESPACE to LJCATT;
grant EXECUTE on SYS.UTL_FILE to LJCATT with grant option;

PL/SQL procedure successfully completed.

SQL>

3. The above SQL statements can now be used against any other Oracle database to recreate the user account with the same rights which exist in this database. NOTE: There exist several dependencies in the above statements, such as: tablespaces, profiles, roles, and object privileges. For example, we need to take care that a default tablespace defined in the alter user statement truly exist in the destination database.

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

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

Oracle auditing of user’s connections

Leave a comment

Oracle provides the ability to audit a great range of activities within the Oracle RDBMS environment. An administrator has the ability to audit as much as the activities of the entire database all the way to any select, insert, or update on a single table. 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 connects by a single user account.

1. Logon to SQL*PLUS as sysdba

mylinux:>sqlplus ‘/ as sysdba’

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Jul 27 19:13:26 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 and audit_sys_operations parameters are set to TRUE.

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> alter system set audit_trail=TRUE scope=spfile;

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. Begin auditing of acccount which you suspect is causing an issue.

SQL> audit connect by ljcatt;

Audit succeeded.

SQL>

6. Logon as the user and perform some actions.

SQL> connect ljcatt/ljcatt
Connected.
SQL>

7. Log back in to oracle as sysdba

SQL> connect sys as sysdba
Connected.
SQL>

8. Perform the following SQL to extract the connection information for the USER LJCATT.

SQL> select userhost, terminal, timestamp, action_name from dba_audit_session wh
ere username=’LJCATT’;

USERHOST TERMINAL TIMESTAMP ACTION_NAME
———— ——– ——— ——————
Mylinux Mylinux1 27-JUL-09 LOGON
Mylinux Mylinux1 27-JUL-09 LOGOFF

9. There is a wealth of information that can be obtained by looking at the various dictionary views which cover the Oracle auditing process. You can easily see if someone is accessing your database in an improper way or manipulating data with a direct connection such as SQL*PLUS instead of through an application. This article covered a brief overview of auditing a single user’s connections; however it is easy to see the power that Oracle auditing can provide.

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

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

Oracle auditing insert, update, and delete on a table

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, SQL, sqlplus

Safely removing an Oracle user account

Leave a comment

In an oracle database every user has the ability (may not have the privilege) to own objects, this collection of objects is referred to as a schema. A typical user of a database application will not have any objects within their schema. To safely remove an Oracle user you must ensure that they do not own any objects which may be referenced by other users. This article outlines the step to ensure that the user does not own any objects.

1. Logon to SQL*PLUS with DBA privileges.

2. Select the number of objects owned by the user account you wish to delete with the following statement. In this example we are trying to delete user JDOE.

SQL> select count(*) from dba_objects where owner='JDOE';

COUNT(*)
----------
0

SQL>

3. If the above SQL statement returns a number greater than 0, further investigation is required. If the above SQL statement returns 0, you can safely remove the user account with the below statement.

SQL> drop user jdoe;

User dropped.

SQL>

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

Oracle, script, SQL, sqlplus

Dropping a user and the cascade option

Leave a comment

In an oracle database every user has the ability (may not have the privilege) to own objects, this collection of objects is referred to as a schema. A typical user of a database application will not have any objects within their schema. To safely remove an Oracle user you must ensure that they do not own any objects which may be referenced by other users. The cascade option of the drop command, allows the administrator to by-pass the deletion of objects owned by the user. NOTE: It is very important to verify that you do not need any objects owned by a user before dropping the account with the cascade option. This article outlines the step to list all objects owned by a user before dropping the account with the cascade option..

1. Logon to SQL*PLUS with DBA privileges.

2. Select the object name and type owned by the user account you wish to delete with the following statement. In this example we are trying to delete user JDOE.
SQL> column object_name format a30
SQL> select object_name, object_type from dba_objects where owner='JDOE';

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
TEMP TABLE

SQL>

3. If the above SQL statement returns a number greater than 0, you should validate that the objects are not used by any other user. If the above SQL statement returns 0, you can remove the user account without the cascade option as shown below.

SQL> drop user jdoe;

User dropped.

SQL>

4. Once you are sure that the objects are not being used by any other user, use the following SQL to remove the account and all associated objects.

SQL> drop user jdoe cascade;

User dropped.

SQL>

NOTE: The associated table TEMP was removed before the actual account was removed.

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