oracle database limits

Here is the  limitation of oracle database

Physical Database Limits

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048 bytes; must be a multiple of operating system physical block size
	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment.	2 blocks
	Maximum per datafile	Platform dependent; typically 222 - 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
	Size of a control file	Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually 1022
	Maximum per database	65533 May be less on some operating systems Limited also by size of database blocks and by the DB_FILESinitialization parameter for a particular instance
Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier);4 GB (without K/M modifier)
	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier);2 GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
	Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement  Control file can be resized to allow more entries; ultimately an operating system limit
	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	50 KB
	Maximum size	Operating system limit; typically 2 GB
Tablespaces	Maximum number per database	64 KB Number of tablespaces cannot exceed the number of database files, as each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	232 (4 GB) blocks
Smallfile (traditional) Tablespaces	Number of blocks	222 (4 MB) blocks

Logical Database Limits

Item	Type			Limit
GROUP BYclause	Maximum length			The GROUP BY expression and all of the nondistinct aggregate functions (for example, SUM, AVG) must fit within a single database block.
Indexes	Maximum per table			Unlimited
	total size of indexed column			75% of the database block size minus some overhead
Columns	Per table			1000 columns maximum
	Per index (or clustered index)			32 columns maximum
	Per bitmapped index			30 columns maximum
Constraints	Maximum per column			Unlimited
Subqueries	Maximum levels of subqueries in a SQL statement			Unlimited in the FROM clause of the top-level query  255 subqueries in the WHERE clause
Partitions			Maximum length of linear partitioning key	4 KB - overhead
			Maximum number of columns in partition key	16 columns
			Maximum number of partitions allowed per table or index	64 KB - 1 partitions
Rows			Maximum number per table	Unlimited
Stored Packages			Maximum size	PL/SQL and Developer/2000 may have limits on the size of stored procedures they can call. The limits typically range from 2000 to 3000 lines of code.  See Also: Your PL/SQL or Developer/2000 documentation for details
Trigger Cascade Limit			Maximum value	Operating system-dependent, typically 32
Users and Roles			Maximum	2,147,483,638
Tables			Maximum per clustered table	32 tables
			Maximum per database	Unlimited

Process and Runtime Limits

Item	Type	Limit
Instances per database	Maximum number of cluster database instances per database	Operating system-dependent
Locks	Row-level	Unlimited
	Distributed Lock Manager	Operating system dependent
SGA size	Maximum value	Operating system-dependent; typically 2 to 4 GB for 32-bit operating systems, and 4 GB for 64-bit operating systems
Advanced Queuing Processes	Maximum per instance	10
Job Queue Processes	Maximum per instance	1000
I/O Slave Processes	Maximum per background process (DBWR, LGWR, etc.)	15
	Maximum per Backup session	15
Sessions	Maximum per instance	32 KB; limited by the PROCESSES and SESSIONS initialization parameters
Global Cache Service Processes	Maximum per instance	10
Shared Servers	Maximum per instance	Unlimited within constraints set by the PROCESSES and SESSIONS initialization parameters, for instance
Dispatchers	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Parallel Execution Slaves	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance
Backup Sessions	Maximum per instance	Unlimited within constraints set by PROCESSES and SESSIONS initialization parameters, for instance

---

oracle database block corruption scripts.

