Oracle Cast Function: ORA-01438: value larger than specified precision allowed for this column

Error Description:

Oracle SQL query with cast function failed with following error message.

SQL> select cast(deptno as numeric(1)) from dept;

select cast(deptno as numeric(1)) from dept

            *

ERROR at line 1:

ORA-01438: value larger than specified precision allowed for this column

Opatch Failed error code 73: OUI-67073:UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables"

Error Description:

Oracle SPU / CPU patch deployment using Opatch filed with following error message.

Following executables are active :
/u00/app/oracle/product/11.2.0.3/db_1/lib/libclntsh.so.11.1
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /u00/app/oracle/product/11.2.0.3/db_1/cfgtoollogs/opatch/opatch2012-11-22_14-10-00PM.log

OPatch failed with error code 73

Data Pump Expdp Impdp Exclude Include Table Partitions – Table_data option

There are 2 ways to exclude and include table partition to the data pump utility.

1.   Is using data pump APIs. This technique I have explained in this post. Data Pump API

2.   Using exclude table_data option. I have mentioned one example below.

Create a table with partitions to test the expdp.

Reason for ORA-00439: feature not enabled: Partitioning

Error Description:

Create table command filed with following error.

CREATE TABLE EMP

*

ERROR at line 1:

ORA-00439: feature not enabled: Partitioning

Problem Description

The database portioning option works only with the enterprise edition not with the standard edition.

Create Jpg, png, PDF Oracle Database Schema ER / Table Relationship Diagram Using Data Modeler

Step 1. Open sql developer data modeler

Step 2. Navigate through File -> import -> data dictionary

Step 3. Click on Add button and provide the database connection details

Step 4. Connect to the database and click Next and the select the schema details

Step 5. Select the tables which you wanted to get the diagram and click finish.

The above steps will give you the ER diagram on the screen. From there you can easily generate the .jpeg,

Fill Maadi Win Aagi: Win Prizes from IOC Fuel Outlets

Indian Oil Corporation (IOC) introduced a new promotion campaign called "FILL MAADI WIN AAGI". If you are filling you fuel tank with Rs.500/- then you are eligible to participate in the campaign. There are lots of prizes waiting for you including 4 Tata Nano cars, Hero bikes, daily gifts etc.

What you have to do?

You have to get the electronically printed bills from the fuel outlet and send an SMS mentioned in the format mentioned in the fuel station.

The Format is

ORA-01732: data manipulation operation not legal on this view

Problem Description:

Oracle view DML operation fails with following error.

SQL> update vw_emp set ename ='xyz';

update vw_emp set ename ='xyz'

       *

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

SQL> explain plan for update vw_emp set ename ='xyz';

explain plan for  update vw_emp set ename ='xyz'

Fix ORA-08189: cannot flashback the table because row movement is not enabled

Error Description:

Flashback table command failed with following error.

SQL> FLASHBACK TABLE test2 TO TIMESTAMP TO_TIMESTAMP('2013-10-28 12:00:00', 'YYYY-MM-DD HH24:MI:SS');

FLASHBACK TABLE test2 TO TIMESTAMP TO_TIMESTAMP('2013-10-28 12:00:00', 'YYYY-MM-DD HH24:MI:SS')

                *

ERROR at line 1:

ORA-08189: cannot flashback the table because row movement is not enabled

Oracle Create View Syntax: 9 Different Examples

1. Create Simple View with Selected columns of a table

SQL> create view vw_emp as

select empno, ename from emp;

View created.

2. Create a View with all columns of a table

SQL> create view vw_emp as select * from emp;

Oracle Create Table Syntax: 19 Different Examples

1. Simple syntax for create table

SQL> CREATE TABLE CR_TABLE_EX

       (EMPNO NUMBER(4) ,

        ENAME VARCHAR2(10)); 

Table created.

2. Create table with primary key

SQL> CREATE TABLE CR_TABLE_EX

NoMachine Tool: Better Remote Desktop Connectivity across Different OS platforms

NoMachine is similar to any desktop sharing tool like VNC, etc. But only thing it has lot of advanced features.

·        NoMachine gives better speed compared others.

·        NoMachine gives connectivity to any desktop across different OS platforms where you have NoMachine installed.

·        NoMachine can work with any type of content like DVD, video, YouTube, watch TV etc with good speed

Oracle NO_INDEX hint: Syntax, Purpose and Example

Syntax: /*+no_index (Table_name Index_name)*/

Purpose: NO_INDEX hint explicitly notifies the optimizer to not to use the specified index(s). This can be used for query testing purpose without dropping the actual index. In some cases queries will give better performance without indexes. This difference can be tested using this hint. This hint applies to function_based, B-tree, bitmap, cluster indexes.

