CTAS - With few additional columns apart from the query

May be once a while requirement but simple trick to remember though...

Wanted to create a table with CTAS (Create Table As Select) to avoid the performance issues using insert statement. But, wanted to create table with few additional columns also instead of just the columns from select statement.

For Ex,.:

Existing Table (EMP) Structure:

Emp_ID number(6)

Emp_Name varchar2(50)

Dept_No number(4)

CTAS Table (NEW_EMP) Needs to Have columns like this:

Emp_ID number(6)

Emp_Name varchar2(50)

Dept_No number(4)

Salary number(8,2)

SQL:

create table new_emp as select emp_id, emp_name, dept_no from emp; -- will only gets those three columns select in the sql.

Following SQL will create the table with four columns with the data type that is needed also.

create table new_emp as

(select emp_id, emp_name, dept_no, cast(1 as number(8,2)) salary

from emp);

cast(1 as number(8,2)) salary --> this trick can be used not only to create a new column but also can be used to modify the existing column data type.

For ex., if the new_emp table emp_id should be of number(9) instead of the source table type number(6), then use the query as showed below:

create table new_emp as

(select cast(emp_id as number(9)) emp_id, emp_name, dept_no, cast(1 as number(8,2)) salary

from emp);

Hope this helps me in future when i get old... :)

Changing the Database Name in 10g

Changing Database ID and Name is just so easy as 1,2 and 3...

Here it goes:

NID --> is the command that is used to change the database name.

Let's say I need to change the database name from SAN1 to SAN2.

Steps to follow:

01. No need to say Backup is important correct !!!

02. Shutdown the database that needs a database name change

03. Startup in MOUNT mode

04. Run NID command:

nid TARGET=sys/sys_password_here@SAN1 DBNAME=SAN2

In the above command:

nid --> Oracle Command Tool DBNEWID

Target --> The database connection information for the source database that needs a name change.

DBNAME --> Its the New name for your database

05. From the NID command, it will prompt you to make up your mind before renaming the database. Respond according to your mind set to that prompt and continue.

06. Once its done, you need to set your db_name parameter in the spfile.

07. Make sure to set your oraenv is still pointing to the OLD DB Name (SAN1)

08. So, Start the database in MOUNT mode. (dont worry about ORA-01103 here)

09. change the spfile:

SQL> alter system set db_name=SAN2 scope=spfile;

10. Come out from sqlplus and create a password file using ORAPWD utility

11. Copy/Change your initfile (that refers to your spfile) name to reflect the NEW DB Name

12. Connect to sqlplus and start the database in MOUNT mode

13. Now Open with resetlogs:

SQL> alter database open resetlogs;

14. Last but least, reload the listener:

lsnrctl reload

15. Now the database name is SAN2 and Listener status also shows its active and you are good to go.

Following is the run thru of the above steps:

/oracle/product/10.2.0/db_1:(+ASM)$ . oraenv

ORACLE_SID = [+ASM] ? SAN1

/oracle/product/10.2.0/db_1:(SAN1)$ s

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 26 11:34:31 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size 2083656 bytes

Variable Size 704644280 bytes

Database Buffers 536870912 bytes

Redo Buffers 14692352 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

/oracle/product/10.2.0/db_1:(SAN1)$ nid TARGET=sys/lmao_sorry@SAN1 DBNAME=SAN2

DBNEWID: Release 10.2.0.4.0 - Production on Wed Aug 26 11:36:05 2009

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

Connected to database SAN1 (DBID=3609722023)

Connected to server version 10.2.0

Control Files in database:

+DGROUP1/SAN1/control01.ctl

+DGROUP1/SAN1/control02.ctl

+DGROUP1/SAN1/control03.ctl

Change database ID and database name SAN1 to SAN2? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 1234567890 to 0987654321

Changing database name from SAN1 to SAN2

Control File +DGROUP1/SAN1/control01.ctl - modified

Control File +DGROUP1/SAN1/control02.ctl - modified

Control File +DGROUP1/SAN1/control03.ctl - modified

Datafile +DGROUP1/SAN1/datafile/system.350.695906669 - dbid changed, wrote new name

Datafile +DGROUP1/SAN1/datafile/undots1.1361.695906675 - dbid changed, wrote new name

Datafile +DGROUP1/SAN1/datafile/sysaux.348.695906679 - dbid changed, wrote new name

Datafile +DGROUP1/SAN1/datafile/users.1201.695906683 - dbid changed, wrote new name

