http://asktom.oracle.com/tkyte/
This page will be published and republished over the next couple of days and into the future. It will contain pointers to the material that was previously hosted on http://asktom.oracle.com/tkyte. I'll be reformatting those pages to fit into blogspot here and host the data supporting the pages in my "files" tab on asktom. After that is all done, I'll be doing an update to the questions and answers on asktom to map to the new URLS for everything.
So - beware - you will see a flurry of activity on my blog for a bit. It won't be anything new, but it might be something you didn't see before...
Selected Utilities/Frequently Asked Questions
So - beware - you will see a flurry of activity on my blog for a bit. It won't be anything new, but it might be something you didn't see before...
Selected Utilities/Frequently Asked Questions
- Update Cascade Package
- Base conversion routines
- Unindexed Foreign Keys
- Who Called Me
- Data Unloader
- Getting Result Sets
- Mutating Tables
- Skipping Columns in sqlldr
- Getting the difference between two dates
- Inserting large strings
- Moving LONG columns
- Why doesn't my init.ora parameter NLS_DATE_FORMAT seem to work
- Why do I get an ORA-1031 or PL-201 with stored procedures
- Spelling a number
- How to become another User in SQLPlus
- How to convert the UNIX time_t to an Oracle DATE
- How to build a simple test harness (RUNSTATS) (HOWTO) to test two different approaches from a performance perspective
- free.html (HOWTO) Report database free space by tablespace
posted by Thomas Kyte at 2:21 PM
19 Comments:
Hi Tom,
Could you please recommend a reference book for Oracle Web toolkit.
Thanks,
SS.
Hi tom,
I'd love to see your hash join presentation URL reposted :)
http://asktom.oracle.com/tkyte/hj/hj.html
Regards
And this too please..
http://asktom.oracle.com/tkyte/article1/autotrace.html
We are moving our Data Mart from Oracle to Netezza because Oracle seems to be performing very slow for large volume of data(table with 500+ million rows).
@Haneda
first - 500,000,000 rows is pretty small (probably). Actually, the number 500 million 'rows' is absolutely meaningless.
what is that? 500mb? 500gb? 5tb? whatever
In anycase, I doubt it is more than 5tb - which means "it is small"
If your approach is to simply through hardware at it - then you should consider at least evaluation exadata as it would allow you to leverage what you have already built without changing a thing in your application (although, I'm pretty sure this is a design problem, not a hardware problem given the small sizes involved)
I have a doubt that you spent any time sizing the existing hardware
I have many doubts that you (not you personally, your company) spent much time in the thought of the design.
if you just want to wave the magic "we got hardware" flag - you might seriously consider asking your current Oracle contact to prototype your system on exadata (does what netezza does and then some and more and more).
Thanks a lot Tom.
I work in Risk technology of Bank of America. I have been working in Oracle for for than 15 years. I am not too happy to jump into Netezza and start coding in NZSQL and migrating thousands of lines of PL/SQL code to NZPL/SQL. It appears to me that, our management already made the decision to move to Netezza. I guess Oracle should do much better job in marketing exadata which I am sure is as good as Netezza appliance.
@Haneda
About the marketing comment.
If your management even remotely followed Oracle a little bit - Exadata would have been something they heard of - we premiered it last year at Oracle Open Oracle (2008) and it has been on the tip of the tongue of most in the Oracle community since.
The problem is as I see it - if you have thousands of lines of code, I've a feeling "you done did it wrong and netezza isn't going to change a thing, you are doing slow by slow processing and all of the hardware on the planet isn't going to do a thing for you"
500,000,000 row table - my laptop has come close to processing data on that volume (assuming a reasonable row width, since 500 million rows is a rather meaningless figure by itself). IF you use set based operations.
but if you try to process it procedurally, slow by slow, like it was in a file - look up here, look up there, look up a million times everywhere - it will remain slow.
I'd be interested to hear what happens - unless you massively re-architect - nothing will change
hi Tom!
i work wito Oracle 10g r2 and need that the users can to post comments about the news.
Is there a portlet that does this?
Hi Tom,
I am going through Expert Oracle Database Architecture. Could you kindly post contents of what was on the link below;
http://asktom.oracle.com/~tkyte/alert_arch.html
Thanks,
LP
http://asktom.oracle.com/~tkyte/alert_arch.html
Hi Tom,
I want to know the oracle user who created the tablespace with out enabling the auditing.Is it possible.
Thanks,
Sujatha.Ch
@sujatha
this isn't really the appropriate place to ask a question - asktom.oracle.com would be, otn.oracle.com -> forums would be.
we don't track that information in the dictionary.
hi tom,
this is adi........
i want to know which way is the better one. to use the bind variables or not to use them.
i have a situation like this:
sql_stmt := 'select count(*)
' || 'from LX_AUTH_RESPONSE ' ||
'where CC_ERR_CODE =:1 ' || 'and CVV_RESP =:2 ' ||
'and BOOKING_SOURCE IS NOT NULL';
open cur_err_code for sql_stmt
using v_err_code, v_cvv_code;
here can i use bind variables or not.
u said all the pl/sql variables are bound.
please help me.........
@adi
couple of things
first, "U" isn't available, "U" is dead as far as I know. Look it up, it is true.
http://en.wikipedia.org/wiki/U_of_Goryeo
second, you probably do NOT need to execute that query. when I see "select count(*) from ..." - that indicates to me that someone is looking to see if there is a row to process and if so - they process it. The *smart* way to do that is......
JUST TRY TO PROCESS IT, if it isn't there - so be it, you'll figure that out really fast. Counting records is not smart, inefficient and should always be avoided.
third - there is no reason in your example to use dynamic sql at all. The comments I've made about PLSQL and binding is "if you use STATIC SQL it is impossible to bind incorrectly, it happens automatically and you need do nothing special, it is only when you use DYNAMIC SQL in plsql that you need to think about binding correctly". so, I would say a) do not run this query, it is a count(*), it is a waste. b) do not run this query using dynamic sql, it isn't needed and is less efficient than static sql.
lastly, yes, if you persist in this course of action and you a) do run the query and b) do use dynamic sql - you will absolutely want to use bind variables as you have in your example. That is a "no brainer"
Hi Tom,
I attended your PL/SQL presentation at the St. Louis Oracle Users Group. You had provided several tips on tuning SQL procedures. We have a procedure which processes EDI orders that was running between an hour and a half and two hours depending on volume. I used your tips and modified the procedure accordingly. I benchmarked processing one day's EDI orders using the original procedure and the modified version. The original version ran in 1 hour and 40 minutes, and the modified version completed in 26 minutes.
Thanks for those tips!
Andrew
Hi Tom,
I wanted to know., How do we delete view without using instead of triggers..
I'll be obliged if you could help me with this.
Thank You.
Upasna
@up_s
if you have a simple view whereby the delete is unambiguous - you can just delete from the view.
if you have a complex view - whereby we cannot figure out what it means to delete from it - you have to program it via an instead of trigger.
actually, I would say if you are the second case, do NOT delete from that view, be unambiguous in your code and delete from the base tables.
Hi Tom
I've got a tab separated file with double quotes around character fields (including nulls). However, on number fields there are no double quotes (including nulls).
I've tried using the following syntax. This loads the character strings but for number fields, it skips the null values thereby displacing the data.
LOAD DATA
INFILE hello.txt'
TRUNCATE
INTO TABLE hello
replace
FIELDS TERMINATED BY X'9' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
Please can you let me know an easy way round this.
Also I don't have a specification for this file and I have to assume that anything with double quotes is a string and anything that doesn't have double quotes is a number.
Many Thanks
We have a package developed using Oracle 11g & Apex 3.1. We would like to start having a licence key so that users do not exceed usage beyond a certain date & no. of concurrent users.
Any pointers, samples, suggestions, comments would be very welcome.
Thanks
Sanjay
POST A COMMENT
<< Home