Questions tagged [oracle]
All versions of Oracle database. Add a version-specific tag like oracle-11g-r2 if that context is important in the question. Do not use for Oracle products such as applications or middleware or other database products owned by Oracle, like MySQL or BerkeleyDB.
7,711
questions
12
votes
2
answers
24k
views
Why do we need to backup Oracle archive logs along with datafiles
Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) says that
Archived redo logs are the key to
successful media recovery. Back them
up regularly.
But, I wonder, why backups of ...
- 223
8
votes
3
answers
173k
views
Oracle SQL - CASE in a WHERE clause
Is it possible to somehow do this?
WITH T1 AS
(
SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
SELECT 3 AS SEQ, '...
- 183
3
votes
2
answers
177
views
I'm performing a post-mortem on an Oracle database that's crashed its' host. Where should I begin?
I have an Oracle database (10.2.0.1.0 I believe) on a Windows 2003 server that I believe has successfully crashed the OS itself twice. Debates on platforms and stability aside, the evidence suggests ...
- 133
3
votes
3
answers
5k
views
cursor: pin S wait on X in 11g
This has been one of top5 wait events in our database, even though there were not many waits but we see it as a potential threat, we want to find the root cause and solution.
Any advice will be ...
- 269
13
votes
4
answers
67k
views
How to find the actual space being consumed by indexes on a table in Oracle?
I want to find the actual space being consumed by indexes on a table in oracle 10g . I do not intend to include the space reserved by oracle for future usage. (The overhead by oracle should not be ...
- 559
5
votes
3
answers
4k
views
Alternatives to an INSERT Trigger
Looking for an example code in Oracle to do the same. i.e.
Remove an insert trigger on table T1 whose job is to create a row in T2 based on the derived data. How would this PL-SQL api look like?
...
- 221
17
votes
3
answers
15k
views
How to differentiate between SQL and PL/SQL?
I know the question might sound too stupid, but I never understood this part.
SQL*Plus works with both SQL and
PL/SQL. How do I know whether some
code is SQL or PL/SQL? If my code has
a for ...
- 3,351
7
votes
1
answer
8k
views
What is the difference between ROLE and PRIVILEGE for Oracle?
Is role a collection of certain privileges?
- 3,351
5
votes
2
answers
2k
views
What is the difference between these two create table statements?
a)
SQL> CREATE TABLE xml_tab1(poDoc XMLTYPE);
Table created.
SQL> desc xml_tab1;
Name Null? Type
----------------------------------------- -------- --...
- 3,351
2
votes
1
answer
680
views
SAN Storage & Server for Oracle Replication
I need a 2-3 TB storage server group for Oracle Database. We want SAN Storage & Server for Oracle Replication, but not Microsoft Storage Server.
For example : HP DL380 G6 as a server and EMC ...
- 1,078
9
votes
1
answer
29k
views
How can I import the contents of an Oracle database into Visio to create an Entity Relationship Diagram?
I have an Oracle database, and I would like to create an ERD in Visio 2007 Pro.
I have so far looked at DBMS_METADATA.GET_DDL commands to generate DDL. I know that I can import an Access DB into ...
kg.
6
votes
1
answer
737
views
How can I make the audit system create new audit files if they already exist?
I am trying to make my audit files easier to read, I have audit trail configured as xml,extended. The problem is that if my server gets busy and reuses the pids - the logs are being appended to the ...
user782
2
votes
1
answer
5k
views
Local package won't compile when remote package recompiles. (Oracle)
We have a package on a 10.2.0.4 database(A) and a package on an 11.2.0.2 database(B) that references A's package. When something on A changes that requires the package on A to do an implicit re-...
- 23.8k
6
votes
1
answer
2k
views
Using sys.diana and sys.pidl to parse PL/SQL
It seems to me that it should be possible to parse existing PL/SQL with the packages sys.diana and sys.pidl, but I have found nothing on the internet with the exception of a few scripts that seem to ...
- 3,743
19
votes
6
answers
17k
views
Oracle Creating ER Diagrams and Data Dictionaries
I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?
- 657
4
votes
2
answers
97k
views
Expired archived logs not deleted from RMAN catalogue
If I issue a LIST EXPIRED command in RMAN, I see a list of files with expired status, that is, they exist in the catalogue, but not on the disk (verified by ls). After issuing CROSSCHECK and DELETE ...
- 11.2k
4
votes
3
answers
2k
views
Detect Index Coalesce
Is there a way to know if a COALESCE has been done on an index? Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes.
Note: I am not referring to the COALESCE ...
- 23.8k
6
votes
2
answers
2k
views
EXP-00085 warnings while doing full backup
During a full export of my Oracle 10.2.0.2 database, I encounter the following warnings
[...]
. exporting post-schema procedural objects and actions
EXP-00008: ORACLE error 6502 encountered
ORA-06502:...
- 240
5
votes
2
answers
13k
views
Calling SQL Loader From SQL Plus
I was wondering if there was a way I can execute a SQL Loader script from SQL Plus.
We are using Oracle 10g.
- 657
15
votes
3
answers
38k
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 ...
- 3,351
4
votes
7
answers
12k
views
What database front end do you use? [closed]
What database front end do you use?
It should let you manage databases, tables, indexes and ideally even data records.
And it should ideally be free so we can all use it too.
Databases that it could ...
19
votes
6
answers
83k
views
Best way to delete very large recordset in Oracle
I manage an application which has a very large (nearly 1TB of data with more than 500 million rows in one table) Oracle database back end. The database doesn't really do anything (no SProcs, no ...
- 335
6
votes
2
answers
3k
views
Identify which parameters were passed to a procedure (Oracle)
Procedures in PL/SQL can have default values for parameters, but the caller can pass values that are identical to the default values. Is there a way from within the PL/SQL to determine whether a ...
- 23.8k
4
votes
1
answer
185
views
Oracle setup required for heavy-ish load
I am trying to make a comparison between a system setup using Hadoop and HBase and achieving the same using Oracle DB as back end. I lack knowledge on the Oracle side of things so come to a fair ...
FvV
48
votes
15
answers
187k
views
Eliminate duplicates in ListAgg (Oracle)
Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row. 11.2 Added the LISTAGG function, so I am trying to use that instead. My problem is that I need to ...
- 23.8k
7
votes
4
answers
27k
views
Building Dynamic Oracle Where Clause
I'm working on application that uses dynamic query to do a select statement based on user input, after discussing security with DBAs they want me to convert my dynamic select statement into Stored ...
- 849
5
votes
1
answer
713
views
Case Behavior Changed Between 11.2.0.1 and 11.2.0.2
I have a case open with Oracle and they say they can't reproduce this. Running the following in 11.2.0.1 or lower produces two rows from both of these queries, but in 11.2.0.2 the second query ...
- 23.8k
24
votes
7
answers
101k
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 in ...
- 12.2k
4
votes
2
answers
8k
views
How to return a CTE as REFCURSOR from an Oracle stored procedure?
I tried to modify an Oracle stored procedure to use a CTE instead of a simple Select Statement.
My procedure looked like this:
Create or replace Myproc ( MyRefCursor IN OUT SYS_REFCURSOR)
as
begin
...
- 12.2k
19
votes
4
answers
3k
views
SSDs with Oracle
We've been looking into using SSDs with Oracle to speed up our test migration runs. It currently takes 12-18 hours to complete a migration run, depnding on the volume of data (we're obviously doing ...
- 303
2
votes
2
answers
15k
views
Information on Oracle MKSTORE utility for creating wallet
I'm looking for more information about the utility MKSTORE that can be used for creating and modifying a Wallet. I would like to know things like what the -createALO option is and what the difference ...
- 23.8k
32
votes
9
answers
62k
views
How does the Oracle DUAL table work?
SQL> desc dual
Name Null? Type
----------------------------------------- -------- ----------------------------
DUMMY ...
- 3,351
3
votes
3
answers
474
views
How does a database use the SYS schema even when we change the password?
From the thread What is the difference between sys and system accounts in Oracle databases?, I understand that sys schema is supposed to be used only by the database.
But still an administrator can ...
- 3,351
8
votes
1
answer
6k
views
Why does an Oracle database require both the SYSTEM and the SYSAUX tablespaces?
Quoting from Oracle documentation about logical storage structures:
A database must have the SYSTEM and
SYSAUX tablespaces:
Why is it compulsory to have these tablespaces?
- 3,351
16
votes
4
answers
14k
views
What is the significance of a semicolon at the end of SQL*Plus commands?
Some statements like create table, insert into etc take a semicolon at the end:
CREATE TABLE employees_demo
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name ...
- 3,351
16
votes
2
answers
1k
views
Peculiar Oracle outer join syntax case
I have seen the following in a query that was supposed to be ported from Oracle outer join syntax to SQL standard outer join syntax:
SELECT ...
FROM A, B, C, D, E
WHERE A.A_ID = B.A_ID
AND B.B_ID = C....
- 10.3k
0
votes
3
answers
394
views
Is Oracle not suited as the database behind websites like Stackoverflow? [closed]
I see from the thread Which tools and technologies build the Stack Exchange Network? that the database specifications are:
Database SQL Server 2008 R2 running Microsoft Windows Server 2008 ...
- 3,351
50
votes
4
answers
96k
views
What is the difference between sys and system accounts in Oracle databases?
There are two ways to connect to Oracle as an administrator using SQL Plus:
sqlplus sys as sysdba
sqlplus system/manager
These accounts should be used for different purposes, I suppose.
Which tasks ...
- 3,351
7
votes
3
answers
2k
views
How can a datapump export have its resource usage limited on windows?
Doing an export of a number of users causes high CPU and I/O usage. I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how ...
- 23.8k
3
votes
1
answer
461
views
How to get the Oracle Text Index's underlying Virtual XML Document?
The Oracle Text Reference 10g states that when we index our table using multi-column datastore, the system concatenates the text columns, tagging the column text, and indexes the text as a virtual XML ...
- 378
8
votes
1
answer
1k
views
How can a datapump export have its resource usage limited on linux?
Doing an export of a number of users causes high CPU and I/O usage. I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how ...
- 23.8k
20
votes
4
answers
91k
views
Can I change the SID of an Oracle database?
The Server is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit
Is there an easy & quick way to change the SIDs of the test databases on the server?
Dropping & recreating of ...
- 12.2k
7
votes
2
answers
15k
views
Can I use OpenLDAP to authenticate users in Oracle?
I like to use OpenLDAP to authenticate my Oracle users. It should be possible to fetch at least the password from the directory.
There appearently is something for the Oracle LDAP servers, but would ...
- 1,356
19
votes
6
answers
39k
views
What is the recommended RAID configuration for an Oracle database?
RAID (Redundant Arrays of Inexpensive Disks) comes with different configurations (RAID-0, RAID-1...). What is the recommended RAID configuration that I should set up and use when installing an Oracle ...
- 778
13
votes
2
answers
4k
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 ...
- 23.8k
29
votes
4
answers
17k
views
Why is OS authentication considered poor security for Oracle databases?
Oracle is deprecating OS authentication according to the Oracle Database Security Guide, which says
Be aware that the REMOTE_OS_AUTHENT
parameter was deprecated in Oracle
Database 11g Release 1 ...
- 23.8k
10
votes
2
answers
6k
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 ...
- 12.2k
8
votes
2
answers
1k
views
Is this a new common pattern in Oracle Where exists ( Select NULL FROM...)?
Years ago, it was common to write
where exists (Select * from some_table where some_condition)
Last year I noticed that many t-sql scripts switched to using the number 1 instead of the star
where ...
- 12.2k
32
votes
9
answers
38k
views
How do you version your Oracle database changes?
I am interested to know what methods other people are using to keep track of changes made to the database including table definition changes, new objects, packages changes, etc. Do you use flat files ...
- 23.8k
11
votes
2
answers
17k
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 ...
- 1,874