DATA PUMP QUESTIONS & ANSWERS

DATA PUMP QUESTIONS & ANSWERS
------------------------------------------------------

1) What is use of CONSISTENT option in exp?
A)Cross-table consistency. Implements SET TRANSACTION READ ONLY. Default value N.

2) What is use of DIRECT=Y option in exp?
A)Setting direct=yes, to extract data by reading the data directly, bypasses the SGA, bypassing the SQL command-processing layer (evaluating buffer), so it should be faster. Default value N.

3) What is use of COMPRESS option in exp?
A)Imports into one extent. Specifies how export will manage the initial extent for the table data. This parameter is helpful during database re-organization. Export the objects (especially tables and indexes) with COMPRESS=Y. If table was spawning 20 Extents of 1M each (which is not desirable, taking into account performance), if you export the table with COMPRESS=Y, the DDL generated will have initial of 20M. Later on when importing the extents will be coalesced. Sometime it is found desirable to export with COMPRESS=N, in situations where you do not have contiguous space on disk (tablespace), and do not want imports to fail.

4) How to improve exp performance?
a) Set the BUFFER parameter to a high value. Default is 256KB.
b) Stop unnecessary applications to free the resources.
c) If you are running multiple sessions, make sure they write to different disks.
d) Do not export to NFS (Network File Share). Exporting to disk is faster.
e) Set the RECORDLENGTH parameter to a high value.
f) Use DIRECT=yes (direct mode export).

5) How to improve imp performance?
a) Place the file to be imported in separate disk from datafiles.
b) Increase the DB_CACHE_SIZE.
c) Set LOG_BUFFER to big size.
d) Stop redolog archiving, if possible.
e) Use COMMIT=n, if possible.
f) Set the BUFFER parameter to a high value. Default is 256KB.
g) It's advisable to drop indexes before importing to speed up the import process or set INDEXES=N and building indexes later on after the import. Indexes can easily be recreated after the data was successfully imported.
h) Use STATISTICS=NONE
i) Disable the INSERT triggers, as they fire during import.
j) Set Parameter COMMIT_WRITE=NOWAIT(in Oracle 10g) or COMMIT_WAIT=NOWAIT (in Oracle 11g) during import.

6) What is use of INDEXFILE option in imp?
A)Will write DDLs of the objects in the dumpfile into the specified file.

7) What is use of IGNORE option in imp?
A)Will ignore the errors during import and will continue the import.

8)What are the differences between expdp and exp (Data Pump or normal exp/imp)?
A)Data Pump is server centric (files will be at server).
Data Pump has APIs, from procedures we can run Data Pump jobs.
In Data Pump, we can stop and restart the jobs.
Data Pump will do parallel execution.
Tapes & pipes are not supported in Data Pump.
Data Pump consumes more undo tablespace.
Data Pump import will create the user, if user doesn’t exist.

9) Why expdp is faster than exp (or) why Data Pump is faster than conventional export/import?
A)Data Pump is block mode, exp is byte mode. 
Data Pump will do parallel execution.
Data Pump uses direct path API.

10) How to improve expdp performance?
A)Using parallel option which increases worker threads. This should be set based on the number of cpus.

11) How to improve impdp performance?
A)Using parallel option which increases worker threads. This should be set based on the number of cpus.

12) In Data Pump, where the jobs info will be stored (or) if you restart a job in Data Pump, how it will know from where to resume?
A)Whenever Data Pump export or import is running, Oracle will create a table with the JOB_NAME and will be deleted once the job is done. From this table, Oracle will find out how much job has completed and from where to continue etc.
Default export job name will be SYS_EXPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.
Default import job name will be SYS_IMPORT_XXXX_01, where XXXX can be FULL or SCHEMA or TABLE.

13) What is the order of importing objects in impdp?
A) Tablespaces
 Users
 Roles
 Database links
 Sequences
 Directories
 Synonyms
 Types
 Tables/Partitions
 Views
 Comments
 Packages/Procedures/Functions
 Materialized views

14) How to import only metadata?
A)CONTENT= METADATA_ONLY

15)How to import into different user/tablespace/datafile/table?
A)REMAP_SCHEMA
REMAP_TABLESPACE
REMAP_DATAFILE
REMAP_TABLE 
REMAP_DATA

