Oracle is a RDBMS (Relational Database Management System) created by Oracle Corporation. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java. Also included is support for C (Pro*C, OCI and EXTPROC), C++ (OCCI) and Java (JDBC) ...

learn more… | top users | synonyms

7
votes
2answers
49k views

How do you show SQL executing on an Oracle database?

How can you show the SQL that is currently executing on an oracle db? Extra information that would be useful would include user, session id etc.
0
votes
1answer
23 views

Why would DBMS_METADATA.GET_DDL Fail when i have the SELECT_CATALOG_ROLE Role?

I am trying to get the DDL using METADATA_DBMS.GET_DDL for an object in a database, and I get the following error: ORA-31603: object "MY_PACKAGE" of type PACKAGE not found in schema "S1" ORA-06512: ...
0
votes
2answers
31 views

DBMS_REDEFINITION don't copy data?

Is there an option to use Oracles online table redefinition feature but only copy a subset of the data from the source table? I mean - the source table has 2M records and I want to only copy those ...
1
vote
1answer
45 views

Understanding of schema and tablespaces - recommendation for a later seperation [closed]

DLDR: I would like to get a confirmation of my understanding of tablespaces and schemas (see image) a recommendation how to organize schemas and tablespace to make a later separation easy. ...
-1
votes
0answers
28 views

Exchange Partition

