Information abut Oracle Database User Profiles.

How to Open / Close Oracle Pluggable Database.

How To Switch Change Between PDB's in Oracle 12c

 

How to enable archivelog/Noarchivelog in oracle 12c.

Oracle Database Basic & Crucial Information (USER OBJECT TYPES) Part - III

How To Check Tablespace Size Dynamically

------------------------------------------------------------------------
 How To Check Tablespace Size Dynamically 
------------------------------------------------------------------------

If you want to check Tablespace Size then use Below query.

 select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES;

 If you want to check Tablespace Size of particular tablespace then use below Query

 select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name='USERS';

 If you do not want to mention tablespace name again and again for different tablespaces then use Dynamic query for it

 select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name='&VALUE';

 Here i have used '&' symbol ahead of VALUE ,by using it it will ask for tablespace name at run time.

 If you do not want to write query again and again for tablespace size make it as a saved notepad file with .sql extension

 Name of .sql file is Tablespace_size.sql .

 Content of file :

col tablespace_name for a25
col file_name for a40
select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name='&VALUE';

Here

col tablespace_name for a25
col file_name for a40

used for getting output in readable form.

To run it from sqlplus just use @filelocation.file_name.sql

example : SQL> @Tablespace_Size.sql;

after invoking script , it asks for tablespace name ,then enter name in capital form or use UPPER function before '&VALUE' like

select tablespace_name,file_name,bytes/1024/1024 MB from DBA_DATA_FILES where tablespace_name=UPPER('&VALUE');

Here you can easily check that either you use Tablespace Name in small letters or capital you will get the desired result.

Use of TABLE_EXISTS_ACTION parameter in IMPDP

Use of TABLE_EXISTS_ACTION parameter in IMPDP

Table_Exists_Action used in impdp if table is already exists in the database where you want to import.
Possible values of TABLE_EXISTS_ACTION are:-


TABLE_EXISTS_ACTION = SKIP

Table_Exists_Action =Skip: skip is for skipping the table which are already exist in the database this is the default value for parameter table_exists_action.

TABLE_EXISTS_ACTION = APPEND

Table_Exists_Action =Append: append is for appending the data in tables which are already in the database .by using append new rows are inserted in tables and skipping already exist rows and for this number of columns and type of column matches.

TABLE_EXISTS_ACTION = TRUNCATE

Table_Exists_Action =Truncate:  In this option if tables is already exist in the database then truncate that table and insert the data into existing table and not changing the definition.
 
TABLE_EXISTS_ACTION = REPLACE

Table_Exists_Action =Replace:  replace is for replacing the data as well as the definition of existing table and it is basically for when table definitions are different in database from dmp file.