16) How to export/import without using external directory?
a) Run the older CATEXP.SQL script on the database to be exported
b)use the older export utility to create the dump file
c)use the older import utility to import to the target db

17) Using Data Pump, how to export in higher version (11g) and import into lower version (10g), can we import to 9i?
A) No guarantee that an later release expdp dmp file will import into a earlier relese in 10g or 91 this is called forward compatibility ,impossible we can not import to earlier releses


18) How to do transport tablespaces (and across platforms) using exp/imp or expdp/impdp?
A) $impdp directory= datapump dumpfile=emp_bkp.dmp logfile =imp_emp.log tables='EMP' remap_schema='SCOTT:SCOTT' remap_tablespace='MYDATA:MYTBS'




THANK YOU  FOR VIEWING MY BLOG MORE UPDATES VISIT MY BLOG  

ORACLE RMAN(RECOVERY MANAGER) COMPONENTS AND CONFIGURATIONS

ORACLE RMAN(RECOVERY MANAGER) COMPONENTS AND CONFIGURATIONS:  

RMAN:

             Rman or Oracle Recovery Manager introduced in oracle 8i,oracle proprietary software client or utility similar to sqlplus used to perform backups,restores,recoveries and other Database operations,Rman takes backup only usedblocks in entire database blocklevel backup,it performs block level backup parallelism,Rman is the utility to take backups and restore oracle recommends,Rman is faster it takes block level backups,Rman is faster because we can initiative parallesism,validate your database using Rman detect block corruption,Rman will repair the database block corruption for you validating backup,incremental backup,recovery catalog.and it is a platform independent tool.

            Rman stores backup metadata inforamtion in the database ,use control file to hold backup metadata information catalog, also using remote repsitory catalog schema database it is a online backup tool its a special feature does block backup with incremental concept,metadata is the control file data stores the information into different database on server,Multi destination backups like tapes,Archive log mode is must to use Rman. Rman utility comes with oracle binaries,no special installation orlicence required for using Rman at command prompt just type rman,it defaults connects to database environmental variables defined, Rman utility can be used only when Database is atleast mount stage,Rman is used while the database is UP and running and have a very little performance impact is backup is running .

COMPONENTS OF RMAN

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

RMAN PROMPT

TARGET DATABASE

RECOVERY CATALOG

AUXILAR DATABASE-->clone DB connection target clone

MEDIA MANAGEMENT LAYER-->RMAN and third party tools (net backup)

RMAN CHANNELS---> back up is speed 

FULL BACKUP-----> ENTIRE DATABASE BACKUP (RMAN FULL BACKUP IS EQUAL TO HOT BACKUP  CANNOT APPLY INCREMENTAL BACKUP ON FULL BACKUP)

              

INCREMENTAL BACKUP----> LEVEL 0--->FULL DB BACKUP--> Full Db backup takes bakup of the used blocks( we can restore Db level0)

  |----> LEVEL 1--->BACKUP CHANGES ONLY FROM LAST LEVEL 0 BACKUP--->Takes Backup of only the changed blocks from last backup,Rman will know which block will be changed block header SCN number backup only changed block  taken by referrring data block header for updated SCN (we can recover DB applying archives and open database)

RECOVERY IN TWO PARTS----->RESTORE--->LEVEL 0

                                              |--->RECOVERY--> LEVEL 1

CONFIGURATIONS OF RMAN

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

To connect using rman

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

$rman target/

To Display the configuration of RMAN.

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

rman>show all;

Configuring Device Type - Tape / Disk

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

For Tape: Tivoli manager (IBM)

rman>Configure default device type to sbt;

For Disk : Default

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

rman>Configure default device type to disk;

Configuring Channels with Parallelism Option - EE

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

Multiple backupsets can run with parallel option.

Depends on number of cores, can increase the performance of the backup using multiple channels.

$mkdir -p /orabackup/prod/rman/stream1

$mkdir -p /orabackup/prod/rman/stream2

rman>Configure device type disk parallelism 2;

rman>CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/orabackup/prod/rman/stream1/backup%U';

rman>CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/orabackup/prod/rman/stream2/backup%U';

Maximum Piece Size

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

Limit the backup piece size 

rman>CONFIGURE CHANNEL  DEVICE TYPE DISK MAXPIECESIZE 1000m;

Optimization

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

