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

0
votes
0answers
55 views

Enumerate open oracle connections

How can I troubleshoot which processes are keeping connections open? Background: A QA installation of several software components is generating an ORA-12516 error now and then, and I'm not sure which ...
1
vote
1answer
2k views

Error calling stored procedure from trigger in Oracle Database 11g

I'm trying to create a trigger that calls some Java code whenever a table is inserted/updated. I'm aware of possible performance issues, I don't need advice that I should not do this; I just need to ...
2
votes
1answer
10k views

How do I grant execute permission to an Oracle database user?

I'm trying to call a Java function from Oracle database that does the following: Process p = Runtime.getRuntime().exec("..."); When I try to run it, I get the following error: ORA-29532: Java call ...
2
votes
2answers
3k views

Setting NLS params in logon trigger don't work in oracle 11g

We have a trigger setup so that when a specific user logs in, their NLS session parameters are modified. This used to work on Oracle 10g. We've just migrated to Oracle 11g RAC, and the session ...
4
votes
1answer
949 views

Comments in EXPDB parameter file

Is there a way to put comments in a data pump export parameter file?
3
votes
2answers
17k 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.
1
vote
1answer
214 views

Can I recover an overwritten incident trace file?

I was looking at incident trace file in oracle database but some how that file got overwritten. As of now I have ecrep_ora_26467_i49966.trm but trhe corresponding .trc file is lost. is it possible to ...
2
votes
1answer
1k views

Can RMAN duplicate be used to “replace” an existing database?

I have an unused database B, not valuable. I'd like to make a copy of database A, but do not want to go through all the manual steps RMAN requires to set up the auxiliary database. Can I use RMAN ...
1
vote
1answer
1k views

Rule of thumb: AUTOALLOCATE or UNIFORM size for tablespaces

Is there a rule of thumb for creating a tablespace with extent management AUTOALLOCATE or UNIFORM? If the latter applies, how to estimate a reasonable extent size? Even more, what should apply for ...
0
votes
2answers
2k views

oracle 10g xe to 11g xe upgrade

The oracle 10g DB that is being currently used has exceeded the limit of 4GB. For the application to continue the db needs to be upgraded to 11g. But the upgrade doc requires a data pump export to be ...
3
votes
1answer
248 views

Oracle 11, incremental backup goes wrong

Got a pretty big instance on a one-week backup cycle with an incremental (differential) strategy .. or rather, that's what it was supposed to be. Somehow the daily differentials seem to come out as ...
0
votes
1answer
1k views

Override default SERVICE_NAME in init.ora

I'd like to define several service names for my instance in such a way that users should not know the actual instance name. This is what I have done in my init.ora: db_domain='company.com' ...
1
vote
1answer
5k views

ORA-39165 Schema was not found in export

I'm getting the following error on a data pump export: ORA-39165: Schema OFDAT was not found. First I verified that I had full export permission: GRANT EXPORT FULL DATABASE TO OFDAT; Then I ...
3
votes
2answers
477 views

Oracle extents - How can I have so few with this large size?

Note on duplication: This is not a duplicate of "Oracle extents - How can I have so many with this small size?" This is a separate question I entered which happens to be in the same domain. This is ...
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 ...
8
votes
3answers
2k 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 ...
0
votes
1answer
1k views

Upgrading 10g database to 11g database

Is doing a full data export (expdp) from an Oracle 10g (10.2.0.1.0) database and then doing an import of that dump file (impdp) into an 11.2.0.2 database a valid and acceptable upgrade path from 10g ...
3
votes
1answer
132 views

Thoughts about Oracle backup procedure for 11.2 (beginner question)

Two days ago we "found out" that our old, not taken care of, horribly neglected, Oracle DB server was extremely important to our business. The only person who knew anything about this server was ...
0
votes
2answers
568 views

schema analyzer tool

I am currently using TOAD to connect to the database. I have around 20 tables that I have been asked to analyze ( I am new to project ). I think the first place to start is to understand the relation ...
1
vote
1answer
659 views

Oracle Client vs Oracle Instant Client on Linux!

to have the following setup on a Linux box: SQLPlus client library (oci.dll)!!!! ORACLE_HOME is set for users is Oracle Instant Client enough or I need to have the Oracle Client installed on the ...
1
vote
1answer
8k views

Why does DBMS_JOB.SUBMIT execute immediately when NEXT_DATE is in the future?

I wanted to create a job that will run tomorrow at noon and recur every day at noon, so I executed this script: DECLARE X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what ...
1
vote
1answer
464 views

how to see multiple column values in new line [duplicate]

