All versions of Oracle. Also add a version-specific tag like oracle-11g-r2 if that context is important in the question.

learn more… | top users | synonyms

3
votes
1answer
1k views

Oracle RAC on Virtual machine

I have Oracle RAC on the very powerful servers and it can be said that these servers have extra memory,cpu and storage :) ...so my servers are much more powerful than it is needed. My manager and ...
9
votes
2answers
2k 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 ...
4
votes
1answer
144 views

Rank Partition?

I have a table which holds customer information with an Account state (working or Deactivated) for every single day. I'm trying to reduce this information by showing start and end dates for the ...
2
votes
1answer
464 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)) ) ...
9
votes
4answers
35k 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 ...
1
vote
2answers
1k views

Commercial database systems versus open source database systems [closed]

Can anyone tell me what are the reasons for buying a commercial database system and not using an open-source free version? The reason for my question is that: Commercial systems cost upwards of 10....
2
votes
2answers
12k views

Reduce Size of SYSAUX

I am trying to reduce the size of our SYSAUX Table. Currently the largest four occupants are below. As you can see it is mostly the Server Manageability items. select occupant_desc, ...
0
votes
1answer
275 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. ...
2
votes
2answers
2k views

How do I recreate a deleted logfile on an Oracle database?

For my database assignment we were looking into "database corruption". I was asked to delete the second redo log file, which I have done with the command: rm log02a.rdo this was in the $HOME/ORADATA/...
7
votes
2answers
27k 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 /u01/app/11.2.0/grid/log/...
3
votes
1answer
1k 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 ...
3
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 ...
3
votes
0answers
2k 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
3k 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 ...
3
votes
1answer
139 views

How to search for an ID and its descendants in this query?

I have the following tables: Project Unit ------------- --------------- ID | IDUnit ID | IdParent I have to select all projects that have the IDUnit ...
1
vote
1answer
914 views

Error with Oracle trigger, invalid identifier

I have the following DML statement that is working perfectly, i have tried to make it into a trigger but it isn't working. DML statement UPDATE HOLIDAY_RESERVATION R SET SUBTOTAL = NVL((SELECT F....
0
votes
1answer
1k views

export SQL statements from Oracle 11g database via command line

