Tag Info

New answers tagged

0

According to the Oracle documentation for the SELECT ... SAMPLE clause, the sample is random; it is just a coincidence that you are always getting the same rows on the local machine. You could try adding the SEED(x) clause, where x is a number from 0 to 4294967295; but that may not help going across a database link. select * from myTable@myDbLink ...


0

For me, when I am comparing the cost of two or more versions of the same query, I usually compare the logical reads. Logical reads can be compared across different queries and as long as you are getting the data you want and it has the lowest possibly logical IO, then you must be doing something right.


2

It sounds like you're dumping 30 million rows into an existing table, then immediately trying to update those rows. I have a couple of thoughts, which may or may not work for you (since I can't see your code currently): 1) Is there a way to combine the inserts with the updates? It seems suspect to me that you're creating 30 million rows, then immediately ...


4

You simply don't use begin in sqlplus if you're just going to issue a series of SQL queries. You're in a transaction already. You can't really be outside of a transaction anyway. A few things to be careful with though: sqlplus does have an autocommit setting. It's off by default in modern versions, but just to make sure: SQL> show autocommit ...


2

Got a workaround from Oracle: /*+ opt_param('_simple_view_merging', 'false') */ or alter session set "_simple_view_merging"=false; So the following query works: SELECT COUNT(patientId) n FROM ( SELECT /*+ opt_param('_simple_view_merging', 'false') */ DISTINCT pat.patientId, pat.bval1, pat.bval2 FROM pat LEFT OUTER JOIN (SELECT /*+ CARDINALITY(tab ...


4

Unfortunately the answers are as follows: Is there a unit for cost in an Oracle execution plan? Not really. I mean if the cost of an operation is 50 then can I map this number to CPU cycles or utilisation percentage? Nope. What does this number stand for? It's defined like this: (see the glossary) A numeric internal measure that ...


0

This solved the issue for me: grant execute on sys.dbms_crypto to myuser; I had a similar problem with the random function.


0

Following query gives details of all locks. SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name FROM V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID


2

Short answer: CREATE TABLE must be explictly granted to the object owner for DBMS_DATAPUMP to be called from within a package or stored procedure. Long Answer: In an effort to solve this question, I interrogated the data dictionary to get all of the object and system privileges granted by the IMP_FULL_DATABASE and EXP_FULL_DATABASE roles (and all roles ...


0

Privileges acquired via roles are not effective in a stored procedure. You need to grant the required privileges to the account that creates the package.


0

change to insert into t select 1, to_timestamp ('2009-02-10 12:34:45.56','YYYY-MM-DD HH24:MI:SS.FF2') from dual; should work. Don't rely on default formats.


0

There is no such thing as a read only privilege. If someone grants update,delete,insert on x to public, everyone who can create a session can modify the contents of that table. Also, if you create a user x and using a dba account create a table x.y, the user x - who owns the table y - can modify the contents of his/her table, because he/she is the owner of ...


3

Regular trigger will see changes made in other tables within the same transaction. However, if you create the trigger (or other programmable object) with PRAGMA AUTONOMOUS_TRANSACTION, it will be executed within the scope of new transaction and won't be able to see "parent" transaction changes.


0

Seems like a good reason to visit the Oracle Documentation site. The 2-day dba documents are very good. If your database is running in ARCHIVELOG mode, it copies all transactions to the archivelog destination. The transactions are always written to the redolog files but when they are full, they are only saved when running in archivelog mode. This enables ...


1

You can use SQL%ROWCOUNT. This return rows processed in last sql statement For example: declare res number; begin select 0 into res from dual; dbms_output.put_line(sql%rowcount || ' rows selected'); end This code prints 1 rows selected because you selected one row. I hope this helps


0

As always, It will depend on how much money are you able to spend. (may I say "invest"?) Although the feature of storing binary files into Oracle is included in all Database editions, storing binary files into the database will increase hardware and human resources costs since your db will become bigger and bigger, and it's very common in these cases that ...


2

Have you considered Oracle's BFILE ? Sounds like it's a perfect fit for you; http://www.orafaq.com/wiki/BFILE Here's some further reading material on the subject ; http://docs.oracle.com/cd/B10501_01/appdev.920/a96591/adl12bfl.htm


5

My strong advice (regardless of the RDBMS): store the files separately. This makes a much smaller database, important when you backup, migrate, replicate etc. Additionally, you can separate db and files on different disks, having so much more control over storage locations (e.g. disk subsystems with different fail-over strategies). This means however, you ...


1

I completely agree with dartonw's comments about reading through the guide. That's the only real way to start understanding the concept of indexing as it relates to Oracle and the Optimizer. One thing I will add to it though is this - Philosophically, as a DBA, I rarely decide what columns to index on my own. What I mean by this is that I go back to the ...



Top 50 recent answers are included