Datafile +DGROUP1/SAN1/datafile/tools.345.695906685 - dbid changed, wrote new name

Datafile +DGROUP1/SAN1/tempfile/temp.1205.695906681 - dbid changed, wrote new name

Control File +DGROUP1/SAN1/control01.ctl - dbid changed, wrote new name

Control File +DGROUP1/SAN1/control02.ctl - dbid changed, wrote new name

Control File +DGROUP1/SAN1/control03.ctl - dbid changed, wrote new name

Instance shut down

Database name changed to SAN2.

Modify parameter file and generate a new password file before restarting.

Database ID for database SAN2 changed to 169189862.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

/oracle/product/10.2.0/db_1:(SAN1)$ . oraenv

ORACLE_SID = [SAN1] ? SAN1

/oracle/product/10.2.0/db_1:(SAN1)$ s

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 26 11:41:33 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size 2083656 bytes

Variable Size 704644280 bytes

Database Buffers 536870912 bytes

Redo Buffers 14692352 bytes

ORA-01103: database name 'SAN2' in control file is not 'SAN1'

SQL> alter system set db_name=SAN2 scope=spfile;

System altered.

SQL>

$> orapwd file=/oracle/product/10.2.0/db_1/dbs/pwdSAN2 password=lmao_sorry entries=10

$> Copied existing initSAN1.ora to initSAN2.ora

/oracle/product/10.2.0/db_1/dbs:(hmm9test)$ . oraenv

ORACLE_SID = [hmm9test] ? SAN2

/oracle/product/10.2.0/db_1/dbs:(SAN2)$ s

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Aug 26 11:52:46 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size 2083656 bytes

Variable Size 704644280 bytes

Database Buffers 536870912 bytes

Redo Buffers 14692352 bytes

Database mounted.

SQL>

SQL> alter database open resetlogs;

Database altered.

SQL>

/oracle/product/10.2.0/db_1/dbs:(SAN2)$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 26-AUG-2009 11:54:29

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

The command completed successfully

/oracle/product/10.2.0/db_1/dbs:(SAN2)$

olsnodes hangs!!!

Alright, our SA team wanted to change the SAN storage and had to reboot the servers. Once the RAC servers got rebooted, it wont let me get to the prompt when trying to login with Oracle user.

After debugging a while, I found that its due to . oraenv in the .profile thats halting my login. So, removed . oraenv with in the .profile and loged in.

Now, its time to digg in to see why . oraenv is halting.

I had my own peice of code kept in that oraenv file to get the node# by using olsnodes command and thats where its halting the process!!!

Strange isnt it. Generally olsnodes command should return the number of nodes in the RAC in seconds. When I run olsnodes it just hangs and never comes back.

Found this metalink id: 729349.1. !!!an unpublished bug#6004127!!!

Basically, what its doing is going thru all the log files under $CRS_HOME/log//clients/ directory to create a new css*.log file and my box had too many to halt the command :)

So, remove all those log files from that folder and olsnodes works like a champ...

Long running Update SQL

Not the first time that I have done such thing(s) but this time I decided to write it here.

There was this update statement which was written quite efficiently to update a table rows by matching with another table entries. The other table will have the original rowid of the main table that's being updated (basically need to modify the main table column type so had to create a temp table with all the records for those columns and stored the rowid also to make the later update faster).

So, when the table ddl operation is done, the update statement triggers and even with the rowid search criteria the update was taking more than one hour (killed the process after an hour).

So, made that as a pl/sql block by updating a single row at a time in a loop and it finishes in less than a minute.

So, pl/sql block to update a table is not bad as every body thinks. Of-course its like a small code rather than a single SQL but its sure very efficient as the single update statement has to do all searching, matching and updating in the memory where as the pl/sql block don't have to deal all of the records at once.

update sql:

update table1 a

set (a.col1, a.col2) = (select b.ncol1, b.ncol2

from temp_table1 b

where a.rowid = b.org_rowid)

where exists (select 1 from temp_table1 where a.rowid = org_rowid);

pl/sql Block:

begin

for i in (select org_rowid, ncol1, ncol2 from temp_table1) loop

update table1

set col1 = i.ncol1, col2 = i.ncol2

where rowid = i.org_rowid;

end loop;

end;

Hope this might help to some1.

I really appreciate if there is any better of handling this.

Changing the SQL Prompt to reflect the user and db connected to:

Ever wonder which database you connected to with what user when you are busy changing the connections in SQL*Plus?