How to Bypass DROP ANY TABLE Privilege for Truncate Table Command

You have to create a procedure with truncate table command (dynamic SQL) and grant the execute procedure privilege to the other user. This method will help you to avoid the granting of drop any table privilege to the target user. Here is the example for the same.

Example

I have 2 users called test and sthomas. Test wanted to drop the table from the sthomas schema. Dept table has 4 records.

How to Check AWR Snapshot Setting using SQL Queries?

a.   Query to check AWR snapshot interval and retention period set for the database. In below example the snapshot interval is one hour and retention is 8 days. The data type for the columns SNAP_INTERVAL RETENTION is having two fields – days and time with hour, min, sec.

select * from dba_hist_wr_control;

Expdp Export Failed: ORA-00439: feature not enabled: Dump File Data Compression

Error Description:

Data pump expdp export failed with following error

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

ORA-39002: invalid operation

ORA-00439: feature not enabled: Dump File Data Compression

Solution Description:

The advanced compression options of oracle works only with Enterprise Edition. I have used compression=all option and the Oracle version which I am using is standard edition. That is the reason for the error.

Expdp / Impdp Tablespace Parameter Benefits and Examples

Oracle data pump provides tablespace parameter to export and import the data in tablespace level. You can club one or more tablespaces in single dump while export and you can import one or more tablespace from the full database dump during the import. The benefits of this option are as below.

a.   It would be very helpful during the tablespace level database reorganizations. The backup can be performed only on the targeted tablespace.

How to Perform a Date Test Case with Old or Upcoming Sysdate in Oracle? - FIXED_DATE Parameter

Oracle provides a parameter called FIXED_DATE to set the oracle sysdate to a fixed date which can be a prior or upcoming date. By default the fixed_date parameter value would be null or NONE. You can set the fixed_date parameter value using alter system command. This parameter mainly used for testing purpose by setting the sysdate to an old or upcoming date.

Initially the parameter value would be null and it will show the current system date.

Oracle Delete and Update Commands Executes With Set Autotrace Traceonly Explain

When you set autotrace traceonly explain in SQLPlus prompt, generally the select command will not execute but it will show the execution plan. However, it will differ in case of DMLs like delete and update. It executes the query first and will provide the execution plan.

SQL >select * from tab;

SQL Developer, Data Modeler Failed: Unable to Launch the Java Virtual Machine Located at Path:

Error Description:

SQL Developer Data Modeler launch fails with following error in windows 7 64bit machine.

Unable to Launch the Java Virtual Machine Located at Path:

Solution Description:

The issue is because the windows x64 Java JDK kit will not work with SQL developer/data modeler. 

How to Check Which User is Connected to Current Oracle Session? 3 different methods

There are three different simple ways to identify the current user session in oracle sqlplus.

1.     Using DBMS_UTILITY. OLD_CURRENT_USER function.

select DBMS_UTILITY.OLD_CURRENT_USER from dual;

SQL> /

OLD_CURRENT_USER

How to Modify / Set SQLPlus Prompt With Connected Username and SID?

It is always better have the username and the database SID to be set in the sqlplus prompt to avoid confusions. It will be very useful to identify the database where you connected especially your work involved in multiple databases.

        1.   Setting the prompt temporarily with the current connected session.

set sqlprompt "_CONNECT_IDENTIFIER'-'_USER> "

esbtst-SYS>

How to Avoid / Stop /Restrict the Dropping of User Schema by Mistake from Oracle Database?

Suppose multiple DBAs are working on simultaneously in different databases, there are chances of dropping the user or schema by mistake. Eg: Wrongly dropping the user schemas in similarity of the names etc. Suppose if the schema is having objects it will throw the error if you are not using the cascade option. If you use cascade option with drop command it will delete the user without asking for any kind of confirmation. It can be avoided by using the following trigger which can be created under sys/system schema.

How to Exclude MLOG$ Materialized View Log Tables from the Data Pump IMPDP

Generally if you use exclude parameter with materialized view, materialized view log, it will not exclude the mlog$ tables from the import. You have to useTABLE also with the exclude parameter. Please see an example here.

Example

I am going to create a materialized view and a log for the same.

SQL> create materialized view log on dept;

Oracle dbms_utility.get_dependency to Get Dependent Objects or Dependencies of a Table

It is a common requirement for a DBA or developer to find out the dependent objects or dependencies for a table. You can use dbms_utility .get_dependencypackage for this purpose.

Syntax:

exec dbms_utility.get_dependency  ('TABLE', 'TABLEOWNER', 'TABLENAME');

See some examples here.

SQL> SET SERVEROUTPUT ON

SQL> CREATE SYNONYM SYN_EMP FOR EMP;

Synonym created.

How to Kill or Resume [1]+ Stopped Unix Jobs

When you press ctrl-z on actively running job or command it will suspend the job and release the prompt. But the job will be stopped and remain idle in the back ground. If you try to logout the window or session you will get a message saying “There are stopped jobs.” You can stop or resume the jobs using following commands.

jobs –p command will give the process id for the suspended job.

jobs –l command will list the details of the suspended job

See examples below.

How to Add or Insert Current Log Time and Date to a Notepad File Or Whenever You Open the file by Default

Suppose if you have habit of logging all tasks to be performed or already performed with time and date, then you can use windows notepad easily for this purpose. This trick works with all major versions of the Windows.

There are two options

1. Insert time stamp whenever and wherever you needed.

Open the notepad file and move the cursor to the location where you need the timestamp and just press Function Key 5(F5).

Oracle 12c Data Pump EXPDP IMPDP Logtime Option Parameter

One new parameter introduced in Oracle 12c data pump which is called logtime. This is a command-line parameter which will display the messages or status in log with time stamped format. This parameter helps the DBA to identify the time taken for each granule of export job. There are 4 possible options available for logtime parameter.

How to Identify Connected Database Instance is Primary or Standby?

For primary and standby database the database name will be same and instance name will be different. These are the main differences to identify the primary and standby database instances.

Primary

1.  SQL> select name from v$database;

NAME

---------

KMPRD

SQL*Loader PARFILE Parameter

There are 2 ways to specify the parameters to the SQL* loader-command line and parameter file. Suppose if you are running the SQL* loader through command line by giving the credentials you cannot hide the password from the process list (ps –ef). Also, if you have more number of parameters needs to specified with the SQL* loader in that case there are some limitations for the command prompt.

SQL*Loader DISCARDMAX Parameter

This parameter specifies the maximum number of discard records can be allowed during a particular data load. This is a number parameter and if you are not specifying any number for this argument then it will consider all discard records. Otherwise it will exit the data load once it reaches the limit. See an example below

Here is my data file

How to Remove / Hide Post Counter from Blogger Archive Widget in New Interface

Generally there would be a number of post counter with blogger archive widget. If you don’t want to show the number of posts against each month/year you can use the following method to hide the counter.

How to Hide / Remove ‘Subscribe to: Post Comments (Atom)‘ From the New Blogger Interface 2013

Subscribe to: Post Comments (Atom) is the link for subscribing the atom feeds from the blog posts. It generally comes under the each and every blog post. It is consumes some space under each and every post and it is not relevant for the blogs much. You can follow the below method to remove / hide this Subscribe to: Post Comments (Atom) link.

Shell Script to Execute Oracle Sql Script in Crontab for a Specified SID

For general shell script to execute SQL statements does not require the environment as those required parameter might be set already for the sessions. But when it comes to Crontab job, script will run independently from other sessions and it needs all the required parameter to be set inside the script.

What are the required parameters to run a SQL in a Crontab shell script?

How to Automate / Schedule Oracle Gather Table Statistics Job through Crontab?

Step 1: Create a shell script to accept a sql file and execute against a particular database. Make necessary changes to the ORACLE_SID, ORACLE_BASE, ORACLE_HOME parameters.

$ vi sql.sh

export ORACLE_SID=transprd

export ORACLE_BASE=/u00/app/oracle

export ORACLE_HOME=/u00/app/oracle/product/11.2.0.3/db_1

export

SQL Loader bad file vs Discard File: Difference and Examples

BAD File contains the records which are rejected either by SQL Loader or by the database because of the bad formatting or data type mismatch.

More details and example here.

DISCARD file is to store the records that are neither inserted into table nor rejected as bad. This is an optional parameter with SQL Loader and by default name would be .dsc.

Oracle SQL Loader Bad file (.bad) Parameter Example

The BAD parameter specifies the name of bad file created by SQL*Loader to store the records that rejected during the insert process because of the data type mismatch or improper format. If the file name is not specified, by default the SQL*Loader will take the .bad as the file name. If you specify the bad file name with parameter bad then it will override the default file name. See some examples below.

Shell Script: 3 Methods to Find Out Nth Word in a String

Here is my test string - This is a temporary change to complete the export. I wanted to find out nth word from the string.  I have specified the nth word in red color below

$ mystring="This is a temporary change to complete the export"

Method 1 – awk – 4th word

