Skip to Main Content

Popular: Top 20 Most Viewed Questions

SubjectHitsLast 90 days
1Finding the number of rows in each table by a single sql
hi tomi have a databse with 125 tables. i can find the total number of tables of the database by the sql select * from tab; now i would like to know the number of rows in each table of my database with out executing select count(*) from...; each time.please...
2,777,8755,315
2Difference between Full Index Scans and Fast Full Index Scans
Hi Tom, What is the difference between Fast Index Scan and Fast Full Index Scan ?As per oracle documentation," A full scan is available if a predicate references one of the columns in the index. The predicate does not need to be an index driver. A full scan is...
1,185,3883,362
3IN (vs) EXISTS and NOT IN (vs) NOT EXISTS
Hi Tom, Can you pls explain the diff between IN and EXISTS and NOT IN and NOT EXISTS. Because I have read that EXISTS will work better thanIN and NOT EXISTS will work better than NOT IN (read this is Oracle server tunning). Regards,Madhusudhana Rao.P ...
761,90110,640
4ORA-12560: TNS:protocol adapter error
i'm running oracle on standalone computer at home.it used to work properly but from couple of days i'm getting error stating "<b>ORA-12560: TNS:protocol adapter error</b> " what should i do?please help.. ...
756,70815,097
5Converting CLOBS TO VARCHAR
Can you give me a solution for converting CLOBS datatype to VARCHAR datatypes, all the documents I refer to talk about converting BLOBS to VARCHAR and when I try and apply the examples to CLOBS, get errors ...
732,30318,280
6Format the Number for display
Hello Guru,Q1) I need to display numbers from a database in a specific format.Table Tn (n number(6,3));The format is 999.999SQL> insert into tn values( 123.123) ;1 row created.SQL> insert into tn values(0) ;1 row created.SQL>insert into tn values(0.123)1 row c...
631,88715,478
7How to connect SQLPlus without tnsnames.ora
HiI am trying to use SQLPlus to connect to database directly using just the connect string (ie without referencing to tnsnames.ora)I have this in my tnsnames.oraPOD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = PODS.GATE.COM)(POR...
621,64014,454
8How to Update millions or records in a table
Good Morning Tom.I need your expertise in this regard. I got a table which contains millions or records. I want to update and commit every time for so many records ( say 10,000 records). I dont want to do in one stroke as I may end up in Rollback segment i...
601,97017,654
9How To FULL DB EXPORT/IMPORT
Hello Tom, How to do a Full DB exp and Import.I do say exp system/manager@xyz FULL=Y FILE=FULL.DMPThen If I want to do a full Import to a new freshly created DB which only has the default schemas sys , system , etc.1) Please correct me :- I would n...
601,5249,512
10SQL Query aggregation and subqueries
Tom:I have a table that initially stores information about items in a warehouse stored in different bins. Table look like this where manual inventories are done every 3 months. Effective date is the sysdate when record is inserted.Inventory:Item_no Qty Bi...
576,88522,066
11What is a SID, how to change it, how to find out what it is.
Hi Tom,A trivia question for you, what is SID and what is it used for? Would changing it have any impact on the different instances of the database? And lastly, where can I find out the SID of my database? I don't seem to able to find it in technet.oracle.c...
505,1278,561
12Inserting values into a table with '&'
Hi,I want to insert a values into a table as follows:create table test (name varchar2(35));insert into test values ('&Vivek');I tried the escape character '\' but the system asks for a value of the substitution variable.I also did a "set define off" but then a...
500,90010,409
13ALTER SESSION SET CURRENT_SCHEMA
I have an application which requires us to change schema's within the application to update a table for various users. We have a variable in cobol called DB-SCHEMA which gets set. I am able to issue the with a cobol module.EXEC SQL ALTER SESSION SET CURRENT...
497,27610,257
14How can one insert a carriage return
How can one insert a carriage return orline-feed into a text field so that one can "force" a blank line between paragraphs in a form in a way that itwill also print as multiple paragraphs? ...
377,3387,464
15Adding a column with a default value to a table
Tom, I have a doubt about adding columns with default values to a table. Recently, I have stumble upon an Arup Nanda article on it where he states the following"When a user selects the column for an existing record, Oracle gets the fact about the default value...
367,42510,226
16DATE queries using BETWEEN
Tom:1. I had a problem with a date query using the operator 'between'.WHen I doSelect * from table where date is between '01-JAN-02' and '17-JAN-02'it does not give me the records marked with a date '17-JAN-02'I have to change it to '18-JAN-02' to get those....
359,86425,457
17Case construct with WHERE clause
Hi Tom, I have a question and I don't know if this is possible or if i'm jsut doing something wrong because i get multiple errors like missing right paren, or missing keyword.I want to use the CASE construct after a WHERE clause to build an expression. for exa...
323,22712,969
18Howto select first value in a group by bunch of rows....
Hi TomI have just begun using analytic functions, but have come up short on this:In a query where I group by a field, I would like to select the first values from a specific row.I have using something like: select distinct a.name , first_value(...
310,2264,829
19CONNECT BY basics
hi tomcan u explain me in detail about "start with connect by" sql statement (tree structure).i know there is documentatin but it is very confusing. ...
300,98910,060
20export to CSV
Tom,I need to export data from a table into a .csv file. I need to have my column headres in between "" and data separated by ','. also depending on the column values a row may be printed upto 5 times with data differing in only one field.Also I need to run ...
287,0974,778