Here is the simple trick that shows you the user you connected as on the current database. Also note that the values will change automatically when you make another connection from the same window using "connect".

set sqlprompt "_user'@'_connect_identifier: SQL>"

SQL> set sqlprompt "_user'@'_connect_identifier>"

HR@test: SQL>

Now change the connection:

hr@test: SQL> connect san@prod

Enter password:

Connected.

SAN@prod: SQL> connect san@test2

Enter password:

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

Warning: You are no longer connected to ORACLE.

@: SQL> 

Hope this trick comes in handy.

BTW, this trick is from 10g onwards only.

PZnn Processes!!!

Dont know dont ask but these PZ99 and PZ98 processes contributed in loosing my hair!!!
Never heard of them before and ever cared to see what those are untill I recently found that the little tool that I wrote to kill all the sessions in the database was failing in RAC databases (you can find that in my earlier posts).

This little tool was suppose to go and kill all the sessions that are connected in the database prior to doing any upgrades and they started failing.
When I looked at there are these processes PZ99 and PZ98 sitting in gv$session and thier serial# keeps changing everytime you run a query against gv$session view and thus my little killing a session in RAC fails as the serial# is changed!!!

Anyways, what are these PZnn processes?
These are PQ slaves used for global views that are RAC Parallel Server Slave processes, but they are not normal parallel slave processes, PZnn processes (starting at 99) are used to query GV$ views which is done using Parallel Execution on all instances, if more than one PZ process is needed, then PZ98, PZ97,... (in that order) are created automatically.

RAC - Commonly used Terms and Utilities

. InShort Meaning Details . RAC Real Application Clusters Is Oracle Implementation of Clustered Database Instances. . CRS Cluster Ready Services Oracle's own cluster ware tightly coupled with Oracle Real Application Clusters . OCFS Oracle Cluster File System Oracle File system Implementation For Linux. . PCP Parallel Concurrent Processing PCP allows concurrent processing activities to be distributed across multiple nodes in an RAC environment, maximizing throughput and providing resilience to node failure. . OCR Oracle Cluster Registry The OCR contains cluster and database configuration information for RAC and Cluster Ready Services . ASM Automatic Storage Management Simplifies database administration by enabling you to create disk groups and manage them instead of individual datafiles. . ASMCMD ASM Command Utility ASM Command Utility to monitor ASM . SSH Secure Shell SSH is a set of standards and an associated network protocol that allows establishing a secure channel between a local and a remote computer. . RSH Remote Shell . DSA Digital Signature Algorithm The Digital Signature Algorithm . SCSI Small Computer System Interface Standard interface and command set for transferring data between devices commonly used for interfaces for hard disks, and USB and FireWire connections for external devices. . NAS Network-attached storage Is a dedicated data storage technology that can be connected directly to a computer network to provide centralized data access and storage to heterogeneous network clients. . NFS Network Filesystem Its a protocol allows a user on a client computer to access files over a network as easily as if attached to its local disks. . OSCP Oracle Storage Compatibility Program An Oracle Program To assist third-parties with their Oracle compatibility with respect to storage devices. . DTP Distributed Transaction Processing DTP model is the synchronization of any commits and rollbacks that are required to complete a distributed transaction request. . VIP Virtual IP Address Its an IP address that is not connected to a specific computer or network interface card . TCP/IP Transmission Control Protocol TCP . DNS Domain Name Server The domain name system is responsible for translating it translates domain names . MAC Media Access Control Media Access Control address

. JDBC Java Database Connectivity A Java API that allows independent connectivity between the Java programming language and a wide range of databases. . LB Load Balancing Is a technique to balance the load between configured servers. . FAN Fast Application Notification Enables end-to-end, lights-out recovery of applications and load balancing when a cluster configuration changes. . FCF Fast Connection Failover Fast Connection Failover provides the ability to failover connections in the connection cache as quickly and efficiently as the database itself. . TAF Transparent Application Failover Using transparent application recovery . OCI Oracle Call Interface OCI allows you to develop applications that take advantage of the capabilities of SQL from within the application. . ODP Oracle Data Provider ODP.NET features optimized data access to the Oracle database from a .NET environment. . ONS Oracle Names Server Oracle Names makes network address and database link information available to all nodes throughout the network. . ORION Oracle I/O Numbers ORION is a test tool freely available tool which simulates Oracle I/O. . GSD Global Services Daemon The Global Services Daemon . NTP Network Time Protocol The Network Time Protocol . CVU Cluster Verification Utility CVU is used to verify all the important components that need to be verified at different stages in a RAC environment. . VSD Virtual Shared Disk A Virtual Shared Disk . LPARs Logical Partitions Logical Partition, commonly called an LPAR, is a virtualized computing environment abstracted from all physical devices. . DBCA Oracle Database Configuration Assistant An Oracle utility that facilitates the creation and configuration of a database. . ORACM Oracle Cluster Manager Allows to add new nodes to an existing cluster without having to stop/start the whole cluster. . RDA Remote Diagnostic Agent An Oracle Diagnostic Tool used in troubleshooting. . GFS Global Filesystem GFS allows a cluster of Linux servers to share data in a common pool of storage. . NIC Network Interface Controller Its a piece of computer hardware designed to allow computers to communicate over a computer network. . LVM Logical Volume Manager

