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 is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more
20 Mar 2019 - 2 minute read
How 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 dump it into a SQL file to modify and replay. Here’s how to do it: set head off set pages 0 set long 9999999 spool user_script.sql SELECT DBMS_METADA... Read more
20 Feb 2018 - 1 minute read
Restart 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 called a hung database and must be quickly resolved as the database isn’t accessible for your users anymore. As you cannot connect to the hung database, yo... Read more
20 May 2017 - 1 minute read
Resolve 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 the column exists you should be able to query it and not get this error. The invalid identifier error most commonly happens when you are referencing an... Read more
20 Apr 2017 - 1 minute read
How 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 can be a useful way to implement custom messages when you execute a stored procedure, or it can help with testing by generating alerts. SQL> exec... Read more
20 Mar 2017 - less than 1 minute read
PL/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 query a set of specific dictionary objects for example. Note that you can also make this static list a dynamically generated list. set serveroutput on ... Read more
20 Feb 2017 - less than 1 minute read
Index 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 and efficient. Will work flawlessly for small indexes (> 200-300 Mb) Alter index index_name rebuild tablespace new_tablespace_name; Alter index I... Read more
20 Jan 2017 - 1 minute read
Shared 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 adaptive cursor sharing was kicking in. I needed to flush that plan out of the shared pool without flushing the entire pool. Here’s how to do that. Get... Read more
20 Dec 2016 - 1 minute read
Sync 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. Artificially invoke it to catch up with the real maximum ID used. I advise using the first solution in most cases, as it will be instant and won’t waste ... Read more
20 Nov 2016 - 1 minute read
Install 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 license agreement and proceed to download the Linux ARM x32 version. Login to your Raspberry Pi and extract the archive in the /opt directory. $ sudo tar ... Read more
20 Oct 2016 - less than 1 minute read
Implement 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 generate an MD5 checksum in Oracle. However, the function exists in the Oracle supplied package dbms_obfuscation_toolkit, so here’s the way to make a usab... Read more
20 Aug 2016 - less than 1 minute read
Resolve 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 ---------------------------------------------------------- An uncaught error happened in fetching the records : ORA-01157: cannot identify/lock data ... Read more
20 Apr 2016 - less than 1 minute read
Kill 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 sessions locked the server load will increase. And there’s a risk of crashing the whole database if this happens to many user sessions at the same time. ... Read more
20 Mar 2016 - 1 minute read
Extract 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. Whether you keep the same tablespace configuration, or should you decide to change it. Here’s how to get the DDL necessary to check the structure or eventu... Read more
20 Dec 2015 - less than 1 minute read
Fixing 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, arguments: [ktfbtgex-7], [2031625], [128], [2031624], [], [], [], [] This error was related to extent management bitmap for locally managed tablespace... Read more
20 Nov 2015 - 1 minute read
SQL*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 packet. The server process knows that the call hasn’t ended and there is more data/packets to come before the call ends. Also, the large amount of data... Read more
20 Aug 2015 - 1 minute read
Registering 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 responsible for registration of oracle server with listener. Pmon process wakes up at every 60 seconds and provide information to the listener. If any pro... Read more
20 Jun 2015 - less than 1 minute read
Resolve PLAN_TABLE is old version error
#oracleWhen displaying execution plans on SQL*plus, you may notice an information message like so: ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ----------------------------------------------------------... Read more
20 Apr 2015 - 1 minute read
Check 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 database SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') AS "DB Startup Time" FROM sys.v_$instance; On Mysql database SHOW GLOBAL STATUS LI... Read more
20 Mar 2015 - less than 1 minute read
Change 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 the old one entirely, and reduce the total database size. In the following example, Temp1 is the current temporary tablespace, that we want to switch... Read more
20 Feb 2015 - 1 minute read
Enable 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 (Data Definition Language) refers to Create, Alter or Drop statements and is used to change the structure of database objects. Using parallel DML is us... Read more
20 Jan 2015 - less than 1 minute read
ORA-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 configured as a Standby one. Therefore, you can only open it as a Standby database. Simply start if with the following commands : sqlplus / as sysdb... Read more
20 Oct 2014 - less than 1 minute read
Oracle 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 off; SET linesize 0; SET newpage none; SET newpage none; SET pagesize 0; SET sqlprompt ''; SET sqlnumber off; SET sqlbl off; SET trim... Read more
20 Jul 2014 - less than 1 minute read
How 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 production environment. Because depending on how many lines are you going to modify between commits, and the amount of queries that will run during the scr... Read more
20 May 2014 - 1 minute read