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


4

The first thing RMAN does it automatically perform an ALTER SYSTEM ARCHIVE LOG CURRENT to switch logfiles so that the active log at the point the backup was started will be included. It doesn't do this in all backup situations, but this answers your question.


4

Telling us that you are using a CASE statement but not showing us the actual code that you're using doesn't help us much. Assuming that "Result Table" is the desired output and that you want to use the old-style MAX(CASE syntax to do your pivot rather than using the newer PIVOT operator SELECT emp_id, emp_name, nvl( max( case when sal_paid = ...


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

I would recommand always to use the appropriate datatypes, operators and functions and explicit conversion and not to use string literals and string processing and implicit conversion. Look at your statement select from_date from date_table where length(extract(year from from_date))='2' / The return type of the length statement is clearly a number. So ...


3

I am going to go out on a limb here and try and answer even though you haven't provided a lot of details. It looks like you want to create a comma-separated list of the columns and then the values below it but in sqlplus you are getting the column header as the actual SQL string. If I were to use the following in a SQL Fiddle I will get the same result: ...


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


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

You can add a number of days to from_date like this: UPDATE table_date SET from_date = from_date + 730487 WHERE LENGTH(extract(YEAR FROM from_date)) = '2' AND from_date <= to_date('01/01/0014','dd/mm/yyyy') UPDATE table_date SET from_date = from_date + 693962 WHERE ...


2

You firstly need to create and Oracle directory. As a user with the relevant permissions: CREATE OR REPLACE DIRECTORY HOMEJOE AS '/home/joe'; Grant permission to the user that needs to use DBMS_XMLSCHEMA.registerSchema: GRANT READ, WRITE ON DIRECTORY HOMEJOE TO YOURUSER; Then register the schema, using the newly created directory: DECLARE BEGIN ...


2

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.


2

Yes, if the database is open in read-write mode there is always a number of changes to database that reside only in the current redo log and not in any archived log. If you want protect the current redo against hardware-related corruption, you need to add redundant storage (with either a remote replication or local mirror). If you want to protect the current ...


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

Alas, the ora_rowscn approach is not going to work very well. It still enables "lost updates". I have my information from AskTom: ORA_ROWSCN for optimistic locking Copied example here to anticipate dead links... set echo on drop table t; create table t ( x int primary key, name varchar2(30) ) rowdependencies; insert into t values ( 1, 'john' ); insert ...


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

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