Oracle DBA Crib Sheet

Introduction

The Oracle DBA Crib Sheet is not an exhastive list of what a DBA has to do, instead it has been written for a particular client whose support team occasionally need dba commands and don’t have them written down any where else.

It features the commands that we have been asked to put in rather than an exhaustive list that attempts to re-write existing manuals, for which there are links.

Conventions

Oracle is generally case insensitive and normally stores key values such as user names and table names in upper case. This is only important when using the name in a condition (e.g. where username=’PALADIN’)

In the sample SQL I have generally used BOLD UPPERCASE to denote something that should be replaced when entering the statement. Notes that are not SQL statements are generally in italics

An oracle statement can be ended with a semi-colon (;) on the same line as a statement or a slash (/) as the first character of the first line after a statement

All Oracle manuals can be found online at http://www.oracle.com

Creating a Normal User

grant connect, resource to USERNAME
identified by PASSWORD
/

alter user USERNAME
default tablespace TABLESPACE_NAME
temporary tablespace TABLESPACE_NAME
/

Note: users default tablespaces will either be TOOLS, USERS or a specific tablespace e.g PALADIN_DATA for PALADIN user, whilst the temporary tablespace will normally be called TEMP

Creating a DBA User

grant connect, resource, dba to USERNAME
identified by PASSWORD
/

alter user USERNAME
default tablespace TABLESPACE_NAME
temporary tablespace TABLESPACE_NAME
/

Note: users default tablespaces will either be TOOLS, USERS or a specific tablespace e.g PALADIN_DATA for PALADIN user, whilst the temporary tablespace will normally be called TEMP

Creating a tablespace

create tablespace TABLESPACE_NAME
datafile ‘PATH_TO_FILE
size SIZE
reuse
/

Note: The PATH_TO_FILE must be fully qualified e.g. /database/oradata/database.dbf and should always end in .dbf. Also that size should be a number followed by the letter ‘M’ for megabytes e.g. 2048M would give you 2Gb whilst 2048 on it’s own will give you 2Kb of usable space.

Adding more space to a tablespace

alter tablespace TABLESPACE_NAME
add datafile ‘PATH_TO_FILE
size SIZE
/

Note: The PATH_TO_FILE must be fully qualified e.g. /database/oradata/database.dbf and should always end in .dbf. Also that size should be a number followed by the letter ‘M’ for megabytes e.g. 2048M would give you 2Gb whilst 2048 on it’s own will give you 2Kb of usable space. Finally this can not be the same file as any that already exists hence the common use of sequence numbers in the filename

Selecting a list of tablespaces and data files with sizes

select tablespace_name, file_name, size/(1024*1024) sizem
from dba_data_files
/

Selecting the size of tablespaces

select tablespace_name,
sum(bytes/(1024*1024)) sizem,
count(1) filecnt
from dba_data_files
group by tablespace_name
/

Changing a users password

grant connect to USERNAME identified by PASSWORD
/

Checking which users exist

select username
from all_users
/

Checking a specific user exists

select username
from all_users
where username = ‘USERNAME
/

Listing all available roles

select * from dba_roles
/

Creating a role

create role ROLENAME
/

Granting a role to a user

grant ROLENAME to USERNAME
/

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.