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

5
votes
1answer
2k views

Oracle concatenation of queries and function index usage

I have a problem with something I can't understand about indexes in Oracle 11g. We can create test data with: create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 ...
3
votes
4answers
10k views

Sqlplus parameterized query via file

I would like to issue a parameterized to Oracle via sqlplus with all the the parameters provided in a text file. Is there a way to do this? The query has more parameters than is reasonable for a ...
4
votes
2answers
2k views

SELECT statement with an explicit list of columns and then an asterisk (*)

SQL Server allows to use SELECT statements similar to: SELECT FirstName, LastName, City, * FROM dbo.Customers When I try to execute such a query on Oracle database, I get the following error: ...
15
votes
5answers
161k views

How to update a table from a another table

I have one table A has column (id, field_1,field_2), and another table B has column (id,field_2) Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. ...
5
votes
1answer
2k views

Do I need to enumerate the columns used in a materialized view when creating the MV log?

I'm currently creating materialized view logs on a whole bunch of tables in order to support materialized views with FAST REFRESH. The materialized views we're creating contain approx 30 columns from ...
5
votes
3answers
1k views

What could be the reason for disallowing a sub query in the values clause?

For example SQL> create table dates(d date); Table created. SQL> insert into dates select sysdate from dual; 1 row created. SQL> select * from dates; D --------- 28-MAY-11 SQL> ...
3
votes
2answers
1k views

Mapping SQL Server integer to Oracle Datatype

Well I know, that an SQL Server integer is best represented by NUMBER(10). But sometimes I have migrate some maintenance scripts to Oracle and I wonder if I must carefully map integer to NUMBER(10) ...
20
votes
7answers
16k views

Oracle - Any way to view uncommited changes to a particular table?

I'm debugging through a batch process currently that does a lot of DML statements, but doesn't do a commit right away. It would be nice to be able to view the "pending" changes from another session ...
10
votes
5answers
22k views

In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?