oracle skips the files that are already backedup by enabling optimization.

rman>configure backup optimization on;

Control file autobackup

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

Control file holds data and control information of database

 as well metadata of backup information.

On every backup event, the control has to get backed up. Enable autobackup.

With control file - includes spfile also

rman>configure controlfile autobackup on;

rman>configure controlfile autobackup off;

rman>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabackup/prod/rman/%F';

Eg:

Output :

--------

Starting Control File and SPFILE Autobackup at 23-JUL-20

piece handle=/orabackup/prod/rman/c-369100151-20171110-00 comment=NONE

Finished Control File and SPFILE Autobackup at 23-JUL-20

Retention Policy - default 1 day

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

is defined , how to long to hold the backups ..

Once retention value reached, the backup files will expire.

rman>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Snapshot controlfile

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

rman>CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/orabackup/prod/rman/snapcf_prod.f';

To update the recovery catalog to get sync for consistent , will have a snapshot controlfile.

Backup sets / Backup Pieces

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

RMAN can also store its backups in an RMAN-specific format called a backup set.

A backup set is a collection of files called backup pieces, each of which may contain the backup of one or several database files.

retention

optimization

control on , path/loc

snapshot

parallelism

channels 

maxpiecesize

disk/tape

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

How to have a backup using rman?

rman target/

rman>backup database plus archivelog;

Only datafiles

rman>backup database;

only archivelog files

rman>backup archivelog all;

Image backup - same as dbf format

rman>backup as copy database;

list of backup files

rman>list backup;

rman>list backup summary;

Error : scenario:

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

ORA-19625: error identifying file /archives/prod/1_986936449_51.arc

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

in case : 

archive log missing , cannot continue backup.

for that , use crosscheck command . will validate the existing archives and mark has expired for

those missing archives in control file. so next backup will skip those missing archives found expired.

rman>crosscheck archivelog all;

rman>backup database plus archivelog;

For backups 

rman>crosscheck backup;

Error : Scenario:-

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

ORA-19502: write error on file "/orabackup/prod/rman/stream2/backup0kth1k51_1_1", block number 51456 (block size=8192)

ORA-27072: File I/O error

Verify the physical file system space on /orabackup

$df -h

if 100% 

remove old files using

Step1 : rman>report obsolete;

will list all files that are expired/obsolete;

Step2 : rman>delete obsolete;

will delete those listed obsolete files.

If those files not listed and not part of the same database.

then delete using rm -rf * from /orbackup/prod/rman/stream1

1. cd /orabackup/prod/rman/stream1

2. ls -ltrh

3. $rm -rf *

Compressed backup

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

rman>BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Validate the db

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Skip those archives missing,and free up space on archive location while 

backup.

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

RMAN>backup archivelog all delete input skip inaccessible;

/archives - 100%

Take a archive log backup,and resume space by deleteing those backedup archives.

move -/archives - /orabackup (1-10 -old ls -ltr)

backup - delete - space resumed

/orabackup-/archives (1-10) - free already

crosscheck

backup - delete

Connect - RMAN

Backup and Recovery

Configuration

retention policy

controlfile auto

path - controlfile

parallelism - faster

channels

optimization - to skip

snapshot -

maxpiece size

Backup sets - specific format

Backup pieces - files

backup database plus archivelog

crosscheck - validate

delete obsolete

report obsolete

Incremental - Level0 - level 1 | Cummulative

Difference - delete obsolete | delete expired

compressed

Backup Strategy - Explain ?

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

Incremental backup - Block Level - only changes.

Two types of incremental backup.

    Level 0 - Full incremental backup

    Level 1

        Differential incremental backup.

        Cummulative Backup

Full Incremental - Level 0 Backup -is the full complete

 base backup taken normally on peak off hours.

RMAN>BACKUP INCREMENTAL LEVEL 0 DATABASE;

Level - 1

Differential Backup

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

Sunday – full backup Including all archivelogs – this is a base backup.

Up on this – all changes made will be backedup on every day since last

 incremental backup.

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Cummulative – Including all Previous Changes from

base backup (Level 0) on Sunday.

----------

RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

So we can use next incremental backup for recovery , if previous level 1 backups are lost.

Its the best backup. If no redundancy.

But still, having multiple reduncies in terms tapes/disks. Will

recommend differential.

