| 1 | Finding 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,887,277 | 12,105 |
| 2 | 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... | 1,222,638 | 3,099 |
| 3 | 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 ... | 978,393 | 17,707 |
| 4 | 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 thanIN and NOT EXISTS will work better than NOT IN (read this is Oracle server tunning). Regards,Madhusudhana Rao.P ... | 934,261 | 9,372 |
| 5 | 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 "<b>ORA-12560: TNS:protocol adapter error</b> " what should i do?please help.. ... | 916,255 | 9,297 |
| 6 | 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 i... | 873,833 | 30,217 |
| 7 | 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 Bi... | 816,808 | 36,997 |
| 8 | 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.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... | 773,955 | 7,667 |
| 9 | How 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... | 738,826 | 7,812 |
| 10 | ALTER 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... | 704,648 | 16,967 |
| 11 | 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.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... | 697,772 | 6,434 |
| 12 | 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 technet.oracle.c... | 668,217 | 11,092 |
| 13 | DATE 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.... | 650,685 | 24,418 |
| 14 | Inserting 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... | 621,266 | 6,227 |
| 15 | How to calculate current DB size
We requested oracle apex work space area 100MB?It exceeds the over 90% size? How to calculate Current file Utilization and current database size?Requested Size: 100(in MB)Current File Utilization: 6.49(in MB) What is this?Current Database Size: 91.... | 522,306 | 15,755 |
| 16 | Adding 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... | 502,027 | 11,301 |
| 17 | Case 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... | 494,863 | 10,480 |
| 18 | CONNECT 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. ... | 493,894 | 13,409 |
| 19 | How can I track the execution of PL/SQL and SQL?
Hi Tom,How can I know what a given user is executing if his status is ACTIVE ? How can I know which PL/SQL blocks or SQL statements are being run by him ? As to SQL statemets, I can join v$session.user# with v$sqlarea.parsing_user_id ( am I really right ??? ),... | 473,237 | 18,866 |
| 20 | How 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? ... | 466,058 | 4,693 |