I'd like to export an Oracle 11g database as a series of SQL statements via the command line. I'd like the functionality of SQL Developer (the process described here http://dba.stackexchange.com/a/...
1
vote
1answer
320 views

Update statement errors, parenthesis, failed validation ORACLE

i am writing an update query and it is giving me errors, what i am trying to do is to take a value multiply it added to another value. Original statement The statement below works flawlessly, ...
5
votes
3answers
3k 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 ...
0
votes
0answers
4k views

Oracle GoldenGate replication is stuck [closed]

I deployed Oracle GoldenGate to integrate many-to-one databases. After initial loading everything works fine. Until recently, some of the replicat seem to stuck and do not proceed to change ...
3
votes
1answer
3k views

Where can I download older versions of Oracle Instantclient?

Version 10.2 is linked from here, but I need 10.1. Any clues where to get older copies of the instantclient packages? http://www.oracle.com/technetwork/database/features/instant-client/index.html
4
votes
3answers
12k views

Import Oracle full dump file to fresh Oracle installation

I am importing a Oracle dump fill created on a AIX Oracle 9.2 installation with a Full Export option to a Windows Oracle 10.2.0.4 on Windows 2008 R2 x64. I want to import the file to a clean ...
2
votes
1answer
8k views

How to convert postgresql dump files to oracle dump files? [closed]

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
7k 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 ...
10
votes
2answers
2k views

Recovering logical space from a tablespace

I have a tablespace called DATA, and it is setup with auto-extend as false. This tablespace has two datafiles and is set up so it takes up 350 GB of physical space. A week ago I queried ...
4
votes
1answer
1k views

How long does it take for Oracle Flashback Database to restore the data?

How long does it take for Oracle Flashback Database to restore the data? Is it an instantaneous operation or dependent on the data volume?
3
votes
3answers
5k 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 ...
2
votes
1answer
729 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 ...
1
vote
1answer
5k views

Resolving frequent LGWR switch events recorded in Event log with heavy dblink/materialized view activity - Oracle 11g XE

We use a student information system, PowerSchool, marketed by Pearson. PowerSchool uses Oracle as a backend database, and we have the need to create some of our own reporting views. Pearson says if ...
9
votes
2answers
2k views

100 TeraBytes Capacity Database - Resources and Time Estimates

I am working on a 'back of the envelope' calculation for a 100TB reporting database setup. I am seeking out thoughts from the experts here. Proposed environment: Storage Capacity ~ 100TB Tables ~ ...
1
vote
3answers
588 views

changing a range into discrete values for a field in oracle

I have a data table that contains records like this: ZIP Ground Air 010-041 005 305 150-163 004 304 As I have it right now, it's actually an excel file but I eventually need to ...
1
vote
1answer
139 views

What should be checked after an Oracle migration

After a large data migration, what needs to be checked to be confident that the data move has succeeded? the number of tables and rows in each schema is the same. the text data in the tables has ...
4
votes
1answer
863 views

Why does it take a long time to drop a function based index in Oracle?

I have a function based index that takes 25 minutes to create on a table of 94 Million rows. When I drop the index, it takes 18 minutes. Why does it take so long? I would have thought the drop would ...
3
votes
2answers
26k 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 ...
-1
votes
2answers
2k views

Data Base Administration Oracle vs MySql? [closed]

I have worked with MSSQL for a while now, but now we want to try to move to some open source for a specific part of the company's products or explore Oracle if possible. What would you say are the ...
5
votes
2answers
232 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 ...
2
votes
1answer
2k views

Installing Oracle XE 11gR2 on Fedora 15 - linux group permission deadlock

During installation of Oracle XE 11gR2 on Fedora 15, I am presented with: The install cannot proceed because ORACLE_BASE directory (/u01/app/oracle) is not owned by "dba" group. You must change ...
3
votes
3answers
5k views

Can Oracle SQL developer export data as a set of Update commands?

Oracle SQL developer gives us the option of exporting data. But it only gives us the option to export as a set of Insert statements. I have two databases with a mirrored table. One of them has some ...
3
votes
1answer
3k views

How to qualify variable inside trigger body (PL/SQL )?

I can explicitly qualify variable in the body of stored procedure or function : create or replace PROCEDURE ptest AS int_val INT; BEGIN ptest.int_val:=0; END; / How to do the same inside trigger ...
17
votes
2answers
117k views

oracle - list users with access to certain tables

I'm sure this has been asked before but I can't seem to find the relevant details for the following. Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
0
votes
0answers
238 views

Can not open oracle 10g database when migrated from oracle sql developer 1.5x to 3.1x [closed]

I have migrated from Oracle sql developer tool 1.5x to 3.1x and imported my connection settings with database 10g. When I try to open a connectionstring in the developer tool I get this message: ...
4
votes
2answers
602 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 ...
5
votes
4answers
174 views

Getting the value of 'foo' for the closest previous row with higher 'bar'

From this initial data: select level as foo, mod(ora_hash(level),4) as bar from dual connect by level<9; /* FOO BAR ---------------------- --------------------...
2
votes
1answer
637 views

Access Data that is stored in Primary FileGroup On SQL Server through DBLinks on Oracle

I am using dbLinks to fetch data from SQL Server 2008 to Oracle (10g)database. When I fire the command : select * from "test_table"@dbLink Oracle throws error : select * from "test_table"@dbLink ...
4
votes
3answers
7k 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
158 views

Is a process created in Oracle for every SQL statement?

After reading the Oracle docs, it´s still not clear for me if an Oracle process is created for every SQL statement or just for every connection (or not really related to both). Any comment here would ...
2
votes
1answer
595 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
1k 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 ...
8
votes
3answers
2k views

Should date formats be specified in SQL statements?

I see code from developers using implicit date conversion. I would like a definitive answer to why they should not do this. SELECT * from dba_objects WHERE Created >= '06-MAR-2012';
2
votes
1answer
505 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 ...