The Global & Local indexes are mainly related to Oracle table partitions. On a partitioned table (it can be partitioned by range, hash, list) you have the authority to create a local or global index. It is up to your choice. Index partitioning is transparent to all the SQLs. The advantage of the indexes is the Oracle query engine will scan only the required index partition to serve the query, thus speeding up the query significantly.
How to get Google Analytics Code again?
It is possible to get Google Analytics code once again from the https://www.google.com/analytics site. There are chances of deletion of the analytics code from your blog or website due to the template modification or virus attack. Here are the steps to get the code.
Step 1: Login to https://www.google.com/analytics site using your credentials.
Create PFILE SPFILE from memory 11g New Feature
Oracle 11g provides a new feature to create PFILE or SPFILE using the current values in the Oracle Memory (active Instance). This will be useful if you delete the spfile or pfile by mistake. J see examples below.
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Jun 29 01:32:04 2011
Oracle 11g New Feature Parameter DDL_LOCK_TIMEOUT : DDL waiting time for DML Locks
Oracle 11g introduced a new parameter ddl_lock_timeout which controls the waiting time duration for a DDL statement wait for a DML Lock. Prior to 11g and in 11g if you are not specifiying the ddl_lock_timeout, if you perform a ddl operation on a table which is locked by a end user DML, immediately you will get error “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”.
Troubleshoot & fix ORA-01002: fetch out of sequence
Error Description: PL/Sql block exist with ORA-01002: fetch out of sequence.
See example below
1 declare
2 cursor c1 is select * from emp1 for update;
3 begin
4 for i in c1 loop
How to check whether oracle is 32bit/64bit and running OS platform is 32bit/64bit?
For Oracle:
Go to ORACLE_HOME/bin directory and type file oracle
cd $ORACLE_HOME/bin
file oracle
$ file oracle
oracle: ELF 64-bit MSB executable SPARCV9 Version 1, dynamically linked, not stripped
Workaround for ORA-600 [k2vpci_2] SMON Crash and Subsequent startup fails
Error description: Database SMON crash with ORA-600 [k2vpci_2]
Workaround:
Step 1: open the alert log file for the database.
Step 2: Find out the incident trace file which is mentioned with the ORA-600
Troubleshoot Fix EMCA CONFIG: emca/emcaDbUtil: perl/bin/perl: not found
Error Descirption: emca -config dbcontrol db command fails with perl: not found
Jun 23, 2011 7:25:41 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
CONFIG: Exit value of 1
Jun 23, 2011 7:25:41 AM oracle.sysman.emcp.util.PlatformInterface executeCommand
CONFIG: /data/oracle/product/11.1.0/sysman/admin/scripts/emca/emcaDbUtil: /db01/oracle/product/11.1.0/perl/bin/perl: not found
Data Pump EXPDP IMPDP EXCLUDE and INCLUDE Options Features and examples
Data Pump provides fine filtering of objects during the export or import through this exclude and include feature. We can use these exclude and include options with both the EXPDP and IMPDP utilities. It is kind of object exception marking during the expdp or impdp.
Data Pump EXPDP : How to EXCLUDE table partition explained with example
Data pump will not do exclude for the table partitions. If you use exclude=table:”IN (‘EXAMPLE: EXAMPLE_P2’)” in the expdp, it will just ignore the exclude and it will perform the full table export with all the partitions for the table. To achieve this goal you have to use data pump API package using the DBMS_DATAPUMP.DATA_FILTER.
How to create and remove soft link (symbolic) in Solaris AIX?
Soft link is the shortcut or alias name for a file or directory. It is just access path or name for an actual file or directory. So when you remove the soft link it will not affect the actual files present in the directory.
When you discuss about soft link, I wish to mention about hard link also. Hard links are just opposite to soft links.
Troubleshoot Fix emca oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-12541: TNS:no listener
Error Description:
emca -config dbcontrol db command fails with following error messages.
Jun 23, 2011 8:28:17 PM oracle.sysman.emcp.util.GeneralUtil initSQLEngine
CONFIG: SQLEngine created successfully and connected
Jun 23, 2011 8:28:17 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
CONFIG: ORA-12541: TNS:no listener
Steps to configure Oracle 11g OEM DBConsole manually for an existing database
In most of the time DBAs used to go for manual configuration of the database after the database creation. Suppose your database is cloned from other database where you have grid already installed and configured. After the cloning, in the target database the same set of configuration will not work. You have to reconfigure the same.
Restrict the commands (SELECT DELETE UPDATE) in SQL*PLUS for a particular user
DBAs can restrict the commands like select, delete, update etc. in SQL*Plus by adding one row in PRODUCT_PROFILE table. Each row in the table is explains which command is blocked for which user. It can be re-enabled by removing the corresponding row from the table.
These are the list of Commands which can be disabled.
Troubleshoot Fix EMCA Could not connect to SYS/(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) ORA-01031: insufficient privileges
This error is generally comes with the emca OEM configuration. To fix this you have set following parameters. Also recreate the password file.
Error Description: [23-06-2011 05:15:18] Enter SYS user's password :
[23-06-2011 05:15:18]
[23-06-2011 05:15:18] Getting temporary tablespace from database...
[23-06-2011 05:15:18] Could not connect to SYS/(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=125.204.31.14)(PORT=1522)))(CONNECT_DATA=(SERVICE_NAME=
RMAN Switch command script to rename the datafile
Overview: RMAN switch command is equivalent to the alter database rename file. By default the RMAN will restore the file to the original location of the file and it will overwrite the file already present in the original location. If you wanted to restore a datafile to different location you need to run “set rename” command.
ORACLE BASE Vs ORACLE_HOME - Difference between ORACLE_BASE and ORACLE_HOME
Oracle BASE and Oracle HOME are the directories defined by the Oracle Flexible Architecture. Oracle Base is the top level directory or you can say root directory. Under Oracle Base you can have multiple Oracle versions with different Oracle HOME. You can have multiple oracle products under Oracle BASE directory.
Oracle 10g, 11g User Password Last Modification / Reset time - sys.user$
In Oracle the password reset/change time is stored in ptime column of sys.user$ table and the user created time is stored in ctime column.
See Example
Initially the CTIME and PTIME column values are same.
How to change or modify the SQLPlus prompt?
Suppose if are working on multiple environments at a time, there are chances of command execution mistakes like executing the development database commands to production sqlplus prompt. To avoid such kind of confusions, it is better to set the sqlplus command prompts in different.
Expdp/Impdp Transportable Tablespace Movement across Different OS Platforms in Oracle 9i, 10g, 11g Examples, Advantages
Transportable Tablespace Advantages:
Oracle allows you to transport the tablespaces from one database to other database across different OS platforms. It is a quick and most efficient way of bulk data movement
It is much faster than any other traditional data transfer methods like export import.
Oracle 9i, 10g, 11g: Methods to Rename a username / schema to a new name
There is no direct method to perform the schema or username renaming. Oracle does not provide any single command to perform this. There are two indirect ways to perform this. Out of these 2 methods one is not recommended by the Oracle.
Method 1
How to Create Virtual Columns with Oracle 11g New Feature?
Oracle 11g supports virtual columns which are derived from the other columns in the table. You can use the virtual columns in your queries, you can index them, you can collect the statistics, etc.
There are few restrictions on the virtual columns.
You cannot write/insert the data into virtual columns.
There is no support for index_organized, external, object, cluster, temporary tables.
SQL Query Check Oracle (9i/10g/11g) tablespace free space and space Usage
select a.tablespace_name name,
b.tablespace_name dummy,
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))/1024/1024 mbytes,
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )
- sum(a.bytes)/count( distinct b.file_id ))/1024/1024 used,
(sum(a.bytes)/count( distinct b.file_id ))/1024/1024 free,
100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))
How to Login with more than One (Multiple) Yahoo Messenger simultaneously?
Yahoo Messenger: Generally with yahoo messenger allows only one login at a time. Below mentioned workaround will allow you to multiple logins.
- Open windows registry
Go to Windows Start > Run > Type regedit, hit enter
2. Open HKEY_CURRENT_USER > Software > Yahoo > pager >Test
2. Open HKEY_CURRENT_USER > Software > Yahoo > pager >Test
Oracle 11g New Feature password security dba_users password column is null and stored in user$
Oracle 11g released with lot of enhanced security features. In prior versions of Oracle the hashed passwords were stored in dba_users table password column. So in those versions (10g and below) users can temporarily reset (in case if you forgot the password or unknown) the user passwords by updating the password column in dba_users table.
OPatch fails with wrong platform ID (610, 212)
Error Description: Error is due to the bug where the file is populated with incorrect platform ID. File is $ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml
$ cat oraclehomeproperties.xml
<?xml version="1.0" standalone="yes" ?>
How to correct & fix the corrupted Oracle (9i, 10g, 11g) inventory using runInstaller & opatch?
Inventory is the index for the oracle installation. All the details about the ORACLE_HOME including different utility versions, patch details etc. If you do a fresh Oracle installation the inventory will be updated. But if you copy the ORACLE binaries from some other location and if you are not updating the inventory, we cannot read the inventory using opatch utility.
How to generate/spool index creation script for a schema using dbms_metadata.get_ddl?
Step 1
$ vi gen_index_script.sql
set pages 0
set lines 90
set echo off feedback off verify off heading off
set termout off
spool cr_index_script.sql
select 'set long 50000' from dual;
select 'spool cr_index.sql' from dual;
How to generate/spool table creation script for a schema using dbms_metadata.get_ddl?
Step 1
$ vi gen_table_script.sql
set pages 0
set lines 90
set echo off feedback off verify off heading off
set termout off
spool cr_table_script.sql
select 'set long 50000' from dual;
select 'spool cr_table.sql' from dual;
DBMS_ROWID for Oracle ROWID Pseudo column Format. Structure, Purpose, Features
ROWID:
- It is a unique address identifier or pointer for each row to locate the row resides in the disk
- The details in the ROWID gives Oracle everything it needs to find your row, disk number, block and offset to the block
- ROWID is an 18 digit unique number and that internally represented as a base-64 number.
What is Oracle memory HIT and Memory Miss? Buffer Cache, Library Cache, Dictionary Cache HIT Ratio
When a user submits a query to the oracle session, oracle will parse the query and check out for the result set blocks in the memory (SGA). If the server process can find out the required block from the SGA, then that is called a memory HIT. In case it is not readily available in the memory, it has to get it from the datafiles to the SGA and this is called a MISS.
Troubleshoot fix resolve ORA-12988: cannot drop column from table owned by SYS. How to drop a column of a table owned by SYS
Example
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> show user
USER is "SYS"
SQL> create table example as select * from scott.example;
Table created.
what is ORA-00031: session marked for kill , ALTER SYSTEM KILL SESSION, resource busy and acquire with NOWAIT
Problem Description: DBA wanted to kill a long running oracle job/process and he issued ALTER SYSTEM KILL SESSION ‘SID,serial#’ command. DBA got a message ORA-00031: session marked for kill. But, after killing the job, still he is not able to modify the table which was involved in the job or not able to add an index to the table. When he tries do perform any kind DDL change on the table, getting resource busy and acquire with NOWAIT error message.
Troubleshoot Fix Resolve ORA-00257: archiver is stuck. CONNECT INTERNAL only, until freed
Scenario#1
Error Description: Your database is running on ARCHIVELOG MODE and when you try to connect through sqlplus you are getting following error. ORA-00257: archiver is stuck. CONNECT INTERNAL only, until freed.
Problem Description: If your database is running on archive log mode, database redo log files will be frequently archived to the archive log destination.
Troubleshoot fix resolve ORA-04091: table scott.EMP is mutating, trigger/function may not see it
ORA-04091: table scott.EMP is mutating, trigger/function may not see it
Explanation:
04091, 00000, "table %s.%s is mutating, trigger/function may not see it"
// *Cause: A trigger (or a user defined plsql function that is referenced in
// this statement) attempted to look at (or modify) a table that was
// in the middle of being modified by the statement which fired it.
// *Action: Rewrite the trigger (or function) so it does not read that table.
Troubleshoot fix resolve ORA-00936: missing expression
ORA-00936: missing expression an Overview:
This error is related to Oracle expressions which is used in SQL or PL/Sqls . An incomplete or missing expression in DML/DDL will fire ORA-00936 error. A part of a clause or expression has been missed or omitted from the oracle expression. This is mainly related to the SELECT statement.
Some examples
Troubleshoot Fix Resolve ORA-00060: deadlock detected while waiting for resource
Overview:
This error is due to the resource contention between two sessions. It indicates that a dead lock happened due to the resource contention with other session. Oracles itself does a rollback on your current session and resolve the error. Other session can proceed as usual. The alert.log will record the error with all details. Current session will be rolled back and need to resubmitted once the required resources are available.
Troubleshoot ORA-00938: ORA-00939: not enough arguments & too many arguments for function
Reason:
This error is related to the oracle functions. For each and every function there should be well defined set of arguments. If you miss to specify the exact number of arguments, you will get these errors. In case if you are specifying less number of arguments you get ORA-00938: not enough arguments for the function and if you specify more number of arguments to a function you will get ORA-00939: too many arguments for function.
Creation, Features of a global temporary table, Privileges to populate
How to create Global Temporary table?
Temporary tables are the specific to sessions and the data in table is private to the session which they created. There are two types of temporary tables and those are
1. Transaction specific: The data in the table will be preserved until the transaction ends and the syntax to create the table is
How to disable flash recovery area of existing open Oracle (10g, 11g) database?
Flash recovery area can be disabled by setting DB_RECOVERY_FILE_DEST initialization parameter to a null value.
Connect to SQL*Plus as sys as sysdba and issue following command to disable the flash recover area.
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID="*";
How to Check Flash Recovery Area Usage for Oracle(10g, 11g) Database?
There are two dictionary views to monitor the flash recovery area usage.
1.
V$RECOVER_FILE_DEST can be used to find out the physical location,
How to configure or setup Flash Recovery Area to an Existing oracle (10g, 11g)Database?
Overview:
To setup the flash recovery area in your existing database, you need to set below mentioned required parameters in the database parameter file (PFILE) and also database needs a bounce or restart.
You can also set the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters to setup a flash recovery area to a running/open database.
Oracle 11gR2 relink new feature
Oracle 11g release 2 has two methods to relink the product home.
1. Traditional relink all. The relink executable resides under $ORACLE_HOME/bin/ directory and the new relink accepts only all option. No other parameters accepted with new relink.
How to check patches applied on oracle database? Patch history details
Overview
Opatch is the commonly used oracle utility check the database patch details. These are the methods to check the patch details. opatch gives the details by checking the oracle inventory location. So database need not be up and running.
Troubleshoot Fix Resolve ORA-00054: resource busy and acquire with NOWAIT
Overview ORA-00054:
This is a quite normal message and is reported if a SQL statement would have blocked waiting for another user to complete some operation. The SQL command with either:
- Had a 'NOWAIT' clause so instead of blocking the statement returns this error.
- Was DDL and was blocked.
How to disable all constraints on a table or schema - Oracle sql Script
Overview: These are the sql commands to enable/disable oracle constraints.
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
Suppose if we have multiple constraints to disable or enable, in this situation executing these commands for each and every constraints is difficult.
SET AUTOTRACE EXPLAIN or EXECUTION Plan Options
You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (Data Manipulation Language - that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.
SET AUTOTRACE Options Usage
RMAN Point in time Recovery (PITR) scenario of a dropped oracle tablespace
Facts: Tablespace point in time recovery cannot be used in this scenario because the control file will not have any details about the dropped tablespace. If you try “restore tablespace <tablespace_name>” command in RMAN it will throw error “RMAN-06019 – could not translate tablespace name”.
