Identify duplicate rows in a table and delete them

I create a table and insert some rows into the table t11. When i create id column with unique constraint,the data dictionary is not allowing to insert duplicates to the table. So we can either drop or disable the constraint on the id column I disable the constraint and somehow inserted the rows by mistakeContinue reading “Identify duplicate rows in a table and delete them”

How many log switches per hour is normal?

we had a production database for one of our cloudera application.The application users started scheduled and batch jobs on the database.There was a complaint from application team about the slow performance.When we diagnosed the logfile,we found out that there was frequent log switches and archive generation which filled FRA space very fast.So we had toContinue reading “How many log switches per hour is normal?”

Calculate the bandwidth of IO subsystem from OS linux

We can calculate the IO rate of the disk from the OS to get an estimate of read/write to and from disk. Locate a particular datafile on any of storage partition Use dd to read the datafile and write the output to a output file and use time to determine the time taken for roundContinue reading “Calculate the bandwidth of IO subsystem from OS linux”

./runInstaller: line 106: /: No such file or directory

Junior DBA’s struggle sometimes and scratch their head without knowing the cause of simple errors. Instead of trying to search for the error on internet,it is better to look at the error and identify the cause. I try to execute runInstaller from a directory path to install the grid software where the software files actuallyContinue reading “./runInstaller: line 106: /: No such file or directory”

Does hard parsing lead to sort in memory???

I was running a simple sql select query to fetch some details from a table.I generated an execution plan for the query to check for optimizers plan for the query processing I observe some interesting details on the execution plan results I can observe 3 sorts in PGA when i ran the query for theContinue reading “Does hard parsing lead to sort in memory???”

Drop online redolog group

It is very dangerous when we deal with online redolog files in database which records the active transaction changes into it and needed for media ,block and crash recovery based on their states We have four usual states of redologs in a typical oracle database 1)active 2)inactive 3)current 4) unused active – require for instanceContinue reading “Drop online redolog group”

TNS:listener could not find available handler with matching protocol stack

Client use the user process and try to connect to the oracle database where PMON monitors and registers the incoming connections. PMON updates the service to the listener every now and then ,when a new connection connect to the database. PROCESSES is the parameter which allocates the maximum load for a connection. When the loadContinue reading “TNS:listener could not find available handler with matching protocol stack”

How to avoid ORA-00018 maximum number of sessions exceeded

Oracle doc says PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes. SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login SESSIONS specifiesContinue reading “How to avoid ORA-00018 maximum number of sessions exceeded”

Why database remote connection fail?? ORA – 01017

I create a test user and grant basic connect privilege to the database I configure the tns entry and listener entry on remote server I try to connect to the rman from remote client !! I get ORA – 01017 error!! However i given the correct username and password with right service name I amContinue reading “Why database remote connection fail?? ORA – 01017”