Tablespace in Oracle Posted by MOHAMMED ABDUL HAMEED on 12:23 AM

A Tablespace is a container for segments (tables, indexes, etc). A database consists of one or more tablespaces, each made up of one or more data files. Tables and indexes are created within a particular tablespace.

When a New database is created, it will have the following tablespaces :

SYSTEM (the data dictionary)
SYSAUX (optional database components)
TEMP (temporary tablespace)
UNDOTBS1 (undo tablespace, contains pre image data)
USERS (default users tablespace created)

Syntax :

Create tablespace
Datafile '/u01/home/prd/cmm.dbf' size 100m 
autoextend on 
next 100m maxsize unlimited;

Check Free/Used space per tablespace :

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Sample output:

Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- -----------------
    UNDOTBS1                               65    17.8125         27         73
     EXAMPLE                               100     22.625         23         75
    USERS                                         5     1.0625         21         79
    TEMP                                         20          2             10         90
   SYSAUX                               625.125     304.5        48      52
   SYSTEM                                  700     9.0625          1         99

Oracle Data Pump (expdp and impdp) in Oracle Database 11g Posted by MOHAMMED ABDUL HAMEED on 5:09 PM

Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.

Table Exports/Imports

expdp system@databasename tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp system@databasename tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

Schema Exports/Imports

expdp system@databasename schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp system@databasename schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports

expdp system@databasename full=Y directory=TEST_DIR dumpfile=DB11G.dmp logfile=expdpDB11G.log

impdp system@databasename
  full=Y directory=TEST_DIR dumpfile=DB11G.dmp logfile=impdpDB11G.log

To Create a Logical Directory : 

CREATE DIRECTORY datapump AS 'C:\oradata\datapump';
GRANT EXP_FULL_DATABASE  to scott;

GRANT READ, WRITE ON DIRECTORY datapump to scott;

DB_RECOVERY_FILE_DEST - Alter parameters ORACLE Posted by MOHAMMED ABDUL HAMEED on 11:19 PM

DB_RECOVERY_FILE_DEST specifies the default location for the flash recovery area backups. The flash recovery area contains multiplexed copies of current control files and online redo logs, as well as archived redo logs, flashback logs, and RMAN backups.

Specifying this parameter without also specifying the DB_RECOVERY_FILE_DEST_SIZE initialization parameter is not allowed

Below are the steps to Change  DB_RECOVERY_FILE_DEST & DB_RECOVERY_FILE_DEST_SIZE Parameter:

[oracle@]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 16 09:30:39 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

-------------------------------- ----------- ------------------------------

db_recovery_file_dest                string      /opt/oracle/test01/flash_recovery_area

db_recovery_file_dest_size           big integer 10G

Command for changing db_recovery_file_dest

SQL> alter system set db_recovery_file_dest='/opt/oracle/test01/dbs/arch' scope=both;

System altered.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      /opt/oracle/test01/dbs/arch

db_recovery_file_dest_size           big integer 10G

Command for changing db_recovery_file_dest_size

SQL>  alter system set db_recovery_file_dest_size=20G scope=both;

System altered.

SQL> show parameter db_recovery

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest                string      /opt/oracle/test01/dbs/arch

db_recovery_file_dest_size           big integer 20G

Flash/Fast Recovery Area (FRA) in Oracle Posted by MOHAMMED ABDUL HAMEED on 10:08 PM

Flash Recovery Area can be defined as a single, centralized, unified storage area that keep all the database backup & recovery related files and performs those activities in Oracle databases.

Related views
V$RECOVERY_FILE_DEST
V$FLASH_RECOVERY_AREA_USAGE
V$DBA_OUTSTANDING_ALERTS
V$FLASHBACK_DATABASE_LOGFILE

# Instance Name

select instance_name from v$instance;

# Used GB in FRA

select name

, floor(space_limit / 1024 / 1024/1024) "Size GB"

, ceil(space_used  / 1024 / 1024/1024) "Used GB"

from v$recovery_file_dest

order by name;

# FRA Occupants

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

SELECT NAME,

TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,

TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999') AS SPACE_AVAILABLE,

ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL

FROM V$RECOVERY_FILE_DEST;

select * from v$Recovery_file_dest;

SELECT

ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)

AS PERCENT_FULL

FROM V$RECOVERY_FILE_DEST;

# Location and size of the FRA

show parameter db_recovery_file_dest;

# Size, used, Reclaimable

SELECT

  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB,

  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB,

  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,

  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED

FROM

  V$RECOVERY_FILE_DEST A,

  V$FLASH_RECOVERY_AREA_USAGE B

GROUP BY

  SPACE_LIMIT,

  SPACE_USED ,

  SPACE_RECLAIMABLE ;