. GPFS General Parallel File System GPFS is IBM’s high-performance parallel, scalable file system for IBM UNIX clusters. . HA High Availability . MAA Maximum Availability Architecture . RACDDT RAC Data Collection Tool Is a data collection tool designed and configured specifically for gathering diagnostic data related to Oracle's Real Application Cluster . OSW OSWatcher Is an operating system diagnostic utility tool that gathers archival performance data using various native Unix utilities, such as vmstat, iostat and top. . OLS Oracle Label Security Oracle Label Security is developed based on virtual private database . RSM Remote Shared Memory Is a feature that bypasses the UDP/IP communication in Solaris. . UDP/IP User Datagram Protocol Can send short messages sometimes known as datagrams to one another. . TNS Transparent Network Substrate Allows peer-to-peer connectivity where no machine-level connectivity can occur. . EMCA Enterprise Manager Configuration Assistant An Oracle tool to set up the Enterprise Manager. . OPS Oracle Parallel Server The OPS option allows multiple instances on different computer systems . SRVCTL Server Control A utility to assist in administration and maintenance of RAC databases. . CRSCTL CRS Control A utility to assist in administration and monitoring of CRS. . CRS Commands . CRS_STAT List the status of resources . CRS_GETPERM Checks the permissions that are associated with each resource . CRS_PROFILE Creates, validates, deletes, and updates an Oracle Clusterware profile . CRS_REGISTER This command registers configuration information for an application with the OCR . CRS_RELOACTE Used to relocate an application profile from one node to another. . CRS_SETPERM Sets and modifies permissions associated with a resource

How to set autotrace in SQL*Plus...

Setting autotrace allows displaying some statistics and/or a query execution plan for DML statements.

Set autotrace on	Shows the optimizer execution plan as well as statistics of the statement
Set autotrace on explain	Shows only the optimizer execution plan
Set autotrace on statistics	Shows only the statistics
Set autotrace traceonly	Like SET AUTOTRACE ON, but doesn’t print a query result.
Set autotrace off	Disables all autotrace

Combinations:

set autotrace off

set autotrace on

set autotrace traceonly

 

set autotrace on explain

set autotrace on statistics

set autotrace on explain statistics

set autotrace traceonly explain

set autotrace traceonly statistics

set autotrace traceonly explain statistics

set autotrace off explain

set autotrace off statistics

set autotrace off explain statistics

 

If autotrace is enabled with statistics, then the following statistics are displayed:

recursive calls

db block gets

consistent gets

physical reads

redo size

bytes sent via SQL*Net to client

bytes received via SQL*Net from client

SQL*Net roundtrips to/from client

sorts (memory)

sorts (disk)

Additionally, there is rows processed which is not found in v$statname.

Prerequisites:

The explain plan feature of autotrace requires a plan_table which can be created with$ORACLE_HOME/rdbms/admin/utlxplan.sql

The statistic feature requires that the user is granted select on v_$sestat, v_$statname, v_$session. 

An Oracle installation comes with $ORACLE_HOME/sqlplus/admin/plustrce.sql which installs the role plustrace. plustrace is granted those select rights. If plustrace is granted to a user, he will then be able to turn autotrace on. Alternatively, plustrace can be granted to public.

SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.

If flagger is set to something different than off, it's not possible to issue a set autotrace.. in SQL*Plus, it will throw an SP2-0575: Use of Oracle SQL feature not in SQL92 Entry Level.. In this case, flagger must be turned off:

Set flagger off

Storing settings across sessions:

Settings (such as autotrace) can be stored across sessions with the glogin.sql and/or login.sql file. 

Is that database affected with "Bind Peeking"!!!

