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

learn more… | top users | synonyms

5
votes
1answer
649 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
327 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 ...
5
votes
3answers
655 views

Return a fully dynamic table from an Oracle function

I'd like to write a function with two IN parameters where the first one is a varchar and the second a list of varchars. Based on these I want to return a table with varying column amounts and names of ...
4
votes
4answers
3k views

Why does = NULL not work in Oracle?

I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL. Why does = NULL not work here?
4
votes
3answers
2k 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
981 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
3answers
4k views

Trouble killing sessions then immediately dropping users in Oracle 11g XE

I would like to be able to kill active sessions by a given user, and then drop that user, and do so within a single database call. Here is what I have so far: DECLARE cursor c is select * ...
4
votes
1answer
296 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
996 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
8k views

Which picture does the ORA-01830 error message refer to?

ORA-01830: date format picture ends before converting entire input string About the error message. I am confused. Which picture is the error message talking about? All I provided was a date.
4
votes
2answers
1k 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 ...
4
votes
2answers
12k views

“ORA-01950: no privileges on tablespace 'SYSTEM' ” : What error is it?

Earlier I created a user : SQL>create user suhail identified by password SQL>User created. SQL>Grant create session,create table to suhail; SQL>Grant Succeeded. but when I login as ...
4
votes
2answers
138 views

Is there a bug with PL/Scope in combination with associative arrays?

I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something. I have the following package create or replace package tq84_pkg_c as ...
4
votes
2answers
758 views

Identify which parameters were passed to a procedure (Oracle)

Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values. Is there a way from within the PL/SQL to determine whether a ...
4
votes
2answers
313 views

Multiple JOIN of 6 tables via 5 database instances

I'm currently reengineering my corporates user management and created a table which lists all users of all the 5 database instances. Next step is, that I need to write a query which shows me all the ...
4
votes
1answer
725 views

Is there a way to export an Oracle database to a CREATE DATABASE command?

I have an existing Oracle 11 instance, I want to export the database (which was created via DBCA) to an equivalent "CREATE DATABASE" command, handling the character set, etc. I'd also like to get a ...
4
votes
1answer
193 views

How to use simple output parameters?

I'm new to Oracle, so I apologize for such a simple question, but I've already wasted several hours on it. I have the following stored procedure: create or replace procedure MyTestProc (outparam OUT ...
4
votes
1answer
856 views

Comments in EXPDB parameter file

Is there a way to put comments in a data pump export parameter file?
4
votes
2answers
350 views

Oracle build_plan_xml visualizer

Background: Please review my related question that involves SQL Server : Interpreting SQL Server's Showplan XML I now have a very nice looking Execution Plan display for SQL Server (as a result of my ...
4
votes
3answers
15k views

Oracle 11g - Create Database fails?

I've just installed Oracle 11g and linked it to SQL Developer. I want to create a database for my django install. CREATE database 'django'; Error starting at line 1 in command: create database ...
4
votes
3answers
485 views

What are the 'free to use' workload generators available for Oracle?

A couple that I know about are http://www.dominicgiles.com/swingbench.html and http://hammerora.sourceforge.net/ Are there any others? I am looking for tools what can generate random workload ...
4
votes
1answer
416 views

Help with Query Tuning

EDIT - drachenstern: see this question for more information on this problem and on the source of his query below: Group data by non-unique keys by distinct time range Can someone please advise ...
4
votes
1answer
781 views

What is the difference between these two create table statements?

a) SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE); Table created. SQL> desc xml_tab1; Name Null? Type ----------------------------------------- -------- ...
4
votes
2answers
2k views

How to remove all hidden parameters from Oracle Database

How do I remove all hidden parameters from Oracle Database before upgrading? I'm using the SQL query below. So what do you recommended? SELECT name,description from SYS.V$PARAMETER WHERE name LIKE ...
4
votes
1answer
5k views

oracle format specifiers: to_number vs to_char

SQL> select TO_NUMBER(123.56,'999.9') from dual; select TO_NUMBER(123.56,'999.9') from dual * ERROR at line 1: ORA-01722: invalid number SQL> select TO_CHAR(123.56,'999.9') ...
4
votes
1answer
727 views

Materialized view: compilation_error

When executing the following code on the SCOTT schema I receive a compilation_error. What am I doing wrong here? I doesn't even tell me what the error is. SQL> create materialized view dept_emp_mv ...
4
votes
1answer
3k views

Is there a timeout for a query over database link?

Edit/Preface: This question has been migrated from SO as I am particularly interested on the question on timeouts on DB link queries. The provided workaround from SO is somewhat OK, but I'm really ...
4
votes
2answers
2k views

Determine Index Compression Candidates Online

By doing the following I can determine if an index will benefit from compression and how many columns should be included in the compression: ANALYZE INDEX Owner.IndexName VALIDATE STRUCTURE OFFLINE; ...
4
votes
2answers
576 views

