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

2
votes
1answer
1k views

Oracle 10gR2 to 11gR2 upgrade - performance hit [closed]

We've recently upgraded from Oracle 10gR2 (10.2.0.4 ) to 11gR2 (11.2.0.3) and we are noticing a significant hit in performance although execution plans are the same for the offended queries before and ...
0
votes
3answers
578 views

Can a RDBMS connect to a remote server, execute a select query and copy them into a local table just with SQL commands?

I'm sorry it may sound stupid but I've been wondering about how multiple databases are automated before the virtualization era. Can a DB Adminsitrator make a stored procedure to get data from a remote ...
10
votes
1answer
1k views

Do Oracle databases use Java internally?

My question is fairly simple. Is Java used internally by Oracle in their DBMSs?
10
votes
1answer
49k views

How do I declare and use variables in Oracle?

My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL: declare @startDate int select @startDate = 20110501 And I get this ...
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 ...
9
votes
2answers
4k views

Why can't I read Oracles CLOB columns via SQL-SERVER 2008 linked server?

I want to access data in an Oracle 11g database from SQL-Server 2008 I set up a linked server and when I execute select * from [Link_server_name]..Oracle_schema.Oracle_table and Oracle_table ...
8
votes
2answers
5k views

Why is truncate DDL?

I have an interview question, which was asked during my interview. I answered the question, but interviewer was not so convinced with my answer. So, anyone please correct me with my understanding ? ...
7
votes
2answers
3k views

Oracle 10g - How to prevent one user from hogging all resources?

I am wondering if there is any way to limit how much resources one user can take on my Oracle system. I want to limit some queries and systems from being able to hog all system resources when they run ...
7
votes
4answers
5k views

Multiple operations using WITH

Is there a way to execute multiple operations using the WITH statement? Something like WITH T AS ( SELECT * FROM Tbl ) BEGIN OPEN P_OUTCURSOR FOR SELECT * FROM T; SELECT COUNT(*) INTO ...
6
votes
1answer
80 views

Parallel option performing worse on cluster

I'm trying out oracles parallel option on a cluster and surprisingly I am getting worse results with the parallel option. I was expecting some improvement with the parallel option but certainly not ...
6
votes
2answers
97 views

Will the Oracle optimizer still plan a join if I'm not reading any columns in the joined table?

Suppose I have the following view: CREATE VIEW dummy AS SELECT a.some_column, b.other_column FROM a LEFT JOIN b USING (key_column); When I do a SELECT some_column FROM dummy, is there a way to ...
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?
6
votes
2answers
854 views

Resources for SQL Server developer migrating to Oracle

What resources can you recommend for a SQL Server developer wanting to learn Oracle basics? I am looking for a comprehensive whitepaper or blog post describing differences between these systems and ...
5
votes
3answers
2k views

Do roles have an owner in oracle?

Suppose that I have logged in to oracle database using user X, then user X creates some roles using create role command. I want to know that is user X the owner of the roles? Can I select all roles ...
5
votes
2answers
8k views

Database logon trigger

To block a particular user and IP combination on an Oracle Database , I created the following trigger, and compiled without errors. Create or replace trigger you_may_not_login after logon on database ...
5
votes
4answers
9k views

Why does = NULL not work in Oracle?

I recently realized that we need to use a special syntax IS NULL to compare a literal to NULL. Why does = NULL not work here?
4
votes
2answers
11k views

Estimate a Database Size

I have to do some project, and need to estimate the DB size. I'm gonna to use Oracle in the project, because will have a lot of transactions and size. But in the "presentation" I need to specify, in 5 ...
4
votes
3answers
727 views

Is Oracle DB immune to the InnoDB deadlocks found in MySQL?

It seems odd to me that MySQL would not handle this internally: "Deadlock found when trying to get lock; try restarting transaction" Does Oracle DB work around this issue? After all, Oracle owns ...
4
votes
2answers
3k views

How to handle multiple Oracle homes?

On my desktop I have an Oracle client and and Oracle ODAC installed in different homes. I need ODAC because there ADO components off the Client installation don't work correct with CLOB data. I think ...
3
votes
1answer
7k views

Oracle program “lsnrctl” is missing after the installation of 32bit Oracle client

I cleaned up my computer for all old Oracle stuff using Uninstall (OUI) and then later used regedit to delete all references to Oracle and then deleted all remains of Oracle directories. I have HP ...
3
votes
1answer
5k views

Oracle: Full database (user) restore requires drop?

In our development environment (Oracle 11g), we often have to restore databases (users) to previous versions to test upgrades, verify defects, etc. We are using expdp/impdp to perform our ...
3
votes
2answers
8k views

Troubleshooting Oracle ORA-08103 - object no longer exists

I am getting this error from Oracle intermittently. A trigger is truncating a partition from a partitioned table while a long-running query is running against it. Since I'm truncating a different ...
3
votes
2answers
12k views

Improve BLOB Writing Performance in Oracle 11g

I have an application that writes a lot of rows into a table that contains a blob column. The average size of the blob is 3k bytes. Is there any buffer that i can resize to improve inserts into this ...
2
votes
1answer
13k views

What is the best way to insert a XML input to an oracle table in stored procedure?

