Tag: oracle
Forcing ASMM component to shrink
#oracle #databaseHere’s the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can’t shrink. When the automatic shared memory management feature i... Read more
20 Mar 2019 - 2 minute readHow to extract Oracle user DDL
#database #oracleIn some cases, you need to duplicate an Oracle user, along with all it’s privileges. Doing this manually can be tedious. Hopefully, we can use the data dictionary to extract the data we need and du... Read more
20 Feb 2018 - 1 minute readRestart a hung Oracle database
#oracleOn rare occasions, mostly during a high server load peak or a process failure, an Oracle instance may not accept any connection. Either from regular users as well as SYSDBA. This situation is calle... Read more
20 May 2017 - 1 minute readResolve the ORA-00904 invalid identifier error
#oracleEventually, you’ll issue a valid SQL statement and SQLPlus* will return the error ORA-00904 invalid identifier. First, make sure it’s not a typo and you typed a valid and existing column name. If t... Read more
20 Apr 2017 - 1 minute readHow to write to the alert log
#oracleJuste in case you wondered, yes it is possible to write custom messages directly to the alert log. For this, you’ll have to use the procedure ksdwrt stored in the dbms_system supplied package. This... Read more
20 Mar 2017 - less than 1 minute readPL/SQL loop through a set of values
#oracle #plsqlHere’s a simple trick on how to make a PL/SQL loop iterate through a set of predefined values. This can be useful when you need to perform a report query on a set of Oracle user account, or to quer... Read more
20 Feb 2017 - less than 1 minute readIndex managment
#oracleIn this article, I’ll show you various ways to move an index to another tablespace when you need to reorganize database objects or free some space. Move an index on a different tablespace Simple ... Read more
20 Jan 2017 - 1 minute readShared pool purge
#oracleA few days ago, I was trying to optimize a single SQL statement in a production environment. The statement was using bind variables, so the plan would change to a different (sub-optimal) plan as ad... Read more
20 Dec 2016 - 1 minute readSync Oracle sequences
#oracleIn a database copy scenario, or to fix an applicative bug, you may have to resync sequences. Basically, there’s two ways to achieve this: Drop and recreate the sequence. Artifici... Read more
20 Nov 2016 - 1 minute readInstall Oracle JDK 8 on Raspberry Pi
#oracle #javaIn this post, I’ll show you how to download and install Oracle JDK 8 on a Raspberry Pi. Visit Oracle download website and click the download button for Java Platform (JDK) 8. Then accept the licen... Read more
20 Oct 2016 - less than 1 minute readImplement an MD5sum function in Oracle
#oracleA few days ago, I was looking for a way to get an MD5sum function to work directly in Oracle, like the MD5() function in MySQL. After some searching I found out that there was no direct way to gene... Read more
20 Aug 2016 - less than 1 minute readResolve the ORA-01157 Error
#oracleAnother day at the office, I was investigating a performace issue when I encountered the following error: Select count(*) from purchase.table where CREATION_DATE > SYSDATE - 40; Execution Plan... Read more
20 Apr 2016 - less than 1 minute readKill locked sessions
#oracleSometimes, when there’s many database sessions connected and depending on the user activity, locks can occur on a database. While this may only affect a few users, the problem is that with many ses... Read more
20 Mar 2016 - 1 minute readExtract tablespace DDL
#oracleShould you need to perform a large data export and import on different Oracle databases, you’ll need to make sure that the tablespace configuration matches your export and import parameters. Wheth... Read more
20 Dec 2015 - less than 1 minute readFixing the ORA-00600: [ktfbtgex-7] error
#oracleIt was a normal day at the office, when I noticed an ORA-00600 on a production database. Upon investigation, I found the following details in the alert.log file: ORA-00600: internal error code, ar... Read more
20 Nov 2015 - 1 minute readSQL*Net more data from client
#oracle #networkThe error SQLNet more data from client* usually happens when there’s a large amount of data sent from client (or other database in case of dblinks), which doesn’t fit into single SDU size Oracle pa... Read more
20 Aug 2015 - 1 minute readRegistering with the listener
#oracle #networkWhen restarting a database, if you can’t acess it from outside, you may have to associate the newly restarted database with the listener. The Pmon process that is started with the instance is respo... Read more
20 Jun 2015 - less than 1 minute readResolve PLAN_TABLE is old version error
#oracleWhen displaying execution plans on SQL*plus, you may notice an information message like so: ------------------------------------------------------------------------------------------------- | Id ... Read more
20 Apr 2015 - 1 minute readCheck database uptime
#database #oracle #mysqlSometimes you have to check when a database was started for the last time. To get the information, just log on to the database and use the following query to get the last startup time: On Oracle d... Read more
20 Mar 2015 - less than 1 minute readChange the default temporary tablespace
#database #oracle #sqlWhen your temporary tablespace gets full and you can’t resize it, you may consider switching ti a new ont to reclaim disk space. Changing the default temporary tablespace will allows you to discard... Read more
20 Feb 2015 - 1 minute readEnable parallel DML
#oracle #sqlEffectively using parallel DML can speed up data manipulation. By the way, DML stands for (Data Manipulation Language), and refers to Insert, Update and Delete which plays at the row level. DDL (D... Read more
20 Jan 2015 - less than 1 minute readORA-01666 When Activating a Physical Standby Database
#oracle #standbyYou’re waking up a Physical standby database, and are welcomed by the following ORA-Code: ORA-01666: controlfile is for a standby database Don’t panick, that’s just because the database has been... Read more
20 Oct 2014 - less than 1 minute readOracle sqlplus silence output
#oracle #sqlplus #databaseUse the following code on your scheduled Oracle scripts to silence terminal output except your data. SET autocommit off; SET echo off; SET feedback off; SET head off; SET heading ... Read more
20 Jul 2014 - less than 1 minute readHow to prepare a DML list script
#database #oracle #scriptSometimes, you need to execute a lot of DML instructions on a database. This might look trivial but there are some very important elements to consider before running this kind of script on a produ... Read more
20 May 2014 - 1 minute read