How to extract SQL from SQLT

SQLTXPLAIN gets installed into separate schema called SQLTXPLAIN. It can be installed on RAC and on any version greater than 9i. When it is installed, it will ask for application schema so make sure that installation schema has SELECT_CATALOG_ROLE privilege. In order to run the script to create SQLTXPLAIN schema, one need to connect as […]

Read More

Create Database using DBCA silent mode

DBCA is great tool to create database using GUI , but sometimes when we don’t have access to GUI, we can still create database using DBCA silent mode creation where you need to pass silent option with dbca and some parameter values to create database. In following example we have used template General_Purpose ,   […]

Read More

ESTIMATE in Oracle Expdp

The value for parameter ESTIMATE  is either BLOCKS (default) or STATISTICS. BLOCKS: The estimated space used is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes. It is  the least accurate because of: The table was created with a much bigger initial extent size than was needed for […]

Read More

ESTIMATE_ONLY in Oracle Expdp

This parameter is useful when you wanted get the approximate size of the dump file. Suppose you have only limited space available in the disk and if you wanted to check whether your dump will be fit into the available space then you can use this parameter to check or estimate the size of the […]

Read More

Flashback Technology

Flashback Technology in Oracle : Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. Today we will see following feature of Flashback Technology : 1. Oracle Flashback Query . 2. Flashback […]

Read More

List of other Certification earned

Skant Gupta is certified for: Microsoft Microsoft® Certified Technology Specialist: SQL Server® 2005 Microsoft® Certified Solutions Associate: Office 365 (MCSA) Microsoft® Specialist: Server Virtualization with Windows Server Hyper-V and System Center Specialist Microsoft® Certified Professional IBM IBM Certified Deployment Professional – Tivoli Directory Server V6.1 IBM Certified Associate Developer — Rational Application Developer for WebSphere Software […]

Read More

Undo Segments are corrupted

Database is shutdown and can’t start it, asking for Undo recovery while dropping Undo tablespace Dropping a Undo tablespace give message The UNDOTBS for our RMAN Catalog database was dropped. Any attempts to switch to a newly created UNDOTBS or removing the old UNDOTBS failed with the following error: ORA-01548: active rollback segment Or Undo […]

Read More

Read the Trace file from SQL Plus

Find trace file in Oracle The following script returns the path to the trace file that the current session writes. It returns the path whether or not tracing is enabled.

Check the value for user_dump_dest

Create a directory

Create a function

Now just the select the trace file name.

Thank you for giving your valuable […]

Read More

Oracle Fixed Views

Oracle maintains a set of virtual tables that record current database activity. These tables are called dynamic performance tables. This post was written by Sriram Sanka in his blog. Why the term “Fixed”? Because They are fixed ..One cannot perform any DML , DDL or any operation other than “SELECT“,that’s why these views/Tables are known […]

Read More

ORA-00059:maximum number of DB_FILES exceeded

By default in every Oracle Database, you can have up to 200 total data files (.DBF files) in each Oracle Database instance as indicated by the value of the parameter “db_files” (you can check the value by running “show parameter db_files” in your database. If you need to create more data files than what the […]

Read More