Archive

Archive for the ‘Oracle 9i’ Category

Oracle – Find users with DBA privilege

July 20, 2011 3 comments

If you wish to know which users have been granted the dba role then you need to query the dba_role_privs in the SYS schema.

This role tells you the grantee, granted_role, whether they have admin option granted, and whether the role is their default role:

SQL> desc dba_role_privs
Name         Null?    Type
------------ -------- ------------
GRANTEE               VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION          VARCHAR2(3)
DEFAULT_ROLE          VARCHAR2(3)

To find a list of all users with DBA privilege execute the following code:

SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE   GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS       DBA          YES YES
SYSTEM    DBA          YES YES

Further Reading

http://download.oracle.com/docs/cd/B14117_01/server.101/b10755/statviews_2291.htm

Oracle – Check Auditing Policy


To check what auditing has been set on your Oracle database you can query the following:

SQL> SELECT *
FROM dba_stmt_audit_opts;

Further Reading

DBA_STMT_AUDIT_OPTS
Auditing in Oracle 10g Release 2

Oracle – Check Users Connected to Database


To find how many users are on the database issue the following:

SQL> SELECT username
FROM v$session;

Show what users are running

SQL> SELECT a.sid
, a.serial#
, a.username
, b.sql_text
FROM v$session a
, v$sqlarea b
WHERE a.sql_address=b.address;

You can add the following to the script if you have a lot of users and want to find one specific users code, but you will need to remove a.username from the above script:

AND a.username = '<username>';

Oracle – Pfile and Spfile

July 11, 2011 46 comments

The default location for a spfile or pfile is $ORACLE_HOME/dbs however if you are unsure as to where your spfile is located you can issue the following from SQLPLUS:

SQL> SHOW PARAMETER spfile;
NAME     TYPE       VALUE
--------     -------       ---------
spfile       string       /app/oracle/product/10.2.0.4server/db_1/dbs/spfileictst3f.ora

Create pfile from spfile

If you wish to backup your spfile, you can create a pfile from the spfile which will save the current parameter configuration.

SQL> create pfile='<pfile location>' from spfile;

or

SQL> CREATE PFILE='<pfile location>' FROM SPFILE = '<spfile location>';

Create spfile from pfile

If you want to then revert back to a saved pfile, you can overwrite your current spfile with the following:

SQL> CREATE SPFILE FROM PFILE = '<pfile location>';

or

SQL> CREATE SPFILE='<spfile location>' FROM PFILE='<pfile location>';

Check spfile parameters

To check all the spfile parameters you can issue the following:

SQL> SHOW PARAMETER;

Check pfile parameters

You can either check the pfile parameters, after starting a database up with a pfile, using the above method. Or you can go to the pfile location and either cat or view the file. To change the pfile parameters you can directly edit the pfile using vi from the command line.

Change spfile parameters

Some parameters you can save to memory, for use just within the current session; some you can save within the spfile; and some must be set at both. Parameters saved within the spfile can only be initiated following a database restart.

To save a new parameter within the spfile you issue the following within SQLPLUS:

SQL> ALTER SYSTEM SET <parameter name>='<value>' SCOPE=[SPFILE/MEMORY/BOTH];

Startup database with pfile or spfile

If you want to startup a database using either a different spfile or using a pfile you first need to shut it down. Then you should issue the following:

SQL> CONNECT sys/password AS SYSDBA
SQL> startup pfile='<pfile location>';

or

SQL> CONNECT sys/password AS SYSDBA
SQL> startup spfile='<spfile location>';

Oracle – Change user password


There are two different methods for changing a users password. The user is able to do it themselves using the following:

SQL> password
Changing password for <username>
Old password:
New password:
Retype new password:
Password changed
SQL>

Or as sysdba you can do the following:

SQL> alter user <usernamer> identified by <password>;
User altered.

Oracle – Drop User/schema


To drop a user/schema and all associated tables etc, issue the following command:

SQL> drop user <username> cascade;
User dropped.

Oracle – Create User


If you wish to create a new user on the oracle database you would need to perform the following:

  • Define the username
  • Define the password
  • Identify a default tablespace
  • Identify a default temporary tablespace
  • Allocate a quota on additional tablespaces (optional)

sql statement within SQLPLUS:

SQL> CREATE USER <username>
IDENTIFIED BY <password>
DEFAULT TABLESPACE <tablespace name>
TEMPORARY TABLESPACE <tablespace name>
QUOTA <quota amount> ON <tablespace name>;

You then need to grant appropriate roles to the user for example, session, connect, resource:

SQL> grant connect to <username>;

Further Reading

Oracle 10g Create User Documentation