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

6
votes
2answers
2k views

Making the Oracle optimizer use a virtual column to find out about a partition

I have a large table that has three columns like this: "START_DATE" DATE, "START_VALUE" NUMBER(10,7) "START_DATE_VALUE" NUMBER(18,7) GENERATED ALWAYS AS ( (extract(YEAR FROM START_DATE) * ...
4
votes
1answer
4k views

Drop user did not reduce size of tablespace file

I have a development environment in which I frequently copy a certain schema. When I'm done with the copy, I will use OracleEM to drop that user/schema. My problem is that this does not seem to ...
4
votes
2answers
2k views

Oracle extents - How can I have so many with this small size?

This is a different question than the one with the almost-identical title. Again, this is more curiosity about how Oracle Database works than anything I need to change (yes I know multiple extents ...
4
votes
2answers
1k views

Tracking SQL queries and rows operated on for documentation/audit

Is there a way to record the SQL queries (and rows that were acted upon) in a session for documentation/audit purposes? For example, in deleting duplicate rows from a table I use the following SQL ...
3
votes
1answer
136 views

How many blocks is my database

How can I write a query using Oracle SQL Developer to find how many blocks my database is using? (tables & indexes)
3
votes
5answers
684 views

Query optimization [Oracle]

The Query: select JOIN_RESULT.batch_id as BATCH_ID, JOIN_RESULT.unique_doc_id as DOCUMENT_ID, JOIN_RESULT.brand as BRAND, JOIN_RESULT.message_date_time as UPLOAD_DATE, ...
2
votes
1answer
164 views

Move Complete ORACLE from one system to another [duplicate]

I have 2 identical server with exact disk partitions (OS CENTOS 6.4 both ) [My college central oracle server]. [Oralce 11G Enterprise Edition] Entire ORACLE HOME ( bin , control files, data files ...
2
votes
2answers
6k views

How to recreate or restore Corrupted Data Files in Oracle 11g

Few days back ago my power cable went down and I was working in Oracle. So after power cable restoration when I tried to login to SQL PLus: It did not let me to login and I got error "Oracle ...
2
votes
2answers
3k views

Why does the same query run faster the second time I run it?

I noticed when running a SQL query it is faster the second time than the first. So my question is, in Oracle SQL Developer where can you see the statistics that explain this behaviour?
2
votes
1answer
5k views

Why isn't oracle using an index for distinct query ?

This is the scenario SQL> exec dbms_stats.gather_table_stats(user,'TM', cascade=>true) PL/SQL procedure successfully completed. SQL> SELECT SEGMENT_NAME , SEGMENT_TYPE , BYTES / 1024 / ...
2
votes
4answers
13k views

ORA-09925: Unable to create audit trail file

I'm using Oracle 11.1.0.6.0 db, on Linux 64 bit server on Amazon cloud. I'm getting following error when I run the connect command SQL> connect / as sysdba ERROR: ORA-09925: Unable to create ...
2
votes
3answers
622 views

RAM Impact on Oracle database operations

We are seeing drastic change in the amount of time taken for db operations depending on ram size. The same content when inserted into a db running on a 4gb ram machine takes about 600% more than into ...
1
vote
2answers
304 views

Problem Installing 11.2.0 on RHEL 7 64 Bit

I have problem in stalling Oracle ASM on RHEL 7, Please advice [root@localhost grid]# ./root.sh Performing root user operation for Oracle 11g The following environment variables are set as: ...
1
vote
2answers
590 views

Use column alias in GROUP BY

I have a query where I chain two columns. Why does the alias not work in the GROUP BY clause but in ORDER BY clause, it does work? How can I write the select correctly? SELECT KOS_VER_ID AS ...
1
vote
0answers
177 views

How to ensure on Oracle RAC One Node that the OWB control service is started on the active node after a planned relocate or unexpected fail-over? [closed]

I followed the document 'How To Install a Warehouse Builder 11g Repository on a RAC' (Doc ID 1571166.1) to set up the OWB repository (11.2.0.4) on my Oracle RAC One Node system. Because it's a RAC One ...
1
vote
1answer
12k views

Oracle XE 11g (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor )

I have already seen the questioned in the stack overflow that might relate to my problem but let me emphasize that none of them could resolve my issue. issue is: when I wanted to integrate OWASP ...
1
vote
1answer
2k views

How to generate create table script without primary and foreign key in Oracle

I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well. SELECT ...
1
vote
0answers
434 views

How to display '0' where no data found in PL/SQL Oracle Reports

I have an Oracle Report (11g) in which I have selected a number field containing amount and have mentioned the format mask as 999,999,999,999. The data is displayed as follows. Data1 ...
1
vote
1answer
76 views

Oracle - Date of Selection on table

how to get last date when someone perform selection on table? Last accessed in terms of select statement only not last updated date
1
vote
1answer
143 views

how to write pl/sql named programme for this [closed]

I have a table M_PARAMETER which has a column MMRUN. When this MMRUN='Y' then the User can not modify any transaction in the app's front end. When MMRUN='N' then the User can do the modification. ...
0
votes
1answer
65 views

Oracle 11 rman db restore file corruption

First to mention that I came to the need of asking help after furious research I reattempts for about 3 weeks. I checked previous threads in Oracle community but nothing works for me. I am trying to ...
0
votes
2answers
89 views

Unable to start Oracle database

We have a oracle database, one data file(users table space) of the instance got corrupt and another data file(containing application data) was deleted due to some issue. The database is in ARCHIVELOG ...
0
votes
0answers
46 views

IO contention on raid5 happens?

I found all of the Oracle data files including redo, control, system, index and everything are in just one directory. And that directory is on Raid 5 disks. In this case do I need to do something ...
0
votes
1answer
94 views

Need query for the below problem [duplicate]

Year Value 2012 10 2013 20 2014 0 I need 10 * 20 * 0 for multiple rows. If I use this function EXP (SUM (LN (Value))), I get an ORA-01428 error: argument '0' is out of range.
0
votes
0answers
162 views

Why does second instance of database not open?

SQL> startup ORACLE instance started. Total System Global Area 3841982464 bytes Fixed Size 2025392 bytes Variable Size 788531280 bytes Database Buffers ...
0
votes
1answer
327 views

How to rollback last executed sql script fired as sysdba

I have fired one sql script as sysdba which I have to fire using other oracle user. I have already created some user and each application running on each oracle user. By mistake I had fired a script ...
0
votes
1answer
506 views

Oracle trigger to update columns daily

I'm trying to create a trigger that would run twice daily and do updates based on a specific condition. To be run on Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi We have Table A ...
-3
votes
1answer
737 views

Windows Server 2008 R2, Active Directory, and Oracle Database Server

I have a group of clients, Windows server 2008 R2(Active Directory Installed in it), and an oracle database Server I Want to know how to make the configuration of the following Diagram in this link: ...
-6
votes
1answer
10k views

how to query multiple row data in single column in oracle?

i have a table in which the values are id value ---------------- 1 10 1 20 1 30 2 33 2 34 I want the output given below using sql: id value ...
26
votes
13answers
69k 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 ...
16
votes
2answers
103k views

oracle - list users with access to certain tables

I'm sure this has been asked before but I can't seem to find the relevant details for the following. Is there some sort of pre-built table that can do the following (I have used dba_tab_privs but it ...
9
votes
3answers
111k 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 ...
14
votes
6answers
41k 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 ...
13
votes
3answers
10k 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 ...
12
votes
8answers
17k views

Tools to monitor Oracle Database Performance on Standard Edition

Here is what I am looking for: Works on Standard Edition i.e. No Diagnostic Pack. Provides both current and historical performance data. Good visuals for easy spotting of trends and presentation ...
10
votes
1answer
72k views

How to transfer data using expdp and impdp commands?

I'm Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump expdp and impdp commands. I have ...
9
votes
2answers
19k 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 ...
25
votes
4answers
11k 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 ...
12
votes
2answers
21k views

Is commit necessary after DML operation in Function/Procedure?

I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure? Example: create or replace function test_fun return number is begin delete from a; return ...
7
votes
1answer
85k views

“ORA-03113: end-of-file on communication channel” on startup

I have been reading posts here, on Oracle support, and anywhere else I can find for the last three days and I've given up on this problem... An Oracle database hung. Shutdown of the database sat for ...
4
votes
1answer
27k views

temporary table inside procedure oracle

I'm in a situation right now. I'm migrating several procedures from Mysql 5.0 to Oracle 11g. Mysql procedures allow me to: 1. Create temporary tables 2. Insert data into temporary tables / Query these ...
12
votes
2answers
76k views

How is the default tablespace determined when creating a table?

The Tablespace parameter is optional when creating tables. Upon execution of a CREATE TABLE statement, Oracle assigns the default one if it was not defined. In the default database there is a ...
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 ...
7
votes
2answers
9k views

Oracle 11g: performance improvements of inserts

I have a table of 500 millions of rows (and growing) I did the following to improve performance of inserts: On database side: dropped all indexes and constraints disabled logging On application ...
7
votes
1answer
19k 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 ...
6
votes
2answers
4k views

A database in MySQL is a schema in Oracle : I do not understand this statement

Please help me understand this statement: A database in MySQL is a schema in Oracle. I have just started to use Oracle and I find it different from other RDBMS softwares I have used like MSSQL, ...
6
votes
2answers
11k 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 ...
5
votes
3answers
1k views

How to store many smallish time-series in a relational DB?

Input Data Multiple Tests Beds generate measurement data of various complexity. In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few ...
5
votes
5answers
19k views

Simplified/automated datafile free disk space reclaim

On Oracle version 11g: After Googling, I can't find a simple way to reclaim free space after deleting a table. I've found a lot of explanations, telling how the datafile becomes fragmented, the big ...
4
votes
7answers
41k views

How to export an ER diagram from an Oracle DB?

I am developing this application with an Oracle database. I haven't been documenting the data model, but now an auditor wants to have an entity relationship diagram of my database. There are many ...