Ask Anantha

I got a following query from Suvankar Das. "I need to unload few Oracle tables with average data volume of 15M records to flat files. Is the SQL* Plus spool a good option for this volume of data from performance point of view OR is it better to do through PL/SQL UTL_FILE option." And this is how I responded: "SQL* Plus will have the fastest result to the flat file. Only use UTL_FILE if you need some more programmatic control in between the spooling. To make sure first take a sample set of data (some thousands of records) for spooling using SQL *Plus and do the same using UTL_FILE. Compare both the timings. Also based on the sample estimate the time for the entire 15M records." The Verdict: There is no hard rule that says either SQL *Plus or UTL_FILE will perform faster. It varies from environment to environment. Where SQL *Plus/UTL_FILE could perform faster SQL *Plus is a client program which is accessing Oracle database. Therefore it highly depends on the network. ...

Today I was answering one of the queries, this question seriously made my interest. The question was "I have 15 million records in my table. What do you suggest a best method for unloading them from Oracle table to a flat file". Seriously till that time I had never done this. The need had not come. I clearly know two ways of doing this: Using SQL Plus and SPOOL command Using UTL_FILE built-in package UTL_FILE vs SPOOL I know that SPOOL command will be faster in execution than UTL_FILE package , but also I reminded myself that this may not be the case always. SPOOL commands operates in the client machine and depending upon the network traffic it might take time for the SPOOL operation than UTL_FILE.  SO I ADVICE TO TEST BOTH OPTIONS IN YOUR ENVIRONMENT BEFORE FINALISING ON ONE OF THE ABOVE. I will soon publish routines for this purpose. Your patience is appreciated. More resources for your inquisitive mind A little surfing did a wonderful job in putting this article up. In ask...

Q: How to enter a single quotation mark in Oracle? Ans: Although this may be a undervalued question, I got many a search for my blog with this question. This is where I wanted to address this question elaborately or rather in multiple ways. Method 1 The most simple and most used way is to use a single quotation mark with two single quotation marks in both sides. SELECT 'test single quote''' from dual; The output of the above statement would be: test single quote' Simply stating you require an additional single quote character to print a single quote character. That is if you put two single quote characters Oracle will print one. The first one acts like an escape character. This is the simplest way to print single quotation marks in Oracle. But it will get complex when you have to print a set of quotation marks instead of just one. In this situation the following method works fine. But it requires some more typing labour. Method 2 I like this method personally becaus...

Oracle Tips by Burleson Consulting Today, systems with thousands of individual Oracle user ID's are not very common.  Almost all enterprise applications (ERP packages like SAP, Oracle Financials) use pre-spawned Oracle connections that are created by a single Oracle user ID with names like AR or SAPR3. The application server manages the connections to Oracle for us. This is called a "proxy", the term proxy meaning an act where a 3rd party does something on our behalf.   Unfortunately, when our end-users connect anonymously through a proxy, we do not have any end-user level security through traditional security tools like granting privileges or using roles and the only Oracle security is that is granted the ERP "master" user ID. Who goes there? From a DBA perspective, all database activity is being done by this single user and a query of the v$session view for an ERP does not identify any specific user, because they are connected to Oracle via the proxy o...

This mini tip comes from my friend Guruswamy.   How do I pass parameters to a SQL-Script?   Well the answer is simple, simply separate them with a space. For example: SQL> @script.sql parameter1 parameter2 parameter3   How do you access the parameters inside the SQL file? Refer the parameters as &1, &2, and &3.