If we can divide the function of query optimizer then we can get the following components of a query optimizer.
1)Query Transformer.
2)Estimator.
3)Plan Generator.
The functions of these components are given below.
Transforming Queries
--------------------------------------
Parser passed the parsed query to the query transformer as input, which is represented by a set of query blocks. The main objective of the query transformer is to determine if it is advantageous to change the form of the query so that it enables generation of a better query plan. Several different query transformation techniques are employed by the query transformer, including:
i)View Merging
ii)Predicate Pushing
iii)Subquery Unnesting
iv)Query Rewrite with Materialized Views
v)OR-expansion
Any combination of these transformations can be applied to a given query.
i)View Merging: The query transformer analyze the view query block separately from rest of the query and generate a view subplan. The optimizer then processes the rest of the query by using the view subplan in the generation of an overall query plan. The query transformer then removes the potentially suboptimal plan by merging the view query block into the query block that contains the view. This technique usually leads to a suboptimal query plan, because the view is optimized separately from rest of the query.
ii)Predicate Pushing: For those views that are not merged, the query transformer can push the relevant predicates from the containing query block into the view query block. This technique improves the subplan of the non-merged view, because the pushed-in predicates can be used either to access indexes or to act as filters.
iii)Subquery Unnesting: Often the performance of queries that contain subqueries can be improved by unnesting the subqueries and converting them into joins. Most subqueries are unnested by the query transformer. For those subqueries that are not unnested, separate subplans are generated. To improve execution speed of the overall query plan, the subplans are ordered in an efficient manner.
iv)Query Rewrite with Materialized Views: A materialized view is like a query with a result stored in a table. When a user query is found compatible with the query associated with a materialized view, the user query can be rewritten in terms of the materialized view. This technique improves the execution of the user query, because most of the query result has been precomputed. The query transformer looks for any materialized views that are compatible with the user query and selects one or more materialized views to rewrite the user query. The use of materialized views to rewrite a query is cost-based. That is, the query is not rewritten if the plan generated without the materialized views has a lower cost than the plan generated with the materialized views.
So if you convert a view query into a materialized view then there is possibly improving performance of the query.
v)OR-expansion:
2)Estimator.
--------------------------
The estimator generates three different types of measures:
i)Selectivity
ii)Cardinality
iii)Cost
These measures are related to each other, and one is derived from another. The end goal of the estimator is to estimate the overall cost of a given plan.
i)Selectivity: The selectivity of a predicate indicates how many rows from a row set will pass the predicate test. Selectivity lies in a value range from 0.0 to 1.0. A selectivity of 0.0 means that no rows will be selected from a row set, and a selectivity of 1.0 means that all rows will be selected.
ii)Cardinality: Cardinality represents the number of rows in a row set. Here, the row set can be a base table, a view, or the result of a join or GROUP BY operator.
iii)Cost: The cost represents units of work or resource used. The query optimizer uses disk I/O, CPU usage, and memory usage as units of work. So, the cost used by the query optimizer represents an estimate of the number of disk I/Os and the amount of CPU and memory used in performing an operation.
The access path determines the number of units of work required to get data from a base table. The access path can be a table scan, a fast full index scan, or an index scan. During table scan or fast full index scan, multiple blocks are read from the disk in a single I/O operation. Therefore, the cost of a table scan or a fast full index scan depends on the number of blocks to be scanned and the multiblock read count value. The cost of an index scan depends on the levels in the B-tree, the number of index leaf blocks to be scanned, and the number of rows to be fetched using the rowid in the index keys. The cost of fetching rows using rowids depends on the index clustering factor.
The join cost represents the combination of the individual access costs of the two row sets being joined, plus the cost of the join operation.
3)Generating Plans
-----------------------------------
The main function of the plan generator is to try out different possible plans for a given query and pick the one that has the lowest cost. Many different plans are possible because of the various combinations of different access paths, join methods, and join orders that can be used to access and process data in different ways and produce the same result.
Saturday, June 14, 2008
Physical and Logical Oracle Database Architecture
Physical Database Structure
------------------------------------------------------
If you call about the physical oracle database structure then it includes the file of database that we can see from Operating Systems. It consists of Datafiles, Control Files, Redo Log Files, Archive Log Files, Parameter Files, Alert and Trace Log Files and Backup Files.
1)Datafiles
-----------------
•All the database data are stored in datafiles.
•A datafile can be associated with only one database.
•All logical objects such as tables, indexes, functions are stored in data files.
•You can see your datafiles location in oracle by querying,
SQL> select file_name from dba_data_files;
2)Control Files
----------------------------
•Every Oracle database has a control file.
•A control file contains entries that specify the physical structure of the database.
•If the physical makeup of the database is altered (for example, if a new datafile or redo log file is created), then the control file is automatically modified by Oracle to reflect the change.
•You can see your control files location in oracle by querying,
SQL> select name from v$controlfile;
Or from Sql*Plus,
SQL> show parameter control_files
To know more about database control file please visit Control file in Oracle
3)Redo Log files
--------------------------
•Every Oracle database has a set of two or more redo log files.
•The set of redo log files is collectively known as the redo log for the database.
•A redo log is made up of redo entries (also called redo records).
•The primary function of the redo log is to record all changes made to data.
•The information in a redo log file is used only to recover the database from a system or media failure that prevents database data from being written to the datafiles.
•You can see your online redo log file location by querying,
SQL> select member from v$logfile;
The process of applying the redo log during a recovery operation is called rolling forward.
To know more about redo log files please visit
4)Archive Log Files
-----------------------
•The online redo log files is archived to another place if database archivelog mode is on. It ensures that all changes of database are saved to another place.
•You can see the location of your archived redo log file by querying,
SQL> select name from v$archived_log;
To know about archival destination please visit Archive log Destination
5)Parameter Files
------------------------------
A parameter file is used to start up the instance. It can be either spfile or pfile.
You can see the location of your spfile by querying,
SQL> select value from v$parameter where name='spfile';
Or in Sql*plus,
SQL> show parameter spfile;
To know more about it please visit Pfile and Spfile in Oracle
6)Alert and Trace Log Files
--------------------------------------
Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file.
To know about alert log file please visit What is Alert Log
7)Backup Files
------------------------
Backup files are the backup of database or individual tablespace or datafiles. They can be generated using RMAN or user managed backup.
Logical Database Structure
-------------------------------------------
If you call about the logical oracle database structure then it includes the file of database that we can't see from Operating Systems. Within oracle database itself it is maintained. The logical storage structures, includes oracle data blocks, extents, segments and tablespaces.
To know about tablespace visit
To know about oracle data block visit Overview of oracle data block
To know about extent visit Overview of Extents and when extents are allocated and deallocated
To know about segment visit
The details are discussed in another topics in my blog. To know more about those search inside my blog.
------------------------------------------------------
If you call about the physical oracle database structure then it includes the file of database that we can see from Operating Systems. It consists of Datafiles, Control Files, Redo Log Files, Archive Log Files, Parameter Files, Alert and Trace Log Files and Backup Files.
1)Datafiles
-----------------
•All the database data are stored in datafiles.
•A datafile can be associated with only one database.
•All logical objects such as tables, indexes, functions are stored in data files.
•You can see your datafiles location in oracle by querying,
SQL> select file_name from dba_data_files;
2)Control Files
----------------------------
•Every Oracle database has a control file.
•A control file contains entries that specify the physical structure of the database.
•If the physical makeup of the database is altered (for example, if a new datafile or redo log file is created), then the control file is automatically modified by Oracle to reflect the change.
•You can see your control files location in oracle by querying,
SQL> select name from v$controlfile;
Or from Sql*Plus,
SQL> show parameter control_files
To know more about database control file please visit Control file in Oracle
3)Redo Log files
--------------------------
•Every Oracle database has a set of two or more redo log files.
•The set of redo log files is collectively known as the redo log for the database.
•A redo log is made up of redo entries (also called redo records).
•The primary function of the redo log is to record all changes made to data.
•The information in a redo log file is used only to recover the database from a system or media failure that prevents database data from being written to the datafiles.
•You can see your online redo log file location by querying,
SQL> select member from v$logfile;
The process of applying the redo log during a recovery operation is called rolling forward.
To know more about redo log files please visit
4)Archive Log Files
-----------------------
•The online redo log files is archived to another place if database archivelog mode is on. It ensures that all changes of database are saved to another place.
•You can see the location of your archived redo log file by querying,
SQL> select name from v$archived_log;
To know about archival destination please visit Archive log Destination
5)Parameter Files
------------------------------
A parameter file is used to start up the instance. It can be either spfile or pfile.
You can see the location of your spfile by querying,
SQL> select value from v$parameter where name='spfile';
Or in Sql*plus,
SQL> show parameter spfile;
To know more about it please visit Pfile and Spfile in Oracle
6)Alert and Trace Log Files
--------------------------------------
Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file.
To know about alert log file please visit What is Alert Log
7)Backup Files
------------------------
Backup files are the backup of database or individual tablespace or datafiles. They can be generated using RMAN or user managed backup.
Logical Database Structure
-------------------------------------------
If you call about the logical oracle database structure then it includes the file of database that we can't see from Operating Systems. Within oracle database itself it is maintained. The logical storage structures, includes oracle data blocks, extents, segments and tablespaces.
To know about tablespace visit
To know about oracle data block visit Overview of oracle data block
To know about extent visit Overview of Extents and when extents are allocated and deallocated
To know about segment visit
The details are discussed in another topics in my blog. To know more about those search inside my blog.
Parameters that control the behavior of Query Optimizer
1)CURSOR_SHARING: •The optimizer generates the execution plan based on the presence of the bind variables but not the actual literal values.
•Based on the settings of this parameter -CURSOR_SHARING it converts literal values in SQL statements to bind variables and affect the execution plan of SQL statements.
•This parameter determines what kind of SQL statements can share the same cursors and can have any of either three values EXACT or SIMILAR or FORCE.
•If it is set to EXACT then it only allows statements with identical text to share the same cursor.
•SIMILAR settings causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized. SIMILAR is default.
•Forces specified that statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
2)DB_FILE_MULTIBLOCK_READ_COUNT:•This parameter specifies the number of blocks that are read in a single I/O during a full table scan or index fast full scan.
•The optimizer uses the value of DB_FILE_MULTIBLOCK_READ_COUNT to cost full table scans and index fast full scans.
•Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan.
•If this parameter is not set explicitly (or is set is 0), the optimizer will use a default value of 8 when costing full table scans and index fast full scans.
3)OPTIMIZER_INDEX_CACHING:•This parameter controls the costing of an index probe in conjunction with a nested loop.
•The range of values 0 to 100 for OPTIMIZER_INDEX_CACHING indicates percentage of index blocks in the buffer cache, which modifies the optimizer's assumptions about index caching for nested loops and IN-list iterators.
•A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache and the optimizer adjusts the cost of an index probe or nested loop accordingly.
•Use caution when using this parameter because execution plans can change in favor of index caching.
4)OPTIMIZER_INDEX_COST_ADJ:•OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
•The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost.
•Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
5)OPTIMIZER_MODE:This parameter is discussed on About Parameter OPTIMIZER_MODE
6)PGA_AGGREGATE_TARGET: This parameter automatically controls the amount of memory allocated for sorts and hash joins. Larger amounts of memory allocated for sorts or hash joins reduce the optimizer cost of these operations. This parameter is discussed on About PGA_AGGREGATE_TARGET parameter
7)STAR_TRANSFORMATION_ENABLED: •STAR_TRANSFORMATION_ENABLED determines whether a cost-based query transformation will be applied to star queries.
•If it is set to TRUE the optimizer will consider performing a cost-based query transformation on the star query.
•If it is set to FALSE the transformation will not be applied.
•If it is set to TEMP_DISABLE the optimizer will consider performing a cost-based query transformation on the star query but will not use temporary tables in the star transformation.
•Based on the settings of this parameter -CURSOR_SHARING it converts literal values in SQL statements to bind variables and affect the execution plan of SQL statements.
•This parameter determines what kind of SQL statements can share the same cursors and can have any of either three values EXACT or SIMILAR or FORCE.
•If it is set to EXACT then it only allows statements with identical text to share the same cursor.
•SIMILAR settings causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized. SIMILAR is default.
•Forces specified that statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
2)DB_FILE_MULTIBLOCK_READ_COUNT:•This parameter specifies the number of blocks that are read in a single I/O during a full table scan or index fast full scan.
•The optimizer uses the value of DB_FILE_MULTIBLOCK_READ_COUNT to cost full table scans and index fast full scans.
•Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan.
•If this parameter is not set explicitly (or is set is 0), the optimizer will use a default value of 8 when costing full table scans and index fast full scans.
3)OPTIMIZER_INDEX_CACHING:•This parameter controls the costing of an index probe in conjunction with a nested loop.
•The range of values 0 to 100 for OPTIMIZER_INDEX_CACHING indicates percentage of index blocks in the buffer cache, which modifies the optimizer's assumptions about index caching for nested loops and IN-list iterators.
•A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache and the optimizer adjusts the cost of an index probe or nested loop accordingly.
•Use caution when using this parameter because execution plans can change in favor of index caching.
4)OPTIMIZER_INDEX_COST_ADJ:•OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
•The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost.
•Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal.
5)OPTIMIZER_MODE:This parameter is discussed on About Parameter OPTIMIZER_MODE
6)PGA_AGGREGATE_TARGET: This parameter automatically controls the amount of memory allocated for sorts and hash joins. Larger amounts of memory allocated for sorts or hash joins reduce the optimizer cost of these operations. This parameter is discussed on About PGA_AGGREGATE_TARGET parameter
7)STAR_TRANSFORMATION_ENABLED: •STAR_TRANSFORMATION_ENABLED determines whether a cost-based query transformation will be applied to star queries.
•If it is set to TRUE the optimizer will consider performing a cost-based query transformation on the star query.
•If it is set to FALSE the transformation will not be applied.
•If it is set to TEMP_DISABLE the optimizer will consider performing a cost-based query transformation on the star query but will not use temporary tables in the star transformation.
Parameters that enable and control Query Optimizer Features
You know that Query optimizer is responsible to determine the best execution/explain plan. In many cases you may be astonished that the data is same in both database and you have gather statistics but both database give different execution plan. The possible reason for it it the variation of initialization parameter between two database. Now in the later section in this post we will have a look at the initialization parameter that affect the optimizer to determine execution plan.
A)Enable Query Optimizer Feature
--------------------------------------------
OPTIMIZER_FEATURES_ENABLE Parameter
------------------------------------------------
•It is a string type parameter and takes oracle version number as argument.
•Based on this parameter settings it is determined how oracle optimizer behaves.
•Every new release of oracle version comes with new feature for optimizer. Thus new version of optimizer can collect extra features of a query based on which execution plan can changes. If you upgrade your oracle to newer version and your don't want to change your execution plan according to new one (keep like older) then you can set this parameter to older one.
•The valid values of this parameter can be,
8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 | 10.0.0 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.2.0.1|10.2.0.2|10.2.0.3| etc.
B)Control the Behavior of the Query Optimizer
---------------------------------------------------------------------------
Here is the list of initialization parameters that can be used to control the behavior of the query optimizer.
1)CURSOR_SHARING
2)DB_FILE_MULTIBLOCK_READ_COUNT
3)OPTIMIZER_INDEX_CACHING
4)OPTIMIZER_INDEX_COST_ADJ
5)OPTIMIZER_MODE
6)PGA_AGGREGATE_TARGET
7)STAR_TRANSFORMATION_ENABLED
All of these parameters are discussed in topic Parameters that control the behavior of Query Optimizer.
A)Enable Query Optimizer Feature
--------------------------------------------
OPTIMIZER_FEATURES_ENABLE Parameter
------------------------------------------------
•It is a string type parameter and takes oracle version number as argument.
•Based on this parameter settings it is determined how oracle optimizer behaves.
•Every new release of oracle version comes with new feature for optimizer. Thus new version of optimizer can collect extra features of a query based on which execution plan can changes. If you upgrade your oracle to newer version and your don't want to change your execution plan according to new one (keep like older) then you can set this parameter to older one.
•The valid values of this parameter can be,
8.0.0 | 8.0.3 | 8.0.4 | 8.0.5 | 8.0.6 | 8.0.7 | 8.1.0 | 8.1.3 | 8.1.4 | 8.1.5 | 8.1.6 | 8.1.7 | 9.0.0 | 9.0.1 | 9.2.0 | 10.0.0 | 10.1.0 | 10.1.0.3 | 10.1.0.4 | 10.2.0.1|10.2.0.2|10.2.0.3| etc.
B)Control the Behavior of the Query Optimizer
---------------------------------------------------------------------------
Here is the list of initialization parameters that can be used to control the behavior of the query optimizer.
1)CURSOR_SHARING
2)DB_FILE_MULTIBLOCK_READ_COUNT
3)OPTIMIZER_INDEX_CACHING
4)OPTIMIZER_INDEX_COST_ADJ
5)OPTIMIZER_MODE
6)PGA_AGGREGATE_TARGET
7)STAR_TRANSFORMATION_ENABLED
All of these parameters are discussed in topic Parameters that control the behavior of Query Optimizer.
Choosing an Optimizer Goal
By default, the goal of the query optimizer is the best throughput. This means that it chooses the least amount of resources necessary to process all rows accessed by the statement. Oracle can also optimize a statement with the goal of best response time. This means that it uses the least amount of resources necessary to process the first row accessed by a SQL statement.
You always choose a goal for the optimizer based on the needs of your application from two options.
For, Oracle Reports applications throughput is more important ,because the user initiating the application is only concerned with the time necessary for the application to complete. Response time is less important, because the user does not examine the results of individual statements while the application is running.
For Oracle Forms applications or SQL*Plus queries response time is important because the interactive user is waiting to see the first row or first few rows accessed by the statement.
The goal of the optimizer is affected by,
A)OPTIMIZER_MODE Initialization Parameter
B)Optimizer SQL Hints for Changing the Query Optimizer Goal
C)Query Optimizer Statistics in the Data Dictionary
A)OPTIMIZER_MODE Initialization Parameter
---------------------------------------------------------------------------
The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance. To know your current settings issue,
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
This parameter can have three types of values.
1)ALL_ROWS: The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement). This is the default value.
2)FIRST_ROWS_n: The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.
3)FIRST_ROWS: The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.
B)Optimizer SQL Hints for Changing the Query Optimizer Goal
-------------------------------------------------------------------------------------------
To specify the goal of the query optimizer for an individual SQL statement, use one of the hints from FIRST_ROWS(n) or ALL_ROWS. Actually hints in an individual SQL statement override the OPTIMIZER_MODE initialization parameter for that SQL statement.
C)Query Optimizer Statistics in the Data Dictionary
---------------------------------------------------------------------------------------
The statistics used by the query optimizer are stored in the data dictionary. If no statistics are available when using query optimization, the optimizer will do dynamic sampling depending on the setting of the OPTMIZER_DYNAMIC_SAMPLING initialization parameter. This may cause slower parse times so for best performance, the optimizer should have representative optimizer statistics.
You always choose a goal for the optimizer based on the needs of your application from two options.
For, Oracle Reports applications throughput is more important ,because the user initiating the application is only concerned with the time necessary for the application to complete. Response time is less important, because the user does not examine the results of individual statements while the application is running.
For Oracle Forms applications or SQL*Plus queries response time is important because the interactive user is waiting to see the first row or first few rows accessed by the statement.
The goal of the optimizer is affected by,
A)OPTIMIZER_MODE Initialization Parameter
B)Optimizer SQL Hints for Changing the Query Optimizer Goal
C)Query Optimizer Statistics in the Data Dictionary
A)OPTIMIZER_MODE Initialization Parameter
---------------------------------------------------------------------------
The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance. To know your current settings issue,
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
This parameter can have three types of values.
1)ALL_ROWS: The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement). This is the default value.
2)FIRST_ROWS_n: The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.
3)FIRST_ROWS: The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows. FIRST_ROWS is available for backward compatibility and plan stability; use FIRST_ROWS_n instead.
B)Optimizer SQL Hints for Changing the Query Optimizer Goal
-------------------------------------------------------------------------------------------
To specify the goal of the query optimizer for an individual SQL statement, use one of the hints from FIRST_ROWS(n) or ALL_ROWS. Actually hints in an individual SQL statement override the OPTIMIZER_MODE initialization parameter for that SQL statement.
C)Query Optimizer Statistics in the Data Dictionary
---------------------------------------------------------------------------------------
The statistics used by the query optimizer are stored in the data dictionary. If no statistics are available when using query optimization, the optimizer will do dynamic sampling depending on the setting of the OPTMIZER_DYNAMIC_SAMPLING initialization parameter. This may cause slower parse times so for best performance, the optimizer should have representative optimizer statistics.
Friday, June 13, 2008
Optimizer Operations while executing SQL statement
Whenever you write an sql query, the query can be executed in many different ways, such as full table scans, index scans, nested loops, and hash joins. The query optimizer checks the various ways and determines the most efficient way to execute a SQL statement after considering many factors related to the objects referenced and the conditions specified in the query. This determination is an important step in the processing of any SQL statement and can greatly affect execution time.
The output from the optimizer is a plan that describes an optimum method of execution. The Oracle server provides query optimization.
Let's have a look at the operations that optimizer performs while processing SQL operations.
1)Evaluation of expressions and conditions: The optimizer first evaluates expressions and conditions containing constants as fully as possible.
2)Statement transformation: For complex statements involving, for example, correlated subqueries or views, the optimizer might transform the original statement into an equivalent join statement.
3)Choice of optimizer goals: The optimizer determines the goal of optimization. This topic is discussed later.
4)Choice of access paths: For each table accessed by the statement, the optimizer chooses one or more of the available access paths to obtain table data.
5)Choice of join orders: For a join statement that joins more than two tables, the optimizer chooses which pair of tables is joined first, and then which table is joined to the result, and so on.
Besides optimizer determination, the application designer can use hints in SQL statements to instruct the optimizer about how a statement should be executed.
Note: Before oracle 10g there was available CBO and RBO in case of optimizer optimization.
CBO-Cost Based Optimizer: Execution plan is calculated by taking into account the distribution of data. Starting from 10g optimizer only use CBO while optimization.
RBO-Rule-based optimizer: Chooses an execution plan for SQL statements based on the access paths available and the ranks of these access paths. If there is more than one way, then the RBO uses the operation with the lowest rank. This feature has been desupported.
The output from the optimizer is a plan that describes an optimum method of execution. The Oracle server provides query optimization.
Let's have a look at the operations that optimizer performs while processing SQL operations.
1)Evaluation of expressions and conditions: The optimizer first evaluates expressions and conditions containing constants as fully as possible.
2)Statement transformation: For complex statements involving, for example, correlated subqueries or views, the optimizer might transform the original statement into an equivalent join statement.
3)Choice of optimizer goals: The optimizer determines the goal of optimization. This topic is discussed later.
4)Choice of access paths: For each table accessed by the statement, the optimizer chooses one or more of the available access paths to obtain table data.
5)Choice of join orders: For a join statement that joins more than two tables, the optimizer chooses which pair of tables is joined first, and then which table is joined to the result, and so on.
Besides optimizer determination, the application designer can use hints in SQL statements to instruct the optimizer about how a statement should be executed.
Note: Before oracle 10g there was available CBO and RBO in case of optimizer optimization.
CBO-Cost Based Optimizer: Execution plan is calculated by taking into account the distribution of data. Starting from 10g optimizer only use CBO while optimization.
RBO-Rule-based optimizer: Chooses an execution plan for SQL statements based on the access paths available and the ranks of these access paths. If there is more than one way, then the RBO uses the operation with the lowest rank. This feature has been desupported.
How missing statistics is handled in Oracle
When Oracle encounters a table with missing statistics, Oracle dynamically gathers the necessary statistics needed by the optimizer. Based on the settings of OPTIMIZER_DYNAMIC_SAMPLING parameter which is discussed in my previous topic optimizer gathers statistics and generate execution plan for the table with missing statistics.
Though in case of remote tables and external tables oracle does not perform dynamic sampling.
Whether dynamic sampling enabled or disabled the optimizer uses default values for its statistics.
The values are listed below.
A)Default Table Values When Statistics Are Missing
-----------------------------------------------------------------
1)Cardinality=num_of_blocks * (block_size - cache_layer) / avg_row_len
2)Average row length=100 bytes
3)Number of blocks=100 or actual value based on the extent map
4)Remote cardinality=2000 rows
5)Remote average row length=100 bytes
B)Default Index Values When Statistics Are Missing
------------------------------------------------------------------
1)Levels=1
2)Leaf blocks=25
3)Leaf blocks/key=1
4)Data blocks/key=1
5)Distinct keys=100
6)Clustering factor=800
Though in case of remote tables and external tables oracle does not perform dynamic sampling.
Whether dynamic sampling enabled or disabled the optimizer uses default values for its statistics.
The values are listed below.
A)Default Table Values When Statistics Are Missing
-----------------------------------------------------------------
1)Cardinality=num_of_blocks * (block_size - cache_layer) / avg_row_len
2)Average row length=100 bytes
3)Number of blocks=100 or actual value based on the extent map
4)Remote cardinality=2000 rows
5)Remote average row length=100 bytes
B)Default Index Values When Statistics Are Missing
------------------------------------------------------------------
1)Levels=1
2)Leaf blocks=25
3)Leaf blocks/key=1
4)Data blocks/key=1
5)Distinct keys=100
6)Clustering factor=800
Dynamic Sampling Levels
Dynamic sampling is enabled whenever OPTIMIZER_DYNAMIC_SAMPLING is set to a rather than 0. (i.e between 1 and 10).
To know the current settings just issue,
SQL> show parameter OPTIMIZER_DYNAMIC_SAMPLING
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
Here level is 2 that means dynamic sampling is enabled.
Though level can be specified between 0 to 10 and different level indicates different meaning. In the following section it is specified what they mean.
•Level 0: Do not use dynamic sampling.
•Level 1: Sample all tables that have not been analyzed. In this case the number of blocks sampled is the default number of dynamic sampling blocks (32).
•Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
•Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
•Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
•Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
•Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
To know the current settings just issue,
SQL> show parameter OPTIMIZER_DYNAMIC_SAMPLING
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
Here level is 2 that means dynamic sampling is enabled.
Though level can be specified between 0 to 10 and different level indicates different meaning. In the following section it is specified what they mean.
•Level 0: Do not use dynamic sampling.
•Level 1: Sample all tables that have not been analyzed. In this case the number of blocks sampled is the default number of dynamic sampling blocks (32).
•Level 2: Apply dynamic sampling to all unanalyzed tables. The number of blocks sampled is two times the default number of dynamic sampling blocks.
•Level 3: Apply dynamic sampling to all tables that meet Level 2 criteria, plus all tables for which standard selectivity estimation used a guess for some predicate that is a potential dynamic sampling predicate. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
•Level 4: Apply dynamic sampling to all tables that meet Level 3 criteria, plus all tables that have single-table predicates that reference 2 or more columns. The number of blocks sampled is the default number of dynamic sampling blocks. For unanalyzed tables, the number of blocks sampled is two times the default number of dynamic sampling blocks.
•Levels 5, 6, 7, 8, and 9: Apply dynamic sampling to all tables that meet the previous level criteria using 2, 4, 8, 32, or 128 times the default number of dynamic sampling blocks respectively.
•Level 10: Apply dynamic sampling to all tables that meet the Level 9 criteria using all blocks in the table.
Use of Dynamic Sampling while estimating Statistics
The purpose of dynamic sampling is to improve query performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes. These more accurate estimates allow the optimizer to produce better performing plans.
You can use dynamic sampling to:
•Estimate single-table predicate selectivities when collected statistics cannot be used or are likely to lead to significant errors in estimation.
•Estimate statistics for tables and relevant indexes without statistics.
•Estimate statistics for tables and relevant indexes whose statistics are too out of date to trust.
The dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter.
The default value of OPTIMIZER_DYNAMIC_SAMPLING is depend on settings of OPTIMIZER_FEATURES_ENABLE.
•If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2
•If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1
•If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0 that means dynamic sampling feature is disabled.
How dynamic Sampling Works
--------------------------------
The primary performance attribute is compile time. Oracle determines at compile time whether a query would benefit from dynamic sampling. If so, a recursive SQL statement is issued to scan a small random sample of the table's blocks, and to apply the relevant single table predicates to estimate predicate selectivities. The sample cardinality can also be used, in some cases, to estimate table cardinality. Any relevant column and index statistics are also collected.
Depending on the value of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter, a certain number of blocks are read by the dynamic sampling query.
When to Use Dynamic Sampling
------------------------------------------------
For a query that normally completes quickly (in less than a few seconds), you will not want to incur the cost of dynamic sampling. However, dynamic sampling can be beneficial under any of the following conditions:
•A better plan can be found using dynamic sampling.
•The sampling time is a small fraction of total execution time for the query.
•The query will be executed many times.
Dynamic sampling can be applied to a subset of a single table's predicates and combined with standard selectivity estimates of predicates for which dynamic sampling is not done.
You can use dynamic sampling to:
•Estimate single-table predicate selectivities when collected statistics cannot be used or are likely to lead to significant errors in estimation.
•Estimate statistics for tables and relevant indexes without statistics.
•Estimate statistics for tables and relevant indexes whose statistics are too out of date to trust.
The dynamic sampling feature is controlled by the OPTIMIZER_DYNAMIC_SAMPLING parameter.
The default value of OPTIMIZER_DYNAMIC_SAMPLING is depend on settings of OPTIMIZER_FEATURES_ENABLE.
•If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2
•If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1
•If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0 that means dynamic sampling feature is disabled.
How dynamic Sampling Works
--------------------------------
The primary performance attribute is compile time. Oracle determines at compile time whether a query would benefit from dynamic sampling. If so, a recursive SQL statement is issued to scan a small random sample of the table's blocks, and to apply the relevant single table predicates to estimate predicate selectivities. The sample cardinality can also be used, in some cases, to estimate table cardinality. Any relevant column and index statistics are also collected.
Depending on the value of the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter, a certain number of blocks are read by the dynamic sampling query.
When to Use Dynamic Sampling
------------------------------------------------
For a query that normally completes quickly (in less than a few seconds), you will not want to incur the cost of dynamic sampling. However, dynamic sampling can be beneficial under any of the following conditions:
•A better plan can be found using dynamic sampling.
•The sampling time is a small fraction of total execution time for the query.
•The query will be executed many times.
Dynamic sampling can be applied to a subset of a single table's predicates and combined with standard selectivity estimates of predicates for which dynamic sampling is not done.
Thursday, June 12, 2008
Automatic Statistics Gathering
•Before 10g you enabled DBMS_STATS to automatically gather statistics for a table by specifying the MONITORING keyword in the CREATE (or ALTER) TABLE statement. It is good to remember that starting with Oracle Database 10g, the MONITORING and NOMONITORING keywords have been deprecated and statistics are collected automatically. If you do specify these keywords, they are ignored.
•The job GATHER_STATS_JOB automatically gather optimizer statistics.
•This job gathers statistics on all objects in the database which have either
-Missing statistics or
-Stale statistics
•This job is created automatically at database creation time and is managed by the Scheduler. By default GATHER_STATS_JOB runs every night from 10 P.M. to 6 A.M. and all day on weekends if missing statistics or stale statistics found.
•In fact the Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.
•The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.
•Database automatically collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).
•If the parameter STATISTICS_LEVEL is set to TYPICAL or ALL then database automatically gather statistics for the objects which has stale statistics. If it is set to BASIC then then the automatic statistics gathering job is not able to detect stale statistics.
•To know about job GATHER_JOB_STATS issue the following query,
SQL>select JOB_TYPE,SCHEDULE_TYPE,START_DATE,REPEAT_INTERVAL,END_DATE,ENABLED,STATE,RUN_COUNT FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
JOB_TYPE SCHEDULE_TYP START_DATE REPEA END_DATE ENABL STATE RUN_COUNT
---------------- ------------ ---------- ----- ---------- ----- --------- ----------
WINDOW_GROUP TRUE SCHEDULED 31
To know the database creation date issue,
SQL> select created, sysdate from v$database;
CREATED SYSDATE
--------- ---------
06-MAY-08 12-JUN-08
So between May 06 and Jun 08 this job ran 31 times.
•In order to determine whether or not a given database object needs new database statistics, Oracle provides a table monitoring facility. If STATISTICS_LEVEL is set to TYPICAL or ALL then monitoring is enabled. Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table, as well as whether the table has been truncated, since the last time statistics were gathered. The information about changes of tables can be viewed in the USER_TAB_MODIFICATIONS view.
Like you can query,
select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS;
•Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.
Example:
---------------
SQL> insert into test values('hi');
1 row created.
SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name='TEST';
no rows selected
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name='TEST';
TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
TEST 1 0 0
After analyze they will disappear,
SQL> analyze table test estimate statistics;
Table analyzed.
SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name='TEST';
no rows selected
•If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.
•The job GATHER_STATS_JOB automatically gather optimizer statistics.
•This job gathers statistics on all objects in the database which have either
-Missing statistics or
-Stale statistics
•This job is created automatically at database creation time and is managed by the Scheduler. By default GATHER_STATS_JOB runs every night from 10 P.M. to 6 A.M. and all day on weekends if missing statistics or stale statistics found.
•In fact the Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.
•The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.
•Database automatically collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).
•If the parameter STATISTICS_LEVEL is set to TYPICAL or ALL then database automatically gather statistics for the objects which has stale statistics. If it is set to BASIC then then the automatic statistics gathering job is not able to detect stale statistics.
•To know about job GATHER_JOB_STATS issue the following query,
SQL>select JOB_TYPE,SCHEDULE_TYPE,START_DATE,REPEAT_INTERVAL,END_DATE,ENABLED,STATE,RUN_COUNT FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
JOB_TYPE SCHEDULE_TYP START_DATE REPEA END_DATE ENABL STATE RUN_COUNT
---------------- ------------ ---------- ----- ---------- ----- --------- ----------
WINDOW_GROUP TRUE SCHEDULED 31
To know the database creation date issue,
SQL> select created, sysdate from v$database;
CREATED SYSDATE
--------- ---------
06-MAY-08 12-JUN-08
So between May 06 and Jun 08 this job ran 31 times.
•In order to determine whether or not a given database object needs new database statistics, Oracle provides a table monitoring facility. If STATISTICS_LEVEL is set to TYPICAL or ALL then monitoring is enabled. Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table, as well as whether the table has been truncated, since the last time statistics were gathered. The information about changes of tables can be viewed in the USER_TAB_MODIFICATIONS view.
Like you can query,
select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS;
•Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.
Example:
---------------
SQL> insert into test values('hi');
1 row created.
SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name='TEST';
no rows selected
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name='TEST';
TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
TEST 1 0 0
After analyze they will disappear,
SQL> analyze table test estimate statistics;
Table analyzed.
SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES from USER_TAB_MODIFICATIONS where table_name='TEST';
no rows selected
•If a monitored table has been modified more than 10%, then these statistics are considered stale and gathered again.
Wednesday, June 11, 2008
What is Optimizer Statistics?
•Optimizer statistics are the collection of data that describe more details about the database and the objects in the database.
•These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.
•These statistics include tables, column, system, index level statistics. The following is listed with attributes that query optimizer use.
A)Table statistics
-------------------------
Number of rows
Number of blocks
Average row length
B)Column statistics
------------------------------
Number of distinct values (NDV) in column
Number of nulls in column
Data distribution (histogram)
C)Index statistics
--------------------------------
Number of leaf blocks
Levels
Clustering factor
D)System statistics
----------------------------------
I/O performance and utilization
CPU performance and utilization
•These optimizer statitics are are stored in the data dictionary like DBA_TABLES, DBA_TAB_STATISTICS, DBA_INDEXES, DBA_IND_STATISTICS etc.
•Statistics are maintained automatically by Oracle or you can maintain the optimizer statistics manually using the DBMS_STATS package.
•These statistics are used by the query optimizer to choose the best execution plan for each SQL statement.
•These statistics include tables, column, system, index level statistics. The following is listed with attributes that query optimizer use.
A)Table statistics
-------------------------
Number of rows
Number of blocks
Average row length
B)Column statistics
------------------------------
Number of distinct values (NDV) in column
Number of nulls in column
Data distribution (histogram)
C)Index statistics
--------------------------------
Number of leaf blocks
Levels
Clustering factor
D)System statistics
----------------------------------
I/O performance and utilization
CPU performance and utilization
•These optimizer statitics are are stored in the data dictionary like DBA_TABLES, DBA_TAB_STATISTICS, DBA_INDEXES, DBA_IND_STATISTICS etc.
•Statistics are maintained automatically by Oracle or you can maintain the optimizer statistics manually using the DBMS_STATS package.
Drop User in Oracle
•Never attempt to drop the users SYS or SYSTEM. Doing so will corrupt your database.
•To drop a user you must have the DROP USER system privilege. This is
SQL> grant drop user to arju;
Grant succeeded.
You can whether user currently have DROP USER privilege or not by,
SQL> select * from session_privs where privilege='DROP USER';
PRIVILEGE
----------------------------------------
DROP USER
•To drop a user named test use DROP USER TEST unless inside test schema there is no objects.
•If test schema contains any objects then to drop the user you must use DROP USER TEST CASCADE. If you don't use CASCADE option it will throw ORA-01922: Example in case of user named A.
Create objects inside schema A.
SQL> create table a.test(a number);
Table created.
SQL> drop user a;
drop user a
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'A'
As there is objects inside schema A so use CASCADE clause to drop.
SQL> drop user a cascade;
User dropped
•After dropping a user if the user's schema contains tables, then Oracle Database drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.
•After dropping a user the objects in other schema which are views, synonyms. stored procedures, functions, or packages that query objects in the dropped user's schema become invalid but don't drop.
•Oracle Database does not drop materialized views in other schemas that are based on tables in the dropped user's schema. However, because the base tables no longer exist, the materialized views in the other schemas can no longer be refreshed.
•When you drop a user, Oracle Database also purges all of that user's schema objects from the recycle bin.
•It is very good to remember that dropping user in oracle Database does not drop roles created by the user.
•You can't drop a user that is currently connected. If you try to drop a current connected user you will get ORA-01940.
SQL> drop user arju;
drop user arju
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
If you want to drop a connected user first disconnect it. It is demonstrated in section
Troubleshot ORA-01940
Related Documents:
-----------------------
Troubleshot ORA-01940
•To drop a user you must have the DROP USER system privilege. This is
SQL> grant drop user to arju;
Grant succeeded.
You can whether user currently have DROP USER privilege or not by,
SQL> select * from session_privs where privilege='DROP USER';
PRIVILEGE
----------------------------------------
DROP USER
•To drop a user named test use DROP USER TEST unless inside test schema there is no objects.
•If test schema contains any objects then to drop the user you must use DROP USER TEST CASCADE. If you don't use CASCADE option it will throw ORA-01922: Example in case of user named A.
Create objects inside schema A.
SQL> create table a.test(a number);
Table created.
SQL> drop user a;
drop user a
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'A'
As there is objects inside schema A so use CASCADE clause to drop.
SQL> drop user a cascade;
User dropped
•After dropping a user if the user's schema contains tables, then Oracle Database drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.
•After dropping a user the objects in other schema which are views, synonyms. stored procedures, functions, or packages that query objects in the dropped user's schema become invalid but don't drop.
•Oracle Database does not drop materialized views in other schemas that are based on tables in the dropped user's schema. However, because the base tables no longer exist, the materialized views in the other schemas can no longer be refreshed.
•When you drop a user, Oracle Database also purges all of that user's schema objects from the recycle bin.
•It is very good to remember that dropping user in oracle Database does not drop roles created by the user.
•You can't drop a user that is currently connected. If you try to drop a current connected user you will get ORA-01940.
SQL> drop user arju;
drop user arju
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
If you want to drop a connected user first disconnect it. It is demonstrated in section
Troubleshot ORA-01940
Related Documents:
-----------------------
Troubleshot ORA-01940
About STATISTICS_LEVEL parameter in Oracle
•The parameter STATISTICS_LEVEL is a string type parameter and it can take any of three values- TYPICAL, ALL or BASIC.
•It specifies the level of collection for database and operating system statistics.
•The default is TYPICAL which ensures collection of all major statistics required for database self-management functionality and provides best overall performance. So if it is set to TYPICAL then we don't need to bother about collection statistics by ANALYZE or by DBMS_STATS package.
•When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.
•If the STATISTICS_LEVEL parameter is set to BASIC it disables the collection of many of the important statistics required by Oracle Database features and functionality.
•STATISTICS_LEVEL parameter can be modified by both session level(using ALTER SESSION) or system level (using ALTER SYSTEM).
•To know the current settings of the parameter issue,
SQL> select value from v$parameter where name='statistics_level';
VALUE
--------------------------------------------------------------------------------
TYPICAL
Or,
•To know the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter issue the following query,
As we see as the current settings is TYPICAL so 'Timed OS Statistics' and 'Plan Execution Statistics' are disabled because they enable when STATISTICS_LEVEL is set to ALL. In the query we can also see by SESSION_SETTABLE parameter indicating whether the statistic/advisory can be set at the session level (YES) or not (NO).
•It specifies the level of collection for database and operating system statistics.
•The default is TYPICAL which ensures collection of all major statistics required for database self-management functionality and provides best overall performance. So if it is set to TYPICAL then we don't need to bother about collection statistics by ANALYZE or by DBMS_STATS package.
•When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.
•If the STATISTICS_LEVEL parameter is set to BASIC it disables the collection of many of the important statistics required by Oracle Database features and functionality.
•STATISTICS_LEVEL parameter can be modified by both session level(using ALTER SESSION) or system level (using ALTER SYSTEM).
•To know the current settings of the parameter issue,
SQL> select value from v$parameter where name='statistics_level';
VALUE
--------------------------------------------------------------------------------
TYPICAL
Or,
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
•To know the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter issue the following query,
SQL> COL STATISTICS_NAME FORMAT A50
SQL> SELECT STATISTICS_NAME,SESSION_STATUS,SYSTEM_STATUS,ACTIVATION_LEVEL,
SESSION_SETTABLE FROM V$STATISTICS_LEVEL;
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Buffer Cache Advice ENABLED ENABLED TYPICAL NO
MTTR Advice ENABLED ENABLED TYPICAL NO
Timed Statistics ENABLED ENABLED TYPICAL YES
Timed OS Statistics DISABLED DISABLED ALL YES
Segment Level Statistics ENABLED ENABLED TYPICAL NO
PGA Advice ENABLED ENABLED TYPICAL NO
Plan Execution Statistics DISABLED DISABLED ALL YES
Shared Pool Advice ENABLED ENABLED TYPICAL NO
Modification Monitoring ENABLED ENABLED TYPICAL NO
Longops Statistics ENABLED ENABLED TYPICAL NO
Bind Data Capture ENABLED ENABLED TYPICAL NO
STATISTICS_NAME SESSION_ SYSTEM_S ACTIVAT SES
-------------------------------------------------- -------- -------- ------- ---
Ultrafast Latch Statistics ENABLED ENABLED TYPICAL NO
Threshold-based Alerts ENABLED ENABLED TYPICAL NO
Global Cache Statistics ENABLED ENABLED TYPICAL NO
Active Session History ENABLED ENABLED TYPICAL NO
Undo Advisor, Alerts and Fast Ramp up ENABLED ENABLED TYPICAL NO
16 rows selected.
As we see as the current settings is TYPICAL so 'Timed OS Statistics' and 'Plan Execution Statistics' are disabled because they enable when STATISTICS_LEVEL is set to ALL. In the query we can also see by SESSION_SETTABLE parameter indicating whether the statistic/advisory can be set at the session level (YES) or not (NO).
Analyze and collecting Statistics on Tables Schemas Database Index
Automatic statistics gathering should be sufficient for most database objects which are being modified at a moderate speed. However, there are cases where automatic statistics gathering may not be adequate. Because the automatic statistics gathering runs during an overnight batch window, the statistics on tables which are significantly modified during the day may become stale.
There may be two scenarios in this case.
•Volatile tables that are being deleted or truncated and rebuilt during the course of the day.
•Objects which are the target of large bulk loads which add 10% or more to the object's total size.
So you may wish to manually gather statistics of those objects in order to choose the optimizer the best execution plan. There are two ways to gather statistics.
1)Using DBMS_STATS package.
2)Using ANALYZE command.
A)Using DBMS_STATS package
--------------------------------------------
The DBMS_STATS package have several procedures which help to generate statistics.
1)GATHER_DATABASE_STATS Procedures-Gathers statistics for all objects in the database
2)GATHER_DICTIONARY_STATS Procedure-Gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS components.
3)GATHER_FIXED_OBJECTS_STATS Procedure-Gathers statistics of fixed objects.
4)GATHER_INDEX_STATS Procedure-Gathers index statistics.
5)GATHER_SCHEMA_STATS Procedures-Gathers statistics for all objects in a schema.
6)GATHER_SYSTEM_STATS Procedure-Gathers system statistics.
7)GATHER_TABLE_STATS Procedure-Gathers table and column (and index) statistics.
8)GENERATE_STATS Procedure-Generates object statistics from previously collected statistics of related objects. The currently supported objects are only b-tree and bitmap indexes.
Example:
To gather statistics of all objects inside A schema use
SQL>EXEC DBMS_STATS.GATHER_SCHEMA_STATS('A');
PL/SQL procedure successfully completed.
To gather statictics of table test in ARJU schema use,
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('ARJU','TEST');
PL/SQL procedure successfully completed.
2)Using Analyze:
------------------------
Oracle strongly recommend not use ANALYZE command to estimate statistics. Yet it is supported for backward compatibility. To generate statistics of TEST table using ANALYZE use ANALYZE with estimate statistics keyword.,
In this example I verified that statistics of table column num_rows contain information after analyzing.
SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='TEST';
NUM_ROWS
----------
2
SQL> INSERT INTO TEST VALUES('before');
1 row created.
SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='TEST';
NUM_ROWS
----------
2
SQL> ANALYZE TABLE TEST COMPUTE STATISTICS;
Table analyzed.
SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='TEST';
NUM_ROWS
----------
3
After gather statistics num_rows contain accurate information.
There may be two scenarios in this case.
•Volatile tables that are being deleted or truncated and rebuilt during the course of the day.
•Objects which are the target of large bulk loads which add 10% or more to the object's total size.
So you may wish to manually gather statistics of those objects in order to choose the optimizer the best execution plan. There are two ways to gather statistics.
1)Using DBMS_STATS package.
2)Using ANALYZE command.
A)Using DBMS_STATS package
--------------------------------------------
The DBMS_STATS package have several procedures which help to generate statistics.
1)GATHER_DATABASE_STATS Procedures-Gathers statistics for all objects in the database
2)GATHER_DICTIONARY_STATS Procedure-Gathers statistics for dictionary schemas 'SYS', 'SYSTEM' and schemas of RDBMS components.
3)GATHER_FIXED_OBJECTS_STATS Procedure-Gathers statistics of fixed objects.
4)GATHER_INDEX_STATS Procedure-Gathers index statistics.
5)GATHER_SCHEMA_STATS Procedures-Gathers statistics for all objects in a schema.
6)GATHER_SYSTEM_STATS Procedure-Gathers system statistics.
7)GATHER_TABLE_STATS Procedure-Gathers table and column (and index) statistics.
8)GENERATE_STATS Procedure-Generates object statistics from previously collected statistics of related objects. The currently supported objects are only b-tree and bitmap indexes.
Example:
To gather statistics of all objects inside A schema use
SQL>EXEC DBMS_STATS.GATHER_SCHEMA_STATS('A');
PL/SQL procedure successfully completed.
To gather statictics of table test in ARJU schema use,
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('ARJU','TEST');
PL/SQL procedure successfully completed.
2)Using Analyze:
------------------------
Oracle strongly recommend not use ANALYZE command to estimate statistics. Yet it is supported for backward compatibility. To generate statistics of TEST table using ANALYZE use ANALYZE with estimate statistics keyword.,
In this example I verified that statistics of table column num_rows contain information after analyzing.
SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='TEST';
NUM_ROWS
----------
2
SQL> INSERT INTO TEST VALUES('before');
1 row created.
SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='TEST';
NUM_ROWS
----------
2
SQL> ANALYZE TABLE TEST COMPUTE STATISTICS;
Table analyzed.
SQL> SELECT NUM_ROWS FROM USER_TABLES WHERE TABLE_NAME='TEST';
NUM_ROWS
----------
3
After gather statistics num_rows contain accurate information.
Drop Table in Oracle
If you think you don't need a table then you can drop it with the DROP TABLE table_name clause. It is easy thing to drop but before drop you should know around the consequence of dropping a table.
If you drop a table then before that think about following consequences.
•After dropping table you no longer access the data in it.
•All views and PL/SQL program units dependent on a dropped table remain in tact but they become unusable or invalid.
•All indexes and triggers associated with a table are dropped.
•All synonyms for a dropped table remain, but return an error when used.
•All extents allocated for a table that is dropped are returned to the free space of the tablespace and can be used by any other object requiring new extents or new objects.
Example of This Scenario:
----------------------------------------
1)Look at free space of tablespace user_tbs.
SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98435072
2)Now create a table in that tablespace.
SQL> create table test2 tablespace user_tbs as select level a1, level a2 , level a3 from dual connect by level<=10000;
Table created.
3)Let's check the free space of the tablespace now.
SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98172928
4)Let's drop the table and check again free space.
SQL> drop table test2;
Table dropped.
SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98435072
A)To drop a table simply use DROP TABLE .. keyword. To drop test table use,
DROP TABLE TEST;
B)If the table to be dropped contains any primary or unique keys referenced by foreign keys of other tables and you intend to drop the FOREIGN KEY constraints of the child tables, then include the CASCADE clause in the DROP TABLE statement, as shown below:
Example of This Scenario:
-----------------------------
1)Create both parent and child table.
SQL> create table parent(a number primary key);
Table created.
SQL> create table child ( b number references parent);
Table created.
2)Check the Constraints and their type.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='CHILD';
CONSTRAINT_NAME C
------------------------------ -
SYS_C006345 R
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='PARENT';
CONSTRAINT_NAME C
------------------------------ -
SYS_C006344 P
3)Now drop parent table produce oracle error.
Now try to drop parent table.
SQL> drop table parent;
drop table parent
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
It raises ORA-02449 because the primary keys in table referenced by foreign keys. So to drop the table you have to drop foreign key constraints of the child table. This is done by
4)To drop parent table you have to include cascade constraints which will drop foreign key constraints.
SQL> drop table parent cascade constraints;
Table dropped.
5)Check the child table and see no constraints there.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='CHILD';
no rows selected
If you check the metadata of child table before dropping the parent table and after dropping parent table you will see table definition is changed.
Before drop I got ,
SQL> select dbms_metadata.get_ddl('TABLE','CHILD') from dual;
CREATE TABLE "ARJU"."CHILD"
( "B" NUMBER,
FOREIGN KEY ("B")
REFERENCES "ARJU"."PARENT" ("A") ENABLE
)
After drop parent table I get,
CREATE TABLE "ARJU"."CHILD"
( "B" NUMBER
)
C)When you drop a table, normally the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin. If you should want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, include the PURGE clause as shown in the following statement:
SQL>DROP TABLE child PURGE;
Table dropped.
To drop object from recylcebin use PURGE statement.
SQL> purge table parent;
Table purged.
Related Documents
http://arjudba.blogspot.com/2008/09/how-to-disable-and-enable-all.html
http://arjudba.blogspot.com/2008/05/create-user-in-oracle.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
Create, Alter , Rename, Modify Table SQL
http://arjudba.blogspot.com/2008/06/drop-table-in-oracle.html
If you drop a table then before that think about following consequences.
•After dropping table you no longer access the data in it.
•All views and PL/SQL program units dependent on a dropped table remain in tact but they become unusable or invalid.
•All indexes and triggers associated with a table are dropped.
•All synonyms for a dropped table remain, but return an error when used.
•All extents allocated for a table that is dropped are returned to the free space of the tablespace and can be used by any other object requiring new extents or new objects.
Example of This Scenario:
----------------------------------------
1)Look at free space of tablespace user_tbs.
SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98435072
2)Now create a table in that tablespace.
SQL> create table test2 tablespace user_tbs as select level a1, level a2 , level a3 from dual connect by level<=10000;
Table created.
3)Let's check the free space of the tablespace now.
SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98172928
4)Let's drop the table and check again free space.
SQL> drop table test2;
Table dropped.
SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98435072
A)To drop a table simply use DROP TABLE .. keyword. To drop test table use,
DROP TABLE TEST;
B)If the table to be dropped contains any primary or unique keys referenced by foreign keys of other tables and you intend to drop the FOREIGN KEY constraints of the child tables, then include the CASCADE clause in the DROP TABLE statement, as shown below:
Example of This Scenario:
-----------------------------
1)Create both parent and child table.
SQL> create table parent(a number primary key);
Table created.
SQL> create table child ( b number references parent);
Table created.
2)Check the Constraints and their type.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='CHILD';
CONSTRAINT_NAME C
------------------------------ -
SYS_C006345 R
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='PARENT';
CONSTRAINT_NAME C
------------------------------ -
SYS_C006344 P
3)Now drop parent table produce oracle error.
Now try to drop parent table.
SQL> drop table parent;
drop table parent
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
It raises ORA-02449 because the primary keys in table referenced by foreign keys. So to drop the table you have to drop foreign key constraints of the child table. This is done by
4)To drop parent table you have to include cascade constraints which will drop foreign key constraints.
SQL> drop table parent cascade constraints;
Table dropped.
5)Check the child table and see no constraints there.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='CHILD';
no rows selected
If you check the metadata of child table before dropping the parent table and after dropping parent table you will see table definition is changed.
Before drop I got ,
SQL> select dbms_metadata.get_ddl('TABLE','CHILD') from dual;
CREATE TABLE "ARJU"."CHILD"
( "B" NUMBER,
FOREIGN KEY ("B")
REFERENCES "ARJU"."PARENT" ("A") ENABLE
)
After drop parent table I get,
CREATE TABLE "ARJU"."CHILD"
( "B" NUMBER
)
C)When you drop a table, normally the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin. If you should want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, include the PURGE clause as shown in the following statement:
SQL>DROP TABLE child PURGE;
Table dropped.
To drop object from recylcebin use PURGE statement.
SQL> purge table parent;
Table purged.
Related Documents
http://arjudba.blogspot.com/2008/09/how-to-disable-and-enable-all.html
http://arjudba.blogspot.com/2008/05/create-user-in-oracle.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
Create, Alter , Rename, Modify Table SQL
http://arjudba.blogspot.com/2008/06/drop-table-in-oracle.html
Moving a Table to a New Segment or Tablespace
•The ALTER TABLE...MOVE statement enables you to relocate data of a non-partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace.
•This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE.
•It is good to remember that The ALTER TABLE...MOVE statement does not permit DML against the table while the statement is executing. If you want to leave the table available for DML while moving it it the you have to use DBMS_REDEFINITION package to move online.
•Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.
•You cannot move a table containing a LONG or LONG RAW column.
•You cannot MOVE an entire partitioned table. You must move individual partitions or subpartitions.
Example:
---------------
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USER_TBS 11 1283
Here HEADER_BLOCK indicates the ID of the block containing the segment header. And HEADER_FILE is the data file id. If I move the table to a new segment then header block number will be change. Lets have a look at it.
SQL> alter table test move;
Table altered.
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USER_TBS 11 1459
So HEADER_BLOCK change to 1459 from 1283.
Now I want to move the table test from USER_TBS tablespace to tablespace TBS_AFTER_BACKUP. To see so we have to append TABLESPACE keyword like,
SQL> alter table test move tablespace TBS_AFTER_BACKUP;
Table altered.
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
TBS_AFTER_BACKUP 6 11
•This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE.
•It is good to remember that The ALTER TABLE...MOVE statement does not permit DML against the table while the statement is executing. If you want to leave the table available for DML while moving it it the you have to use DBMS_REDEFINITION package to move online.
•Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.
•You cannot move a table containing a LONG or LONG RAW column.
•You cannot MOVE an entire partitioned table. You must move individual partitions or subpartitions.
Example:
---------------
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USER_TBS 11 1283
Here HEADER_BLOCK indicates the ID of the block containing the segment header. And HEADER_FILE is the data file id. If I move the table to a new segment then header block number will be change. Lets have a look at it.
SQL> alter table test move;
Table altered.
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USER_TBS 11 1459
So HEADER_BLOCK change to 1459 from 1283.
Now I want to move the table test from USER_TBS tablespace to tablespace TBS_AFTER_BACKUP. To see so we have to append TABLESPACE keyword like,
SQL> alter table test move tablespace TBS_AFTER_BACKUP;
Table altered.
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
TBS_AFTER_BACKUP 6 11
Tuesday, June 10, 2008
Find indexes and assigned columns for a table
It is very common that you want to know / see the available index on a table. And also you sometime want to know the columns which are assigned to the indexes.
The view dba_ind_columns or user_ind_columns or all_ind_columns help lot in this regard. As you may know dba_* show all available index information, user_* is for the available in current schema and all_* is for all indexes that current user has permission to it.
The following script will help to identify the owner, table_name associated index and associated column.
SQL>COL index_owner FORMAT A20
column table_owner format a30
column table_name format A28
column index_name format A28
column column_name format A28
COL POS FORMAT 999
SQL> Select index_owner, table_name, index_name, column_name, column_position POS
FROM dba_ind_columns
Where index_owner='&owner_name'
AND table_name='&table_name' Order by index_NAME;
In order to know the avilable index on a table issue,
SQL> SELECT UNIQUE INDEX_NAME FROM dba_ind_columns WHERE TABLE_NAME='&TABLE_NAME';
Example:
------------
SQL> create table test_index_col( a number primary key, b number);
Table created.
SQL> create index test_I on test_index_col(b);
Index created.
SQL> Select index_owner, table_name, index_name, column_name, column_position POS
FROM dba_ind_columns
Where index_owner='&owner_name'
AND table_name='&table_name' Order by index_NAME; 2 3 4
Enter value for owner_name: ARJU
old 3: Where index_owner='&owner_name'
new 3: Where index_owner='ARJU'
Enter value for table_name: TEST_INDEX_COL
old 4: AND table_name='&table_name' Order by index_NAME
new 4: AND table_name='TEST_INDEX_COL' Order by index_NAME
INDEX_OWNE TABLE_NAME INDEX_NAME COLUMN_NAME POS
---------- ------------------ ------------------ ------------------ ----
ARJU TEST_INDEX_COL SYS_C006341 A 1
ARJU TEST_INDEX_COL TEST_I B 1
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
The view dba_ind_columns or user_ind_columns or all_ind_columns help lot in this regard. As you may know dba_* show all available index information, user_* is for the available in current schema and all_* is for all indexes that current user has permission to it.
The following script will help to identify the owner, table_name associated index and associated column.
SQL>COL index_owner FORMAT A20
column table_owner format a30
column table_name format A28
column index_name format A28
column column_name format A28
COL POS FORMAT 999
SQL> Select index_owner, table_name, index_name, column_name, column_position POS
FROM dba_ind_columns
Where index_owner='&owner_name'
AND table_name='&table_name' Order by index_NAME;
In order to know the avilable index on a table issue,
SQL> SELECT UNIQUE INDEX_NAME FROM dba_ind_columns WHERE TABLE_NAME='&TABLE_NAME';
Example:
------------
SQL> create table test_index_col( a number primary key, b number);
Table created.
SQL> create index test_I on test_index_col(b);
Index created.
SQL> Select index_owner, table_name, index_name, column_name, column_position POS
FROM dba_ind_columns
Where index_owner='&owner_name'
AND table_name='&table_name' Order by index_NAME; 2 3 4
Enter value for owner_name: ARJU
old 3: Where index_owner='&owner_name'
new 3: Where index_owner='ARJU'
Enter value for table_name: TEST_INDEX_COL
old 4: AND table_name='&table_name' Order by index_NAME
new 4: AND table_name='TEST_INDEX_COL' Order by index_NAME
INDEX_OWNE TABLE_NAME INDEX_NAME COLUMN_NAME POS
---------- ------------------ ------------------ ------------------ ----
ARJU TEST_INDEX_COL SYS_C006341 A 1
ARJU TEST_INDEX_COL TEST_I B 1
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
Comments in Oracle
You can comments to any SQL statements or any schema objects.
A)Comments Within SQL Statements
----------------------------------------
Comments within SQL statements do not affect the statement execution. The only exception is use of hints. With use hints the SQL statements is affected. In fact comment is used to make your application easier for you to read and maintain.
Within SQL statements you can include comment in two ways.
1)With /* and */
--------------------------
Format is,
Begin with slash (/) and an asterisk (*) together (/*) + comment Text + End with an asetrisk (*) and a slash (/) together (*/).
With this format text can span multiple lines.
The opening and terminating characters need not be separated from the text by a space or a line break.
Example:
-----------
SQL> select second_col /*This is column
2 Name */ from test /*This is Table Name*/ ;
2)with --
-----------------
The text that begin with two hyphens (--) is considered as comments. The comments written in this way can't span multiple lines. End the comment with a line break.
Example:
------------
SQL> select second_col -- THIS IS COMMENT
2 FROM TEST;
B)Comments on Schema Objects
---------------------------------
You can associate a comment with a table, view, materialized view, or column using the COMMENT command. Comments associated with schema objects are stored in the data dictionary.
You can view the comments on a particular table or column by querying the data dictionary views USER_TAB_COMMENTS, DBA_TAB_COMMENTS, or ALL_TAB_COMMENTS or USER_COL_COMMENTS, DBA_COL_COMMENTS, or ALL_COL_COMMENTS.
You can view the comments on a particular operator by querying the data dictionary views USER_OPERATOR_COMMENTS, DBA_OPERATOR_COMMENTS, or ALL_OPERATOR_COMMENTS.
You can view the comments on a particular indextype by querying the data dictionary views USER_INDEXTYPE_COMMENTS, DBA_INDEXTYPE_COMMENTS, or ALL_INDEXTYPE_COMMENTS.
You can view the comments on a particular materialized view by querying the data dictionary views USER_MVIEW_COMMENTS, DBA_MVIEW_COMMENTS, or ALL_MVIEW_COMMENTS.
To drop a comment from the database, set it to the empty string ' '.
Example:
--------------
I have comment on table test and on its columns by,
SQL> COMMENT ON TABLE ARJU.TEST IS 'This is test Table';
Comment created.
SQL> COMMENT ON COLUMN ARJU.TEST.SECOND_COL IS 'This is the only one column';
Comment created.
SQL> column COMMENTS format a30
SQL> col owner format a10
SQL> col table_name format a10
SQL> select OWNER,TABLE_NAME,COMMENTS from dba_tab_comments where table_name='TEST' and owner='ARJU';
OWNER TABLE_NAME COMMENTS
---------- ---------- ------------------------------
ARJU TEST This is test Table
SQL> select OWNER,TABLE_NAME,COMMENTS from dba_col_comments where table_name='TEST' and owner='ARJU';
OWNER TABLE_NAME COMMENTS
---------- ---------- ------------------------------
ARJU TEST This is the only one column
To drop comment,
SQL> COMMENT ON COLUMN ARJU.TEST.SECOND_COL IS '';
Comment created.
SQL> select OWNER,TABLE_NAME,COMMENTS from dba_col_comments where table_name='TEST' and owner='ARJU';
OWNER TABLE_NAME COMMENTS
---------- ---------- ------------------------------
ARJU TEST
A)Comments Within SQL Statements
----------------------------------------
Comments within SQL statements do not affect the statement execution. The only exception is use of hints. With use hints the SQL statements is affected. In fact comment is used to make your application easier for you to read and maintain.
Within SQL statements you can include comment in two ways.
1)With /* and */
--------------------------
Format is,
Begin with slash (/) and an asterisk (*) together (/*) + comment Text + End with an asetrisk (*) and a slash (/) together (*/).
With this format text can span multiple lines.
The opening and terminating characters need not be separated from the text by a space or a line break.
Example:
-----------
SQL> select second_col /*This is column
2 Name */ from test /*This is Table Name*/ ;
2)with --
-----------------
The text that begin with two hyphens (--) is considered as comments. The comments written in this way can't span multiple lines. End the comment with a line break.
Example:
------------
SQL> select second_col -- THIS IS COMMENT
2 FROM TEST;
B)Comments on Schema Objects
---------------------------------
You can associate a comment with a table, view, materialized view, or column using the COMMENT command. Comments associated with schema objects are stored in the data dictionary.
You can view the comments on a particular table or column by querying the data dictionary views USER_TAB_COMMENTS, DBA_TAB_COMMENTS, or ALL_TAB_COMMENTS or USER_COL_COMMENTS, DBA_COL_COMMENTS, or ALL_COL_COMMENTS.
You can view the comments on a particular operator by querying the data dictionary views USER_OPERATOR_COMMENTS, DBA_OPERATOR_COMMENTS, or ALL_OPERATOR_COMMENTS.
You can view the comments on a particular indextype by querying the data dictionary views USER_INDEXTYPE_COMMENTS, DBA_INDEXTYPE_COMMENTS, or ALL_INDEXTYPE_COMMENTS.
You can view the comments on a particular materialized view by querying the data dictionary views USER_MVIEW_COMMENTS, DBA_MVIEW_COMMENTS, or ALL_MVIEW_COMMENTS.
To drop a comment from the database, set it to the empty string ' '.
Example:
--------------
I have comment on table test and on its columns by,
SQL> COMMENT ON TABLE ARJU.TEST IS 'This is test Table';
Comment created.
SQL> COMMENT ON COLUMN ARJU.TEST.SECOND_COL IS 'This is the only one column';
Comment created.
SQL> column COMMENTS format a30
SQL> col owner format a10
SQL> col table_name format a10
SQL> select OWNER,TABLE_NAME,COMMENTS from dba_tab_comments where table_name='TEST' and owner='ARJU';
OWNER TABLE_NAME COMMENTS
---------- ---------- ------------------------------
ARJU TEST This is test Table
SQL> select OWNER,TABLE_NAME,COMMENTS from dba_col_comments where table_name='TEST' and owner='ARJU';
OWNER TABLE_NAME COMMENTS
---------- ---------- ------------------------------
ARJU TEST This is the only one column
To drop comment,
SQL> COMMENT ON COLUMN ARJU.TEST.SECOND_COL IS '';
Comment created.
SQL> select OWNER,TABLE_NAME,COMMENTS from dba_col_comments where table_name='TEST' and owner='ARJU';
OWNER TABLE_NAME COMMENTS
---------- ---------- ------------------------------
ARJU TEST
Reasons for Using the ALTER TABLE Statement
Before Alter Table operation you should keep in ming that if a view, materialized view, trigger, domain index, function-based index, check constraint, function, procedure of package depends on a base table, the alteration of the base table or its columns can affect the dependent object.
Now in which case you need ALTER TABLE operation? The cases are listed below.
•Add, drop, or rename columns, or modify an existing column definition (datatype, length, default value, NOT NULL integrity constraint, and encryption.).
•Move the table to a new segment or tablespace.
•Modify the logging attributes of the table. Either NOLOGGING or LOGGING to generate or avoid redo.
•Modify physical characteristics (INITRANS or storage parameters).
•Explicitly allocate an extent or deallocate unused space
•Modify the CACHE/NOCACHE attributes. It specifies how Oracle Database should store blocks in the buffer cache. NOCACHE is the default.
•Add, modify or drop integrity constraints associated with the table.
•Modify the degree of parallelism for the table. It is specified by PARALLEL keyword.
•To rename a table.
•Add or modify index-organized table characteristics.
•Alter the characteristics of an external table.
•Add or modify LOB columns.
•Add or modify object type, nested table, or varray columns
Related Documents:
-----------------------
Some Alter Table Operations
Now in which case you need ALTER TABLE operation? The cases are listed below.
•Add, drop, or rename columns, or modify an existing column definition (datatype, length, default value, NOT NULL integrity constraint, and encryption.).
•Move the table to a new segment or tablespace.
•Modify the logging attributes of the table. Either NOLOGGING or LOGGING to generate or avoid redo.
•Modify physical characteristics (INITRANS or storage parameters).
•Explicitly allocate an extent or deallocate unused space
•Modify the CACHE/NOCACHE attributes. It specifies how Oracle Database should store blocks in the buffer cache. NOCACHE is the default.
•Add, modify or drop integrity constraints associated with the table.
•Modify the degree of parallelism for the table. It is specified by PARALLEL keyword.
•To rename a table.
•Add or modify index-organized table characteristics.
•Alter the characteristics of an external table.
•Add or modify LOB columns.
•Add or modify object type, nested table, or varray columns
Related Documents:
-----------------------
Some Alter Table Operations
Alter Table - Rename Table Add Column Modify Column Drop Column
With the ALTER TABLE statement you can rename table, rename table column, add columns to the column, modify existing column, drop column from a table or move the table. With an example the all scenarios are demonstrated.
I work with following table,
SQL> create table test (a number);
Table created.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
A)Rename a Table
----------------------
The syntax is,
ALTER TABLE [table name] RENAME TO [new table name];
To rename test to test2 use,
SQL> ALTER TABLE TEST RENAME TO TEST2;
Table altered.
We can also use only RENAME like,
SQL> RENAME TEST2 TO TEST;
Table renamed.
B)Rename Table Column
------------------------
Oracle Database lets you rename existing columns in a table. Use the RENAME COLUMN clause of the ALTER TABLE statement to rename a column.
If you want to rename the column A of Test table to first_col then use,
SQL> ALTER TABLE TEST RENAME COLUMN A to FIRST_COL;
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_COL NUMBER
C)Add Table Column
-----------------------------
To add a column to an existing table, use the ALTER TABLE...ADD statement. If you want to add second_col to table Test then use,
SQL> ALTER TABLE TEST ADD SECOND_COL NUMBER;
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_COL NUMBER
SECOND_COL NUMBER
D)Modify Existing Table Column
--------------------------------------
To modify a column use the ALTER TABLE...MODIFY statement. You can modify column datatype, default value, column constraint, and column encryption.
If I want to change data type to varchhar2 and column contrainst of column SECOND_COL to NOT NULL of table test then use,
SQL> ALTER TABLE TEST MODIFY SECOND_COL VARCHAR2(10) NOT NULL;
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_COL NUMBER
SECOND_COL NOT NULL VARCHAR2(10)
Remember You can increase the length of an existing column, or decrease it, if all existing data satisfies the new length.
E)Drop Table Columns
----------------------------
To drop a column from a table use ALTER TABLE...DROP COLUMN statement.
To drop the first_col of test table use,
SQL> ALTER TABLE TEST DROP COLUMN FIRST_COL;
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
SECOND_COL NOT NULL VARCHAR2(10)
Remember you cannot drop all columns from a table, nor can you drop columns from a table owned by SYS.
F)Marking Columns Unused
--------------------------------
If you see that the table is very large and dropping a table will take much more time then you can mark the column unused instead of dropping it using the ALTER TABLE...SET UNUSED statement.
This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns.
However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.
1)Add new column.
SQL> ALTER TABLE TEST ADD FIRST_COL NUMBER;
Table altered.
2)Describe the table.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
SECOND_COL NOT NULL VARCHAR2(10)
FIRST_COL NUMBER
3)Mark the first_col unused.
SQL> ALTER TABLE test SET UNUSED (first_col);
Table altered.
4)Describe the table.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
SECOND_COL NOT NULL VARCHAR2(10)
To remove unused columns use ALTER TABLE...DROP UNUSED COLUMNS statement. If I want to drop unused column then first see in which table there remain unused columns. To see it issue,
SQL> SELECT * FROM USER_UNUSED_COL_TABS;
TABLE_NAME COUNT
------------------------------ ----------
TEST 1
Now drop it by,
SQL> ALTER TABLE TEST DROP UNUSED COLUMNS;
Table altered.
Now check for unused columns by,
SQL> SELECT * FROM USER_UNUSED_COL_TABS;
no rows selected
It is very good to remember that the clause 'COLUMN' is used only for dropping a column and for rename a column. For modify a column or for adding a column 'COLUMN' clause is not present.
Related Documents
http://arjudba.blogspot.com/2008/09/how-to-disable-and-enable-all.html
http://arjudba.blogspot.com/2008/05/create-user-in-oracle.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
I work with following table,
SQL> create table test (a number);
Table created.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
A)Rename a Table
----------------------
The syntax is,
ALTER TABLE [table name] RENAME TO [new table name];
To rename test to test2 use,
SQL> ALTER TABLE TEST RENAME TO TEST2;
Table altered.
We can also use only RENAME like,
SQL> RENAME TEST2 TO TEST;
Table renamed.
B)Rename Table Column
------------------------
Oracle Database lets you rename existing columns in a table. Use the RENAME COLUMN clause of the ALTER TABLE statement to rename a column.
If you want to rename the column A of Test table to first_col then use,
SQL> ALTER TABLE TEST RENAME COLUMN A to FIRST_COL;
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_COL NUMBER
C)Add Table Column
-----------------------------
To add a column to an existing table, use the ALTER TABLE...ADD statement. If you want to add second_col to table Test then use,
SQL> ALTER TABLE TEST ADD SECOND_COL NUMBER;
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_COL NUMBER
SECOND_COL NUMBER
D)Modify Existing Table Column
--------------------------------------
To modify a column use the ALTER TABLE...MODIFY statement. You can modify column datatype, default value, column constraint, and column encryption.
If I want to change data type to varchhar2 and column contrainst of column SECOND_COL to NOT NULL of table test then use,
SQL> ALTER TABLE TEST MODIFY SECOND_COL VARCHAR2(10) NOT NULL;
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
FIRST_COL NUMBER
SECOND_COL NOT NULL VARCHAR2(10)
Remember You can increase the length of an existing column, or decrease it, if all existing data satisfies the new length.
E)Drop Table Columns
----------------------------
To drop a column from a table use ALTER TABLE...DROP COLUMN statement.
To drop the first_col of test table use,
SQL> ALTER TABLE TEST DROP COLUMN FIRST_COL;
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
SECOND_COL NOT NULL VARCHAR2(10)
Remember you cannot drop all columns from a table, nor can you drop columns from a table owned by SYS.
F)Marking Columns Unused
--------------------------------
If you see that the table is very large and dropping a table will take much more time then you can mark the column unused instead of dropping it using the ALTER TABLE...SET UNUSED statement.
This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns.
However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.
1)Add new column.
SQL> ALTER TABLE TEST ADD FIRST_COL NUMBER;
Table altered.
2)Describe the table.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
SECOND_COL NOT NULL VARCHAR2(10)
FIRST_COL NUMBER
3)Mark the first_col unused.
SQL> ALTER TABLE test SET UNUSED (first_col);
Table altered.
4)Describe the table.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
SECOND_COL NOT NULL VARCHAR2(10)
To remove unused columns use ALTER TABLE...DROP UNUSED COLUMNS statement. If I want to drop unused column then first see in which table there remain unused columns. To see it issue,
SQL> SELECT * FROM USER_UNUSED_COL_TABS;
TABLE_NAME COUNT
------------------------------ ----------
TEST 1
Now drop it by,
SQL> ALTER TABLE TEST DROP UNUSED COLUMNS;
Table altered.
Now check for unused columns by,
SQL> SELECT * FROM USER_UNUSED_COL_TABS;
no rows selected
It is very good to remember that the clause 'COLUMN' is used only for dropping a column and for rename a column. For modify a column or for adding a column 'COLUMN' clause is not present.
Related Documents
http://arjudba.blogspot.com/2008/09/how-to-disable-and-enable-all.html
http://arjudba.blogspot.com/2008/05/create-user-in-oracle.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
ORA-01843: not a valid month
Problem Description:
---------------------------
SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
A TO_TIMESTAMP(B,'DD-MON-RRHH.MI.SSXFFAM')
----------- ----------------------------------------------------------------
1 10-JUN-08 03.21.33.106197 AM
SQL> alter session set NLS_TIMESTAMP_FORMAT='DD-MM-YY HH';
Session altered.
SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts
*
ERROR at line 1:
ORA-01843: not a valid month
Cause of The Problem:
-----------------------------
The settings of the date or timestamp does not match with the settings or current date or timestamp format.
To know current session settings issue,
SQL> select * from NLS_SESSION_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MM-YY HH
TO know current instance settings query,
SQL> select * from NLS_INSTANCE_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT
To know database settings,
SQL> select * from NLS_DATABASE_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
To know currently affected parameter issue,
SQL> select * from V$NLS_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
---------------- -----------------------------------
NLS_TIMESTAMP_FORMAT DD-MM-YY HH
As this settings does not match with 'DD-MON-RR HH.MI.SSXFF AM' format so error comes.
Solution of The Problem:
---------------------------------
1)Use a format that match current settings. like,
SQL> select a, to_timestamp(b,'DD-MM-RR HH.MI.SSXFF AM') from test_ts;
A TO_TIMESTAMP(B,'DD-MM-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1 10-06-08 03
2)Or left it to default as ,
SQL> select a, to_timestamp(b) from test_ts;
A TO_TIMESTAMP(B)
---------- ---------------------------------------------------------------------------
1 10-06-08 03
3)Or exit session if it is set session wise and issue same query.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
-bash-3.00$ sqlplus arju/a
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 10 04:51:05 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from V$NLS_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
-------------------- --------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
SQL> select a, to_timestamp(b,'DD-MM-RR HH.MI.SSXFF AM') from test_ts;
A
----------
TO_TIMESTAMP(B,'DD-MM-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1
10-JUN-08 03.21.33.106197 AM
SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
A
----------
TO_TIMESTAMP(B,'DD-MON-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1
10-JUN-08 03.21.33.106197 AM
Both version worked and returned the same formatted result.
SQL> select * from NLS_SESSION_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
-------------------- --------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
---------------------------
SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
A TO_TIMESTAMP(B,'DD-MON-RRHH.MI.SSXFFAM')
----------- ----------------------------------------------------------------
1 10-JUN-08 03.21.33.106197 AM
SQL> alter session set NLS_TIMESTAMP_FORMAT='DD-MM-YY HH';
Session altered.
SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts
*
ERROR at line 1:
ORA-01843: not a valid month
Cause of The Problem:
-----------------------------
The settings of the date or timestamp does not match with the settings or current date or timestamp format.
To know current session settings issue,
SQL> select * from NLS_SESSION_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MM-YY HH
TO know current instance settings query,
SQL> select * from NLS_INSTANCE_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT
To know database settings,
SQL> select * from NLS_DATABASE_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
To know currently affected parameter issue,
SQL> select * from V$NLS_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
---------------- -----------------------------------
NLS_TIMESTAMP_FORMAT DD-MM-YY HH
As this settings does not match with 'DD-MON-RR HH.MI.SSXFF AM' format so error comes.
Solution of The Problem:
---------------------------------
1)Use a format that match current settings. like,
SQL> select a, to_timestamp(b,'DD-MM-RR HH.MI.SSXFF AM') from test_ts;
A TO_TIMESTAMP(B,'DD-MM-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1 10-06-08 03
2)Or left it to default as ,
SQL> select a, to_timestamp(b) from test_ts;
A TO_TIMESTAMP(B)
---------- ---------------------------------------------------------------------------
1 10-06-08 03
3)Or exit session if it is set session wise and issue same query.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
-bash-3.00$ sqlplus arju/a
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 10 04:51:05 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from V$NLS_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
-------------------- --------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
SQL> select a, to_timestamp(b,'DD-MM-RR HH.MI.SSXFF AM') from test_ts;
A
----------
TO_TIMESTAMP(B,'DD-MM-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1
10-JUN-08 03.21.33.106197 AM
SQL> select a, to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') from test_ts;
A
----------
TO_TIMESTAMP(B,'DD-MON-RRHH.MI.SSXFFAM')
---------------------------------------------------------------------------
1
10-JUN-08 03.21.33.106197 AM
Both version worked and returned the same formatted result.
SQL> select * from NLS_SESSION_PARAMETERS where PARAMETER='NLS_TIMESTAMP_FORMAT';
PARAMETER VALUE
-------------------- --------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
ORA-01830: date format picture ends before converting
Problem Description:
-------------------------
SQL> create table test_t (a date, b timestamp) ;
Table created.
SQL> insert into test_t values(SYSDATE,SYSTIMESTAMP);
1 row created.
SQL> select * from test_t;
A B
--------- ---------------------------------------------------------------------------
10/JUN/08 10-JUN-08 03.59
SQL> select to_date(a,'DD-MON'), to_timestamp(b,'DD-MON-RR') from test_t;
select to_date(a,'DD-MON'), to_timestamp(b,'DD-MON-RR') from test_t
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
Cause of The Problem:
---------------------------------
We can't use here TO_DATE or TO_TIMESTAMP function here to define or display date as our wish is to omit any value. If we want to display date or timestamp value then the format must be valid as of default format in database _properties. If we want to omit any portion or add any then the date becomes invalid, hence error produces. So if we want to display as our wish we must have to use TO_CHAR conversion. Also we can set NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT by using ALTER SYSTEM.
If you want to use TO_DATE and TO_TIMESTAMP then we have to use FULL format of default date settings.
Solution of The Problem:
---------------------------------
1)Using a TO_CHAR conversion.
SQL> select to_char(a,'DD-MON'), to_char(b,'DD-MON-RR') from test_t;
TO_CHA TO_CHAR(B
------ ---------
10-JUN 10-JUN-08
2)Setting NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT by ALTER SESSION.
SQL> alter session set NLS_TIMESTAMP_FORMAT='DD-MM-YY';
Session altered.
SQL> alter session set NLS_DATE_FORMAT='DD-MM';
Session altered.
SQL> select * from test_t;
A B
----- ---------------------------------------------------------------------------
10-06 10-06-08
3)Providing Valid Date and Timestamp valie.
If you want to use TO_DATE or TO_TIMESTAMP then you must give the valid date and timestamp settings. To know valid date and timestamp settings issue,
SQL> select property_name , property_value from database_properties where property_name='NLS_DATE_FORMAT' or property_name='NLS_TIMESTAMP_FORMAT';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
Reset any value,
SQL>exit;
SQL> select to_date(a,'DD-MON-RR'), to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') timstamp from test_t;
TO_DATE(A TIMSTAMP
--------- ---------------------------------------------------------------------------
10-JUN-08 10-JUN-08 03.59.33.274624 AM
In RMAN you can get the error like below,
RMAN> run{
.
.
SET UNTIL TIME '06-JUN-08 15:15:00';
.
.
}
executing command: SET until clause
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 09/08/2008 02:40:52
ORA-01830: date format picture ends before converting entire input string
To solution is to use TO_DATE conversion like,
RMAN>run{
.
.
SET UNTIL TIME "TO_DATE('06-JUN-08 15:15:00','DD-MON-YY HH24:MI:SS')";
.
.
}
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
-------------------------
SQL> create table test_t (a date, b timestamp) ;
Table created.
SQL> insert into test_t values(SYSDATE,SYSTIMESTAMP);
1 row created.
SQL> select * from test_t;
A B
--------- ---------------------------------------------------------------------------
10/JUN/08 10-JUN-08 03.59
SQL> select to_date(a,'DD-MON'), to_timestamp(b,'DD-MON-RR') from test_t;
select to_date(a,'DD-MON'), to_timestamp(b,'DD-MON-RR') from test_t
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
Cause of The Problem:
---------------------------------
We can't use here TO_DATE or TO_TIMESTAMP function here to define or display date as our wish is to omit any value. If we want to display date or timestamp value then the format must be valid as of default format in database _properties. If we want to omit any portion or add any then the date becomes invalid, hence error produces. So if we want to display as our wish we must have to use TO_CHAR conversion. Also we can set NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT by using ALTER SYSTEM.
If you want to use TO_DATE and TO_TIMESTAMP then we have to use FULL format of default date settings.
Solution of The Problem:
---------------------------------
1)Using a TO_CHAR conversion.
SQL> select to_char(a,'DD-MON'), to_char(b,'DD-MON-RR') from test_t;
TO_CHA TO_CHAR(B
------ ---------
10-JUN 10-JUN-08
2)Setting NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT by ALTER SESSION.
SQL> alter session set NLS_TIMESTAMP_FORMAT='DD-MM-YY';
Session altered.
SQL> alter session set NLS_DATE_FORMAT='DD-MM';
Session altered.
SQL> select * from test_t;
A B
----- ---------------------------------------------------------------------------
10-06 10-06-08
3)Providing Valid Date and Timestamp valie.
If you want to use TO_DATE or TO_TIMESTAMP then you must give the valid date and timestamp settings. To know valid date and timestamp settings issue,
SQL> select property_name , property_value from database_properties where property_name='NLS_DATE_FORMAT' or property_name='NLS_TIMESTAMP_FORMAT';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
Reset any value,
SQL>exit;
SQL> select to_date(a,'DD-MON-RR'), to_timestamp(b,'DD-MON-RR HH.MI.SSXFF AM') timstamp from test_t;
TO_DATE(A TIMSTAMP
--------- ---------------------------------------------------------------------------
10-JUN-08 10-JUN-08 03.59.33.274624 AM
In RMAN you can get the error like below,
RMAN> run{
.
.
SET UNTIL TIME '06-JUN-08 15:15:00';
.
.
}
executing command: SET until clause
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 09/08/2008 02:40:52
ORA-01830: date format picture ends before converting entire input string
To solution is to use TO_DATE conversion like,
RMAN>run{
.
.
SET UNTIL TIME "TO_DATE('06-JUN-08 15:15:00','DD-MON-YY HH24:MI:SS')";
.
.
}
Related Documents
http://arjudba.blogspot.com/2009/12/oracle-object-type-exercises-varray.html
http://arjudba.blogspot.com/2009/12/practice-oracle-joins-examples.html
http://arjudba.blogspot.com/2009/12/oracle-security-practices.html
http://arjudba.blogspot.com/2009/12/exercises-with-oracle-create-table-add.html
http://arjudba.blogspot.com/2009/12/oracle-database-creation-exercises.html
http://arjudba.blogspot.com/2009/12/basic-oracle-sql-exercise.html
http://arjudba.blogspot.com/2009/08/format-model-modifiers-fx-and-fm.html
http://arjudba.blogspot.com/2009/08/number-format-models-in-oracle.html
http://arjudba.blogspot.com/2009/08/format-models-in-oracle.html
http://arjudba.blogspot.com/2009/07/sql-decode.html
http://arjudba.blogspot.com/2009/07/how-to-know-row-of-table-belong-to.html
http://arjudba.blogspot.com/2009/06/how-to-know-which-objects-are-being.html
http://arjudba.blogspot.com/2009/06/ddl-with-wait-option-in-11g.html
http://arjudba.blogspot.com/2009/06/ora-00939-too-many-arguments-when-case.html
http://arjudba.blogspot.com/2009/03/oracle-datatype-internal-code.html
http://arjudba.blogspot.com/2009/03/how-to-know-list-of-constraints-and.html
http://arjudba.blogspot.com/2009/02/how-to-know-dependent-objectswhich.html
http://arjudba.blogspot.com/2009/02/how-to-search-stringkey-value-from.html
http://arjudba.blogspot.com/2009/02/how-to-know-when-tableobjects-ddlcode.html
http://arjudba.blogspot.com/2009/02/ora-00920-invalid-relational-operator.html
http://arjudba.blogspot.com/2009/01/adding-default-value-to-column-on-table.html
http://arjudba.blogspot.com/2009/01/ora-12838-cannot-readmodify-object.html
http://arjudba.blogspot.com/2009/01/ora-01779-cannot-modify-column-which.html
http://arjudba.blogspot.com/2009/01/updating-table-based-on-another-table.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/12/troubleshoot-ora-02292-ora-02449-and.html
http://arjudba.blogspot.com/2008/06/ora-00903-oracle-database-reserved.html
http://arjudba.blogspot.com/2008/06/hints-in-oracle.html
http://arjudba.blogspot.com/2008/06/examples-of-usage-of-composite-index.html
http://arjudba.blogspot.com/2008/06/find-indexes-and-assigned-columns-for.html
http://arjudba.blogspot.com/2008/06/reasons-for-using-alter-table-statement.html
http://arjudba.blogspot.com/2008/06/alter-table-rename-table-add-column.html
http://arjudba.blogspot.com/2008/06/ora-01830-date-format-picture-ends.html
http://arjudba.blogspot.com/2008/06/default-date-timestamp-and-timestamp.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
http://arjudba.blogspot.com/2008/06/example-of-antijoin-semijoin-curtesian.html
http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html
http://arjudba.blogspot.com/2008/10/convert-decimal-to-hexadecimal-on.html
http://arjudba.blogspot.com/2008/10/how-to-generate-fibonacci-series-in.html
http://arjudba.blogspot.com/2008/10/same-sounded-words-in-oracle.html
http://arjudba.blogspot.com/2008/09/type-of-constraint-in-oracle.html
http://arjudba.blogspot.com/2008/09/how-to-move-lob-data-to-another.html
http://arjudba.blogspot.com/2008/08/subqueries-in-oracle-with-example.html
http://arjudba.blogspot.com/2008/08/how-to-monitor-alert-log-file-in-oracle.html
http://arjudba.blogspot.com/2008/08/solution-of-ora-01873-leading-precision.html
http://arjudba.blogspot.com/2008/07/literals-and-literal-types-in-oracle.html
http://arjudba.blogspot.com/2008/07/ora-01722-invalid-number.html
http://arjudba.blogspot.com/2008/07/ora-00936-missing-expression.html
http://arjudba.blogspot.com/2008/07/ora-01756-quoted-string-not-properly.html
http://arjudba.blogspot.com/2008/07/pls-00428-into-clause-is-expected-in.html
http://arjudba.blogspot.com/2008/07/schema-object-naming-rules.html
http://arjudba.blogspot.com/2008/06/datetime-and-interval-datatypes.html
http://arjudba.blogspot.com/2008/06/large-object-lob-datatypes-with-example.html
http://arjudba.blogspot.com/2008/06/history-of-sql.html
http://arjudba.blogspot.com/2008/06/what-is-sql.html
Subscribe to:
Posts (Atom)
Tag Cloud
10.2g
10g
11g
11gR2
Abasa
About Oracle
Administration
Adsense
Alerts
Archival
ASM
ASP.Net
Audit
Audit Vault
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Mining
Data Pump
Data Type
Database Administration
Database Vault
DBConsole
Developer
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Facebook
Firefox
Firmware
Flashback
Forum
Functions
Games
Globalization Support
Grid Control
Hardware
History
HTML
IE
Import
Indexes
initializaion parameter
initialization parameter
Installation
Internals
Internet
Interview
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Mobile
Money
Multimedia
MySQL
Net Services
Network
OCP
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
Photos
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quiz
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Social Marketing
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
System Analysis
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Upgradation
Utilities
Version
Views
Vmware
Windows
Wordpress
XML