Balaji's Blog: Learning Oracle

Wednesday, April 20, 2011

The Following Points needs to be considered when we plan to migrate our Oracle database 10gR2 to database 11gR2 and if we are using Oracle Application server 10gR2.

SEC_CASE_SENSITIVE_LOGON
Fine Grained network access (ACL)
Character set of old and new DBs
NLS setting of old and new DBs
Context, DB Links and DB Jobs (When Export and Import)
FORMS_USERNAME_CASESENSITIVE

To Compile all the forms at a time use the following code as bacth

echo ...Compilation Started...This may take few minutes...
del *.err

for %%i IN (*.pll) do frmcmp module=%%i userid=plas/plas@plas11g batch=yes module_type=LIBRARY compile_all=yes window_state=Minimize
echo Libraries Compilation completed Completed
for %%i IN (*.mmb) do frmcmp module=%%i userid=plas/plas@plas11g batch=yes module_type=MENU compile_all=yes window_state=Minimize
echo Menu Compilation completed Completed
for %%i IN (*.fmb) do frmcmp module=%%i userid=plas/plas@plas11g batch=yes module_type=FORM compile_all=yes window_state=Minimize
echo Form Compilation completed Completed

dir *.err /b >>Error_Forms.txt

Here the date modified of fmb and fmx will be changed to the run date

To convert all the reports (RDF to REP) use the following code as bacth

echo Compilation Started,This may take few minutes.

for %%i IN (*.rdf) do rwconverter source=%%i userid=plas/plas@plas11g stype=rdffile DTYPE=repfile compile_all=yes OVERWRITE=yes batch=yes

echo Reports Compilation completed Completed

Here the date modified of rep will be changed to the run date and rdf will not have any change.

Tuesday, July 6, 2010

How to Execute a Select Statement

variable x varchar2(25);

exec select 1 into :x from dual;

Print x;

Recycle Bin

What is Recycle Bin
Oracle has introduced "Recycle Bin" Feature Oracle 10g to store all the dropped objects.
If any table in Oracle 10g is dropped then any associated objects to this table such as indexes,
constraints and other dependant objects are simply renamed with a prefix of BIN$$.

Why Recycle Bin
A user drops a very important table--accidentally, of course--and it needs to be revived as soon as possible.
Oracle9i Database introduced the concept of a Flashback Query option to retrieve data from a point in time in the past, but it can't flash back DDL operations such as dropping a table.
The only recourse is to use tables pace point-in-time recovery in a different database and then recreate
the table in the current database using export/import or some other method.
This procedure demands significant DBA effort as well as precious time, not to mention the use of a different database for cloning.
But with Oracle 10g Recycle bin Feature the user can easily restore the Dropped Object.

How to Enable/Disable Recycle Bin

The Following property sets the Recycle Bin to be enabled or not for the DB.

SQL > SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

Value

-----

if the Value Is “on” then recyclebin feature is enabled for the Database.
If the Value is “off” the recyclebin feature is disabled.

The following commands are used to enable or Disable the Feature

SQL > ALTER SYSTEM SET recyclebin = ON;

SQL > ALTER SESSION SET recyclebin = ON;

SQL > ALTER SYSTEM SET recyclebin = OFF;

SQL > ALTER SESSION SET recyclebin = OFF;

Show the Contents in RECYCLEBIN
Use the following commands to show all the objects that are stored in Recycle Bin,

SQL > SHOW RECYCLEBIN;

SQL > SELECT * FROM USER_RECYCLEBIN;

Example
The Following Example explains the moving the object to the Recyclebin

SQL > CREATE TABLE TEST_RBIN(VAL NUMBER);

SQL > INSERT INTO TEST_RBIN(VAL) VALUES(10);

SQL > COMMIT;

SQL > DROP TABLE TEST_RBIN;

Print the Recycle bin Entries,

SQL > SHOW RECYCLEBIN;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

TEST_RBIN BIN$7fq9jEy8RSadimoE4xGjWw==$0 TABLE 2010-05-26:11:27:12

Restore the Objects

User can restore the Dropped tables by issuing the following commands,
The following Commands can be used to restore the dropped Objects,

SQL > FLASHBACK TABLE <<Table_Name >> TO BEFORE DROP;

Example,

SQL > FLASHBACK TABLE TEST_RBIN TO BEFORE DROP;

SQL > SELECT * FROM TEST_RBIN;

VAL

---

It is possible to restore the table in to different name by issuing the following SQL Command,