script to get corrupted block list.

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
     , greatest(e.block_id, c.block#) corr_start_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) 
       - greatest(e.block_id, c.block#) + 1 blocks_corrupted
     , null description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks - 1
   AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
     , header_block corr_start_block#
     , header_block corr_end_block#
     , 1 blocks_corrupted
     , 'Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
     , greatest(f.block_id, c.block#) corr_start_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) 
       - greatest(f.block_id, c.block#) + 1 blocks_corrupted
     , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks - 1
   AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

check the corrupted block on database level using db verify & rman validate.

RMAN> backup validate check logical datafile  #

dbv userid=system/****** file= file path  blocksize=8192

DBVERIFY - Verification complete




Total Pages
Examined         : 3968000

Total Pages Processed (Data) :
2113698

Total Pages Failing  
(Data) : 0

Total Pages Processed (Index):
1311235

Total Pages Failing  
(Index): 0

Total Pages Processed (Other):
502143

Total Pages Processed
(Seg)  : 3553

Total Pages Failing  
(Seg)  : 0

Total Pages
Empty            : 37371

Total Pages Marked
Corrupt   : 0

Total Pages
Influx           : 0

Highest block
SCN            :
1095073332 (1.1095073332)

Replication methods in oracle and MSSQL server database

In oracle/MSSQL database we can have  multiple replication /standby database available for DR purpose.

oracle to oracle replication methods


1.RAC --Real application cluster for High availability
2.oracle Data guard for standby DR
3.oracle golden gate replication methods
4.we can achieve oracle to oracle replication with streams

oracle to other database replication methods

1.we can do oracle to MSSQL server & IBM DB2  replication using golden gate.
2..we can do oracle to MSSQL server & IBM DB2  replication using gateway server
3.we can do oracle to MSSQL server & IBM DB2  replication using  drivers to support
4.we can do oracle to MSSQL server & IBM DB2  replication using  third party tools like DBmoto

MSQL server to oracle replication methods
1.we can do MSSQL server to oracle DB replication using golden gate
2.we can do MSSQL server to oracle DB replication using native MSSQl server replication method with publisher/ditribuitor/subscriber
3.we can do MSSQL server to oracle DB replication using third party tools like DBmoto
4.we can do MSSQL server to oracle DB replication using gateway server

steps to clone PDB manually in oracle 12c

Clone a Pluggable Database (PDB) Manually

Cloning an existing local PDB is similar to creating a new PDB from the seed PDB, except now we are using non-seed PDB as the source, which we have to identify using the FROM clause. Make sure the source PDB is open in READ ONLY mode.

Here pdb1 is SOURCE pdb4 as target(new DB )


ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;--source DB needs to be read only mode

CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/');

ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;

-- Switch the source PDB back to read/write
ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ WRITE;

SQL>  select file_name from cdb_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/users01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/system01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/pdb2_users01.dbf

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf

14 rows selected.

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY
*
ERROR at line 1:
ORA-65019: pluggable database PDB1 already open


SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE;

Pluggable database altered.

SQL> CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/');
  2  CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode


SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN READ ONLY;

Pluggable database altered.

SQL> CREATE PLUGGABLE DATABASE pdb4 FROM pdb1
  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/');
  2


Pluggable database created.

SQL> SQL> SQL>
SQL> ALTER PLUGGABLE DATABASE pdb4 OPEN READ WRITE;

Pluggable database altered.

SQL> ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 OPEN READ WRITE;
Pluggable database altered.

SQL>

Pluggable database altered.


SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;  2    3

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE
PDB4                           READ WRITE

 Delete a Pluggable Database (PDB) Manually

When dropping a pluggable database, you must decide whether to keep or drop the associated datafiles. The PDBs must be closed before being dropped.

ALTER PLUGGABLE DATABASE pdb2 CLOSE;
DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

ALTER PLUGGABLE DATABASE pdb3 CLOSE;
DROP PLUGGABLE DATABASE pdb3 INCLUDING DATAFILES;

ALTER PLUGGABLE DATABASE pdb4 CLOSE;
DROP PLUGGABLE DATABASE pdb4 INCLUDING DATAFILES;  2  SQL> SP2-0734: unknown command beginning "When dropp..." - rest of line ignored.
SQL> SQL>
Pluggable database altered.

SQL>
Pluggable database dropped.

SQL> SQL>
Pluggable database altered.

SQL>
Pluggable database dropped.

SQL> SQL>
Pluggable database altered.


SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;  2    3

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB4                           MOUNTED

SQL> alter PLUGGABLE DATABASE  pdb4  open read write;

Pluggable database altered.

SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;  2    3

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB4                           READ WRITE

table space administration on PDB in oracle 12c

adding  tablespace to PDB database
connect to the PDB

SQL> ALTER SESSION SET CONTAINER = pdb4;

Session altered.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB4
SQL> create tablespace test datafile '/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/test.dbf' size 200m;

Tablespace created.

SQL> SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/system01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/sysaux01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/pdb1_users01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/test.dbf

SQL> DROP TABLESPACE test INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> SELECT name FROM v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/system01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/sysaux01.dbf
/media/sf_12cR1/12cDB/cdb1/pdb2/pdb4/pdb1_users01.dbf

SQL> SELECT tablespace_name FROM dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS

scrip to check the query execution time in oracle database

You can use below query to find out the execution time of the query. Just replace the SQL_ID with your SQL_ID.

SELECT
SQL_ID,
EXECUTIONS,
ELAPSED_TIME/1000000 TOTAL_ELAPSED_TIME_SEC,
ELAPSED_TIME/1000000/EXECUTIONS ELAPSED_TIME_SEC_PER_EXEC,
CPU_TIME/1000000 TOTAL_CPU_TIME_SEC,
CPU_TIME/1000000/EXECUTIONS CPU_TIME_SEC
FROM
V$SQL WHERE SQL_ID='dx647nxstqu260';

To calculate the execution time from the AWR tables, you can use below query.

SELECT
SNAP_ID,
SQL_ID,
EXECUTIONS_DELTA EXECUTIONS,
ELAPSED_TIME_DELTA/1000000 TOTAL_ELAPSED_TIME_SEC,
ELAPSED_TIME_DELTA/1000000/EXECUTIONS_DELTA ELAPSED_TIME_SEC_PER_EXEC,
CPU_TIME_DELTA/1000000 TOTAL_CPU_TIME_SEC,
CPU_TIME_DELTA/1000000/EXECUTIONS_DELTA CPU_TIME_SEC
FROM
DBA_HIST_SQLSTAT WHERE SQL_ID='dx647nxstqu260';

steps to Plugin a Pluggable Database (PDB) Manually in oracle 12c

Plugin a Pluggable Database (PDB) Manually

Plugging in a PDB into the CDB is similar to creating a new PDB. First check the PBD is compatible with the CDB by calling the DBMS_PDB.CHECK_PLUG_COMPATIBILITY function, passing in the XML metadata file and the name of the PDB you want to create using it.


SET SERVEROUTPUT ON
DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => '/media/sf_12cR1/12cDB/cdb1/pdb2/pdb2.xml',
                pdb_name       => 'pdb2');

  IF l_result THEN
    DBMS_OUTPUT.PUT_LINE('compatible');
  ELSE
    DBMS_OUTPUT.PUT_LINE('incompatible');
  END IF;
END;
/

SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14  compatible -->it supposed to return compatible to plug in the database again to the container.

PL/SQL procedure successfully completed.


we want to plug the database back into the same container, so we don't need to copy the files or recreate the temp file, so we can do the following.

CREATE PLUGGABLE DATABASE pdb2 USING '/media/sf_12cR1/12cDB/cdb1/pdb2/pdb2.xml'
  NOCOPY
  TEMPFILE REUSE;

ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;

Pluggable database created.

SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;  2    3

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           MOUNTED
PDB3                           READ WRITE

steps to unpug the PDB database from container in oracle 12c

Unplug a Pluggable Database (PDB) Manually

Before attempting to unplug a PDB, you must make sure it is closed. To unplug the database use the ALTER PLUGGABLE DATABASE command with the UNPLUG INTO clause to specify the location of the XML metadata file.


ALTER PLUGGABLE DATABASE pdb2 CLOSE;
SQL> ALTER PLUGGABLE DATABASE pdb2 CLOSE;

Pluggable database altered.

ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO '/media/sf_12cR1/12cDB/cdb1/pdb2/pdb2.xml';

SQL> ALTER PLUGGABLE DATABASE pdb2 UNPLUG INTO '/media/sf_12cR1/12cDB/cdb1/pdb2/pdb2.xml';

Pluggable database altered.

SQL> SELECT name, open_mode

FROM   v$pdbs

ORDER BY name;  2    3

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB2                           MOUNTED

PDB3                           READ WRITE

SQL> DROP PLUGGABLE DATABASE pdb2 KEEP DATAFILES;

Pluggable database dropped.

SQL> SELECT name, open_mode

FROM   v$pdbs

ORDER BY name;  2    3

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDB1                           READ WRITE

PDB3                           READ WRITE

steps to create PDB manually in 12c

Create a Pluggable Database (PDB) Manually

To create a new pluggable database from the seed database, all we have to do is tell Oracle where the file should be placed. We can do this using one of two methods. The first method uses the FILE_NAME_CONVERT clause in the CREATE PLUGGABLE DATABASE statement.

CONN / AS SYSDBA

SQL>CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1

  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/media/sf_12cR1/12cDB/cdb1/pdb2/');

database created.

SQL> ALTER PLUGGABLE DATABASE pdb3 OPEN READ WRITE;

SQL> SELECT name, open_mode
FROM   v$pdbs
ORDER BY name;
  2    3
NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE

How to create manual profile in oracle 10g database?

issue:
oracle 10g database sql execution plan got changed and sql performing badly.
root cause:
oracle picked up the new plan which is a bad plan and same time we have good plan in AWR.
we have to force oracle to pick the good plan. but in 10g we dont have baseline features,so we cant forcefully oracle to pick the new plan.
solution:
In oracle 10g database version we don't have SPM and we have some  other  method to pick  the old plan.

we have SQLT feature is there and using it we can create manual profile by passing the SQL_ID and plan_hash_value.

coe_xfr_sql_profile.sql

SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
it will create new SQL file to create a profile on the database and we can create the profile on the database.
check the profile status using dba_sql_profiles