Tag Info

Hot answers tagged

7

I found the solution! It is so beautiful and I actually learned a LOT about Oracle. In one word: histograms. I started reading a lot about how Oracle's CBO works and I stumbled upon histograms. I didn't fully understand so I took a look at the USER_HISTOGRAMS table, and voilá. There were several rows for the sick table, and practically nothing for the ...


7

(This answers the other question about why the histograms are different.) Histograms are created by default based on column skew and whether the column was used in a relevant predicate. Copying the DDL and the data is not enough, the workload information is also important. According to the Performance Tuning Guide: When you drop a table, workload ...


7

It seems the CBO does not consider a skip scan at all with dynamic sampling, is this true? Actually this is really easy to verify, you can do this by enabling 10053 trace. You will see that the optimizer does not even consider skip scan at all. The reason for this, is the "_optimizer_skip_scan_guess" parameter. The default value for this parameter is ...


5

I emailed Jonathan Lewis about this and got a very helpful reply: The oddity in the calculation is a consequence of the limits on character-based histograms, see particularly: http://jonathanlewis.wordpress.com/2010/10/13/frequency-histogram-5/ http://jonathanlewis.wordpress.com/2010/10/19/frequency-histograms-6/ Looking at the example, the ...


5

Several things might be going on here and might be difficult to pin down but here's a few things. After Oracle crashes, it must do some type of recovery (as in media or crash recovery) to bring the database back to a consistent state. Depending on what you were doing (and whatever else was going on in the instance at the time of the crash) transactions may ...


5

If all else fails read the docco. Try here for starters (v. good diagram). In Oracle, a schema is a database. Also see here - particularly this "One characteristic of an RDBMS is the independence of physical data storage from logical data structures. In Oracle Database, a database schema is a collection of logical data structures, or schema objects [i.e. ...


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


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


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


4

It seems that Oracle doesn't like ANSI-style joins in the materialized view definition when refereshing... Changing the definition to create materialized view dept_emp_mv as select dname, job, hiredate, count(*) as nbr from emp e, dept d where e.deptno = d.deptno group by dname,hiredate,job; makes it ...


4

If the table is in a single database, you don't want to replicate it. You don't want to create a second copy of the data. You simply want to give access to whatever schemas need access to the data. You may also want to create some synonyms so that you don't have to use fully qualified names. If userA owns table1 and you want that data to be visible to ...


4

This article explains how DDL statements work and how they need exclusive table locks, which plays out nicely when there are transactions running against the table. To summarize: If the table cannot be locked due to another transaction having a lock on the table ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired would be ...


3

It means it was adjusted by Oracle, because it was not specified explicitly. When using Automatic Memory Management, you need to set only the memory_target parameter, the sga/pga parameters are handled internally by the database automatically.


3

Several errors: varstocks_id varchar2; The VARCHAR2() datatype requires a data length. .. as indicated by the error message Error(7,15): PLS-00215: String length constraints must be in range (1 .. 32767), which tells you the error is on line 7. The 3rd from last line: AND b.orders_id = a.orders_id requires a ...


3

You should benchmark it using autotrace (http://betteratoracle.com/posts/10-using-autotrace) for example. If you expect millions of rows to be returned, then I do not think indexing will help at all. Simple full table scan and hash joining is the most efficient way of executing this kind of query ...


3

If you want to go down this route (be aware there's a limit of 4000 values per IN, and all of the individual SQL statements are going to flood your shared pool, potentially causing performance problems), you can alter your code to use execute immediate and concatenate the IN list into the query. You'd be better off combining the 2 queries in your code into ...


3

Unfortunately DBMS_CRYPTO in Oracle 11.2 only supports SHA1 (documentation link), which is 160-bit. . DBMS_CRYPTO in Oracle 12.1 supports SHA2 HASH_SH256 (documentation link), which does what you require. There are some free implementations of SHA2 just a google away. This blog post, for example. As for decrypting a hashed password? I don't think you ...


3

The correct answer for licensing questions is: contact your oracle sales representative. With Oracle you license cpu's. If your test and prod run on the same cpu's, they don't need a new license. As long as it all runs on the same cpu's, it does not matter if you have multiple databases (and instances) or run it all in one single database. Normally we don't ...


3

The documentation clearly describes the precedence for NLS settings: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG188 1 (highest) Explicitly set in SQL functions 2 Set by an ALTER SESSION statement 3 Set as an environment variable 4 Specified in the initialization parameter file 5 Default ...


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.


2

You need to set LD_LIBRARY_PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib and to add $ORACLE_HOME/bin (sqlplus path is inside) in your path PATH=:$ORACLE_HOME/bin:$PATH


2

This is what Oracle Database Vault was made for. http://www.oracle.com/us/products/database/options/database-vault/overview/index.html Note that, it is an extra cost option for Enterprise Edition.


2

I found a solution on my own! It seems that there is a bug in the Oracle installer - it doesn't like it if you start your system password with a number. I completely uninstalled Oracle using the deinstall utility and then reinstalled Oracle, this time specifying a system password beginning with a letter. This solved the problem! I hope this helps ...


2

I found the answer as I think: "Starting with Oracle 11g Release 2, RMAN creates a single autobackup file encompassing all of the structural changes that have occurred within a few minutes of each other rather than creating a new backup of the controlfile on each structural change to the database." After about(not sure how much) 10-15 minutes I run the ...


2

No, it can't be queried. These values are stored in sqlnet.ora on either or both of the client and the server. In either case, none of the contents of the network configuration files sqlnet.ora, tnsnames.ora, listener.ora etc (eg protocol.ora, snmp.ora, cman.ora) can be queried. If you really need to read the contents of these files and can't solve this ...


2

Check that auditing is enabled: SQL> SHOW PARAMETER AUDIT NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/phil12c1/adump audit_sys_operations boolean FALSE audit_syslog_level ...


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


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

Flashback Data Archive (formerly known as Oracle Total Recall) let you store history for all the changes to one or more tables automatically. If you have the Advanced Compression option, Oracle will apply a number of optimizations to the history table that will significantly decrease the amount of space that is required and that will generally make queries ...



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