I'm writing a script to populate some tables with data for testing. I would like to write something like the following but I don't know how to do it (I'm Oracle 11g) SET ENABLED_USER_ID = ...
5
votes
3answers
896 views

Database replication and consistency check

I got a question about 2 databases (oracle 10g) that I have, let's call them A and B. A have some information(in various tables) and I want B to get a partial copy of some tables from A, and ...
2
votes
1answer
2k views

Oracle Create Materialized View Script - Takes 12 minutes in DEV Schema, 5+ days in QA Schema

I have a materialized view in my Oracle 10g database that I need to rebuild. So I'm doing something like this... drop materialized view ViewName; CREATE MATERIALIZED VIEW ViewName NOCACHE LOGGING ...
1
vote
0answers
346 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 ...
6
votes
3answers
5k views

Add Oracle Label Security to an existing Oracle installation

We are using Amazon EC2 to host some Oracle database instances while we are evaluating and prototyping some software. We are using the AMIs provided by Oracle - specifically, Oracle Database 11g ...
1
vote
3answers
413 views

Insert to particular location in Oracle DB table?

Suppose I have a table containing the following data: Name | Things ------------- Foo | 5 Bar | 3 Baz | 8 If I want to insert a row, so that the final state of the table is: Name | Things ...
7
votes
2answers
6k views

How to extract Procedure, Function, etc. Source Code from an Oracle Export

I have generated an EXPDAT.DMP file of an Oracle database with the command: exp userid=usr/pass@db owner=own rows=n compress=n I then ran the following command to generate a file containing the ...
8
votes
4answers
302 views

What would I need to do to make sure my DB policies have to pass a security audit?

I have an audit coming up, and I was wondering what physical, electronic, and logical access controls an auditor would look for when auditing a database for an ERP system. I'm really new to this ...
1
vote
1answer
135 views

Can SQL profiles be deployed to other Oracle Databases?

When using The SQL Tuning Advisor in SQL-Developer. I get often some recommendations to accept a SQL-Profil. I guess it is a good idea to do so. But is this similar to proposed indexes, which I can ...
3
votes
2answers
3k views

What specific tests need to be performed under Database migration from DB2 to Oracle?

Currently I am going to be testing (I am a Data Analyst) of a larger migration project where they are planning to migrate from DB2 to Oracle. My question may be little broader; however any ...
1
vote
1answer
301 views

Difference between Flashback Query and Serializable Transaction Mode?

Is there any conceptual difference between a SERIALIZABLE Transaction and a Flashback Query?
8
votes
2answers
1k views

LNNVL Justification

LNNVL is an oracle built in function that returns TRUE for conditions evaluating to FALSE or UNKNOWN, and returns FALSE for conditions evaluating to TRUE. My question is what would be the benefit of ...
5
votes
2answers
3k views

I get ORA-13605 when trying to run SQL Tuning Advisor from SQL Developer

When trying to use the SQL Tuning Advisor from SQL Developer I get this error. Tuning advisor was working some days before.
1
vote
2answers
4k views

Oracle Forms 6i on Windows 7

Given I have a system of Oracle Forms 6i (legacy software), how could I make it work on Windows 7. We have serious problems with apps like PLUS80.exe crashing ("Oracle SQL*Plus has stopped working"). ...
4
votes
1answer
861 views

What are the differences between Oracle UCM and Oracle Content Server?

Whenever I do searches on them, I keep seeing them in search results together. What are the differences between Oracle UCM and Oracle Content Server? Are they just two names for the same thing? Is ...
2
votes
1answer
752 views

access all_mview or user_mviews from app code

I need to access the user_mviews or all_mviews table to get meta information about materialized view in my app. Which grants are necessary to read the user_mviews table?
3
votes
2answers
2k views

What are the benefits of using a different tablespace for a new user schema?

The tables in the schema are intended to be used read only and is only updated once each three month. My question refers to performance maintenance (backup/restore, export/import) What about the ...
5
votes
1answer
14k views

Query execution time in Oracle SQL Developer

I know I can use set timing on to see how long it took to execute a query after the execution is finished. But is it possible to see current execution duration in Oracle SQL Developer while the query ...
13
votes
5answers
2k views

What features of Oracle make it an attractive choice for small projects?

Given Oracle's licensing handling[a] (and, to a lesser extent, cost) I have always been wondering what the deciding factors would be for choosing Oracle over PostgreSQL or MySQL. My company is nearly ...
4
votes
1answer
5k views

What does TOAD do, when I terminate script execution?

When it stops the execution of a long running script. The script terminating needs even longer then the time the script was running. I.e. I have to kill Toad. 2nd EDIT: Just found that this is a ...
1
vote
2answers
3k views

hierarchical query in oracle

Suppose I want to perform data migration operation for which I would like to load the parent tables first and then the child tables for the obvious reason. Some of these tables play dual role. Assume ...
5
votes
1answer
1k views

DROP PARTITION strategy using LIST-REF composite partitioning

We have a large Oracle 11gR2 instance with some tables that have recently become an annoyance when deleting data. This database powers a large optimization cluster that takes input metadata from the ...
2
votes
2answers
496 views

Use different Windows account in pl/sql

Is it possible to use/log in to a different account in pl/sql. For example, I would like to write to a file in a folder only a specific account has access to. Here's an example of what I mean: Other ...
4
votes
2answers
945 views

MAX for each subset

I have a table that looks like this: +---------------------------------------------+ | EVENT_ID | ITEM_ID | PERSON_ID | EVENT_DATE | +---------------------------------------------+ | 123 | 1 ...
2
votes
2answers
576 views

Encountering exception ORA-01555

I was given a problem to solve , in which there is table called Scenarios in the Master Db which contains the details of all the Tablespace for which I have to find the size. The O/P should contain ...
2
votes
3answers
446 views

Huge Data Migration in Oracle and space concerns

I am trying to do Insert append as part of data migration from SchemaA.table1 to SchemaB.table2 in the same Oracle database. Due to refactoring of Table2, there will be some form of data ...
2
votes
1answer
551 views

Recover Oracle database after drive loss Interview Question

How would you answer this as an interview question? The database crashed and you lost a drive containing one of your control files and another drive containing an entire redo log group. You ...
2
votes
3answers
1k views

Can Oracle row-level security work with user session credentials?

Can row-level security in Oracle be controlled by a user's session credentials or user context? Here's an example to illustrate what I'm looking for: Our database contains a bunch of sensitive ...
3
votes
1answer
4k views

What are the pros/cons of Oracle's Grid Control compared to DB Control?

Database control is a web based tool for managing a single Oracle database that is installed on the same box as the database itself. Grid Control is a web based tool for managing multiple Oracle ...
2
votes
1answer
2k views

Oracle implicit parameter casting - why doesn't it always work? (ORA-06502 error)

I have a Java (Spring) app that uses Oracle 10 as DB. Some legacy code has wrong parametrization of PL/SQL procedures. For instance, some input parameters are declared as OracleTypes.VARCHAR in Java ...
6
votes
2answers
368 views

In Oracle, can we restore a database onto the same instance with a different name?

In SQL server I can restore a database onto the same instance with a different name. Eg: If there is a database DB_prod, I can take a full backup and restore it on the same instance with a different ...
5
votes
3answers
24k views

How to connect to a remote Oracle server from Windows machine using PL/SQL Developer?

I understand that I need to configure database address in the TNSNAMES.ORA file but where should I put this file? UPDATE I defined the $TNS_ADMIN variable, created the TNSNAMES.ORA file, added new ...
5
votes
2answers
6k views

Current isolation level in oracle

How to get the current (default) transaction isolation level in oracle?
2
votes
2answers
1k views

Does the target for Double-Take replication require an Oracle license? [closed]

Where would Vision Solution's Double-Take product fall with regard to Oracle Licensing? It is both a backup and a replication of the database. Oracle's Software Investment Guide has a section on ...
6
votes
2answers
5k views

Is Oracle DB licensing needed for a test environment?

I'm planning to setup an Oracle Standard Edition instance on a server in our test environment. Do I need to purchase a license for this?
3
votes
1answer
1k views

Oracle 10.2.0.1.0 listener process stopped

When I am checking my servers I see that there is an error about listener process stopped. I logged in and checked the status of listenere. please see the below status; LSNRCTL> status Connecting ...
3
votes
3answers
10k views

How to convert a Top 1 subquery using outer table alias to Oracle?

I have the following SQL Server query select (select top 1 b2 from BB b where b.b1 = a.a1 order by b2) calc, a1, a2 from AA a where a2 = 2; which I can rewrite using analytic functions ...
4
votes
1answer
2k views

How do I horizontally partition an oracle database table, and should I?

We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment. Create ...
15
votes
7answers
135k views

How do I Create Tablespace in Oracle 11g?

I am not too savvy in Oracle, and I have been trying to create a TABLESPACE, which keep on giving me an error message of invalid file name. Below is the syntax that I have used: SQL> create ...
4
votes
1answer
218 views

When refreshing materialised views does the index automatically refresh

When refreshing materialised views does the index automatically refresh
7
votes
1answer
987 views

Materialized views in oracle

How are materialized views implemented in oracle? I've created a materialized view in oracle. What I now see is a quite normal table with a job. Are materialized views translated into tables & ...
7
votes
1answer
3k views

Oracle materialized view refresh schedule

Since I see that it is possible to specify an expression like sysdate + 1, here are my questions: Is it possible to specify the exact time of the refresh for materialized views in oracle? Is it ...