The second and terminal release of Oracle Corporations Oracle 11g database product. 11.2 was released for Linux on September 1st, 2009. New features added include RAC One Node, Improved data compression ratios (up to 20x), Edition Based Redefinition, Recursive Subquery Factoring, Instance Caging, ...
19
votes
4answers
5k views
Why is OS authentication considered poor security for Oracle databases?
Oracle is deprecating OS authentication according to the Oracle Database Security Guide, which says
Be aware that the REMOTE_OS_AUTHENT
parameter was deprecated in Oracle
Database 11g Release ...
17
votes
3answers
13k views
What is the difference between sys and system accounts in Oracle databases?
There are two ways to connect to Oracle as a administrator using sqlplus:
sqlplus sys as sysdba
sqlplus system/manager
These accounts should be uses for different purposes, I suppose.
Which tasks ...
15
votes
7answers
5k views
How do you version your Oracle database changes?
I am interested to know what methods other people are using to keep track of changes made to the database including table definition changes, new objects, packages changes, etc. Do you use flat files ...
13
votes
9answers
16k views
Eliminate duplicates in ListAgg (Oracle)
Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to ...
12
votes
4answers
1k views
What is the significance of a semicolon at the end of SQL*Plus commands?
Some statements like create table, insert into etc take a semicolon at the end:
CREATE TABLE employees_demo
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name ...
9
votes
2answers
1k views
Security for Application Developers doing PL/SQL work in Oracle
How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need ...
9
votes
1answer
421 views
Commit vs Fast Commit vs Commit Cleanout in Oracle Database
I was wondering whether someone could verify my understanding regarding the differences between those 3 terms as pertaining to Oracle Databases.
Many sources confuse these terms and do not explain ...
9
votes
2answers
141 views
Why is a “non-single-group group function” allowed in a subselect but not on it's own?
Why does the first query not fail with the same error as the second:
with w as (select 1 product_id, 10 units from dual union all select 2, 5 from dual)
select sum(units) from (select product_id, ...
8
votes
3answers
916 views
I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created?
I have powerful machine with 70 GB RAM. I created one Oracle instance with 20 GB as sga_target. I am not able to create another Oracle instance with sga_target > 10G even when I keep the ...
8
votes
2answers
618 views
Oracle Shutdown Method
Shutting down a database before doing an upgrade or a patch can be done several ways.
shutdown immediate;
or
shutdown abort;
startup restrict;
shutdown immediate;
or
shutdown abort;
startup ...
8
votes
2answers
410 views
LNNVL Justification
LNNVL is an oracle built in function that returns TRUE for conditions evaluating to FALSE or UNKNOWN, and returns FALSE for conditions evaluating to TRUE. My question is what would be the benefit of ...
8
votes
1answer
285 views
How can a datapump export have its resource usage limited on linux?
Doing an export of a number of users causes high CPU and I/O usage. I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how ...
7
votes
2answers
285 views
are there significant differences with 500+ million row tables in Oracle?
I am in a database designer in a data warehouse environment. I am used to dealing with tables with a maximum of 1 millions rows and am now faced with tables with more than half a billion rows. Are ...
7
votes
1answer
123 views
Cardinality is so far off from reality
I've got a table where the cardinality for plans just seems waaaaaay too high - despite updating stats and even trying dynamic sampling for Oracle 11gR2.
select count(*)
from table1
where col1 = ...
7
votes
2answers
1k views
Limit redo for materialized view complete refresh or manual equivalent
A materialized view(MV) log can be used to allow a MV to do a fast refresh which only modifies the data that has changed. However, various conditions prevent the MV from using the log and therefore ...
7
votes
1answer
137 views
Generate XML using Oracle XML schema
I have some XML schema definitions that have been registered using DBMS_XMLSCHEMA. I have created tables using the XMLTypes generated. I can create XML using XMLQuery and if I understand correctly, ...
6
votes
3answers
2k views
How to differentiate between SQL and PL/SQL?
I know the question might sound too stupid, but I never understood this part.
SQL*Plus works with both SQL and
PL/SQL. How do I know whether some
code is SQL or PL/SQL? If my code has
a for ...
6
votes
4answers
410 views
How do I sync instance A to instance B without losing any of the data already on instance B?
Like I said in a comment below, I'm no DBA and I'm still in a learning process.
Here's my scenario :
I have an Oracle server running a couple schemas (let' call it Source). What I would like to do ...
6
votes
4answers
322 views
Can this business logic be enforced by a conditional database constraint?
I am trying to duplicate the business logic embodied an intranet C# web application in the database so that other databases can access it and work under the same rules. This "rule" seems difficult to ...
6
votes
3answers
314 views
How can I ensure that only one copy of a procedure is running in Oracle?
We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error.
Whats the best method ...
6
votes
1answer
234 views
Package state not re-intialized on raise_application_error
When a package has state and the header is changed, the first call gets an error stack something like this:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of ...
5
votes
4answers
384 views
Does an IOT guarantee order in a select?
We need to add a priority column to a table that gets hit about 250 times a second, approx 170 selects, 125 inserts and 60 updates. The column will be a simple number(1).
The priority does not matter ...
5
votes
2answers
168 views
Defining constraints in `CREATE TABLE` statements
Recently I have been using a Database Abstraction Layer built by a Python web-framework called web2py (click for their DAL syntax). They include the option to include your constraints within the ...
5
votes
2answers
392 views
What would be the correct size of redo logs?
I installed and put in production a 11g database. I was adviced to size redo logs to 200MB each one. Now I see that my archived redo logs are each one 200 MB and all of them occupy a lot os disk ...
5
votes
2answers
4k views
Learning to Optimize SQL Queries and Understand Execution Plans - Resources?
I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team.
They ...
5
votes
2answers
390 views
Oracle Advanced Queuing LIFO
With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
5
votes
2answers
113 views
Product of n Rows
From this data (assuming the number of rows is not known in advance):
with q1 as (select mod(ora_hash(level),5) c1 from dual connect by level <=4)
select * from q1;
/*
C1
--
2
1
4
1
*/
I ...
5
votes
1answer
268 views
Are there performance benefits to turning off archivelogging? Is it wise?
The first part of the question is pretty simple; has anyone run a DB in both archivelog and noarchivelog mode? Were there any measurable performance benefits to turning off logging that you could ...
5
votes
1answer
945 views
Oracle 10g express database link to Oracle 8i
I upgraded a 9i install to 11g with only one problem. Database links between 11g and our legacy 8i database are no longer supported. I have to transfer data from 11g to 8i two or three times a day ...
5
votes
1answer
578 views
Case Behavior Changed Between 11.2.0.1 and 11.2.0.2
I have a case open with Oracle and they say they can't reproduce this. Running the following in 11.2.0.1 or lower produces two rows from both of these queries, but in 11.2.0.2 the second query ...
5
votes
1answer
2k views
Oracle RAC failover not working correctly
I have Oracle 11gR2 set up on a RAC environment with two nodes. I have SCAN set up, eg: I have two entries on the DNS set up so I can ping the SCAN name and reach either node. I can also ping both ...
5
votes
1answer
135 views
how do I configure a new Oracle 11g instance to match the configuration of the old Oracle 10g server
How can I compare two Oracle database servers which are on two different machines?
We need to ensure that the new server is setup correctly.
For example, one machine is running Oracle 10g on ...
5
votes
3answers
663 views
How can a datapump export have its resource usage limited on windows?
Doing an export of a number of users causes high CPU and I/O usage. I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how ...
5
votes
1answer
4k views
Why in Oracle 11gR2 I can't drop the materialized view with the same user that created it?
I created a materialized view with the DI_TEST_AL user, let's name it MY_MVIEW. It appears in the USER_OBJECTS table as MATERIALIZED VIEW, I try to drop it, I get a success message, but the object is ...
5
votes
1answer
2k views
Add Oracle Label Security to an existing Oracle installation
We are using Amazon EC2 to host some Oracle database instances while we are evaluating and prototyping some software. We are using the AMIs provided by Oracle - specifically, Oracle Database 11g ...
5
votes
1answer
833 views
DROP PARTITION strategy using LIST-REF composite partitioning
We have a large Oracle 11gR2 instance with some tables that have recently become an annoyance when deleting data. This database powers a large optimization cluster that takes input metadata from the ...
5
votes
1answer
1k views
DBMS_Resource_Manager.Calibrate_IO Usage
DBMS_Resource_Manager has a procedure called Calibrate_IO with a Physical Disks input parameter. The documentation says this value should be set to the...
Approximate number of physical disks in ...
5
votes
1answer
58 views
Sudden increase in log_file_sync waits
I'm on Oracle 11gR2 with a 2 node RAC system. It's shared fiber storage to an EMC Clariion.
Last friday things went bad..fast. All of the sudden processes that normally ran fine for years became ...
5
votes
1answer
388 views
Global locking for multi-master Oracle GoldenGate replication
This is a very complex scenario, but I figured a state-of-the-art challenge might interest some of the many high-end users of dba.se.
Problem
I'm working on an intercontinental data replication ...
5
votes
3answers
319 views
Partition pruning with multiple date columns
I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are ...
5
votes
1answer
483 views
Trying to copy a schema using impdp
I'm trying to copy a SCHEMA in its entirity using oracle's IMPDP tool. I've setup a database link back to itself and the link works fine. Then I'm running this:
impdp ...
5
votes
2answers
611 views
How to install Oracle 10g Express and 11g Enterprise on same machine
I have to continue maintaining an Oracle 8i database that receives information (about 20 Inserts and Updates a day) from a 9i database. I am upgrading the 9i database to 11g which does not support ...
5
votes
1answer
480 views
How do I have Oracle ASM scan for and repair uncorrectable ECC errors (bad sectors)?
I'm running Oracle ASM (11gR2) with normal redundancy on SATA disks (WD Raptors). This is on a physical standby database, if it matters. Occasionally, disk sectors develop read errors (ECC failures). ...
5
votes
1answer
134 views
What must be in place to validate a XMLTYPE against a schema?
I have a procedure that generates an XMLTYPE and I want to validate it against a schema. The problem is that there seems to be a permissions issue running createSchemaBasedXML because when I run the ...
4
votes
2answers
457 views
What storage engines work with Oracle?
http://en.wikipedia.org/wiki/Database_engine
mentions about database engines aka storage engines. What all storage engines can be used with the Oracle database?
4
votes
3answers
890 views
Is (and if how) it possible in Oracle to “insert into <table> values <rowtype-variable>” if <table> has virtual columns
Here's a table
create table tq84_virtual_test_without (
col_1 number,
col_2 number,
col_3 number,
col_4 number,
col_5 number
);
with the rule that col_5's value is the sum of the other ...
4
votes
3answers
771 views
Detect Index Coalesce
Is there a way to know if a COALESCE has been done on an index? Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes.
Note: I am not referring to the COALESCE ...
4
votes
1answer
217 views
Dodgy causes of ORA-12549?
I am dealing with a rather massive ETL processing which keeps throwing me the ORA-12549 error.
Have been looking for problems in the database so far and nothing. The DBA says the database is "fine" - ...
4
votes
2answers
523 views
Oracle 11g see transaction or session start time
I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records?
I've got a transaction id "TX-1234-abcd", a ...
4
votes
2answers
614 views
Updating rows conditionally with a gapless ascending value
A user has a table they would like to update. Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less. The rows ...
