Temp Space Usage in Oracle

Don’t just grow your TEMP tablespace, examine it!

Recently I was facing a problem not unusual in high-throughput Oracle databases… a full TEMP tablespace. I’ve run into this before and it can be frustrating trying to determine where all the space went. In this case we are using a 6 gig temporary tablespace and for some reason it was full.

Well, all the gorey details aside, one thing that helped me determine what was going on was this tip from Oracle which contains code to examine who is using your temporary tablespace up.

By running the following command (reprinted here for easy reference and because there are nasty line breaks on Oracle’s site) you get a fairly good idea of the usage of temporary space by tablespace, username and amount used.

set pagesize 10000
set linesize 133
column tablespace format a15 heading 'Tablespace Name'
column segfile# format 9,999 heading 'File|ID'
column spid format 9,999 heading 'Unix|ID'
column segblk# format 999,999,999 heading 'Block|ID'
column size_mb format 999,999,990.00 heading "Mbytes|Used"
column username format a15
column program format a15

select b.tablespace, b.segfile#, b.segblk#,
round(((b.blocks*p.value)/1024/1024),2) size_mb,
a.sid, a.serial#, a.username, a.osuser, a.program, a.status
from v$session a, v$sort_usage b, v$process c, v$parameter p
where p.name='db_block_size' and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;

While I consider rewriting this into a somewhat more palatable form, on the occasion that I need this I don’t typically care about nice formatting… I just want the numbers in a hurry.

To clear up unused TEMP space Oracle also recommends running this as sysdba:

alter tablespace temp default storage(pctincrease 0);

oracle, dba, database, database administration, database administrator, database tuning

What Are the Default Restrictions on Oracle Passwords?

What are the valid characters for Oracle passwords? This is a more complicated question than you would think. Here are the basic restrictions on Oracle passwords. I believe these apply to all (8i or later) Oracle database versions; however I did these examples in a 10gR2 instance.

First the rules:

Passwords can be from 1 to 30 characters.

The first character in an Oracle password must be a letter.

Only letters, numbers, and the symbols “#”, “_” and “$” are acceptable in a password.

Examples:

SQL> alter user jemmons identified by abc123;

User altered.

SQL> alter user jemmons identified by 123abc;
alter user jemmons identified by 123abc
*
ERROR at line 1:
ORA-00988: missing or invalid password(s)

SQL> alter user jemmons identified by abc!123;
alter user jemmons identified by abc!123
*
ERROR at line 1:
ORA-00922: missing or invalid option

In the first example we see a password that meets all the rules. The second password starts with a number and therefore fails. The third example contains the special character “!” and fails.

Now the exception:

By placing double quotes around a password you can use most standard ASCII characters in a password.

Examples:

SQL> alter user jemmons identified by "123abc";

User altered.

SQL> connect jemmons/123abc;
Connected.
SQL> alter user jemmons identified by "abc!123";

User altered.

SQL> connect jemmons/abc!123;
Connected.

We see that the quotes let us work around some of these restrictions. This is useful if you are working in a fairly simple environment, however if it is possible that a user’s password could be changed by means which you do not control it is likely doing this would cause more confusion than it would solve.

It is also worth note that capitalization is not considered when storing Oracle passwords. Here’s an example:

SQL> alter user jemmons identified by ABC123;

User altered.

SQL> connect jemmons/abc123;
Connected.

Before the password is encrypted into the database it is put in all caps. When you enter a password for authentication it is capitalized so it can be compared against the stored encrypted password.

oracle, dba, database, database administration, database security, database administrator

SQL*Plus Substitution Variables

This tutorial from the Oracle Technology Network outline the use of the three types of variables available in SQLPlus. Bind variables, substitution variables and system variables are all explained in this succinct yet thorough tutorial. The best I’ve seen on the subject.

SQL*Plus Substitution Variables from Oracle Technology Network.

For information on other Oracle web resources, check out my other article on the topic.

oracle, dba, database, database administrator, rdbms, database development, database design

Useful Oracle Security Views

One of the cool things about oracle is that if you have the right privileges you can learn all it’s secrets from the so-called “data dictionary.”

If you’re interested in this then you probably already know what that’s about, so here are what I consider the important data dictionary views for Oracle security:

DBA_SYS_PRIVS – Probably most important, this show system privileges granted to users and roles

DBA_TAB_PRIVS – While the name implies table privileges this actually incorporates view privileges, procedures, packages and functions

DBA_COL_PRIVS – Not always used, but this view tracks grants on columns

DBA_ROLE_PRIVS – This shows who (or what roles) a role has been granted to

These views don’t seem to have changed much from 9i to 10g (or in 8i at that matter) but it is always best to check documentation for your version of Oracle.

Stay tuned for some handy queries to run on these to get quick reports on who can see what.

Technorati tags: , , , , , ,

Oracle CONNECT and RESOURCE Roles

So what exactly is in those CONNECT and RESOURCE Oracle roles? The ship with every Oracle database and many apps require they be granted. I did a little digging and found the following:

In Oracle 10gR2 things are fairly sane:
CONNECT role has only CREATE SESSION
RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

In Oracle 9iR2 things get a little scary:
CONNECT has ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE and CREATE VIEW. Rather a scary lot for a role called ‘connect’
RESOURCE has CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER and CREATE TYPE

The admin option would allow the users to grant the privlelge to another user. Thankfully neither of these roles have the admin option in the versions of Oracle I checked.

To find these privileges you can query the DBA_SYS_PRIVS table with a query like this:

select grantee, privilege, admin_option from dba_sys_privs where grantee='CONNECT';

Of course an oracle role could also have table or column privileges granted to it, so to be thorough you should also check for entries in DBA_TAB_PRIVS and DBA_COL_PRIVS.

NOTE: You should always check these privileges in your database before granting roles as a script, application or previous DBA may have granted or revoked additional roles.

Technorati tags: , , , , , ,