Crosscheck

---------

we have archives ,... while backup if archives are missing ? can we continue

 the backup?

What happens ?

 The backup fails.

How to continue ?...

ORA-19625: error identifying file /archives/prod/1_998901437_22.arc

RMAN> crosscheck archivelog all;

Crosscheck command validates archives physically exists and updates control

 file repository the file status.

So while rman backup , will verify the catalog and skip those files are

expired(marked) which are physically not exists.

rman>backup archivelog all;

But recommended to continue with full backup in this situation.

every 2/4 hrs will have archive log backup.

Difference between delete obsolete and delete expired?

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

Obsolete? : out of retention period. physically files exists and files are

 out retention.

Expired : physically not exists and marked has expired in catalog.

rman>report obsolete;

rman>delete obsolete;

rman>delete expired backup;

will delete records from catalog those expird.

Note:

Level 0 - Full Incremental Backup - Complete

1TB

Level 1

Differential -- Only changes from last incremental

Mon - 1GB -

Tues - 0.5G

Wed - 0.25G -

Only those changes - the difference

Cummulative - best - including previous changes on top of base backup

Mon - 1G

Tues - 1G+0.5G - lost

Wed = 1G+0.5+0.25G - can retain from wed backup of Tues

which includes previous changes as it is cummulative

Space and time

Real time , we use only differential if redundancy managed with additional tape and storage backup.

If tape backup of tuesday with differential and lost disk backup

Can we retain from tape

/orabackup - rman - tues -

once done

will have tape backup

Note:

------

rman>backup archivelog all delete input skip inaccessible;

difference delete obsolete and expired

based on retention, the backup files get expired but physically exists

those need to be deleted

as backup policy , retention 7 days if incremental

after 7 days - expired

need to clean up space to resume

use obsolete

Recoveries

----------

Recover - Lost datafile

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

    users will not be able to write in users datafile (scott/hr-eg)

Make sure - we have full DB backup - last night including - archives.

as we lost - keep offline datafile.

@09 last night

rman>backup database plus archivelog;

@next day at 03PM - lost datafile

$rm users01.dbf

SQL> alter database datafile '/oradata/prod/users01.dbf' offline;

rman target/

RMAN>restore datafile '/oradata/prod/users01.dbf';

rman>recover datafile '/oradata/prod/users01.dbf';

will recover changes from last night 09 till today 03pm from

archives.

SQL> alter database datafile '/oradata/prod/users01.dbf' online;

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

inconsistent Recovery - If no archives.

    Timebased

        using SCN number(v$database)

As you would expect, RMAN allows incomplete recovery to a specified time,

 SCN or sequence number:

$rman target/

$rman>run {

 shutdown abort;

 startup mount;

 set until sequence 21;

 restore database;

 recover database; #media recovery from archiveslogs

 alter database open resetlogs;

}

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

Whole database recovery

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

If the controlfiles and online redo logs are still present a whole database

 recovery can be achieved by running the following script:

rman target/

rman>

run { shutdown abort

# use abort if this fails

 startup mount;

 restore database;

 recover database;

alter database open;

}

Note: Using run{} block ,we can execute multiple commands in rman.

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

Difference between delete obsolete and delete expired

Delete Obsolete - will delete, where the files physical exists and out of

 retention period.

rman>delete obsolete;

rman>delete obsolete noprompt;

Delete expired - will delete those files are marked as expired and physically

 not exists.

rman>delete expired backup;

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

Compressed backup

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

rman>BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

Validate the db

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

Skip those archives missing,and free up space on archive location while

backup.

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

RMAN>backup archivelog all delete input skip inaccessible;

/archives - 100%--->/orabackup

space resume by delete

if missed,skipping

move -/archives - /orabackup (1-10 -old ls -ltr)

backup - delete - space resumed

/orabackup-/archives (1-10) - free already

crosscheck

backup - delete

13.arc bkp arclog

14.arc

15.arc

16.arc till - next fail. due to missing archives

18.arc skip(17) - inaccessible will continue also delete

those backedup(13,14,15,16,18).

21.arc skip(19,20) - inaccessible

backup completed and resumed space

22.arc

23.arc

missing 17/19

NOTE: info on Rman may be differs in  your environment like production,testing,development or u r host machine.