I want to monthly move partitions of my current table into archive. For example, I have two tables with primary key constraint: create table tableSource( id number, create_date timestamp ...
0
votes
0answers
24 views

How can I install Oracle (10g or above) on my MacBook running OS X Yosemite (10.10.4)? [closed]

I did a fair bit of research on it and I read that it can be done with a virtual machine (as Oracle stopped its support for OS X, even 10g page is taken off from official site) but I'm looking for a ...
0
votes
0answers
11 views

Oracle: Recursively self referential join with nth level record

I have self referential table like this: id |level | parent_id ---------------------- 1 |1 |null 2 |1 |null 3 |2 |1 4 |2 |1 5 |2 |2 6 |3 |5 7 |3 |3 ...
0
votes
1answer
2k views

Copying a table in SQL Server database to Oracle database

I am new to database management. I need to copy a particular table "Attendance" in SQL Server database to Oracle database. There is no connection between the two databases. Also, "Attendance" is ...
0
votes
0answers
25 views

(Oracle PL/SQL) How to capture multiple error lines in exception section?

begin some SQL...; exception send_SMS('SQL error: '||SQLERRM); end; This sometimes reports: ORA-02063: preceding line from DBLINK_NAME How to capture the preceding error lines in a PL/SQL ...
0
votes
3answers
1k views

I'm trying to use a variable in Oracle PL/SQL, but I'm getting an error. What's wrong here?

I come from an SQL Server background and I'm trying to figure out how to use Oracle PL/SQL variables. I'm trying to declare a variable and then select that variable. It sounds simple, but I am doing ...
0
votes
1answer
120 views

Mutiple updates with FORALL

I have 2 tables, one is a fact table (100000000 records), and the other is a catalog (2000), I need to update the FK at the fact table, can I use for all in this case?. I wrote this but it doesn't ...
0
votes
3answers
45 views

Will an update in my trigger trigger itself? Oracle PLSQL

Here's my PLSQL trigger: CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG BEFORE UPDATE OF ROOT_ID,PARENT_PHYS_ID ON UNIQUE_PHYSICIAN FOR EACH ROW DECLARE var_root number := :new.ROOT_ID; var_par number ...
2
votes
1answer
314 views

Database design for a table with both bulk insert and single row insert

I have to design a table where records will be inserted in two ways. Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records ...
3
votes
1answer
16 views

ORA-00845 after deleting files accidentally at /dev/shm

I have two instances (ORA1 and ORA2) of Oracle 11g at the same machine. One is starting the other cannot startup anymore. What happend: First my disk was full, so I accidentally deleted all ...
1
vote
1answer
270 views

One Materialized View in Two Refresh Groups

I have five materialized views that I want to refresh in two occasions, every Sunday and at the 1st of every month. I created a Refresh Group for the weekly and that works fine. But when I tried to ...
3
votes
2answers
715 views

FileWatcher Troubleshooting Options

I have setup a Credential/Procedure/Program/File Watcher/Job to monitor a folder for new files, but it is not working. I am trying to figure out what I can check to troubleshoot why this isn't ...
1
vote
2answers
81 views

Is index performance on a TIMESTAMP(6) column impacted when converting it to a number

If I have an index on a TIMESTAMP(6) column, am I forced to use this column as a TIMESTAMP(6) to get the benefits of the index? Can I convert this column to a number with second precision ...
0
votes
2answers
25 views

List full text indexes in Oracle database

Using SQL Server, it's possible to execute a query that returns all the columns that contain a full text index. How can I do something similar using Oracle? Is it possible?
3
votes
2answers
2k views

Can't drop UNDO TABLESPACE

We have Oracle 11gR1 RAC two instances on two different servers, new undo tablespace UNDOTBS20140508 created 30G, I want to remove the old one UNDOTBSX02, I did the following: CREATE UNDO TABLESPACE ...
2
votes
1answer
19 views

Why does DBMS_SESSION.session_trace_enable create multiple files?

I have a trace on a session configured like this: execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''TEST1'''; DBMS_SESSION.session_trace_enable (waits => TRUE, ...
12
votes
4answers
57k views

How to see list of databases in Oracle?

Is there an equivalent to MySQL SHOW DATABASES statement? Is it possible to find databases in a cluster? i.e. databases present on the network on some other system? Could I analyze the files present ...
0
votes
0answers
330 views

Reconfigure the registry for new Oracle installation

I have the following oracle homes installed on my Windows machine Oracle Developer Suite Oracle Discoverer Oracle 10G Client I installed Oracle 10G database manually by following the following ...
0
votes
0answers
437 views

Oracle Flashback Archive aka Total Recall in MySQL [closed]

What would you suggest as a replacement system to achieve the same functionality of Oracle Flashback Archive aka Total Recall in other DBMS, in general or specifically in MySQL or MariaDB? ...
0
votes
0answers
132 views

Optimization of an Oracle query

I have a view which is accessed via DBLink in Oracle. This view is made up of different tables. Now we have to execute queries on this view. Example: Select * from Viewname@dblink where ...
7
votes
0answers
183 views

9i streams capture doesn't evaluate rules

I have a streams replication set up between two oracle 9.2.0.8 instances as sources and an 11g instance as a destination, processing a small subset of data from each database (let it be one table ...
0
votes
1answer
28 views

Error while updating a date column using SQL*Plus variable

I am not able to update the date time which I have already stored in a variable and I am trying to update the another record with date time present in that variable. last_update_time=`sqlplus -s ...
-1
votes
1answer
2k views

error:ORA-01008: not all variables bound

I created a bus table and when I try to insert values in the table like: insert into bus values(&bus_no,&source, etc.,); I get an error: ORA-01008 not all variables bound
2
votes
1answer
494 views

Historical SQL Monitoring

Is there a built-in Oracle feature that extends Real-Time SQL Monitoring to historical activity? I am specifically interested in getting wait events aggregated at the SQL operation level, not just ...
3
votes
1answer
3k views

Oracle 10g: Optimizing an update query that uses a subquery

I have the following query within a stored procedure, and I've been trying to improve its performance to no avail. UPDATE TBL_REPORTING REP SET (REP.ISSUER_ID, REP.ISSUER_NAME ) = ( ...
2
votes
1answer
1k views

Connecting to Oracle SQL Developer locally on Mac?

I'm trying to connect to Oracle SQL Developer locally on my Mac. How exactly is this done? I've looked all over the network, and can't find anything helpful. The only thing I did is installing the ...
3
votes
2answers
213 views

Is it possible to upgrade Oracle 8i to 12c?

We have an old server running Oracle 8i. Is it going to be possible to directly update it to something newer like Oracle 12c, or would that require a complete uninstall and re-install? If we had to ...
1
vote
2answers
3k views

Trouble setting up Nagios to monitor Oracle services

I've got an install of Nagios XI that doesn't seem to want to talk to any of my Oracle services here. I've pulled out the monitoring command and am running it manually, after setting ORACLE_HOME and ...
0
votes
1answer
171 views

Running Oracle Export Utility within a Shell script

I have wasted a lot of time on this. Any ideas of what I am doing wrong here? Script in bash #!/usr/bin/bash . . . /export/home/app/oracle/product/10.2.0/bin/exp $uname/$passw \ ...
0
votes
1answer
1k views

Export, based on query, into INSERT text file

I need to export some data into a file. The data are selected with a query that will often be made of multiple joins. I want the import operation to be as easy as possible. I was thinking of building ...
0
votes
1answer
125 views

How to perform same DML statements on two different servers (Oracle and SQL-Server) simultaneously

I have two servers, Oracle and SQL-Server. I have same database on both the servers. I want to perform DML (insert,update,delete). How can I perform same DML statements on both the servers ...
1
vote
2answers
1k views

Repopulating Oracle tables using data from Excel/CSV file

I'm developing a Java EE application that utilizes Hibernate/JPA. Some of the data I want to use comes from another Business unit's spreadsheet that I need to periodically (re)load into 5 Oracle ...
8
votes
1answer
475 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 ...
2
votes
0answers
27 views

Oracle 12c connection to SQL Server 2014

I have installed Oracle 12c on Centos 7 and I wanted to connect SQL Server 2014 using freeTDS so I installed: FreeTds. unixODBC. then configured odbc on the linux so when I run isql dsn username ...
0
votes
2answers
462 views

Error in trigger: table X is mutating, trigger/function may not see it

Having looked at the following Trigger, still getting the Row 2 error: "ORA-04091: table SUMMITTEST.APL_LOG is mutating, trigger/function may not see it" Please can anyone provide any help in ...
0
votes
4answers
2k views

Stored Procedure result is not showing in Datagrid pane in Toad for Oracle

I have created a procedure in Toad for Oracle 12. When I execute the procedure it does not show the dataset in datagrid pane, while the procedure is being executed successfully. What I am doing wrong? ...
0
votes
0answers
24 views

Oracle 11g installation on fedora 20 - stuck at step6 - Oracle-base.com installation procedure

I am trying to install Oracle Database 11g Release 2 (11.2.0.1) On Fedora 20 (F20)Oracle 11g in my laptop and I am following the steps provided in this Link But it is getting stuck after step 6. Can ...
0
votes
0answers
16 views

oracle database 12c

When starting up automatic instance INSTANCE: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== ...
1
vote
2answers
5k views

Create Oracle DB LINK to a database with no DB_DOMAIN

I'm trying to create a database link between this two DBs. My local DB is 11.2.0.3. SQL> select * from global_name; GLOBAL_NAME ...
0
votes
0answers
35 views

Update a column in table only if a condition is met for number of values for a field in other table

I am trying to figure out how to achieve the following (This is simplified version that ignores non relevant columns) I have table "Item" with columns item_id business_key I have another table ...
4
votes
2answers
98 views

Cummulative Sum with Reset Value

Consider the following table: ID | GROUP_ID | ORDER_VAL | RESET_VAL | VAL ---+----------+-----------+-----------+----- 1 | 1 | 1 | (null) | 3 2 | 1 | 2 | (null) ...
0
votes
1answer
29 views

Lookup multiple ids in seperate table with 2 subquerys

I have a query from a timesheets tables that I am using to select a submitter, approver, and a date. SELECT PRTIMESHEET.POSTED_TIME, PRTIMESHEET.PRRESOURCEID as "SUBMITTER", ...
1
vote
2answers
464 views

How to set the sequence to the last used id for all tables in Oracle?

I'm working on an open source elearning system called Moodle which usually uses MySql or Postgresql, but can also use MSSQL and Oracle. A client wishes to use Oracle, which I have limited experience ...
0
votes
1answer
130 views

Setting the delimiters for Oracle multiline Procedures Oracle sqlplus

how to set the procedure delimiters in Oracle sqlplus. I am new to Oracle SQLPlus. [oracle@Server1 bin]$ ./sqlplus ******/*********@1ipaddress:1521/test</home/oracle/user/test.sql SQL*Plus: ...
0
votes
2answers
43 views

Use column alias in GROUP BY

I have a query where I chain two columns. Why does the alias not work in the GROUP BY clause but in ORDER BY clause, it does work? How can I write the select correctly? SELECT KOS_VER_ID AS ...
1
vote
2answers
88 views

Why is my query returning an unexpected result?

Here are the tables I am using: parts(pid,pname,color) suppliers(sid,sname,address) catalog(sid,pid,cost) I need to find the name of suppliers that supply two different parts with the same ...