ADD/Remove UNDO tablespace in oracle database

ADD/Remove UNDO tablespace in oracle database:-
=====================================

 every database having default undo tablespace, if you want to change it do follow below procedure.

1. first create new tablespace with new(as you want to create)
Example:-
CREATE UNDO TABLESPACE <NEW_NAME> DATAFILE '<datafile_location/name.dbf>' SIZE 1000M AUTOEXTEND OFF ONLINE RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

ALTER SYSTEM SET UNDO_TABLESPACE = <NEW_NAME>;
ALTER SYSTEM SET UNDO_RETENTION = <SET as per NEED>;

Once new one created, if you want drop old one follow steps below.
check any session already using old one  with below sql

SQL> select tablespace_name, owner, segment_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1' and status='ONLINE';

you will get SID&Serial# if any in use, kill them if have or else you can proceed to drop old one.

To kill
SQL> alter system kill session 'SID,SERIAL#';

To drop
SQL> drop tablespace undotbs1 including contents and datafiles;



please help to share your suggestions,  or  share your experience on same kind of issue in comment session.
  your sharing or suggestions will helps me to improve in my future posts.

Thanks for the visit!!!

ADD/Remove TEMP tablespace in oracle database

ADD/Remove TEMP tablespace in oracle database:-
=====================================

  To add new Temp tablespace and change it as default do follow below procedure.

1. First create New temptablespace and change it to default temp.

CREATE TEMPORARY TABLESPACE <NEW_TEMP_NAME> TEMPFILE '<teampfile location.dbf' SIZE 1000M AUTOEXTEND OFF
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <NEW_TEMP_NAME>;


Remove the old temptable space , before remove you must check any session already in use or not.