Complaint:
The procedure that was written couple of years ago was running fine and smooth until we upgraded to 10g (Rel#1).
Even, in 10g it was fine for the first few weeks and then slowly started seeing lot of slowness in the system.
The stats found out this particular sql as the main culprit.
Amazingly 3GB TEMP tablespace in 8i is not enough in 10G. The TEMP tablespace grows even upto 24G (well thank god thats the max limit they set) and then fails.
Whats going on...
Din't think about this so called feature "bind peeking" from 9i but started looking into the culprit sql.
The SQL is not that complex. It has group by and one sub query. Thats all. And yeah, this SQL is the cursor though. Since its a cursor, there is a variable assignment for a where clause to filter out the records and that variable is of "date" type and the column compares this variable also is of "date" type in the table. So, everything is good there and I don't have to go for converting my variable to to_date etc.,
Well, so, why is that this particular SQL taking too much of TEMP tablespace!!!
Ran the query alone by passing the actual value instead of the variable in where clause to see how many records I get. Interestingly I get only couple of hundred rows.
That made me to think of the variable being binded in the cursor at the runtime.
So, this time my test was little different. Made a pl/sql block with the cursor and passed the variable value at run time (basically pulled out the portion of the code from the procedure) and the query hangs while being busy in increasing the temp space.
Removed the variable in the where clause and changed to get the value directly from the table instead of assigning that at the run time. Boom... query comes back in seconds...
So, my guess was right. Its the bind variable which was causing the issue and some how oracle keep increasing the temp tablespace.
Solution:
Basically the main issue here was to do with the so called feature "bind peeking". In this example while oracle was trying to get the bind variable value from the list when it was issued at the very first time after the database was started and never looked into the current value set. So, the data set returning was huge...
Well, time to disable this feature "bind peeking".
Here is the way to disable it:
There is an Oracle Hidden parameter which allows to disable it and that parameter is
"_optim_peek_user_binds" by default it is enabled.
alter system set "_optim_peek_user_binds" = false scope=both; --> system wide immediate effect and also modifies the spfile
alter session set "_optim_peek_user_binds" = false --> only for the session so that you can test your code.

Good luck with "bind peeking".

Killing a Session in RAC database

I wonder why oracle not thought about it and implemented a mechanism to kill a session in RAC database. Killing a session in oracle database is easy with "alter system kill session". This works gr8 in normal databases.

I had a situation where I had to kill a database session in RAC environment and I was keep getting error stating that the specified sid and session could not be found.

That's when I realized that the instance that I connected to kill a session is different than the session instance.

Hmm...

So, now I need to find the instance where the session exists and then make a connection to that instance and then issue a command to kill that session... what a pain...

Its ok as long as you need to kill session once in a while but not often (don’t ask me why do you need to kill sessions so often as all the work environments are not same).

So, wrote a small package to accept sid, session and instance# and it will take care of killing that session irrespective of instance you connected to it.

Sweet, now I don’t have to wait for Oracle to come with a mechanism anymore!!!

Drop me a note if you are in need of the code and I shall send it you.

Single Row table performs better with WHERE clause!!!???

Yeah I know it sounds funny. 

But its true.

Had a customer who was reporting slowness in the system and some one performed the AWR reports and found that this sql is doing tremendous Logical I/Os in the system.

The SQL is very simple. As said, this table will only have one row at any given time (mind you no inserts and deletes also) and the statement is “select column_name from table_name;”.

So, the request comes to change the SQL to have a where condition so that Oracle can do Index scans instead of FTS!!!

Answer to that request: a very big NO

So started pulling hairs to see why there are so many Logical I/O’s on a single row table which is not even a kilo byte size… almost 1025 buffer gets per execution and this sql runs hundred/thousand times in an hour so.

Since it was doing 1025 buffer gets per execution, they tested with a where clause and the buffer gets value drops down rapidly and thus the request comes in to add where clause.

Well, the mystery is resolved.

This particular table usage shows 1025 USED BLOCKS instead of 1 and thus FTS is doing 1025 buffer gets every time the data is read.

Why the usage is shows 1025??? No answer. Well, pretty simple reason. Some one must of performed heavy insert and then deleted the rows by keeping only the required row and thus the high water mark stays at 1025.

Solution provide was: take the backup up of the record and insert it back by truncating the table and then run the stats to see if FTS still does 1025 buffer gets.

Hurray, it only does 5 now…. Yeah… the problem is resolved.

So you still wants add where clause to a single row table SQL? Naa…. I guess we are fine….