Flash Recovery Area

Flash recovery area or Fast recovery area is a space designed by Oracle for keeping backup files. It is a centralized location where RMAN writes backup file (backup sets) to. Here is what you will see in the FRA:

1. A directory containing backup sets
2. A directory containing controlfiles
3. A directory containing archivelog files

To check the size of your recovery area, issue this command:

select name, space_limit, space_used from v$recovery_file_dest

SPACE_USED/1024/1024/1024 SPACE_LIMIT/1024/1024/1024
------------------------- --------------------------
0 3.8203125

or

select space_used/1024/1024/1024 from v$recovery_file_dest;


To check the usage of FRA, issue this command:

select * from v$flash_recovery_area_usage;

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
CONTROL FILE 0 0
0

REDO LOG 0 0
0

ARCHIVED LOG 0 0
0


FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
BACKUP PIECE 0 0
0

IMAGE COPY 0 0
0

FLASHBACK LOG 0 0
0


FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
FOREIGN ARCHIVED LOG 0 0
0



increasing size of FRA

alter system set db_recovery_file_dest_size=10G;

NOTE: before increasing your FRA, please verify your server has a space to accommodate the new 10G you want to create.

on Linux/Unix, issue this command:

df -h

SIZE of database
You may need to check the size of your database in order to make a report or for capacity planning. Here are some of the commands you can use.

Segments:
select sum(BYTES/1024/1024/1024) from dba_segments;


Datafile:
select sum(BYTES/1024/1024/1024) from dba_data_files;


Tablespace:
select sum(BYTES/1024/1024/1024) from dba_free_space where TABLESPACE_NAME='KUNLE';

or

select sum(BYTES/1024/1024/1024) from v$datafile where TABLESPACE_NAME='KUNLE';

Oracle user creation and privileges in 10g/11g

Oracle user creation and privileges in 10g/11g

Creating a user and setting privileges for them are common everyday tasks of Oracle DBAs and Developers. You can either do them through the commandline or use client tools, eg. SQL Developer.

creating a default tablespace for the user
Default tablespace is the main tablespace where all the objects of a user reside. A quota has to be set for the user on this tablespace, else oracle will assume the quota is unlimited. Here is the syntax to create a default tablespace for our user:

CREATE TABLESPACE ORACLE_USER
DATAFILE '/home/oracle/app/oracle/oradata/ORACLE_USER/datafile/ORACLE_USER01.dbf' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

creating a temporary tablespace for the user
Temporary tablespace is useful for sorting. Here is the syntax to create a temporary tablespace for our user:

CREATE SMALLFILE TEMPORARY TABLESPACE ORACLE_USERTEMP TEMPFILE '/home/oracle/app/oracle/oradata/ORACLE_USER/datafile/ORACLE_USERtemp01.dbf' SIZE 5M AUTOEXTEND ON NEXT 2048K MAXSIZE 1024M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

creating the user
When creating the user, these are important options:

"identified by" is used to specify the password of the user
"profile" the resource limits specified for the user. We will look at profile and roles in subsequent postings
"default tablespace" is used to specify where the objects of the user will be kept
"quota" specifies the percentage of the space in the default tablespace that is assigned to the user
"temporary tablespace" is already explained above
""account unlock" specifies the status of the account. If you specify "account lock", the account will be locked and the user will not be able to connect after creation.

CREATE USER ORACLE_USER PROFILE "DEFAULT" IDENTIFIED BY "XXXXX"
DEFAULT TABLESPACE ORACLE_USER
TEMPORARY TABLESPACE ORACLE_USERTEMP
QUOTA UNLIMITED ON ORACLE_USER ACCOUNT UNLOCK;

granting privileges for the user
This gives the user the privilege to access the database. "Create session" must be granted for the user to be able to connect to the database. Other privileges are self-explanatory. Oracle did a good job by using simple English meaning for the database terms.

GRANT CREATE SESSION TO ORACLE_USER;
GRANT CONNECT TO ORACLE_USER;
GRANT CREATE TRIGGER TO ORACLE_USER;
GRANT CREATE VIEW TO ORACLE_USER;
GRANT CREATE PROCEDURE TO ORACLE_USER;
GRANT CREATE SEQUENCE TO ORACLE_USER;
GRANT CREATE TABLE TO ORACLE_USER;
GRANT CREATE SYNONYM TO ORACLE_USER;

checking privileges granted to user
You can query the view "dba_sys_privs" to check the privileges granted to a user at any point in time. Here is an example:

SQL> select privilege from dba_sys_privs
2 where grantee='ORACLE_USER';

PRIVILEGE
----------------------------------------
CREATE TRIGGER
CREATE TABLE
CREATE SEQUENCE
CREATE SYNONYM
CREATE PROCEDURE
CREATE SESSION
CREATE VIEW
7 rows selected.

checking the user's info
You can query the view "dba_users" to check the user's info. For example:

SQL> select username, default_tablespace from dba_users where username='ORACLE_USER';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_USER ORACLE_USER

SQL>

dropping a user
You can delete a user by using the syntax:

drop user ORACLE_USER cascade;

This will permanently delete the user and all its objects from the database. It will also purge all the users objects in the recycle bin in Oracle 11g.