Oracle DBA help

1. Introduction
2. Starting on a newly installed system
   21. Creation of a tablespace
   22. Creation of a user
   23. Grant access to the database
   24. Altering quota's on the tablespace for the user
   25. Granting user access to create tables,indexes,views
3. Examples
4. Tips on using sqlplus
5. Resources

This document is intended to be a help for those new to Oracle. The primary intention with this little guide is to help people getting started on creating users and tablespaces in their newly installed Oracle database. It's not intended to be a faq on how to install the database, because there are a few of those out there already, for links see resource section.

Any use of this material is on own risk. There's no guarantee that any of the things described in this howto works. The experiences this document describes where made on an Oracle 8.0.5.0.0 Database server installed on a Debian GNU/Linux System.

When you start of on a newly installed system you will need to create a user that will be accessing the database. For security reasons you shouldn't be using the system user nor the system tablespace. In the next sections you will learn how to create users and tablespaces for them to use.

A tablespace is where Oracle stores the data you put into your tables.

Syntax: create tablespace <TABLESPACE> datafile '<TABLESPACEFILE>' size <SIZE> M [autoextend on next <SIZE> M [maxsize <SIZE> M]]

TABLESPACE is the name you want to call the tablespace you're about to create. It's wise to give it a name that says something about what you're going to use it for. TABLESPACEFILE is the file, with fullpath, on your filesystem that has to contain the database. Depending on your installation it will probably reside in ~oracle/oradata/ORCL/ depending on what you set your Oracle SID to when you installed. Name this file after what you call your tablespace and put a .dbf as suffix and to do it the Oracle way you would generally add a number to the filename to, especially if you know that you will have to make more datafiles later on. So that would make the filename look like tablespace01.dbf. SIZE is pretty much self explanatory, depending on wheter you put a K or M after it the size is either in KiloBytes or MegaBytes. The autoextend on next command adds extra size to the tablespace when the amount of data exceeds the current size of the tablespace. It adds more size to the tablespace according to it's SIZE value and keeps extending until it runs out of diskspace. To avoid this you would add the maxsize command, which defines the max size of your tablespace.

Example:

You want to create a tablespace that uses 20 MB of diskspace to start of with, if the limit is hit it will need to add another 10 MB of diskspace to the tablespace. To avoid it from using up all of your diskspace a max limit of 50 MB is made.

Solution:

create tablespace file_data datafile '/home/oracle/oradata/ORCL/file_data01.dbf' size 20M autoextend on next 10M maxsize 50M

To be able to connect to an Oracle database you need a user. A user can have different rights depending on what the user should be able to do.

Syntax: create user <USER> identified bye <PASSWORD> default tablespace <TABLESPACE> temporary tablespace <TEMP>

USER is what you want to call the user you want to create, this username doesn't need to have anything to do with your regular users on your system. PASSWORD is the clear text password you want the user to be using to connect to the database. TABLESPACE is where the user puts his tables by default. TEMP is the temporary tablespace used by Oracle to store temporary tables, rollback, etc. Oracle per default has a temporary tablespace called "temp".

Example:

You want to create a user that will be using the tablespace in the preceding example.

Solution:

create user file_user indentified by file_pass default tablespace file_data temporary tablespace temp

For a user to be able to do anything at all you need to grant different priviligies to the user. There are a lot of different priviligies a user can have, where the most important one probably is being allowed to access the database.

Syntax: grant create session to <USER>

USER is simply the user you wish to give priviligies to connect to the database.

Example:

You want to give a user the possibility to connect to the database.

Solution:

grant create session to file_user

alter user <user> quota <size> on <tablespace>

alter user file_user quota unlimited on file_data"

grant create table to <user>

grant create table to file_user"

create user select_file_user identified by select_file_pass default tablespace file_data temporary tablespace temp;
grant create session to select_file_user;
grant select on <thetableyouwanthimtoselectfrom> to select_file_user;

select 'grant select on ' table_name ' to select_file_user' from user_tables;

set echo off;
set heading off;
set feedback off;
set pagesize 0;
spool grant.sql;
select 'grant select on ' table_name ' to select_file_user' from user_tables;
spool off;
set feedback on;
set heading on;
set echo on;
@grant.sql;

create tablespace file_data datafile '/home/oracle/oradata/ORCL/file_data01.dbf' size 20M autoextend on next 10M maxsize 50M;
create user file_user identified by file_pass default tablespace file_data temporary tablespace temp;
alter user file_user quota unlimited on temp;
alter user file_user quota unlimited on file_data;
grant create session to file_user;
grant create table to file_user;

export EDITOR=emacs

"timestamp date default sysdate null"

select sysdate from dual;

alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

select to_char(timestamp,'MMDDYYYY') from lastchecked;

select count (id) from answer where data like '%mail%';

        boolean varchar(1) default 'N' null
        check (boolean in ('N','Y'))

select location from locations start with id = '$page[0]' connect by prior parent = id order by id asc

NAME=(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=fqdn)(PORT=1521))
  (CONNECTDATA=(SID=ORCL))
)

~oracle/bin/sqlplus user/pass@NAME

LIMIT!
To create the effect of limit function in MySQL you need to use the automatically-on-the-fly created rownum column. You can select and have a where clause that limits the amount of rows you want. Like: 'select id,name from table where rownum >= 1 and rownum <= 10' would get the first 10 results from the database.