Shell Script: Different Method to Find Out Number of Characters in a String

To count total number of characters in a string

Count total number of

$ mystring="This is my test string"

$ echo "${#mystring}"

22

$ expr length "$mystring"

22

Shell Script to Print Only First / Last Words in all Lines of a Text File

I have a text file and I wanted to print only first and last words of the file.

My sample input file is with following data

$ cat dat

 MONTH YYYY WK Mo Tu We Th Fr Sa Su

01-JAN 2013 01    01 02 03 04 05 06

01-JAN 2013 02 07 08 09 10 11 12 13

01-JAN 2013 03 14 15 16 17 18 19 20

Data Guard / Standby: ORA-01156: recovery or flashback in progress may need access to files

Error Description:

In standby or Data guard the clear logfile group, drop logfile group commands are failing with following error.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

ALTER DATABASE CLEAR LOGFILE GROUP 1

*

ERROR at line 1:

How to see / Check the Step by Step Execution of a Shell Script for Debugging?

It is very important have the step by step execution feature for debugging for any kind of programming language. Using this feature you can see the variable values which are getting assigned during the script execution. In shell script you can use the –x option to see the step by step execution. See an example here.

I have a sample program here.

How to Set / Unset Default Value for Command Line Arguments For a shell script?

While doing shell scripting you will use command line arguments to pass the values to the program. The program has to verify the value of the command line arguments whether it is having proper values assigned before further execution. Otherwise you can assign a default value for the parameter or argument if applicable.

Syntax:

Shell Script to Repeat the Execution of Command / Job Based on Time Interval Periodically

Suppose if you are copying some huge files from server to another server and you wanted to see the progress in size, number you can use these scripts. These scripts will run periodically according to the sleep commands.

Using yes Command

Data guard / Stand by: What are the Steps to Move Datafiles / Logfiles to Different Location?

Primary database Steps

1.   Confirm your primary database instance where you wanted to perform the activity

SQL> select name from v$database;

NAME

---------

TRANPRD

Data Guard / Standby: ORA-01624: log %s needed for crash recovery of instance %s (thread %s)

Error Description:

Drop redo log group in data guard database is giving following error.

 

SQL> alter database drop logfile group 1;

alter database drop logfile group 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance TRANPRD_STANDBY (thread 1)

ORA-00312: online log 1 thread 1:

Fix DGMGRL: Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Error Description:

Show configuration command shows following warning in DGMGRL.

 

DGMGRL> show configuration

 

Configuration - PRDCONFIG

 

  Protection Mode: MaxAvailability

  Databases:

How to Find out / Identify the list of Schema / Users Available Inside a expdp data pump .dmp dumpfile

This is also another interesting question. Suppose if you get a dump from the source database and you are not aware of the users/schemas present in the dump. If you wanted to see the list of schemas/users present in the dump file, you can use this method to find out the same.

Step 1. Import the dump with sqlfile option.

How to Identify / Check / Find out Oracle .dmp dumpfile is Taken Using Exp / Expdp – Data Pump

It is a common question or doubt which arises when you get a dump file from the source database without knowing how it is exported. It might be taken using either conventional exp utility or data pump expdp. If you have the log file along with the dump file, it is very easy to identify the utility. You just need to tail the log file and you can identify as mentioned below.

Oracle 11gR2: Truncate Drop Storage Vs Drop All Storage Clauses

Drop Storage clause is to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter in the table. This deallocated space will move to free space category and it can be used for the other objects in the tablespace. So in this drop storage clause there would be some space which is equal to the MINEXTENTS used for the table after the truncate command execution.

How to Pay Kerala Electricity Bill Online? Introduced New System from 23-April-2013

Kerala electricity board introduced a new system to pay the electricity bill online for the consumers in the state. You can see a scrolling flash in KSEB website http://www.kseb.in/ about this. On top of KSEB website you can see a “PAY BILLS ONLINE” icon to initiate a payment. Or you can directly click on below mentioned link. http://pg.kseb.in/ui/qpay.php

Unable Access Password Protected Spread Sheet / Document Google Drive On iPhone

I have installed Google Drive in my iPhone and it is working fine in my phone. As you aware it is running on cloud system and it is sync with my files personal laptop. I had an issue with Google Drive in my iPhone. I saved one password protected Excel and Word Document from laptop.

By Pass the User Password Using Grant Connect Through clause

In some cases we require the application or some specific user credential for testing purpose though if you have DBA privileged user. We will not be able to change the password for this application user on the fly because it will be hard corded or depended to the application. In such situations you can use this privilege grant connect through clause.

ORA-28150: proxy not authorized to connect as client