Multicolumn extended statistiscs, NLS_SORT and NLS_COMP, CHAR columns, and the bane of my existance

This is as a result of this question. So I'm working with a query that looks like this: select count(*) from table1 where col1 = 123 and col2 = '1'; The problem is this: col2 is defined as a ...
4
votes
1answer
4k views

How can I view what NLS settings are active for other session?

I've got another session that I suspect isn't running with the 'proper' NLS settings for us in Oracle 11gR2. What can I query to show the NLS settings for a different active session - all of the ...
4
votes
2answers
2k views

Help to understand explain plan in Oracle

I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with ...
4
votes
2answers
1k views

Oracle extents - How can I have so many with this small size?

This is a different question than the one with the almost-identical title. Again, this is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents ...
4
votes
1answer
961 views

How many control files should I have?

On the Oracle Database Appliance, the default deployment only gives you a single control file. I find this a little puzzling. The single control file causes a policy violation in the automatically ...
4
votes
1answer
416 views

How to I interleave logs from two different Oracle tables?

I have two log tables, which are populated from two different PL/SQL packages. It's often useful to keep the data separate, but sometimes I'd like to "interleave" the two tables so I can see what's ...
4
votes
2answers
443 views

Compare two Oracle database roles

I'm trying to re-engineer my corporates user managament and want to compare all the roles with each other to find duplicate ones. Doing it by hand is really tiring and I need to compare like 150 ...
4
votes
2answers
3k views

Shrinking the SYSTEM tablespace in Oracle

Our SYSTEM tablespace grew out of control because of the SYS.AUD$ table. We have truncated SYS.AUD$ but the datafile is still very big (~30G). Resize doesn't work because the file contains used data ...
4
votes
1answer
6k views

How to free up disk space? which logs/directories to clean?

I want to free up the disk space on my Linux machine. I've drill down the space usage and found that the following directories have a big size /u01/app/11.2.0/grid/cv/log ...
4
votes
1answer
429 views

Ports necessary for Oracle 11g Replication across a firewall?

We have two Oracle 11g Enterprise Edition servers that need to replicate data via Oracle streams. However, the two servers are on untrusted domains and connect via a VPN. On end of the connection is ...
3
votes
4answers
2k views

Is it safe to delete dummy tables Oracle 11g XE

i created a database schema in 11g express edition, however there are some APEX tables and some dummy data... Is it safe to delete them? also i would like to know what does apex means and why they ...
3
votes
2answers
2k views

What is the difference between ROLE and PRIVILEGE for Oracle?

Is role a collection of certain privileges?
3
votes
1answer
1k views

How to combine `delete` and `insert` operations in one statement?

for the following data, I'd like to be able to delete some rows and insert others giving the result below. Is this possible with a single statement (eg with the merge statement)? create table ...
3
votes
2answers
16k views

How do you get the explain plan?

Can some one please explain a bit about explain plan, and how does one get the explain plan for a query. Also, if there is any sql script which can be used as utility for this, that would be great.
3
votes
3answers
432 views

What's the most cost effective or cheapest way for high availabity on Oracle Standard Edition One?

We are currently using Oracle Standard Edition One running on two physical database server. If I wanted to get higher availability, what's the most cost effective way or cheapest way to achieve this? ...
3
votes
2answers
150 views

How to retrieve disk spaces by removing tables from tablespaces in Oracle 10g?

I'm using a Oracle 10g database. I have a big tablespace MYTBS made of a dozen data files (MYTBS_01, MYTBS_02, ...) of different sizes which have been added through the years. Now i have removed very ...
3
votes
2answers
355 views

Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?

I have something that looks like this (SQL Fiddle here): Date Location Value 1/1/2012 Baltimore 36 1/1/2012 Houston 36 1/1/2012 Chicago 55 2/1/2012 Baltimore 49 ...
3
votes
2answers
11k views

Using Oracle client to connect to remote database

Although I can do a telnet to remote oracle port I am unable to connect. I have a following tnsname.ora connecting to oracle database using sql plus: remoteOracle = (DESCRIPTION = (ADDRESS_LIST ...
3
votes
1answer
2k views

What does WE8DEC mean?

It is one of the commonly used character sets. What does it stand for? Is it an acronym for something?
3
votes
1answer
407 views

Index on foreign key makes query extremely slow

We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem. The queried table (table3) has an indexed PK, three FK with indexes ...
3
votes
2answers
5k views

Add additional disks to FRA diskgroup in ASM

We're running Oracle 11.2.0.3 with a two node RAC and our FRA diskgroup is running low on space. I need to allocate some more disks to it through ASM. I'm not sure how to go about doing this. I can ...
3
votes
1answer
109 views

Using Inteval Datatype to store result of calculation

I want to create a table which has hiredate and Interval of hiredate and sysdate. How to create a column that automaticlaly stores the calculation.