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

12
votes
3answers
16k views

Script out Oracle DDL in an automated fashion

Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention. I know of DBMS_METADATA.get_ddl(), but have found that the ...
12
votes
1answer
3k views

Commit vs Fast Commit vs Commit Cleanout in Oracle Database

I was wondering whether someone could verify my understanding regarding the differences between those 3 terms as pertaining to Oracle Databases. Many sources confuse these terms and do not explain ...
11
votes
4answers
8k views

Is Oracle more demanding environment for a DBA than SQL Server? [closed]

I don't know Oracle at all but I have heard Oracle DBA's saying that working with Oracle (as a DBA) is "harder" and more difficult and more demanding than working with SQL Server. Does this statement ...
11
votes
6answers
41k views

How to get rid of ORA-28002 message the password will expire within 6 days?

I have a user getting an ORA-28002 indicating that the password will expire within six days. I ran the following: ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; But when I try to log ...
11
votes
3answers
2k views

How does Oracle know who I am?

Slightly unimportant but it's piqued my curiousity. I've just logged into an Oracle 10g database for the first time using the Oracle SQL Developer Tools. I used a generic user login which isn't my ...
11
votes
3answers
139k views

How to make sqlplus output appear in one line?

I have a table with 100 columns. When selecting data in SQL Plus the output wraps, making it difficult to read. What I'd rather like is either a horizontal scroll bar to appear or somehow send the ...
11
votes
3answers
1k views

Is there any way to break out of the string and inject SQL without using a single quote in oracle?

I'm testing an oracle based application and I've found the following code: Query = "SELECT name FROM employees WHERE id='"+PKID+"';" i.e. the query string contains quotes around the PKID value which ...
11
votes
1answer
15k views

Duplicate an Oracle database user

We'll be having external auditors coming over to perform a review on our Oracle database. They will be running a tool to perform the review and for that, they require a user ID that can connect to the ...
11
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 ...
11
votes
3answers
5k views

How could sequence.nextval be null in Oracle?

I have an Oracle sequence defined like so: CREATE SEQUENCE "DALLAS"."X_SEQ" MINVALUE 0 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 0 NOCACHE NOORDER NOCYCLE ; ...
10
votes
5answers
24k 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 = ...
10
votes
3answers
4k views

Why do I have to select from the dual table?

This works in the major Relation Database Management Systems most likely to appear on StackOverflow/dba.stackexchange, being SQL Server, MySQL, PostgreSQL and SQLite (WebSQL). select 'abc' abc, 1 ...
10
votes
3answers
738 views

Avoiding multiple `or` expressions

I have the following oracle SQL and its works and all but it's quite ugly with all of the ors. Is there a more concise way of doing this? SELECT * FROM foobar WHERE (SUBJECT ='STAT' and TERM ...
10
votes
2answers
9k views

ORDER BY using custom priorities for text columns

This is a sample table: name | cat ---------- hugo | sal bob | mgr mike | dev jeff | mgr john | dev Both name and cat being Varchar. cat reflects a category for these names, but I like to assign ...
10
votes
2answers
2k views

Recovering logical space from a tablespace

I have a tablespace called DATA, and it is setup with auto-extend as false. This tablespace has two datafiles and is set up so it takes up 350 GB of physical space. A week ago I queried ...
10
votes
2answers
17k views

What does the term “projection” refer to in an SQL query (or view definition)

In Oracle's document The Query Optimizer, under View Merging, I found the following information The view merging optimization applies to views that contain only selections, projections, and ...
10
votes
1answer
34k views

Why Oracle sessions are still alive after two weeks being Inactive

DB: Oracle 10g O/S: Windows Server 2003 64 bits I query list of Oracle sessions generated by web applications (filter by program=w3wp.exe) select * from V$session where UPPER(program) LIKE '%W3%' ...
10
votes
2answers
22k views

How to catch and handle only specific Oracle exceptions?

From this and this i guess, that there is no predefined Named System Exceptions for ORA-00955. How can I rewrite the following to catch only the error ORA-00955? begin EXECUTE IMMEDIATE ...
10
votes
5answers
19k views

Cannot drop nonexistent constraint and cannot create it either

While testing some migration scripts with a copy of production data (scripts run fine with development data) I found a curious situation. A CONSTRAINT has changed so I'm issuing DROP + ADD commands: ...
10
votes
3answers
7k 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 ...
10
votes
3answers
30k views

How do I find my current SCN?

Given any version of Oracle: How do I find my current SCN? What is the maximum possible SCN?
10
votes
2answers
704 views

Where to start with Oracle?

This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL. You may find your question has been closed as a ...
10
votes
2answers
3k views

Does Oracle PL/SQL have a standard ASSERT routine?

I'd like to use an ASSERT routine functionally similar to the one found in other languages, i.e. a construct (be it a procedure, syntax...) ASSERT( <condition>, <msg>) such that when ...
10
votes
1answer
8k views

