Most Popular Oracle Question by Ask Tom

These questions are so commonly asked that we have decided to keep list them here.  This courtesy of Thomas Kyte of Oracle Corporation (@OracleAskTom)

  • Finding the number of rows in each table by a single sql
    hi tom i 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......
  • Difference 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 ...
  • IN (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 than IN and NOT EXISTS will work better than NOT IN (read this is Oracle server tunning). Regard...
  • ORA-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 "ORA-12560: TNS:protocol adapter error " what should i do? please help.. ...
  • Converting 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
  • How to connect SQLPlus without tnsnames.ora
    Hi I 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.ora POD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(H...
  • Format 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.999 SQL> insert into tn values( 123.123) ; 1 row created. SQL> insert into tn values(0) ; 1 row created. SQL>inser...
  • How 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.DMP Then 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...
  • How 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 Roll...
  • What 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 te...

Tagged with: ,