Delete vs Truncate in Oracle 9i 10g 11g – Difference and advantages

Delete Command features and advantages:

• Command is for deleting the rows from the table.
• Can delete selective rows from the table using where clause.
• All rows can be deleted if no where clause is specified.
• It needs a commit or rollback command to complete the transaction or make it as permanent change.

How to Create Case Sensitive Password File in Oracle 11g?

In Oracle 11g DBAs can create the case sensitive passwords as well as case sensitive OS authentication which means you can create password file with case sensitive option. So once you upgrade your database to 11g you can implement case sensitive password files for better security.

$ orapwd  file=orapwprod9 entries=5 password=Welcome ignorecase=n

Oracle 11g Enable Case Sensitive Passwords For Improved Password Security

Prior to Oracle 11g, the passwords are not case sensitive. But in Oracle 11g we can enable case sensitive passwords by setting sec_case_sensitive_logon parameter value as TRUE. Once the parameter value set as true the user has to provide the password with proper case (upper, lower, mixed) as they created. The parameter can be altered dynamically.

Kochi Metro Railway Stations Route List

Below mentioned are the proposed stations for the Kochi Metro Railway. As per the plan there are 23 stations for the project.

1. Alwaye.
2. Polinchodu.
3. Companypady.
4. Ambattukavu.

How to Check and Find Out PF Account Balance and Details online? Portal Lauched on 01-07-2011with SMS

It has been long time employees are waiting for the online facility to check the PF details through internet. Yes. It has been released with details of limited states accounts on July 1^st of 2011. It is good news for the employees who are contributing to the Employee Provident Fund Organisation (EPFO) of India.

Oracle 11g Password : Relation between user$, DBA_USERS, PASSWORD_VERSIONS, spare4, IDENTIFIED BY VALUES

password_versions column in user$ table can have 3 types of values.

A value as 10G means the user is created in 10g and migrated to 11g version and password is never changed in 11g version.

A value as 11G represent the user password modified with alter user <username> identified by values ‘spare4’; command.

Explicit Vs Implicit Commit in Oracle– Difference and When it Occurs?

Difference between Explicit and Implicit commit:

When you submit a manual commit (Using Commit Command) after a DML (Insert, Delete, Update) statement, it is called explicit commit. Implicit commit is issued by Oracle database engine automatically after most of the DDL (alter, drop, create etc) execution. In other words, the commit does not need your interference.

How to Check Vehicle Registration Details Online? Goa, Kerala, Delhi

For Goa: http://www.goatransport.com/tax_dues.php

Oracle 11gR2 SQLPlus SET EXITCOMMIT ON/OFF

EXITONCOMMIT is a new set option with Oracle 11g R2 SQL*Plus. Prior to 11gR2, auto commit is on for all other version. But, in release 2 you can set whether you want autocommit during the SQL*Plus exit.

Example

SQL>  select * from dept;

How to hide oracle passwords - sqlldr, sqlplus, exp, imp, expdp, impdp from Unix ps command?

Option#1.

Pass the passwords through parameter file for the utilities exp, imp, expdp, impdp and sqlldr. This is the simple method.

$ vi parameter.pf

"parameter.pf" [New file]

userid=scott/tiger

directory=exp_dir

Export and Create Excel File from Oracle SQL Query Spool Output File

Most of the time, it is a common requirement to export the SQL*Plus spool output to the excel spreadsheet. Earlier I used to spool the SQL output to a text file and I use convert text data to Excel columns. It requires some additional effort. Please see the demo for simple method to create Excel file from sql spool output.

What is the datatype and precision of Oracle NULL column?

Do you know the datatype and precision of Oracle Null column?

It is Varchar2 with 0 as the precision.

SQL> create view test_vw as select null x from dual;

View created.

How to read (command) without enter in Linux bash shell?

By default the read command wait for a Enter Key to complete the command. But if  you –n 1 option it will not wait for the enter key. Once it counts one(n=1) key press in the standard input it will come out and assign the value to the variable.

Normal read command with enter

How to check the pending transaction in your oracle session?

There is one function step_id available with DBMS_TRANSACTION package. This function returns local unique positive integer that orders the DML operations of a transaction. If the function returns any positive number that mean you have a pending transaction in your session. Otherwise it returns null.

How to create dummy NULL column for a Oracle view using function?

Normally we cannot create a view with a dummy null column. The column defined in the view must be present in the base table. If the column is not present in the base tables Oracle will throw ORA-00904: "CARNAME": invalid identifier error. Here I am going to present a demo for creating a view with Null column using a function.

Script to increment or decrement all sequences in a schema by n=1000 numbers

In normal application development or during the application testing phases, at least in some rare situations we would have come across to increment all the sequence in a schema by n numbers. Manually incrementing the values are bit tedious job. You can use below mentioned script to achieve this goal.