Refer this link forConnect Through clause.

Error Description:

        User Connection fails with following error.

SQL> conn user2[user1]

Enter password:

ERROR:

ORA-28150: proxy not authorized to connect as client

How to Append Extra New Records to Table Using Oracle SQL loader?

Suppose if you have some records already inserted in the database table and you wanted to add few more records to the same table, in this case you will need this option to append the record. Most of the time, you will be not in a position to truncate the table and reinsert the data to the table because the available volume of the data in the table might be huge or it might have used or referenced for some other purpose.

What are the Advantages / Benefits / Features of Oracle SQL loader?

SQL* Loader is an Oracle utility to load the data from the flat file in to the Oracle database tables. Major backbone of the SQL Loader utility is the control file during the data load. This control file guides the SQL Loader utility in terms of which tables and columns should receive the data that you are loading. There is no similarity between database control file and SQL Loader control file.

What is CLRExtProc, PLSExtProc and extproc in Listener.ora? Why actual database SID is not mentioned in listener.ora by default?

It is a basic question which may come how the listener works without mentioning the actual database SID in the listener.ora. By default if you create a database using DBCA it will create a listener.ora file which is similar to the below one. If you have create the database name as ORCL which will not be there in the listener.ora and instead of that there you can see SID_NAME asCLRExtProc or PLSExtProc.

CAN FLASH to 111 Method to Disable / Stop / Turn off / Deactivate unwanted Vodafone Flash Messages

If you are using Vodafone, you might experience with the unwanted / money spoiling flash messages in your mobile. The message comes with accept and canceloptions. Most of the time the ACCEPT button comes with the side where you have chance to click. By mistake if you click the accept button you will lose the money.

How to install an application from iTunes to iPhone

If you have GPRS or 3G connection available on your iPhone it is easy to install an application from iTunes using the internet. But if you don’t have internet in the iPhone you can use iTunes application to download and the required application to the iPhone. Here I will describe the steps to install the application through the iTunes application with screenshots.

How to Hide / Disable / Remove DISQUS Discovery “ALSO ON” YOUR SITE feature?

Disqus discovery is an ad publishing program which will give you additional revenue as well as traffic to your website. It automatically comes after the comments section. “Also On Your site” is a automatic system to create back link to your site itself.

F4 Key to Repeat Last or Previous Action / Task in Microsoft Excel or Word 2007 and 2010?

When you work continuously in Microsoft office tools, it is better or easy if you can repeat the previous action / task in the spread sheet or document. Here are some examples like bold the different columns delete some rows or columns from the spread sheet,

How to Hide / Disable / Enable DISQUS AROUND THE WEB Discovery ads?

Disqus discovery is an ad publishing program which will give you additional revenue as well as traffic to your website. It automatically comes after the comments section. This can be disabled by following steps.

Oracle Upgrade Catupgrd.sql failed: with SELECT TO_NUMBER('MUST_BE_11_2_0_3') FROM v$instance

Error Description

During the oracle upgrade from Oracle 11.2.0.1 to 11.2.03(windows), the catupgrd.sql failed with following error.

SELECT TO_NUMBER('MUST_BE_11_2_0_3') FROM v$instance

                 *

ERROR at line 1:

Steps to Configure Oracle 11g 10g RMAN Catalog in Same / Different Database

In this example I have explained the method for configuring the RMAN catalog in same database itself. If you wanted to create the different catalog database for RMAN, create a new database using DBCA and run the step #1, #2 in new RMAN catalog database. Step #3 also needs to be modified with appropriate connection strings which I marked in red color in step #3.

IMPDP EXPDP ENCRYPTION_PASSWORD Parameter ORA-39173: Encrypted data has been stored unencrypted in dump file set

Error Description

Data pump expdp export failed with following error.

Data pump expdp export failed with following error.

. . exported "SCOTT"."TABLE_ENCRPT"                      5.867 KB       1 rows

ORA-39173: Encrypted data has been stored unencrypted in dump file set.

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

Adsense CPC Fluctuation / CPC Drop Reasons, Key Factors for CPC Improvement

CPC-Cost per Click: It is a common for all the publishers why my CPC is too low, why it is dropped suddenly, how I can improve my CPC, etc. etc. CPC is the main factor for the Adsense revenue. Adsense Revenue is directly proportional to the CPC value because your revenue is calculated by CLICKS x CPC. Certain factors are important in the case of the CPC.

ORA-00214 control file flash_recovery_area version %s inconsistent with file version %s

Error Description:

Database startup failed with following error after the database cold backup restoration.

SQL> startup

ORACLE instance started.