Home › Oracle › Most Popular Oracle Question by Ask Tom

Most Popular Oracle Question by Ask Tom

Posted on Posted in Oracle Tagged with ,

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...; each time. please help me sincerely rajesh
  • 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 driver. A full scan is also available when there is no predicate, if both the following conditions are met: ?All of […]
  • 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
  • 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). Regards, Madhusudhana Rao.P
  • 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.. (scott/tiger)
  • 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 Rollback segment issue(s). Any suggestions please ! ! ! Murali
  • 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>insert into tn values(0.123) 1 row created. So I do SQL> select to_char(n,999.999) from tn ; TO_CHAR( -------- 123. […]
  • SQL 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 Bin Effective_Date AC006 10 DC001 2/1/2002 AC006 20 DC002 2/1/2002 AC006 100 DC001 5/1/2002 AC006 50 DC0 […]
  • 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)(HOST = PODS.GATE.COM)(PORT = 1521)) ) (CONNECT_DATA = (SID = ODS) ) ) I can connect to the database if I use the command below, obv […]
  • 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 correct me :- I would need to pre-create all schemas /tablespaces for those schemas needed to import the user and then do a FULL imp […]