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

1
vote
2answers
503 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
132 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
799 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
24k 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
213 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
4k 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 ...
16
votes
2answers
103k 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 ...
1
vote
0answers
221 views

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

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
541 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
166 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
577 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
6k 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
154 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
569 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
968 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
475 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 ...
1
vote
1answer
565 views

Oracle Benchmarking (SQL_NO_CACHE …)

I want to benchmark some queries on an oracle System from a JDBC application. However, the database seems to cache at some point in time. However, this is not intended in this case. Is there ...
1
vote
4answers
889 views

How to find out which data I just imported into Oracle?

I imported two dump files (no idea what they contained, my goal is precisely to find out what they contain) into Oracle, but now I am struggling to identify what is the data I imported, and what data ...
2
votes
3answers
4k views

Oracle “Import from Export Files” screen refusing both SYSADMIN and Normal users

I have just installed Oracle 11g on Win64, and I want to import some .dmp files. I am trying with the tool Oracle Enterprise Manager 11g: If I log in with SYS as SYSADMIN, and go to Import from ...
1
vote
2answers
26k views

ORA-01034: ORACLE not available

I tried to start the listener and do a dbstart, but it looks like it is still saying "Oracle Not available". Maybe the dbstart didn't work. Any suggestions would help me. [user@fedora16 bin]$ echo ...
4
votes
3answers
3k views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I have installed oracle_fdw successfully. After I create extension oracle_fdw and foreign table it shows the following error. Is this a server configuration problem? --env Oracle : 10.2.0.1 ...
1
vote
1answer
752 views

oracle - does this have the potential to cause a lock?

Note how no commit was done in between each statement. Could this cause a table lock or blocked session? delete from tableblah; insert into tableblah (select * from othertable);
1
vote
1answer
2k views

creating a table from a selection and then using group by - oracle sql

I'm working in oracle and I can't figure out what's wrong with this statement. The error it gives me is must name this expression with a column alias CREATE TABLE shipping_costs_grouped AS ...
2
votes
1answer
9k views

Oracle: what permission is necessary to “grant create table” to another user?

What permission should A have in order to grant B permission to create a table? logged in as user A, I execute grant create table to B; and receive the following error: SQL Error: ORA-01031: ...
2
votes
2answers
240 views

Temporary replication of data between continental datacenters

I need a way to temporarily replicate data between two data centers on two continents for a small number of large tables in Oracle. The data in the tables is around 30Gb but the transfer rate is only ...
4
votes
7answers
41k views

How to export an ER diagram from an Oracle DB?

I am developing this application with an Oracle database. I haven't been documenting the data model, but now an auditor wants to have an entity relationship diagram of my database. There are many ...
3
votes
1answer
631 views

oracle 11g seems to die every 2 days, how can i debug what's happening?

I installed oracle 11g on ubuntu 11.10, but it seems that every couple of days I'll get to work and the database isn't running but the listener is. To fix it, all I have to do is login and start it ...
0
votes
2answers
324 views

shall I use multiple separete tables or one table with multiple partition on oracle

I have an entity lets call it Response, and another entity called ResponseType, Response entity contains the ResponseType; so when I am building the physical model I confused with how to implement it; ...
2
votes
2answers
215 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 ...
0
votes
1answer
10k views

How to set up tns listeners for Multiple Oracle Installations on One machine

So I have two oracle installations on a development/testing machine (10g and 11g, respectively). The platform is RHEL4. Now I need to have both these oracle up and have tns listeners for them ...
3
votes
2answers
1k views

Move Oracle data to and from MySQL with the help of PHP?

My application is in PHP which by default uses MySQL db. I have connected MySQL & Oracle dbs with PHP. Now I want to move data from Oracle db into Mysql db through PHP. Is it possible to do? Is ...
0
votes
0answers
244 views

Merge two packages into one package in Oracle 10g

I want to combine two different packages into one single new package in Oracle 10g. Is there some way to accomplish this?
2
votes
3answers
1k views

What is the simplest way of excluding the current row from the result of an analytic?

I want to do something like the following: with w as ( select level as foo, decode(level,8,1,mod(level,4))*100 as bar from dual connect by level<9 ) select foo, ...
3
votes
2answers
2k 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 ...
1
vote
1answer
1k views

Using Apache/ mod_proxy_ajp/ APEX Listener together

Our application is developed with APEX, and it is accessed through APEX listener through HTTP on default port 8080. Now, we want to switch to Apache with mod_proxy_ajp and reconfigure APEX listener to ...
0
votes
1answer
127 views

Regular expression problem

SELECT FIRSTNAME,LASTNAME, EMAIL FROM CUSTOMERS WHERE REGEXP_LIKE(EMAIL,'[A-Z0-9._%-]{1,10}@[a-z0-9._%-]{1,5}\.[net|com]','i'); I know this is not practical or make a ton of sense, but its a ...
2
votes
1answer
1k views

export data from oracle 10g

I installed Oracle 11g on my computer. When I export data from Oracle 10g, I get the following error code. I have translated the error in parenthesis. EXP-00008: 遇到 ORACLE 错误 904 (encouter oracle ...
2
votes
1answer
421 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 ...
7
votes
2answers
2k views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different ...
10
votes
3answers
6k views

Where can I find example databases for MySQL, SQL Server and Oracle?

I want to experiment with the 3 large database management systems. For this purpose it would be cool to have the same database (the same tables, etc.) for each of those. Where can I find sql scripts ...
4
votes
2answers
157 views

Virtual Oracle server for education?

I am currently a College Student and am taking an introduction to Oracle 10G. I cannot seem to make it to class all of the time and am curious to ask if there is any Virtual Oracle Server or something ...
3
votes
2answers
8k views

Synchronizing data between MySQL and Oracle databases? [closed]

I want to sync data between Oracle & MySQL databases. What is the best possible way to achieve this? Are there any tools/software available? This is not a one-time but an ongoing operation. The ...
1
vote
1answer
272 views

Is it possible to schedule table update/refresh in Oracle Application Express?

I have SQL Server data dumped into a text or csv file available via HTTP. Is is possible to schedule Apex to create a table on that data and keep it updated? How?
1
vote
1answer
633 views

Get statistics of open connection to linked servers

How can I get information about connections from my MS SQL 2008 server to linked servers (actually it's an Oracle db)? Things like number of currently open connections, and history of opened and ...
3
votes
2answers
3k 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 ...
3
votes
1answer
876 views

How can I execute multiple DDL statements within a transaction in Oracle?

Is it possible to execute multiple DDL statements under a single transaction with Oracle database (10g etc.?) What I'm trying to accomplish is some sort of "upgrade" functionality of the database ...