to check use below query to get live session(SID&serial#)  those who use old temp.

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

THEN kill session if any session in use.

alter system kill session 'SID_NUMBER, SERIAL#';

we can drop old temporary tablespace.
DROP TABLESPACE TEMP including contents and datafiles;


Be careful while doing this in production.

Thanks for your visit my blog, if you have any suggestion or question please comment below. 

SQL write Query with Tuning tips

Query Performance Tuning
====================

 In this post i will give few tips while writing sql query, how we can write better query with less cost
 If you follow below steps keep in mind while writing query you can tune up your sql while writning
 time. Below are some tips which can be used as guideline.

  Make sure am not expert, But what i learn have been post here to help some one. 
  If you know more points or you want to add on more please post in comment session it help to me and also others to learn. 
  Even though i can miss some silly points or known point as well. 
  so please help to remind them !!!

1. DISTINCT:-  Do not use, If the objective can be achieved otherwise. DISTINCT incurs an extra sort operation and therefore slows your queries down.  
2. Indexed columns:- Do not modify indexed columns with functions like RTRIM, TO_CHAR, UPPER, TRUNC, As this will prevent the optimizer from identifying the index. If possible perform the modification on the constant side of the condition. If the indexed column is usually accessed through a function, consider creating a function based index. (many cases people use and functions and complain my query not using index).
3. UNION:- Do not use UNION, if the objective can be achieved through an UNION ALL. UNION incurs an extra sort operation which can be avoided.
4. EXISTS :- use if selective predicate in parent query .
5. IN : use if selective predicate is in the sub query.
6. joins from one complex view to another not recommended.
7. Do not use HINTS (as oracle new version using CBO(cost based optimizer).
8. statistics for the objects used in the query are up to date or not, need to check.
9. Read explain plan and try  tune your query.
10. Commented lines are not good in between query lines.
11. Instead of using NULL some cases DECODE better to use.

still have more point will add on later .

Thanks for your visit, please help to post in comment session if you have more points or any correction from my statements. it help to me &others!!!

B-tree vs Bitmap index

B-tree vs bitmap index
=================

  In this post i would like talk about index that to mainly b-tree vs bit man index.
  Both are used to play key role in performance of query.

lets some comparison of both:-

B-tree:-

• it is one Default index, Like create index on one or columns using tree like structure.
• Cardinality is High, means less duplicate
• don`t confuse some people call it function based index as in.

syntax example:-

CREATE INDEX <index_name>
ON <table_name>(function(column_name));

Bitmap:-

• Carnality is Low, means more duplicate.
• bitmap index create name with bimap
• key word, Bitmap only can use in oracle Enterprise edition, standard edition we can not use this feature.

syntax example:- CREATE BITMAP INDEX 
<index_name>
ON <table_name> (column_name);


still we can add on more points, but i want to hear from your side also.
so please post your opinion about index types in comment session. 

ORA-01436: CONNECT BY loop in user data

ORA-01436: CONNECT BY loop in user data

======================================

this error will produce mostly from CONNECT BY looping from staement, this looping from sql cause never ending so it generates error.

 check with your trace file and check with  application team to know what they runing and rectify it 

ORA-01000: maximum open cursors exceeded

ORA-01000: maximum open cursors exceeded 

cause : this issue produce reason OPEN_CURSORS parameter size, in your database please check present size and increase it more value to avoid error repetition in future. 

To check present value:-

sho parameter OPEN_CURSORS 

NAME                                 TYPE        VALUE

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

open_cursors                         integer     300

increase value by using below command

alter system set open_cursors = 1000 scope=both;

IMPDP 10g,11g and 12c parameters

IMPDP 10g,11g and 12c parameters  :-
===============================

IMPDP 10g

$ impdp help=y

Import: Release 10.2.0.5.0 - 64bit Production on Monday, 12 February, 2018 12:00:08

Copyright (c) 2003, 2007, Oracle.  All rights reserved.


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

Keyword               Description (Default)
------------------------------------------------------------------------------
ATTACH                Attach to existing job, e.g. ATTACH [=job name].
CONTENT               Specifies data to load where the valid keywords are:
                      (ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY             Directory object to be used for dump, log, and sql files.
DUMPFILE              List of dumpfiles to import from (expdat.dmp),
                      e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD   Password key for accessing encrypted column data.
                      This parameter is not valid for network import jobs.
ESTIMATE              Calculate job estimates where the valid keywords are:
                      (BLOCKS) and STATISTICS.
EXCLUDE               Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN         SCN used to set session snapshot back to.
FLASHBACK_TIME        Time used to get the SCN closest to the specified time.
FULL                  Import everything from source (Y).
HELP                  Display help messages (N).
INCLUDE               Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME              Name of import job to create.
LOGFILE               Log file name (import.log).
NETWORK_LINK          Name of remote database link to the source system.
NOLOGFILE             Do not write logfile.
PARALLEL              Change the number of active workers for current job.
PARFILE               Specify parameter file.
QUERY                 Predicate clause used to import a subset of a table.
REMAP_DATAFILE        Redefine datafile references in all DDL statements.
REMAP_SCHEMA          Objects from one schema are loaded into another schema.
REMAP_TABLESPACE      Tablespace object are remapped to another tablespace.
REUSE_DATAFILES       Tablespace will be initialized if it already exists (N).
SCHEMAS               List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE               Write all the SQL DDL to a specified file.
STATUS                Frequency (secs) job status is to be monitored where
                      the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION   Action to take if imported object already exists.
                      Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES                Identifies a list of tables to import.
TABLESPACES           Identifies a list of tablespaces to import.
TRANSFORM             Metadata transform to apply to applicable objects.
                      Valid transform keywords: SEGMENT_ATTRIBUTES, STORAGE
                      OID, and PCTSPACE.
TRANSPORT_DATAFILES   List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK  Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
                      Only valid in NETWORK_LINK mode import operations.
VERSION               Version of objects to export where valid keywords are:
                      (COMPATIBLE), LATEST, or any valid database version.
                      Only valid for NETWORK_LINK and SQLFILE.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command               Description (Default)
------------------------------------------------------------------------------
CONTINUE_CLIENT       Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT           Quit client session and leave job running.
HELP                  Summarize interactive commands.
KILL_JOB              Detach and delete job.
PARALLEL              Change the number of active workers for current job.
                      PARALLEL=<number of workers>.
START_JOB             Start/resume current job.
                      START_JOB=SKIP_CURRENT will start the job after skipping
                      any action which was in progress when job was stopped.
STATUS                Frequency (secs) job status is to be monitored where
                      the default (0) will show new status when available.
                      STATUS[=interval]
STOP_JOB              Orderly shutdown of job execution and exits the client.
                      STOP_JOB=IMMEDIATE performs an immediate shutdown of the

                      Data Pump job.

====================================================================

IMPDP 11G

====================================================================

$ impdp help=y

Import: Release 11.2.0.4.0 - Production on Mon Feb 12 12:01:58 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

The Data Pump Import utility provides a mechanism for transferring data objects

between Oracle databases. The utility is invoked with the following command:

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed

by various parameters. To specify parameters, you use keywords:

     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

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

The available keywords and their descriptions follow. Default values are listed within square brackets.

ATTACH

Attach to an existing job.

For example, ATTACH=job_name.

CLUSTER

Utilize cluster resources and distribute workers across the Oracle RAC.

Valid keyword values are: [Y] and N.

CONTENT

Specifies data to load.

Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS

Data layer option flags.

Valid keywords are: SKIP_CONSTRAINT_ERRORS.

DIRECTORY

Directory object to be used for dump, log and SQL files.

DUMPFILE

List of dump files to import from [expdat.dmp].

For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION_PASSWORD

Password key for accessing encrypted data within a dump file.

Not valid for network import jobs.

ESTIMATE

Calculate job estimates.

Valid keywords are: [BLOCKS] and STATISTICS.

EXCLUDE

Exclude specific object types.

For example, EXCLUDE=SCHEMA:"='HR'".

FLASHBACK_SCN

SCN used to reset session snapshot.

FLASHBACK_TIME

Time used to find the closest corresponding SCN value.

FULL

Import everything from source [Y].

HELP

Display help messages [N].

INCLUDE

Include specific object types.

For example, INCLUDE=TABLE_DATA.

JOB_NAME

Name of import job to create.

LOGFILE

Log file name [import.log].

NETWORK_LINK

Name of remote database link to the source system.

NOLOGFILE

Do not write log file [N].

PARALLEL

Change the number of active workers for current job.

PARFILE

Specify parameter file.

PARTITION_OPTIONS

Specify how partitions should be transformed.

Valid keywords are: DEPARTITION, MERGE and [NONE].

QUERY

Predicate clause used to import a subset of a table.

For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA

Specify a data conversion function.

For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_DATAFILE

Redefine data file references in all DDL statements.

REMAP_SCHEMA

Objects from one schema are loaded into another schema.

REMAP_TABLE

Table names are remapped to another table.

For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.

REMAP_TABLESPACE

Tablespace objects are remapped to another tablespace.

REUSE_DATAFILES

Tablespace will be initialized if it already exists [N].

SCHEMAS

List of schemas to import.

SERVICE_NAME

Name of an active Service and associated resource group to constrain Oracle RAC resources.

SKIP_UNUSABLE_INDEXES

Skip indexes that were set to the Index Unusable state.

SOURCE_EDITION

Edition to be used for extracting metadata.

SQLFILE

Write all the SQL DDL to a specified file.

STATUS

Frequency (secs) job status is to be monitored where

the default [0] will show new status when available.

STREAMS_CONFIGURATION

Enable the loading of Streams metadata

TABLE_EXISTS_ACTION

Action to take if imported object already exists.

Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

TABLES

Identifies a list of tables to import.

For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES

Identifies a list of tablespaces to import.

TARGET_EDITION

Edition to be used for loading metadata.

TRANSFORM

Metadata transform to apply to applicable objects.

Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.

TRANSPORTABLE

Options for choosing transportable data movement.

Valid keywords are: ALWAYS and [NEVER].

Only valid in NETWORK_LINK mode import operations.

TRANSPORT_DATAFILES

List of data files to be imported by transportable mode.

TRANSPORT_FULL_CHECK

Verify storage segments of all tables [N].

TRANSPORT_TABLESPACES

List of tablespaces from which metadata will be loaded.

Only valid in NETWORK_LINK mode import operations.

VERSION

Version of objects to import.

Valid keywords are: [COMPATIBLE], LATEST or any valid database version.

Only valid for NETWORK_LINK and SQLFILE.

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

The following commands are valid while in interactive mode.

Note: abbreviations are allowed.

CONTINUE_CLIENT

Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT

Quit client session and leave job running.

HELP

Summarize interactive commands.

KILL_JOB

Detach and delete job.

PARALLEL

Change the number of active workers for current job.

START_JOB

Start or resume current job.

Valid keywords are: SKIP_CURRENT.

STATUS

Frequency (secs) job status is to be monitored where

the default [0] will show new status when available.

STOP_JOB

Orderly shutdown of job execution and exits the client.

Valid keywords are: IMMEDIATE.

====================================================================

IMPDP 12c

====================================================================

$ impdp help=y

Import: Release 12.1.0.2.0 - Production on Mon Feb 12 12:02:51 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

The Data Pump Import utility provides a mechanism for transferring data objects

between Oracle databases. The utility is invoked with the following command:

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed

by various parameters. To specify parameters, you use keywords:

     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

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

The available keywords and their descriptions follow. Default values are listed within square brackets.

ABORT_STEP

Stop the job after it is initialized or at the indicated object.

Valid values are -1 or N where N is zero or greater.

N corresponds to the object's process order number in the master table.

ACCESS_METHOD

Instructs Import to use a particular method to load data.

Valid keyword values are: [AUTOMATIC], CONVENTIONAL, DIRECT_PATH

and EXTERNAL_TABLE.

ATTACH

Attach to an existing job.

For example, ATTACH=job_name.

CLUSTER

Utilize cluster resources and distribute workers across the Oracle RAC [YES].

CONTENT

Specifies data to load.

Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS

Data layer option flags.

Valid keywords are: DISABLE_APPEND_HINT and SKIP_CONSTRAINT_ERRORS.

DIRECTORY

Directory object to be used for dump, log and SQL files.

DUMPFILE

List of dump files to import from [expdat.dmp].

For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION_PASSWORD

Password key for accessing encrypted data within a dump file.

Not valid for network import jobs.

ENCRYPTION_PWD_PROMPT

Specifies whether to prompt for the encryption password [NO].

Terminal echo will be suppressed while standard input is read.

ESTIMATE

Calculate network job estimates.

Valid keywords are: [BLOCKS] and STATISTICS.

EXCLUDE

Exclude specific object types.

For example, EXCLUDE=SCHEMA:"='HR'".

FLASHBACK_SCN

SCN used to reset session snapshot.

FLASHBACK_TIME

Time used to find the closest corresponding SCN value.

FULL

Import everything from source [YES].

HELP

Display help messages [NO].

INCLUDE

Include specific object types.

For example, INCLUDE=TABLE_DATA.

JOB_NAME

Name of import job to create.

KEEP_MASTER

Retain the master table after an import job that completes successfully [NO].

LOGFILE

Log file name [import.log].

LOGTIME

Specifies that messages displayed during import operations be timestamped.

Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.

MASTER_ONLY

Import just the master table and then stop the job [NO].

METRICS

Report additional job information to the import log file [NO].

NETWORK_LINK

Name of remote database link to the source system.

NOLOGFILE

Do not write log file [NO].

PARALLEL

Change the number of active workers for current job.

PARFILE

Specify parameter file.

PARTITION_OPTIONS

Specify how partitions should be transformed.

Valid keywords are: DEPARTITION, MERGE and [NONE].

QUERY

Predicate clause used to import a subset of a table.

For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA

Specify a data conversion function.

For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_DATAFILE

Redefine data file references in all DDL statements.

REMAP_SCHEMA

Objects from one schema are loaded into another schema.

REMAP_TABLE

Table names are remapped to another table.

For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.

REMAP_TABLESPACE

Tablespace objects are remapped to another tablespace.

REUSE_DATAFILES

Tablespace will be initialized if it already exists [NO].

SCHEMAS

List of schemas to import.

SERVICE_NAME

Name of an active Service and associated resource group to constrain Oracle RAC resources.

SKIP_UNUSABLE_INDEXES

Skip indexes that were set to the Index Unusable state.

SOURCE_EDITION

Edition to be used for extracting metadata.

SQLFILE

Write all the SQL DDL to a specified file.

STATUS

Frequency (secs) job status is to be monitored where

the default [0] will show new status when available.

STREAMS_CONFIGURATION

Enable the loading of Streams metadata [YES].

TABLE_EXISTS_ACTION

Action to take if imported object already exists.

Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

TABLES

Identifies a list of tables to import.

For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES

Identifies a list of tablespaces to import.

TARGET_EDITION

Edition to be used for loading metadata.

TRANSFORM

Metadata transform to apply to applicable objects.

Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE,

LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, STORAGE, and

TABLE_COMPRESSION_CLAUSE.

TRANSPORTABLE

Options for choosing transportable data movement.

Valid keywords are: ALWAYS and [NEVER].

Only valid in NETWORK_LINK mode import operations.

TRANSPORT_DATAFILES

List of data files to be imported by transportable mode.

TRANSPORT_FULL_CHECK

Verify storage segments of all tables [NO].

Only valid in NETWORK_LINK mode import operations.

TRANSPORT_TABLESPACES

List of tablespaces from which metadata will be loaded.

Only valid in NETWORK_LINK mode import operations.

VERSION

Version of objects to import.

Valid keywords are: [COMPATIBLE], LATEST, or any valid database version.

Only valid for NETWORK_LINK and SQLFILE.

VIEWS_AS_TABLES

Identifies one or more views to be imported as tables.

For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.

Note that in network import mode, a table name may be appended

to the view name.

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

The following commands are valid while in interactive mode.

Note: abbreviations are allowed.

CONTINUE_CLIENT

Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT

Quit client session and leave job running.

HELP

Summarize interactive commands.

KILL_JOB

Detach and delete job.

PARALLEL

Change the number of active workers for current job.

START_JOB

Start or resume current job.

Valid keywords are: SKIP_CURRENT.

STATUS

Frequency (secs) job status is to be monitored where

the default [0] will show new status when available.

STOP_JOB

Orderly shutdown of job execution and exits the client.

Valid keywords are: IMMEDIATE.