AskTom - Hot Questions

Subscribe to AskTom - Hot Questions feed AskTom - Hot Questions
These are the questions on Ask Tom that have the most comments
Updated: 48 min 57 sec ago

TNS Issues when connecting to database with Forms Builder

Sun, 03/19/2017 - 00:00
I am getting the following issue when trying to connect to the database with Oracle Forms Builder ( ORA-12154: TNS: Could not resolve the connect identifier specified. I am using: username: hr password: hrpassword database: pdb...

Daily Insert, Delete on table

Mon, 03/06/2017 - 00:00
I have a table tb_card_data CARD_NO TYPE EMAIL MODULE CREATED_ON SEQ_NO ------- ---- ----- ------ ---------- --------- 12345 C MOD_1 06/03/2017 20170306000001 (create_on + sequence number) .. so on around 500k records for MOD...

I have to import the specific tables using DBMS_DATAPUMP PACKAGE . How to exclude the unwanted tables ?

Thu, 03/02/2017 - 00:00
Hi Tom , I have used the below import par file to exclude the tables those names are with $ DIRECTORY=FEB09_E DUMPFILE= MXPRD_01feb09_%U.dmp LOGFILE=IMP_EDRPRE.log REMAP_SCHEMA=MXPRD:MAXIMO remap_tablespace=MXPRD_DATA:MAXIMO_DATA,MXPRD_INDE...

Optimizer Dynamic Sampling at OPTIMIZER_DYAMIC_SAMPLING level 2

Wed, 02/15/2017 - 00:00
At my Oracle Oracle Standard Edition database, at OPTIMIZER_DYNAMIC_SAMPLING = 2 which is the default, I see Dynamic Sampling is happening for some of the SQLs for which all tables already have already gathered table statistics. Due to this,...

How to extract substrings enclosed within double quotes from a string

Sun, 02/12/2017 - 00:00
I have a requirement to extract column names (there could be multiple instances) from a SQL string. The column names will always be enclosed within double quotes. e.g. "le code" = 802 AND "principal amount" > 1200 OR "branch id" = 'ABC' <b>Req...

What is your salary ?

Fri, 02/10/2017 - 00:00
Tom, WHat is your salary? Thanks

Shuffle values randomly in columns

Tue, 02/07/2017 - 00:00
Note - this is more of an academic question as I have a resolution, I am just keen to see whether my alternative approach is possible. I have a HR table with a list of names. For demo purposes I needed to shuffle these around to make them a bit mo...

Insert Into table_name Select for table with CLOB column is very slow.

Wed, 02/01/2017 - 00:00
Hi Sir, I have a activity for making non partition table to partition table, the table has clob column i was using below approach to fulfill the activity. rename existing table to _old create new partition blank table insert into new blank pa...

SQL Query Help Required

Tue, 01/31/2017 - 00:00
I'm new to Oracle and have got a requirement from my client as follows. Eventhough I have got the PL/SQL solution for this requirement, I'm interested in understanding whether a SQL based solution is possible rather than a PL/SQL approach. If yes, p...

Concat all columns

Tue, 01/31/2017 - 00:00
Hello Tom. I want to concat all columns of a row into one string. <code>select * from table</code> should bring out one colum per row, including all fieldvalues as one string. The use auf || didn't work, because I want it for different table...

Finding if record is overlap or not

Sun, 01/29/2017 - 00:00
Hi Tom, Hi Tom, Could you please help me with the SQL in finding if the record is a overlap or not. Here is the test case.. seq_val is calculated first and would like to check overlaps based on that order.. ID Start_dt end_dt Seq_val 1 01/0...

Memory usage in heavily partitioned database

Fri, 01/27/2017 - 00:00
Hi From the depths of internet I have found sql-statement which I slighly modified. Purpose was to find out what objects exists in the buffer cache and many blocks they were using. </> with t1 as ( select o.owner, o.object_name ...

Performance and commits

Fri, 01/27/2017 - 00:00
Hi team, I have a simple question. Let's say i have 20,000 txn for one session and i have multiple sessions say 10. So is it good to mention commit frequency as 300-400 in code for better performance. Please let me know as what i am follo...

Fetch Ref cursor to another ref cursor

Thu, 01/19/2017 - 00:00
Hi, First of all Thank you Gurus! for your time and effort to solve user issues. I have a requirement where I have to consume first row of the big query result set and fetch the rest of the result into a refcursor(for some reason I can't change...

Edition based redefinition- Data handelling

Tue, 01/17/2017 - 00:00
Hi, I am using EBR to upgrade from one application version to another with zero downtime. Definitely, this includes migration DB scripts to be run to migrate from one version to another. The requirement is that the migration scripts have some DMLs...

Locking issues on Table A with FKs on table X while a procedure inserts records in table B with FKs on table X too

Mon, 01/16/2017 - 00:00
Hello, we use oracle and following topic i would like to discuss here: Setup like this: Table A (partitioned) with an article column and FK defined to masterdata (article) keytable X Table B also with article column and FK define...

Procedure accepting more than 32K text as a parameter and inserting to CLOB coumn

Sat, 01/14/2017 - 00:00
Hi Team, We have one requirement where: 1) we have to write one proceudre which will insert into a clob column meant for email body. This procedure should accept text more than 32K. 2) the application or routine will execute this procedure by p...

Use of Wrap utility for 12K LOC packages

Thu, 01/12/2017 - 00:00
I've create a SP to encrypt the source code of existing packages, my problem is that legacy packages contain more than 12K LoC (lines of code), with small packages I have no issue, but with the heavy ones I got : <i>06502. 00000 - "PL/SQL: nu...

Where clause with multiple arguments which can be null or populated

Wed, 01/11/2017 - 00:00
I have a search feature in my application where a user can type filter on 3 different columns(can be more), each of these filters can be used or null. What is the best way to select a ref cursor for the data? I can think of 2 different ways. 1st wa...

Library cache lock for wrong passwords

Tue, 01/10/2017 - 00:00
Hi, I want to know what exactly happens in the library cache which in turn puts the users into lock/delay logins when a user tries to login multiple time with wrong password. If you could explain me regarding this it would be a great help. T...