SQL > FLASHBACK TABLE << Dropped Table Name >> TO BEFORE DROP RENAME TO <<New Table Name >>;

SQL > FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN1;

Using the above statement, its possible to restore the various version of the table data if the table
is created and Dropped more than once.

While restoring system restores the table in Descending order.

Example,

SQL > CREATE TABLE TEST_RBIN (COL1 NUMBER);

SQL > INSERT INTO TEST_RBIN VALUES (1);

SQL > COMMIT;

SQL > DROP TABLE TEST_RBIN;

SQL > CREATE TABLE TEST_RBIN (COL1 NUMBER);

SQL > INSERT INTO TEST_RBIN VALUES (2);

SQL > COMMIT;

SQL > DROP TABLE TEST_RBIN;

SQL > CREATE TABLE TEST_RBIN (COL1 NUMBER);

SQL > INSERT INTO TEST_RBIN VALUES (3);

SQL > COMMIT;

SQL > DROP TABLE TEST_RBIN;

SQL > SHOW RECYCLEBIN;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

TEST_RBIN BIN$2e51YTa3RSK8TL/mPy+FuA==$0 TABLE 2010-05-27:15:23:43

TEST_RBIN BIN$5dF60S3GSEO70SSYREaqCg==$0 TABLE 2010-05-27:15:23:43

TEST_RBIN BIN$JHCDN9YwQR67XjXGOJcCIg==$0 TABLE 2010-05-27:15:23:42

SQL > FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN1;

SQL > FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN2;

SQL > FLASHBACK TABLE TEST_RBIN TO BEFORE DROP RENAME TO TEST_RBIN3;

SQL > SELECT * FROM TEST_RBIN1;

COL1

----------

SQL > SELECT * FROM TEST_RBIN2;

COL1

----------

SQL > SELECT * FROM TEST_RBIN3;

COL1

----------

Note:
The un-drop feature brings the table back to its original name, but not the associated objects like
indexes and triggers, which are left with the recycled names. Sources such as views and
procedures defined on the table are not recompiled and remain in the invalid state.
These old names must be retrieved manually and then applied to the flashed-back table.

Clearing the Recycle Bin

To Clear the Recycle bin the following SQL Statements can be used.
To Clean only a particular table, the following Statement will be used,

SQL > PURGE TABLE << Table_Name >>

Its possible to Purge the table based on the System Generated table name also

SQL > PURGE TABLE << Table_Name >>

This command will remove the table and all dependent objects such as indexes, constraints,
and so on from the recycle bin, saving some space. To permanently drop an index from the recycle bin,
the following statement can be used

SQL > PURGE INDEX <<Index_Name >>;

This will remove the index only, leaving the copy of the table in the recycle bin.
Sometimes it might be useful to purge at a higher level. For instance, to purge all the objects in recycle bin
in a particular tablespace,

SQL > PURGE TABLESPACE <<Table Space Name>>;

It is possible to purge only the recycle bin for a particular user in that tablespace.
This approach could come handy in data warehouse-type environments where users create and drop many transient tables.:

SQL > PURGE TABLESPACE <<Table Space Name>> USER <<User Name>>;

To clear the complete recycle bin , the following statement can be used

SQL > PURGE Recyclebin;

It is possible to purge the table while dropping the table itself.
The following statement would be used to achieve this,

SQL > DROP TABLE << Table_Name >> PURGE;

Example,
Create the Table and then Drop the table,

SQL > CREATE TABLE TEST_RBIN (COL1 NUMBER);

SQL > INSERT INTO TEST_RBIN VALUES (1);

SQL > COMMIT;

SQL > DROP TABLE TEST_RBIN;

View the recycle bin entries with show recycle bin option,

SQL > SHOW RECYCLEBIN;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

TEST_RBIN BIN$2e51YTa3RSK8TL/mPy+FuA==$0 TABLE 2010-05-27:15:23:43

Clear the table by executing the following Statement

SQL > PURGE TABLE TEST_RBIN; or

SQL > PURGE TABLE “BIN$2e51YTa3RSK8TL/mPy+FuA==$0”

Verify the Recycle bin, it will not have any entries for the table TEST_RBIN,

SQL > SHOW RECYCLEBIN;

Note:
Once the Table is purged from recycle bin, it will not be possible to restore by using FLASHBACK command.

Space/Quota Issue

Objects in the Recycle Bin will remain in the database until the owner of the dropped objects decides
to permanently remove them using the new PURGE command.
The Recycle Bin objects are counted against a user's quota. But Flashback Drop is a non-intrusive feature.
Objects in the Recycle Bin will be automatically purged by the space reclamation process if