Possible Duplicate: how to query multiple row data in single column in oracle? my table structure is given bellow deptid ename ------ ------ 10 CLARK,KING,MILLER 20 ...
0
votes
1answer
129 views

Best practices in securing Oracle Standard edition one [closed]

I have a database server with Oracle Standard Edition One. I'm interested what are the best practices to secure Oracle? I have Java web application installed in the same server. The Java application ...
8
votes
2answers
1k 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 ...
1
vote
1answer
287 views

Error creating linked database to oracle

CREATE DATABASE LINK myLINK CONNECT TO username IDENTIFIED BY password USING 'PROD'; OPT= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.6.1.5)(PORT = 1521)) ) ...
5
votes
4answers
13k views

Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes

Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple ...
0
votes
1answer
240 views

How do oracle date time work?

I have following where condition in query. What the time is between T1 and T2 means it includes or excludes the boundary. Also between T1 and T1 means what?I am confused as both returns result. ...
4
votes
1answer
7k 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 ...
3
votes
1answer
621 views

Oracle explain for cascading delete is wrong

My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it. If I hit the explain button on a statement like this ...
2
votes
1answer
3k views

How to create oracle linked server in oracle server itself?

Is it possible to create oracle linked server in other oracle server? I actually wanted to do this with sql server but in sql server I couldn't run long query (8000 limit). I have access to ...
2
votes
0answers
1k views

ORA-01017: invalid username/password; logon denied

I've come across the abnormal situation, and can't understand what happens. I am connecting databases using PL/SQL Developer 8.0.4.1514(I've also tried v7,but same) . Sometimes(When I am trying to ...
0
votes
2answers
2k views

How do I manage column order when using PL/SQL's %ROWTYPE records?

I have a table with a large number of columns, which has data gathered from several other tables. I want to write some PL/SQL like: DECLARE TYPE t_toptab_list IS TABLE OF toptab%ROWTYPE; l_data ...
5
votes
3answers
711 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 ...
2
votes
2answers
4k views

How to convert postgresql dump files to oracle dump files?

How can I convert postgresql dump files to oracle dump files? If anyone has experience in this, or knows of an open source tool I can use, please share it with me.
1
vote
2answers
2k views

Sum values from two tables ORACLE

I want to use two values, in two different tables, to be used as a total in a third table. For instance: table accommodation and flight Add value from acc_price with value from flight_price Add ...
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 ...
1
vote
1answer
524 views

Reverse engineer Oracle database in visio

Trying to reverse engineer tables added into a database but when i connect, I get strange Chinese symbols with a lot of apex stuff. I dont see anywhere the tables that i added, what am i doing ...
2
votes
1answer
888 views

SQL on Mac OS X

I'm looking for something like "Oracle database express" to be used on a mac. I hope this question won't offend anyone though. I'm absolute beginner on sql. Your suggestions are most welcome.
3
votes
2answers
12k 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 ...
5
votes
2answers
138 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 ...
4
votes
2answers
364 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
3k 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 ...
2
votes
1answer
411 views

Oracle: UTL_RAW.BIT_OR(2,1) gives me 2 instead of 3. Why?

Why am I getting 2 instead of 3 when I perform BIT_OR(2,1)? SELECT UTL_RAW.CAST_TO_NUMBER ( --> decimal: 3? nope, 2 UTL_RAW.BIT_OR( --> binary: 11? ...
6
votes
4answers
602 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 ...
2
votes
1answer
246 views

Oracle ash report

Recently, i have been handed over with an ash report from DBA. To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
2
votes
2answers
189 views

Overhead of DBMS_Profiler

If I use DBMS_Profiler to profile a long running (5 hour) PL/SQL script, how much longer should I expect the script to run with profiling? I realize this will be dependent on what the script is ...
3
votes
2answers
1k views

Oracle 11gR2 expdp table converted to CSV from Linux

With Oracle expdp, I can export the tables I want with expdp INCLUDE=TABLE:table1,table2,table3 I need a way to convert this into CSV on a linux machine. I've seen Windows tools but don't know ...
2
votes
1answer
345 views

Table Statistics Affect Index Only Query?

We have a query that was getting poor performance. The root of the problem could be reproduced using a simple query accessing only one index to retrieve one column (the indexed column) from eight ...
3
votes
2answers
2k views

Create unique DB_NAME and DB_DOMAIN for an Oracle 11g instance

I am somewhat confused by these terms in the init.ora file: db_domain db_name instance_name plus the database name which is provided to the create database command. I do not completely understand ...
1
vote
3answers
3k views

Installing Oracle 11g on Ubuntu 11.10

Does anyone know to how to install Oracle 11g on Ubuntu 11.10?