Oracle DB Administration Part 1.

All of the examples below are presented here without full features, which are presented in the official Oracle DBA documentation. There are some simple steps, after which you can begin to work with Oracle DB Schema asap.

First of all should be created a TABLESPACE in a Database. This code-snippet should be written in the Editor of SYS or DBA Schema:

CREATE TABLESPACE TBSPACE_NAME
   DATAFILE '/u01/app/oracle/tblsp01.dbf' 
   SIZE 2024M;

DROP TABLESPACE TBSPACE_NAME;
DROP TABLESPACE TBSPACE_NAME INCLUDING CONTENTS; -- if it's not empty

Here:

TBSPACE_NAME is a name of TABLESPACE, which should be used during creating a new usable Schema;

‘/u01/app/oracle/tblsp01.dbf’ is a name and a path to the Schema’s active datafile;

2024M is a size of created datafile, which means a free Schema’s space.



Next step is creating a User. This ‘user-login’ and defined password may to be used for PL/SQL Developing inside the bound TABLESPACE.

CREATE USER TEST_USER IDENTIFIED BY t3stp2wd 
DEFAULT TABLESPACE TBSPACE_NAME;

SELECT
     username, 
     default_tablespace, 
     profile, 
     authentication_type 
FROM dba_users WHERE account_status = 'OPEN';

Here:

TEST_USER is a ‘user-login’, which should be used during getting access to the bound Schema;

t3stp2wd is a password to ‘user-login’;

TBSPACE_NAME is a bound TABLESPACE.



If you need to change a password to some defined user, then you can simply ALTER it:

ALTER USER TEST_USER IDENTIFIED BY t4stp3wd;

Here:

TEST_USER is a ‘user-login’, whose password need to be changed;

t4stp3wd is a new password to ‘user-login’.



Happens that during active work with Database its free space comes to 0. Expanding of free memory possible via creating a new datafile in the defined TABLESPACE:

ALTER TABLESPACE "TBSPACE_NAME" 
ADD DATAFILE '/u01/app/oracle/tblsp02.dbf' 
SIZE 2048M;

Here:

TBSPACE_NAME is a name of TABLESPACE to which you will add a new datafile;

‘/u01/app/oracle/tblsp02.dbf’ is a name and a path to the new active datafile.