How to Oracle DB Tablespace & Schema¶
The original article http://www.rastertek.com/oratut04.html.
Now that we have a database we can create schemas inside the database. A schema is defined as a user that owns data such as tables, views, indexes, and so forth. If a user has no data of their own and just connects and queries for information then they are not considered a schema. This is the difference between a schema and a user. Also you may be familiar with other database systems and will notice that what Oracle calls a schema the other systems call a database. The Oracle database can have numerous schemas and each schema in the database can query other schemas in the same database. Also schemas can communicate over database links to schemas in other remote databases. The communication between schemas is only limited by the permissions you put in place. Generally most applications will sit inside just a single schema.
Creating the Tablespace¶
Before creating a schema you will need to create a file (or more than one file) for the schema to place its data into. This file (or set of files) where the schema writes its data is called a tablespace. Note that you can create more than one tablespace for a schema and then have it write tables to one tablespace and indexes into the other and so forth. However we are going to keep this tutorial simple and just create a single tablespace that is made up of a single 1GB file. First connect to the database from the command line as the sys user: $ sqlplus /nolog
SQL> connect / as sysdba
Now that you are connected you can create a tablespace. The definition that I use below creates a tablespace called CRAIG_DATA which is sized 1GB and uses the data file /ora/fs2000/u04/CRAIG/craig_data_01.dbf. You can change this to whatever you want to call the tablespace and data file.
SQL> create tablespace CRAIG_DATA datafile '/ora/fs2000/u04/CRAIG/craig_data_01.dbf' size 1000M extent management local autoallocate segment space management auto;
create tablespace SYC datafile '/u02/oradata/AL32UTF8/syc_data_01.dbf' size 1000M extent management local autoallocate segment space management auto;
Creating the User¶
With the tablespace created we can now create the user. The reasoan we had to create the tablespace first was that the tablespace name is used in the definition of the user.
To create a user we use the following string which creates a user named craig with password craig and uses the tablespace CRAIG_DATA. Of course change the name, password, and tablespace to your own:
SQL> create user craig identified by "craig" default tablespace CRAIG_DATA;
Now that the user is created we will give them some basic permissions so they can start working.
The first permission they will need is called CONNECT. This will allow them to connect to the database. The second permission they will need is quota on their tablespace so they can write to it. And finally we will give them one more permission called CREATE TABLE which will allow them to create tables. You will notice that Oracle has a fine control over everything users are allowed to do. Here is the syntax for the three permissions (change the names to your own):
SQL> grant connect to craig; SQL> alter user craig quota unlimited on CRAIG_DATA; SQL> grant create table to craig;
Drop a Tablespace¶
DROP TABLESPACE SRH INCLUDING CONTENTS and datafiles CASCADE CONSTRAINTS;
Listing Tablespaces and Default Storage Parameters¶
SELECT TABLESPACE_NAME "TABLESPACE", INITIAL_EXTENT "INITIAL_EXT", NEXT_EXTENT "NEXT_EXT", MIN_EXTENTS "MIN_EXT", MAX_EXTENTS "MAX_EXT", PCT_INCREASE FROM DBA_TABLESPACES;
Listing the Datafiles and Associated Tablespaces of a Database¶
SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME FROM DBA_DATA_FILES;
Alter a Tablespace¶
ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>