I want to write a stored procedure which has a XML input (clob type) and inserts the data to a table. my XML structure is something like this: <rowset> <row> ...
2
votes
2answers
3k views

Querying a database efficiently for a huge chunk of data

Here is my query SELECT * FROM ( SELECT a.*, rownum rnum FROM ( SELECT id, data FROM t ORDER BY id ) a ...
2
votes
1answer
206 views

SQL from/where or join?

I was wondering performance wise what would be faster a join or a from and where query. I am trying merge and update query but performance is terrible?
1
vote
1answer
137 views

Startup oracle database 12c manually with SQL*Plus command not when OS loads

I changed oracle instance to manually start from services.msc (service name is "OracleServiceORCL"), Because I want to start it up manually with SQL*Plus commands whenever I want, not with OS load. ...
1
vote
2answers
468 views

Oracle nologging impact for REDO Generation

Oracle database is on noarchivelog mode and tablespace is configured with nologging option keeping there will not be a situation to recover any data in mind. Following scenario was occured during ETL ...
1
vote
1answer
2k views

ORA-00942: table or view does not exist while creating synonym

We are using oracle 11g, the db structure goes like this: We have users CST and FAS. CST contains a table ST_CAT2, a public synonym ST_CAT has been created for the table CST.ST_CAT2 as a SYS user. ...
1
vote
2answers
9k views

What is the difference between Schema and Database in Oracle?

In oracle, Datafile are system files where actual data is stored.The collection to datafile make Tablespace and atlast database is collection of tablespaces Correct me if I am wrong on the concepts ...
1
vote
1answer
671 views

Preloading a Java library in DB2, similar to ORACLE's loadjava

Edit to add details to clarify, based on Chris' comment: A Java stored procedure depends on external libraries. Locally it compiles successfully, since they are added to the classpath. But when ...
0
votes
3answers
143 views

Allowing users to compile / run PLSQL and SQL

As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code. We have an MI database environment where the data ...
10
votes
2answers
3k views

Security for Application Developers doing PL/SQL work in Oracle

How do you handle the lack of Schema level privileges in Oracle? Oracle’s security architecture works well for applications that only need object level privileges and it works well for DBAs that need ...
9
votes
1answer
3k views

Why am I NOT getting a mutating table error in trigger?

It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation: A mutating table is a table that is being modified ...
9
votes
2answers
5k views

Updating 700 million rows to same value

I've got a data warehouse (oracle) where I need to set a column to the same value for all 700 million rows. I don't have admin access, or access to an admin, so this needs to be accomplished with ...
7
votes
1answer
162 views

Cardinality is so far off from reality

I've got a table where the cardinality for plans just seems waaaaaay too high - despite updating stats and even trying dynamic sampling for Oracle 11gR2. select count(*) from table1 where col1 = ...
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 ...
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 & ...
6
votes
1answer
3k views

MySQL or Oracle Associate Certification?

I have 15+ years in software development and 7+ in web development, and I want to get a certification in another area, like DBA. I have 7+ years of experience like MySQL developer. Which is better for ...
5
votes
1answer
807 views

Enforce data integrity with a recursive table

I have an existing oracle 11g database schema that works with a web application. I am planning an expansion to the application so a web service can do data operations on the database. As part of the ...
5
votes
2answers
29k views

Using Same CASE WHEN Conditions For Multiple Query Columns

Is there a "better" way to rewrite a SELECT clause where multiple columns use the same CASE WHEN conditions so that the conditions are only checked once? See the example below. SELECT CASE ...
5
votes
1answer
647 views

Silent truncation of last character when using SSIS with Oracle

I am using SSIS to export data from an Oracle database. Sometimes when I export a VARCHAR2 column to NVARCHAR the last character gets truncated (silently). It's completely random as far as I can tell. ...
5
votes
1answer
1k views

What is Sql Server Clustered Index in Oracle terminology

A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on. If you look up with a name Ozgur you start O then z etc. I would like a simple explaination for this ...
5
votes
2answers
721 views

Oracle Advanced Queuing LIFO

With Oracle's Advanced Queuing is there a way to dequeue messages in a LIFO (last in first out) order? There is an indication through a lack of information that this is not an option, but perhaps ...
4
votes
3answers
1k views

TNS-12541 Error

I have grid installed in my pc but the problem is LISTENER does not start automatically. I have to start listener manually and then only LISTENER get started. But before starting listener if you check ...
4
votes
1answer
529 views

Unable to connect oracle as sysdba tables have been dropped

I have a script which lists all tables belonging to the user and executes DROP for all of them. By mistake, I logged in oracle with 'sys as sysdba' and ran above script. Due to which all sysdba ...
4
votes
1answer
13k views

Oracle: Non key-preserved table should be

I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join. I've searched around the site and found a lot of advice on what key-preserved ...
4
votes
2answers
7k views

How can I view what NLS settings are active for other session?

I've got another session that I suspect isn't running with the 'proper' NLS settings for us in Oracle 11gR2. What can I query to show the NLS settings for a different active session - all of the ...
4
votes
2answers
6k views

In Oracle how do you move a Schema to a different Tablespace?

I noticed that a Schema has been created in the USERS Tablespace but our local rule is that it should be in a Tablespace by itself. In Oracle how do you move a Schema to a different Tablespace?
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 ...