FIRST Operations after installing Oracle DB

Starting on a newly installed 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 should not 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.

Creation of tablespace

What is a tablespace and why do I need one? A tablespace is where Oracle allocates space for your database. Without this you cannot create tables or store data.
To see the Oracle documentation for this operation type 'help create tablespace' on the sqlplus prompt 'SQL>'.
What you basically need to know to get going is how to create a simple tablespace.

CREATE TABLESPACE tablespace
DATAFILE datafile
SIZE size
  [ AUTOEXTEND ON NEXT size [M] 
    [MAXSIZE size [M]]
  ]

With this simplified structure of the command we can build a small statment for creating a small test-tablespace.

SQL> CREATE TABLESPACE test 
       DATAFILE '/path/to/oracle/oradata/SID/test.dbf' 
       SIZE 10M;

Our first tablespace will be able to hold roughly 10 MB of data. You might ask yourself what will happen if you try to store more data than the tablespace will hold? Then Oracle will give you an error and not be able to store the data you're trying insert. You can either add more datafiles or alter the tablespace if you have created the tablespace already. But it's better to be prepared and make the tablespace more extensible. The next example will show just how to do that.

SQL> CREATE TABLESPACE test 
       DATAFILE '/path/to/oracle/oradata/SID/test01.dbf' 
       SIZE 10M 
       AUTOEXTEND ON NEXT 10M
       MAXSIZE 100M;

The datafile I used in this example is different from the one in the earlier example in the way that it names the datafile as tablespace followed by a number followed by the fileextension. This practice makes it a lot easier figuring out how you created the tablespace and a better scheme for adding new datafiles. The example set a maximum size of 100 MB since we don't want the database being able to consume all available space on the disksystem.

Creation of a user

To be able to connect to an Oracle database you need to create a user. A user can have different rights depending on what the user should be privilegded to do. It's generally a very good idea to create a user to not screw anything up on the database. To see the Oracle documentation for creating a user type 'help create user' on the sqlplus prompt.
From the Oracle documentation we can derive the next example.

SQL> CREATE USER test 
       IDENTIFIED BY passwd 
       DEFAULT TABLESPACE test 
       TEMPORARY TABLESPAC temp;

This will create a user test which has the password passwd and with test as the default tablespace, temp is an Oracle temporary tablespace.

Granting rights

Without any rights, the newly created user won't even be able to log on to the Oracle database. Among others creating tables is also a very important privilegde to have. Typing 'help grant' will only refer you to the Oracle server reference so I'll give a quick rundown of the important privilegies.

SQL> GRANT CREATE SESSION TO test;

The session priviledge will allow the user to connect to the database.

SQL> GRANT CREATE TABLE TO test;

The table priviledge will allow the user to create tables in the database.

SQL> GRANT CREATE VIEW TO test;

The view priviledge will allow the user to create views of tables in the database.

SQL> GRANT CREATE SEQUENCE TO test;

The sequence priviledge will alow the user to create sequences for making unique ids for his tables.

Altering quota on tablespaces

This must be all then, right? No, for the user to be able to create tables you should set the quota for the user on the default tablespace. This could have been done in the process of creating the user, but to avoid being to complicated I put this off until now.

SQL> ALTER USER test QUOTA unlimited ON test;

In this example the user would be able to fill up the whole tablespace, which sometimes isn't what you want. Instead you can put a quota in bytes, kilobytes, or megabytes on the user.

Deleting a user

Now that you have messed around with your new user you will probably want to start over fresh and you're asking yourself how to delete what you just created. Again the built-in help of sqlplus is very usefull. So if you try a 'help drop' on the sqlplus prompt you will get a listing of the different options you have.

SQL> DROP USER test CASCADE;

Cascade is the keyword to effectively wipe out everything belonging to the user including, but not limited to, tables, views, and sequences.

Reference : http://thomas.eibner.dk/oracle/basicdba/