THANK YOU VIEWING MY BLOG FOR MORE UPDATES VISIT MY BLOG REGULARLY

MANAGING TABLESPACE AND DATAFILES IN ORACLE

MANAGING TABLESPACE AND DATAFILES IN ORACLE

Introduction: 

Tablespace :In this blog i am going to explain MANAGING TABLESPACE AND DATAFILES IN ORACLE,Tablespace is one or more logical storage units in oracle database  which collectively store all the database data and each tablespace has one or more datafiles, datafiles are physical structure tablespace is not visbile in the filesytem of the machine which datbase resides,The tablespace builds the bridge between the oracle database and the filesystem in which the tables or index's data stored ,The data in oracle database are stored in tablespaces, An oracledatabase can be logically grouped into smaller logical areas of space known as tablespaces,each tablespace consits of one or more operating system files,which are called datafiles

1)SYSTEM TABLESPACE: SYSTEM tablespace is used to store the sytem related data which includes tables,indexes,sequences,and other objects,Contains the data dictionary,including stored program units Contains the SYSTEM undo segment,should not contain user data,SYSTEM tablespace always online when database is open ,oracle database have a SYSTEM tablespace when database created SYSTEM is the first tablespace


2)SYSAUX TABLESPACE : SYSAUX TABLESPACE stores many databse components,always online when database is open,these SYSAUX table created when oracle database installed,if u SYSAUX table gets filled 100% none of the users will not be able to login 


3)UNDO TABLESPACE :  UNDO TABLESPACE is a kind of permanent tablespace used by oracele to manage undo data if your running your database in automatic undo management mode this undo data or undo records are generally used to roll back transations  Recover from logical corruptions using flashback feature 


4)USERS TABLESPACE: USERS TABLESPACE is used to store the user objects and data in permanently every database have a tablespace for permanent user data is assigned to users otherwise the objects will be stored SYSTEM tablespace  


5)TEMP TABLESPACE:  TEMP TABLESPACE is stores the temporary data  that only exists during the database session,oracle uses temporary tablesapce to improve the concurrency of multiple sort operations, temp tablesapce shared by multiple users


DATABASE STORAGE HIERARCHY  HOW DATA IS STORED IN LOGICAL AND PHYSICAL
--------------------------------------------------------------------------------------------------------------------------

  DATABASE
     |
     |
     v
  TABLESPACE  ---------> DATAFILE-----> PHYSICAL------> SAN(STORAGE AREA NETWORK)
     |                                    | 
     |                                    |-----------> NAS (NETWORK ATTACHED STORAGE)
     v
  SEGMENT---------->LOGICAL
     |
     |
     v
  EXTENT----------->LOGICAL
     |
     |
     v
 ORACLE DATA BLOCK------------> LOGICAL OS BLOCK



TABLESPACE------>LOGICAL STORAGE UNIT
SEGMENTS-------->SPACE ALLOCATED FOR A TABLES INFORM OF EXTENTS
EXTENTS -------->CONTIGUOS ORACLE BLOCK 



Dictionary Tables

dba_tablespaces
dba_data_files
dba_segments
dba_extents
dba_free_space


List the tablespaces

sql>desc dba_tablespaces

sql>select tablespace_name from dba_tablespaces;

sql>select tablespace_name,extent_management,allocation_type,segment_space_management from dba_tablespaces;

sql>desc dba_tablespaces

Finding tablespace for sys owned.

Sys stores objects in system tablespace.

SQL> select username,default_tablespace from dba_users where username like 'SYS';


Listing datafiles of a tablespace:

sql>select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'CHAITBS';

Creating Tablespace:

sql>create tablespace CHAITBS datafile '/oradata/prod/chaitu01.dbf' size 100m;

Adding datafile:

sql>alter tablespace CHAITBS add datafile '/oradata/prod/chaitu02.dbf' size 100m autoextend on maxsize unlimited;

Note: 32G is the max file size on unix level

Finding size of a datafile:

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_data_files where tablespace_name like 'CHAITBS';


Resize Datafile:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' resize 200m;

Enable Autoextend on/off:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize 8192m;

Unlimited:

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize unlimited;

In oracle, the maxsize of the datafile on unix level is max 32G.


Dropping tablespace :

sql>drop tablespace CHAITBS including contents and datafiles;