1. A user creates a new table or adds data that causes his/her quota to be exceeded.
2. The tablespace needs to extend its file size to accommodate create/insert operations

Friday, June 25, 2010

The following is a list of database limits which are divided into four categories in addition to the PL/SQL compiler limits. It is an aggregation of 5 separate web pages from the Oracle Database 11g Release 2 documentation library. I put them all here on one page for convenience.

Datatype Limits

Datatypes	Limit	Comments
BFILE	Maximum size: 4 GB Maximum size of a file name: 255 characters Maximum size of a directory name: 30 characters Maximum number of open BFILEs: see Comments	The maximum number of BFILEs is limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open files the operating system will allow.
BLOB	Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000).
CHAR	Maximum size: 2000 bytes	None
CHAR VARYING	Maximum size: 4000 bytes	None
CLOB	Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000).
Literals (characters or numbers in SQL or PL/SQL – more)	Maximum size: 4000 characters	None
LONG	Maximum size: 2 GB – 1	Only one LONG column is allowed per table.
NCHAR	Maximum size: 2000 bytes	None
NCHAR VARYING	Maximum size: 4000 bytes	None
NCLOB	Maximum size: (4 GB – 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)	The number of LOB columns per table is limited only by the maximum number of columns per table (that is, 1000).
NUMBER	999…(38 9’s) x10¹²⁵ maximum value -999…(38 9’s) x10¹²⁵ minimum value	Can be represented to full 38-digit precision (the mantissa) Can be represented to full 38-digit precision (the mantissa)
Precision	38 significant digits	None
RAW	Maximum size: 2000 bytes	None
VARCHAR	Maximum size: 4000 bytes	None
VARCHAR2	Maximum size: 4000 bytes	None

Logical Database Limits

Item	Type of Limit	Limit Value
GROUP BY clause	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
Indexes	Total size of indexed column	75% of the database block size minus some overhead
Columns	Per table	1000 columns maximum
Columns	Per index (or clustered index)	32 columns maximum
Columns	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
Partitions	Maximum number of columns in partition key	16 columns
Partitions	Maximum number of partitions allowed per table or index	1024K – 1
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.
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
Tables	Maximum per database	Unlimited

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
Database Block Size	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment	2 blocks
Database Blocks	Maximum per datafile	Platform dependent; typically 2²² – 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles	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
Database files	Maximum per database	65533 May be less on some operating systems Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents (more)	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents	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 2²² or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	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
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	4 MB
Redo Log File Size	Maximum Size	Operating system limit; typically 2 GB
Tablespaces	Maximum number per database	64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 2³² ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (2²²) blocks.
External Tables file	Maximum size	Dependent on the operating system.An external table can be composed of multiple files.

Process and Runtime Limits

Item	Type of Limit	Limit Value
Instances per database	Maximum number of cluster database instances per database	Operating system-dependent
Locks	Row-level	Unlimited
Locks	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
I/O Slave Processes	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

PL/SQL Compiler Limits

Item	Limit
bind variables passed to a program unit	32768
exception handlers in a program unit	65536
fields in a record	65536
levels of block nesting	255
levels of record nesting	32
levels of subquery nesting	254
levels of label nesting	98
levels of nested collections	no predefined limit
magnitude of a PLS_INTEGER or BINARY_INTEGERvalue	-2147483648..2147483647
number of formal parameters in an explicit cursor, function, or procedure	65536
objects referenced by a program unit	65536
precision of a FLOAT value (binary digits)	126
precision of a NUMBER value (decimal digits)	38
precision of a REAL value (binary digits)	63
size of an identifier (characters)	30
size of a string literal (bytes)	32767
size of a CHAR value (bytes)	32767
size of a LONG value (bytes)	32760
size of a LONG RAW value (bytes)	32760
size of a RAW value (bytes)	32767
size of a VARCHAR2 value (bytes)	32767
size of an NCHAR value (bytes)	32767
size of an NVARCHAR2 value (bytes)	32767
size of a BFILE value (bytes)	4G * value of DB_BLOCK_SIZE parameter
size of a BLOB value (bytes)	4G * value of DB_BLOCK_SIZE parameter
size of a CLOB value (bytes)	4G * value of DB_BLOCK_SIZE parameter
size of an NCLOB value (bytes)	4G * value of DB_BLOCK_SIZE parameter