Oracle 11g is the July 11th, 2007 release of Oracle’s primary database product. New features added in 11.0.1 include Virtual Columns, Automatic Memory Management and Interval Partitioning.
15
votes
3answers
15k views
Can I change the SID of an Oracle database?
The Server is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit
Is there an easy & quick way to change the SIDs of the test databases on the server?
Dropping & recreating of ...
10
votes
4answers
9k views
Best way to delete very large recordset in Oracle
I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end. The database doesn't really do anything (no SProcs, no ...
9
votes
1answer
384 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 ...
8
votes
3answers
5k views
Is there any difference between Rem and — comments for SQL*Plus?
On SQL*Plus prompt, both Rem and -- qualify as comment indicators:
Rem this is a comment
-- this is also a comment
create table emp (
id number primary key,
name cvarchar2(40));
Is there any ...
8
votes
2answers
402 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 ...
7
votes
1answer
631 views
Why am I NOT getting a mutating table error in trigger?
It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation:
A mutating table is a table that is being modified ...
7
votes
2answers
2k views
Why can't I read Oracles CLOB columns via SQL-SERVER 2008 linked server?
I want to access data in an Oracle 11g database from SQL-Server 2008
I set up a linked server and when I execute
select * from [Link_server_name]..Oracle_schema.Oracle_table
and Oracle_table ...
6
votes
4answers
5k views
In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?
I'm writing a script to populate some tables with data for testing.
I would like to write something like the following but I don't know how to do it (I'm Oracle 11g)
SET ENABLED_USER_ID = ...
6
votes
2answers
2k views
How Long Would Take to Upgrade an 1T Oracle DB from 10g to 11g?
How long would take to upgrade an Oracle DB which has 1T data from 10g to 11g usually/roughly? I need to estimate the down time for it since it is a prod db. Thanks much!
6
votes
4answers
11k views
How can I unload Oracle database tables into a csv files?
I need to retrieve certain data from an Oracle 11g release 1 database and provide it as one or more CSV files. The data resides in multiple tables and/or views. All this should work via the command ...
6
votes
3answers
420 views
ALTER SYSTEM DISCONNECT SESSION doesn't work
Oracle 11g. The devs leave sessions connected overnight, sometimes certain cron jobs fail because of that. I am trying to kill all user sessions, but I can't.
I login as:
sqlplus system/[password] ...
5
votes
1answer
371 views
Execution plan using B+ tree index, but also sorts
I'm using Oracle sqlplus. And I have the following query:
SELECT fooID from foo MINUS
SELECT fooID from bar;
I have created two unclustered B+ tree indexes. One in the field fooIDof the table foo ...
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
822 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 ...
4
votes
3answers
273 views
SQL Server pivot query on Oracle source
I have a SQL Server instance that has a linked server to an Oracle server. There is a table on the Oracle server called PersonOptions which contains the following data:
╔══════════╦══════════╗
║ ...
4
votes
2answers
341 views
Learning Oracle, does it matter if I install 10g or 11g?
I'm taking a class in Databases, they're using Oracle 10g in class, but I'm having some trouble installing the database here in Windows 8 (auth problems), so I thought I'd attempt 11g.
Is 11g ...
4
votes
1answer
2k views
Connection to an oracle db from another computer
I have installed Oracle 11g to an ubuntu, I can connect to DB using sqlplus from the localhost, But I cannot connect from another computer, I have configured my tnsnames.ora as followed;
ELIF =
...
4
votes
2answers
728 views
Where can I find autoextend events?
In Oracle, where can I see the history of DBFs autoextending? Or get the timestamp that a DBF last autoextended, and by how much?
4
votes
3answers
202 views
Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837)
I decided to reduce the memory allocation of a local developer instance, and was told that the following command did the job;
alter system set memory_max_target = 512M scope = spfile;
Which I guess ...
4
votes
2answers
1k 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
1answer
514 views
temporary table inside procedure oracle
I'm in a situation right now. I'm migrating several procedures from Mysql 5.0 to Oracle 11g.
Mysql procedures allow me to:
1. Create temporary tables
2. Insert data into temporary tables / Query these ...
4
votes
1answer
10k views
How to configure an Oracle Listener without SID_LIST_LISTENER in listener.ora?
I have access to a ORACLE 11g server using the following listener.ora
# listener.ora Network Configuration File: D:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by ...
4
votes
1answer
202 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
1answer
944 views
Oracle 11g listener fails with ORA-12514 and ORA-12505 errors
I run an instance of Oracle 11g locally on my development machine and can connect to the local instance directly via SqlPlus:
c:\>sqlplus ace
SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar ...
4
votes
1answer
789 views
How to use the METHOD_OPT parameter in DBMS_STATS.gather_table_stats
Hi there I was just wondering what would be the difference between these 2 METHOD_OPT parameter settings:
FOR COLUMNS SIZE 5 some_column
and
FOR COLUMNS some_column SIZE 5
both will create a ...
4
votes
1answer
911 views
Partitioning a table with LOB columns in Oracle 11g
we have a fairly small table (~25k rows) that is being heavily written to and most of the rows go away quickly (it's part of a scheduling application which stores information about the jobs in that ...
4
votes
1answer
95 views
Designing Simple Schema for Disaggregation of Demand Forecast
I am doing a simple database design task as a training exercise where I have to come up with a basic schema design for the following case:
I have a parent-child hierarchy of products (example, Raw ...
4
votes
2answers
453 views
Hibernate Query Running Slow
I am currently facing a problem with a slow running Hibernate query that is being kicked off from a Java program. It is making a call to Oracle 11g at the back end.
This query is taking anything from ...
3
votes
2answers
5k views
Oracle 10g dump file push into Oracle 11g database
Currently when executing the imp command with the following parameters: file=my.dmp log=my.log fromuser=myuser touser=myuser
The following errors occur:
IMP-00058: ORACLE error 12560 ...
3
votes
4answers
3k views
Simplified/automated datafile free disk space reclaim
On Oracle version 11g:
After Googling, I can't find a simple way to reclaim free space after deleting a table.
I've found a lot of explanations, telling how the datafile becomes fragmented, the big ...
3
votes
2answers
123 views
Support ticket system - when/how to move old tickets?
I don't have access to the partitioning feature, but consider a support ticket system with tens of thousands of tickets being opened everyday and taking about a week to a couple of months in getting ...
3
votes
1answer
1k views
Oracle11g password expiry issue
I have a user which has a default profile in oracle db.
I want to reproduce the following warning
ERROR:
ORA-28002: the password will expire within X days
In dba_profile I set following values.
...
3
votes
2answers
324 views
Migrating to Oracle Flashback for Historization on Oracle 11g
Our application built using Oracle 11g requires that we keep historic data.
The historization is legally mandated, and is mainly used for manual queries, when investigating problems, etc. Basically ...
3
votes
2answers
840 views
Oracle 11g setup closes without errors on pre-requisite check
I'm installing Oracle 11g and the setup wizard just closes automatically after the pre-requisite check.
I have administrative access
UAC is disabled
IPv6 is diabled
There is more than enough free ...
3
votes
3answers
2k views
cursor: pin S wait on X in 11g
This has been one of top5 wait events in our database, even though there were not many waits but we see it as a potential threat, we want to find the root cause and solution.
Any advice will be ...
3
votes
3answers
4k views
AWR Report - Tuning Database
We have an OLTP system, following are our AWR reports taken during peak hour window for three days once each day. We see a lot of direct path reads; we want to add more PGA in-order to reduce wait ...
3
votes
2answers
1k views
Connection Pooling for PHP based website using Oracle 11g database
I am using Oracle 11g database server and now I am getting to see that OCI's persistent connection isn't much of a use, and a new connection is created anyway when a new user accesses the page. I ...
3
votes
2answers
1k views
How to return a CTE as REFCURSOR from an Oracle stored procedure?
I tried to modify an Oracle stored procedure to use a CTE instead of a simple Select Statement.
My procedure looked like this:
Create or replace Myproc ( MyRefCursor IN OUT SYS_REFCURSOR)
as
begin
...
3
votes
1answer
90 views
Use a Context namespace in a multi-tenant database?
I'm developing an Apex application that uses database Contexts in conjunction with Row Level Security. I'd like to install a second copy of the schema and workspace on the same instance; this works ...
3
votes
1answer
87 views
Are database backups specific to the OS on which they are created?
I am trying to get a better understanding of the behaviors of database backups for Sybase (15.X) and Oracle (11.x) as it relates to the underlying OS. Is the backup file OS specific or can it be ...
3
votes
1answer
486 views
SOA + Fusion + Weblogic + Oracle 11g - STUCK threads
This is sort of a last-ditch effort. We have Oracle SOA on Fusion, 11.1.1.5.0 (+ patches on a separate server) running, and nearing the point where we're taking it into production. I've posted on ...
3
votes
1answer
245 views
Is there a way to allow a proxy connection to use passed authentication in a public database link?
I think the answer to this is no, but I could be wrong. If nothing else it might be nice for others to be aware of this limitation of proxy connections.
2
votes
1answer
97 views
Generating an arithmetic sequence
I would like to generate a named table in a WITH clause which will contain this arithmetic progression:
╔══════════╗
║ mynumber ║
╠══════════╣
║ 0 ║
║ 30 ║
║ 60 ║
║ 90 ║
║ ...
2
votes
1answer
325 views
How do I export data from Oracle 11.1 to 10.1?
I have a 11.1 oracle server and I need to export a dump file for a client who has a 10.1 server.
While reading the documentation, I figured out I needed the 10.1 Export Utility. I searched the ...
2
votes
1answer
56 views
Regular Expression Difficulty
I'm trying to extract 456 from the string :123:456: as follows:
select regexp_substr(':123:456:', ':(\d+):', 1, 2, 'i', 1) from dual
However, this query returns null. What am I doing wrong?
2
votes
2answers
3k views
Oracle 11g SYSDBA login
I installed Oracle 11g on my win xp machine. Now I want to connect to the database. I was told that to connect to the database from command prompt I should use
SQLPLUS "/ AS SYSDBA". But I want to ...
2
votes
1answer
68 views
Fast paginated result, for various filter clauses
I have been working on obtaining a paginated result from a table in my database (11g). While I have a query that does work (ie, the result is correct) it does not perform as well as I'd like to and I ...
2
votes
1answer
756 views
After truncating a single partition its Primary key's index becomes unusable and all inserts/updates into that partition fail
I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local ...
2
votes
1answer
558 views
Oracle error “too many values”, must be solved with nested SELECTs
I have hit upon a "too many values" error in Oracle, when working with some intricate nested SELECTs.
A simplified description of my problem, on a toy problem describing the hierarchy in a company. ...
2
votes
1answer
1k views
How to move the ownership/copy a table from one user to another?
I've been tasked with doing some tidying of our database. I'm not a DBA expert so here I am. (We don't have a DBA expert either).
We have a user Old_User and another user New_User.
Old_User owns a ...