Why does SQL*PLUS need a slash after CREATE TYPE?

I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error. Example: CREATE OR REPLACE TYPE MyType AS OBJECT ( Item1 NUMBER, ...
10
votes
2answers
596 views

large data movement

I would like to move billions of rows from schema1.table1 to new schema2.table2 where table2 is a refactored one from table1. Hence their table structure is different. both table1 and table2 are ...
10
votes
1answer
59k views

Oracle 11g listener fails with ORA-12514 and ORA-12505 errors

I run an instance of Oracle 11g locally on my development machine and can connect to the local instance directly via SqlPlus: c:\>sqlplus ace SQL*Plus: Release 11.2.0.2.0 Production on Mon Mar ...
10
votes
1answer
28k views

How can I Estimate Table Sizes within Schema (Oracle)

I'm trying to estimate the table sizes within my schema (in MB). This is what I have so far: SELECT table_name, owner, last_analyzed FROM all_tables I'm fairly new to SQL so I have no idea how I ...
10
votes
1answer
12k views

Limit the degree of parallelism (DOP) available to any query

On Oracle Exadata (11gR2), we have a relatively beefy database. cpu_count is 24 parallel_server_instances is 2 parallel_threads_per_cpu is 2 We noted, through observation in Oracle Enterprise ...
10
votes
1answer
742 views

Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state

Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
9
votes
2answers
2k views

100 TeraBytes Capacity Database - Resources and Time Estimates

I am working on a 'back of the envelope' calculation for a 100TB reporting database setup. I am seeking out thoughts from the experts here. Proposed environment: Storage Capacity ~ 100TB Tables ~ ...
9
votes
2answers
124 views

Read ancient Oracle database into current Oracle version

An old computer with Oracle 8i is dead. We have the daily backup of the database (C:\oradata). We are unable to install this old Oracle version anywhere because we don't have the install CDs for it. ...
9
votes
3answers
9k views

What does NVL stand for?

What does NVL stand for? I'm talking about the Oracle and Informix (perhaps some others too) function used to filter out non NULL values from query results (similar to COALESCE in other databases).
9
votes
1answer
1k views

Do Oracle databases use Java internally?

My question is fairly simple. Is Java used internally by Oracle in their DBMSs?
9
votes
3answers
19k views

Is there any difference between Rem and — comments for SQL*Plus?

On SQL*Plus prompt, both Rem and -- qualify as comment indicators: Rem this is a comment -- this is also a comment create table emp ( id number primary key, name cvarchar2(40)); Is there any ...
9
votes
3answers
3k views

How can I ensure that only one copy of a procedure is running in Oracle?

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error. Whats the best method ...
9
votes
2answers
11k views

MySQL equivalent of WITH in oracle

Is there a MySQL equivalent of the WITH clause in Oracle?
9
votes
1answer
960 views

Column level security

I am in need of a solution to hide specific columns in a table. We have people who need to build reports against this database and specifically some of these tables with confidential information but ...
9
votes
2answers
41k views

What is the easiest way to move data from Oracle to SQL Server?

One of our products supports both Oracle and SQL Server as database backend. We have a customer who wishes to switch from an Oracle backend to Microsoft SQL Server, which isn't a typical transition ...
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
3answers
5k views

Oracle: How do I query a Hierarchical table?

Background This is for the construction of some views we'll be using for reporting. I have an table of locations, the key fields being "location" and "parent". The structure that these two fields ...
9
votes
1answer
4k 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
3answers
296 views

Can I rely on functions being executed first in SQL

Please consider the following script: create or replace function f(p_limit in integer) return integer as begin set_global_context ('limit', p_limit); return p_limit; end; / create view v as ...
9
votes
2answers
8k views

How to connect to another db using stored procedures?

I need to get some data from an external db and make some calculations with it in another db, is it possible to connect to an external db from a stored procedure? Thanks guys. PS. Im using Oracle ...
9
votes
3answers
2k views

Is it bad idea to create foreign keys on the tables in a different schemas in the same database for big applications?

I am working on the transfer a big pl/sql web-based application to the dedicated server. This application is located in one schema with 70 packages of programm code. This application was made ...
9
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 ...
9
votes
2answers
6k 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 ...
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 ...
9
votes
2answers
849 views

Why is a “non-single-group group function” allowed in a subselect but not on it's own?

Why does the first query not fail with the same error as the second: with w as (select 1 product_id, 10 units from dual union all select 2, 5 from dual) select sum(units) from (select product_id, ...
9
votes
1answer
448 views

SQL substitution feature in Oracle 10g

Three or four years ago I read in an Oracle blog somewhere that a DBA had used for an emergency incident resolution an Oracle 10g feature of real time SQL substitution. Basically, he configured Oracle ...
9
votes
4answers
34k views

Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes

Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus. Here is a worst case example containing multiple ...