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

0
votes
1answer
32 views

Sub query with not equal

Sub query with not equal table_1 col1 SV0362 RU0177CSPG NO0124CSPG VN3582 AT9923CSPG BZ0324 PE0309 AS4293EXPT ML0331OMKT table 2 colA OO56128665 OO56128665 OO56128665 OO56128665 US0170 US0170 ...
0
votes
2answers
111 views

SQL insert into … values (…, select(…))

I am trying to insert 25 users, user1...25 into Members with the same GR_ID = 2 with command: insert into MEMBERS (GR_id, username) values (2, (select username from USERS where username like ...
0
votes
2answers
90 views

Composite Primary Key column order

How would you do the ordering of the PK columns in this case (Oracle 11gR2 Standard Edition): I have a database where I offer the same application to many users. I want to be perfectly sure that all ...
1
vote
0answers
116 views

sql query takes too long to execute [closed]

I am having trouble with this query. It takes 10 hours to execute. WITH CONSUM AS ( SELECT C.ID_MEASURER,C.ID_MEASURING_POINT,C.CONSUM_TYPE FROM GCGT_RE_CONSUM_H_E C WHERE C.DAY ...
0
votes
1answer
44 views

Compare dates in Oracle SQL [closed]

I have a table LoanDeadline that has a column called deadline, it's simple text and not Date and it's in the format of July 25, 2014 How do I compare this with sysdate ? So far I have SELECT ...
0
votes
0answers
50 views

Is it possible to copy mounted database without RMAN?

I need to make a copy of production database for development. For some reason I can't use RMAN. My question is: can I copy standby database while its in mounted state and recovery is canceled? Of ...
2
votes
1answer
50 views

What is this view being used in my query?

I'm running EXPLAIN PLAN on this query: SELECT DISTINCT l.item_num, l.repl_code FROM item_w l JOIN item i ON l.item_num = i.item_num LEFT JOIN item_w r ON l.item_num = r.item_num AND l.repl_code = ...
0
votes
1answer
112 views

Oracle 12c SYSTEM user locked out

We have a development Oracle 12c database and the SYSTEM user account is locked out for one of the SID's (works fine with another SID). When I try to do a conn SID / as sysdba it keeps connecting me ...
2
votes
1answer
210 views

Oracle DB occasionally slows down when “Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN”

After midnight Oracle Database 12c turned to "slow mode". DML statements became about 10 times slower, though "selects" feel good. CPU load as usual, disk I/O is perfect (tested by copying 500m file), ...
0
votes
2answers
79 views

What is the easiest and best way to migrate data from Oracle in Linux Platform to SQL Server in Windows Platform?

I came to know there is a tool called Microsoft SQL Server Migration Assistant v5.3 for Oracle which can be used to convert the Oracle database to Microsoft SQL. But I have Oracle database in Linux ...
0
votes
0answers
55 views

Last few rows from a non-indexed table in Oracle

I'm trying to run a query on an Oracle DB through SQL Server [dont' ask, it's a requirement] The Oracle table in question [lets call it hugeTable] has over 200 million rows and no index. I'm not ...
0
votes
1answer
43 views

Licensing of Oracle Standard One multiple homes

we I have a server with Oracle SE One ... I need to separate the test data from prod data as each one has it's own schema, I will do 1 of 2 which is both are OK for the dev team: Create a new ...
0
votes
2answers
11 views

Prevent auto commit of DROP without SET AUTOCOMMIT OFF

I dropped my table and I lost my data and I can't roll it back because DROP statement automatically commits. How can I prevent auto commit action of DROP without using SET AUTOCOMMIT OFF?
1
vote
2answers
92 views

Best practices for service name / aliases in tnsnames

I have this idea that service names / aliases given to connections descritors in a tnsnames.ora (not the one in the server that dblinks use, but the one for the public) file should in no way be ...
2
votes
1answer
165 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 ...
1
vote
2answers
302 views

Delete backups from old incarnation in rman

I did a refresh on a non-production database and these are the incarnations: List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time 1 1 ...
0
votes
0answers
29 views

How to manage MySQL database with Oracle Fusion Middleware?

Is it possible to connect and manage a MySQL database with Oracle Fusion Middleware?
0
votes
0answers
47 views

Right Outer Join (column name doubt)

I've read the following code a few times trying to figure out what it is doing. "A right outer join is that which returns the unmatched rows from the right tables with matched rows from the left ...
0
votes
1answer
43 views

Environment variable for two oracle_home

i have installed oracle binaries at two different location on Oracle Linux , /u01 & /u02 when i set environment variable for databases created for oracle_home in /u01 it works fine as i edited ...
1
vote
1answer
76 views

How to get index last modified time in Oracle?

Given an index name, I want to obtain its last modification time and the author of modification in Oracle 11g. Is there any query to achieve this?
0
votes
1answer
177 views

How to write a procedure /job to kill all session running for more than 5mins in Oracle?

How to write a procedure /job to kill all session running for more than 5mins in Oracle? So, I query v$session and find out that for a particular user, program pair if the session has been active for ...
0
votes
1answer
76 views

Oracle : Is there a way to grant the ability to run a job to a role?

We have several dbms_scheduler jobs in an 11.2 database that we would like users with a certain role to be able to run. As such, we don't want to grant the ability to run directly to the users, as it ...
0
votes
1answer
81 views

Modify text file with Oracle PL/SQL

I'm trying to open and replace a string in a text file with the following procedure: BEGIN v_file_name := 'text_file.txt'; v_filehandle := utl_file.fopen(v_file_dir, v_file_name, 'W'); v_id_nextval ...
1
vote
2answers
730 views

sqlplus: ORA-01017 logon denied from one host but OK from another

I have a strange behavior for remote sqlplus login to Oracle 11.2 that I cannot explain: 1) I have an Oracle 11.2 server running on a virtual host, say host1 2) I set up another VM, say host2 (CentOS ...
0
votes
0answers
15 views

sqlplus: ORA-01017 logon denied from one host but OK from another [duplicate]

I have a strange behavior for remote sqlplus login to Oracle 11.2 that I cannot explain: 1) I have an Oracle 11.2 server running on a virtual host, say host1 2) I set up another VM, say host2 (CentOS ...
2
votes
1answer
166 views

Table name with special character in the end

I have a table in database(oracle sql) which somehow contains one special character at the end which I dont know.I am trying to get that special character from its entry in table tab. SQL> SELECT ...
0
votes
1answer
76 views

Distributed transaction coordinator between SQL Server and Oracle

I'm not sure about distributed transaction coordinator so please bare with me on this, I'm probably asking this question wrong. I know distributed transaction coordinator is used in SQL Server and can ...
1
vote
1answer
113 views

Oracle Tables Visible to Other Users

I have an Oracle database that has one user that has access to everything. This was fine when we only had one user working on the database. But now that we have more users we want to tighten up ...
0
votes
2answers
863 views

How to create an oracle dump(.dmp) file using Toad?

We can export the entire database schema(skeleton) along with its data in one .dmp file using oracles "exp" tool. But how do I do it using Toad only. I can't use this C:\Users\kaunds_oliver>exp ...
5
votes
1answer
73 views

Why does dropping a MVIEW trigger a full refresh?

I just created a materialized view in Oracle but realized I used the wrong script, so I wanted to drop the MVIEW again. The MVIEW was created with a statement like this: create materialized view ...
-1
votes
1answer
40 views

problem faced while starting up a database in oracle

I have created a database named 'test' in oracle 11g express edition.Now while I am trying to stratup "test" after shutdown of database, by default "XE" is getting started.How I solve this problem?
0
votes
0answers
42 views

What is the cost to create Materialized View log

Recently I ran DBMS_ADVISOR.TUNE_MVIEW and it gives me several lines to CREATE MATERIALIZED VIEW LOG ON "MYSCHEMA"."COMPANY" WITH ROWID CREATE MATERIALIZED VIEW LOG ON "MYSCHEMA"."BOARD" WITH ...
1
vote
1answer
123 views

Performing deltas / diffs over VPN from Oracle to SQL Server database

I have inherited a reporting structure that is quite the Rube Goldberg device. I have a collection of MS Access 2003 databases that I have begun to migrate to SQL Server. My current hangup is that we ...
2
votes
1answer
102 views

How to move 200 materialized views without having to re-create in the new home

I am moving a database with 200 materialized views into a new server. All of them are 'fast refresh' materialized views , means they have corresponding materialized view logs, in this case sitting on ...
0
votes
1answer
135 views

Oracle dbms_datapump removes dumpfile after succesful import

I'm working on a PLSQL script to import a Oracle datapump dumpfile using the dbms_datapump package. Each time the script has succesfully imported the contents of the dumpfile I notice that the ...
0
votes
1answer
110 views

Oracle Triggers - Before Update and Before Insert - Separate or Combined?

I'm running Oracle DB 9i. I have a table with various constraints to ensure data integrity. In addition to the constraints, I have triggers on BEFORE INSERT and BEFORE UPDATE to ensure that ...
0
votes
1answer
53 views

Migration query taking time

I'm doing a migration from Oracle to SQL Server and, for one of the tables where we have 3 million records, it is taking a lot of time to complete. When I check the activity monitor, I can see the a ...
0
votes
0answers
46 views

oracle emca refusing sys password

I want to reconfigure enterprise manager and change dbcontrol http port but emca is refusing the sys password yet am sure its the correct one am using with sqlplus and even logging onto em db web app. ...
0
votes
1answer
40 views

different outcome based on function execution method

I am mainlining a legacy system and realize that the answer to this may take a deeper explanation of the code but before I go that far I wanted to make sure the solution wasn't something more simple. ...
1
vote
1answer
151 views

On reboot listeners don't start but restarting the service after will start them

when I reboot ubuntu the listener service comes up but it doesn't seem to have any listeners lsnrctl status LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-JUL-2014 15:34:43 Copyright (c) ...
1
vote
1answer
39 views

How to verify that a certain commit has been transmitted to standby

Scenario: Oracle Data guard 12c with 1 primary and 1 standby, Max. Av. protection mode Primary open, standby mounted (all green in OEM) Perform a dummy CTAS in primary Question: Without opening ...
1
vote
2answers
76 views

How to construct a set-based query for subset problem?

I have a DB of the following structure (picture shows simplified version) How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe ...
1
vote
2answers
58 views

Optimize rows to columns conversion

I have a big Oracle table (around 40 million rows) that looks like this: ID Name Question Answer Reason 3 Name1 1 Yes blah blah 3 Name1 2 No NA 3 Name1 3 ...
0
votes
1answer
179 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
2answers
36 views

Update the other columns which are for to have minimum of other 2 columns , difference between 2 columns , and others

I am working on data in oracle 11g as below : subscriber_id | count_s | count_t | min_count | diff | negative_diff | positive_diff 137606401 3 3 3 0 ...
0
votes
2answers
43 views

Modifying a part (one procedure) in oracle database package

Is it possible to modify an individual procedure inside an Oracle DB Package? instead of recreating (modification is not done using a SQL editor, so cannot modify a place and easily recompile the ...
1
vote
2answers
91 views

Aggregate function / query in Oracle 11g

I am working in Oracle 11g. I have been racking my brain all day trying to figure out how to write a particular SQL statement (am a Java/.NET Developer, would not consider myself a SQL expert) I ...
2
votes
1answer
44 views

Oracle 10.1 to 10.2

I'm trying to move an Oracle 10g database from 10.1.0.5 running on W2K Server to 10.2.0.5 running on a Windows x64 install (either Win 7 or 2008 R2). I can't upgrade the source server, so I can't ...
1
vote
1answer
77 views

Oracle RAC concept

This might be a dump question, but I've been reading so much different things, that I'm not sure anymore. I tried to understand the concept of the Oracle RAC, so I've been reading the datasheet form ...
3
votes
3answers
183 views

Nested table as condition prevents using index

The following query joining a subquery does a TABLE ACCESS FULL when the master-table uses nested table as condition. When the condition is a simple =, the same query does an INDEX RANGE SCAN as ...