Tag Info

Hot answers tagged

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 ...


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.


3

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 ...


3

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 ...


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 ...


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


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


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 ...


1

Uninstall Oracle Database Express Edition 11g Release 2 for Windows x32 Download and install Oracle Database Express Edition 11g Release 2 for Windows x64 - the 64-bit installer. Take some time to de-stress. :)


1

Your question amounts to, "How do I use indexes?" You should read some Oracle documentation to get up to speed on how to use indexes and how to determine what columns to index. Oracle has an entire library of free online books and documentation for their products. A few good links to start: The chapter on Indexes and Index-organized Tables from Oracle ...



Only top voted, non community-wiki answers of a minimum length are eligible