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
/