Create tablespace
add datafile
resize
autoextend on
drop tablespaces
finding free space
find datafile location/size/max/autoextnd
list of tablespaces /managed type/segment space management

Dictionary tables/views

    dba_tablespaces
    dba_data_files
    dba_free_space
    dba_segments
    dba_extents


Find the usage

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_data_files where tablespace_name like 'SYSTEM';


Finding free space.of a particular /specific tablespace

sql>select tablespace_name,sum(bytes/1024/1024) from dba_free_space where tablespace_name like 'SYSTEM' group by tablespace_name;

For all tablespaces - using group by

sql>select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;


--------------------90%(7GB)-------limi(8G)----------------------max(32G)
New --------------------------------------------------------------max(32G)
existing limit ------------(8G)

Assume - Limit - 8GB
        threshold reached 90% (7GB)
            but Max size 32GB
Now action:
        Add new datafile with unlimited
        then limit existing datafile to 8GB


sql>alter tablespace CHAITBS add datafile '/oradata/prod/chaitu02.dbf' size 100m autoextend on maxsize unlimited;

Now limit existing

sql>alter database datafile '/oradata/prod/chaitu01.dbf' autoextend on maxsize 8192m;

Temp Tablespace (non system Tablespace)

    purpose : for sorting,analyze,index rebuild,group by

    default - temp(one)

dba_temp_files
v$temp_space_header

we can create many
sql>create temporary tablespace chaitempfile '/oradata/prod/chaitutemp01.dbf' size 100m autoextend on maxsize unlimited;

Adding temp file:

sql>alter tablespace cctemp add tempfile '/oradata/prod/cctemp02.dbf' size 100m autoextend on maxsize unlimited;

sql>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_temp_files where tablespace_name like 'CCTEMP';

Finding free usage:

sql>desc v$temp_space_header

SQL> select tablespace_name,sum(bytes_used/1024/1024),sum(bytes_free/1024/1024) from v$temp_space_header group by tablespace_name;

Error:

unable to extend an extent in temp segment of temporary tablespace
find the usage and add tempfile

Datafile monitoring,check freespace,add , limit,autoextend.

V$sort_usage - inactive - kill - support - approval


Dictionary Tables :

dba_tablespaces
dba_data_files
dba_free_space
dba_temp_files
v$temp_space_header


User management


Create user Chaitanya identified by chaitu123 default tablespace chaitbs temporary tablespace cctemp;

application users ---> application - -->chaitanya- - ->db

schema- - ->collection of objects ---> acces to db users


file limit - 8G
--------------------7G---->8G(Max)-----------setto(32G-YES)
  90% reached - alert received action

add datafile New - -32G
limit existing to 8G - autoextend on maxsize 8G.


resize - if disable autoextend on ,then limit by resize - physically allocate
maxsize- only allocate on growth


Managing Tablespaces and data files locally managed tablespace


sql>create  tablespace tbs1 datafile '/oradata/prod/data01.dbf'
    size=50m
    extent management local autoallocate;

Alternative to Autoallocate is uniform

sql>create tablespace tbs2 datafile' '/oradata/prod/data01.dbf'
   size=50m
  extent management local uniform size 256k;

Dictionary managed tablespace


sql>create tablespace tbs1 datafile '/oradata/prod/data01.dbf'
    size 50m
   extent management dictionary;


Big File Tablespace


sql>create bigfile tablespace tbsbf1 datafile '/oradata/prod/databf01.dbf'
    size 50g;

Coalesce statement

sql>alter tablespace tbs1 coalesce;


Viewing information about tablespace and datafiles

sql>select * from dba_tablespaces;
sql>select * from v$tablespace;


To view information about datafiles


sql>select * from dba_data_files;
sql>select * from v$datafiles;

To view information about tempfiles


sql>select * from dba_temp_files;
sql>select * from v$tempfile;


To view information about freespace in tempfiles

sql>select * from v$temp_space_header;


To view information about free space in datafiles

sql>select * from dba_free_space;


To view the value and type of the blocksize

sql>show parameter db_block_size;

Note : Info on MANAGING TABLESPACE AND DATAFILES IN ORACLE it may be differ in your environment like production,testing,development,etc




THANKS FOR VIEWING MY BLOG FOR MORE UPDATES SUBSCRIBE ME AND FOLLOW ME