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.

What is Async and Sync in Oracle Dataguard

What is Async and Sync in Oracle Dataguard

There are three protection modes for primary database in oracle dataguard

1.Maximum Protection

2.Maximum Performance

3.Maximum Availability

1.Maximum Protection

Transactions on the primary are not commit until redo information has been written to the online redo log and at least one of the the standby standby location. If there is not suitable standby location is available or found for any reason, the primary database shuts down.

2.Maximum Performance

Transactions on the primary database commit as soon as redo information has been written to the online redo log and Transfer of redo information to the standby server is asynchronous .so in this type of protection mode there is no impact on performance of the primary.

3.Maximum Availability

Transactions on the primary are not commit until redo information has been written to the online redo log and at least one of the the standby standby location. If there is not suitable standby location is available or found for any reason it acts same as the maximum performance mode.

For knowing the protection mode of your primary database use query

SQL>SELECT protection_mode FROM v$database;

if you want to switch into other mode from your current protection mode use

1.Maximum Availability

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

2.Maximum Performance

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

3.Maximum Protection

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;