Reset/Modify/alter Oracle sequence nextval, currval to new value without dropping

The simplest method to alter the Oracle sequence currval or nextval is drop and recreate the sequence with new “start with” value.

SQL> create sequence seq_example  start with 1001 increment by 1 cache 10;

Sequence created.

SQL> select seq_example.nextval, seq_example.currval from dual;

Find out the income tax (IT) ward/circle for income tax e-filing

It is the common doubt or question which is the income tax ward/circle. Even I had the same question while filling my ITR1.xls for e-filing. It is simple. If you have PAN number it is easy to find out. Here is the link to find out your IT Ward/Circle

https://incometaxindiaefiling.gov.in/portal/common.do?screen=jurisdiction

How to check file last modification or change date and time details in Linux?

In Linux, you can use stat command to check the change details of a file. Stat command displays file or file system status.

$ stat -L exe.logs

  File: `exe.logs'

  Size: 204654          Blocks: 408        IO Block: 4096   regular file

Device: 903h/2307d      Inode: 5703041     Links: 1

Oracle 11g Data Pump expdp compression option to reduce the export dump file

Oracle 11g provides different types of data compression techniques. Compression is the option to achieve the data compression in data pump. There are 4 options available with compression parameter.

• ALL: Both metadata and data are compressed.

Features and Advantages of Oracle 11gR2 Deferred segments – Segment Creation on Demand

• Deferred segment introduced in Oracle 11gR2 – 11.2.0.1

• It is known as segment creation on demand

• It is a space saving feature for the database and disks

Oracle 11g ASM (Automatic Storage Management) Features, advantages and benefits

ASM is built-in database file system which is used for database related objects like tables, indexes, tempfiles, backup sets, control files, parameter files, redo logs, archive logs, dump sets. ASM simplifies the Oracle database storage administration. It is the centralized way to administer Oracle database disk storage.

Data Pump expdp REUSE_DUMPFILE option: Overwrite existing dumpfile

This is the option with data pump expdp utility. Normally when you perform the export using expdp utility and if the dumpfile is present in the export directory it will throw an error “ORA-27038: created file already exists”. This situation happens when you wanted to perform the repetitive exports using the same dumpfile.

Connect to hanging database using sqlplus –prelim option in Oracle 10g, 11g

In real DBA life there would be several times we faced situations like not able to login to the database due to the ORA-00020 maximum number of processes exceeded etc. Database will not allow any connections during this kind hanging situations. Oracle provides an option in 10g onwards called preliminary connection.

How to install/apply/change XML template on new blogger interface

In this post I just wanted to explain about how to apply a new template to your blog with the new blogger interface. Currently the new blogger interface is available in http://draft.blogger.com/ and this is going to be the new blogger interface soon. It has significant improvement in terms of outlook, security and performance.

Easy and quick way to get Google Plus invites. Steps to get Google Plus invitation from my blog

As you aware Google introduced new social networking site with brand new feature like circles, hang outs, etc. Google Plus population crossed 20+ million already and many are waiting for the same. It might be a challenge for current leading social networking sites like FaceBook, Twitter, etc. Don’t wait to start using Google Plus. Get the invitation from my blog. You just need to following things and enjoy the Google Plus J

Troubleshoot fix ORA-28368: cannot auto-create wallet ORA-28353: failed to open wallet

Problem description:

Alter system set encryption key indentified by <password> command failing with Ora-28368 error.

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "miracle";

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "miracle"

*

ERROR at line 1:

ORA-28368: cannot auto-create wallet

How to setup Oracle 11g, 10g Transparent Data Encryption - TDE? Advantages, SALT Option

Oracle 10g: Transparent Data Encryption (TDE) introduced in Oracle 10g. It is feature to encrypt the sensitive confidential data. There is no need of change in the application logic to implement the same. The encryption can be applicable for index and data values on the disk. Oracle uses opened WALLET to generate master key for the entire database.

Troubleshoot and fix TNS-12560 TNS-00583: Valid node checking: unable to parse configuration parameters

Error description: Getting the following error message during the listener startup

LSNRCTL for Solaris: Version 11.1.0.7.0 - Production on 15-JUL-2011 10:32:37

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Starting /data/oracle/product/11.1.0/bin/tnslsnr: please wait...

Oracle 11g New Feature: Invisible index advantages for performance tuning

Oracle 11g introduced a new feature called invisible indexes. We can make an index to invisible mode when it is not needed. Once we make an index to invisible the index will not come to optimizer reach. The syntax for making an index to invisible is as below.

ALTER INDEX index_name INVISIBLE;

ALTER INDEX index_name VISIBLE;

Oracle INDEX hint : Different usage syntax for performance tuning

One of the most frequent uses of hints is to force a particular access path to be selected, and typically this means forcing the use of a particular index.

The simplest hint for forcing an index is the rather appropriately named INDEX hint. Typically, the INDEX hint is used to force the use of a particular index in this manner

Oracle 11g New Feature : SQLPlus Error Logging Features and advantages

In oracle 11g one new useful feature is added with SQL*Plus which is commonly used by the DBAs. Prior to oracle 11g we need to spool the sqlplus logs to keep track of the error outputs. But here is the advantage that we can simply turn on the ERRORLOGGING with SQL*Plus which will internally log all the errors into SPERRORLOG table.

Oracle 11g New Feature : Advantage of Shrink Temp Tablespace and Tempfile

The temporary tablespace will increase in size depends upon the amount of sorting happens on the database and it occupies the most of the space in the disk. Sorting might be happening occasionally and for that purpose there is no need of keeping huge amount of temporary tablespace. As and when needed we can increase the temp tablespace size.

CHAR vs VARCHAR Difference, Advantage in Performance Tuning

Please see some examples on char and varchar datatype.

create table example_Char (vchar_col1 varchar(10), char_col2 char(10))

SQL> /

Table created.

SQL>

insert into example_char values ('TEMP','TEMP')

SQL> /

Oracle 11g : Read-Only Tables New Feature & advantages

In Oracle 11g the read-only tables are introduced as new feature. Using this we can make a table to read-only and none of the DML operations are allowed after that. A read-only table can make read write anytime. In user_tables dictionary view one more column is added with Oracle 11g. See the demonstration below. The advantage of read-only option is we can use this during table maintenance as DML operations cannot change the table content.

EMCA dbconsole configuration failed with ORA-20001: SYSMAN already exists

Error Description: While creating the dbconsole repository using the command “emca -repos create” getting failed with CONFIG: ORA-20001: SYSMAN already exists

emca_2011_06_22_20_44_02.log

INFO: Creating the EM repository (this may take a while) ...

Data Pump impdp expdp : SQLFILE option to extract DDL and DML from dump file

Using data pump impdp utility we can generate sql or DDL/DML from the dump file using SQLFILE option. When you execute impdp with sqlfile option it won’t import the data into the actual tables or into the schema. Suppose if you wanted to generate some particular DDLs from the database you can use this option. Please find the example below with all syntaxes.

Data Pump impdp expdp NETWORK_LINK option : Transfer schema across database using db links without dump file!

Using the NETWORK_LINK option you can import the schema from source database to target database. One advantage of this option you don’t need export and import as it does the export and import in single shot from the source to destination. Also, the file system space is not needed to accommodate the huge dump files as we can directly import to target using network_link.

Oracle User Password Reset : Two different methods – SQL*Plus and alter user

Method 1: Using SQL*Plus password command

If you connected as sys and trying to reset the password for other user it will not ask for old password.

SQL> connect / as sysdba

Connected.

SQL> show user

How long needed For Google Adsense Approval for Blogspot? I Got In 2 Months for my new blog! 10 Guaranteed Tips!

Getting adsense approval will take atleast 12-48 hours from adsense team after you send a request. But some certain things you have to take care before sending a request for your fresh blog to the adsense team. In earlier days getting the Adsense approval was quite easy. That’s what I heard from my friends. But nowadays it is not easy to get approval from the Adsense team.

Les Paul Interactive Music Google Doodle – With record option still available with doodle collection

Google showed one of the wonderful doodle on Les Paul’s 96^th birthday. That was the first interactive doodle from Google. You can play the guitar and record. The recorded one can be played later J Whoever not seen this doodle or never tried to record your play, still you can see and enjoy the doodle in the Google doodle collection.

How the Optimizer chooses Between Indexes and Full-Table Scan

The rule-based optimizer will almost always favor an access path involving an index to one involving full-table scan. This is because in the absence of any information about the sizes of tables, the index-based access path is the safer choice. A full-table scan might be faster than an index lookup in some circumstances- say 50% faster.

Choosing the best indexing strategy

Oracle’s default index type - the B*Tree index is suitable for improving access for a wide range of queries. B*Tree indexes can optimize exact lookups and range quires and can sometimes be used to resolve queries in their own right and without reference to the underlying table.

Creating indexes on Nullable Columns

Its usually wise to define the columns referenced in the where clause as NOT NULL so that indexing these columns can be effective. However, it can be worthwhile to use NULL values in an indexed column if the following conditions apply:

• The column is almost always NULL.

Oracle 11g RESULT_CACHE Hint : Faster Performance Queries, Advantages, Benefits

RESULT CACHE: Prior to oracle 11g, database stated with caching the data block in memory and which used to construct the result set for user queries. Even though the data blocks are cached in SGA, it needs to be reconstructed for the query output which takes time and resource.