Tag Info

Hot answers tagged

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

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

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

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


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


5

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 as soon as you issue some SQL. You can't really be outside of a transaction anyway for practical purposes (sure, if you've just logged in, or just committed and haven't started anything else, well, you're not in a transaction). A few ...


5

A Fast Method A probably faster way to do this is the following: Provide the data in a database table Execute a MERGE statement that merges the data from this table into the source table To provide the data in a database table you can use sql loader or [external tables]. http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606 create ...


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


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


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

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.


3

Whether the data can be manipulated by a user depends on the rights he has. Rights to database objects like tables and views are given either directly to a user or via a role. Roles have the advantage that you can assign them to new users easily without having to deal with the details. I would suggest at least two roles: An admin role and a user role. ...


3

Maybe those files are not cataloged by RMAN? I would CATALOG them and then DELETE OBSOLETE once again.


3

Yes, that's fine: All software downloads are free, and most come with a Developer License that allows you to use full versions of the products at no charge while developing and prototyping your applications, or for strictly self-educational purposes. The full Developer License can also be found on OTN, including: Oracle grants You a nonexclusive, ...


2

It means that possibly some other DBA issued the command below (alter system with optional comment clause). Then the comment is put into spfile this way. But as I read metalink note: 257643.1 it might some outcome of some changes in automatic memory management. See alter system command: alter system set sga_max_size=2G scope=spfile sid=* ...


2

I see no COMMIT up there in your code. By default SQL*Plus autocommit is off. Data changes are not visible to other sessions until they are not commited.


2

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

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

You can add a where clause to each table to filter rows exported: http://www.dba-oracle.com/t_export_where_clause_selective.htm However your referential integrity will not be maintained by this method, because the export is done per-object and filters are applied in the same way. You will need to either be very precise in how you define each where clause, ...


2

In SQL, the limit is 4,000 characters. Using straight SQL like that, without a bind variable, you'll be limited to 4,000 characters. Steps to insert lob values : 1) Create a table and name it TBL_CLOB with 2 fields: id_int = integer; clob_txt =clob; 2) Create a stored procedure and name it P_CLOB with the following code: (P_ID_INT in ...


2


2

No, it can not be done, because 00:00 is a valid time. That you use it to represent "no time available" in your application is your own decision and you have to take care of the consequences yourself. You could store date and time in separate columns and show only the date if the time is NULL.



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