Oracle is a RDBMS (Relational Database Management System) created by Oracle Corporation. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java. Also included is support for C (Pro*C, OCI and EXTPROC), C++ (OCCI) and Java (JDBC) ...

learn more… | top users | synonyms

4
votes
1answer
309 views

date formatting in Oracle

this query: select to_char(sysdate, 'Ddspth MONTH, Yyyysp') from dual will return: Eighteenth MARCH , Two Thousand Fourteen i am wondering if there are any additional formatting keywords that ...
0
votes
2answers
128 views

In Oracle, why would an OR / LIKE(A,B) take far longer than running the same query for each condition separately and unioning

I have a complicated subquery that looks something like this SELECT ... FROM ... WHERE my_column IN (1,3) It takes a long time to run. I tried splitting it out into: SELECT ... FROM ... WHERE ...
2
votes
4answers
629 views

Oracle How to find Debug Compiled Stored Procedures/Functions

How can I get list of stored procedures/Function which are compiled for debug? I do not see relevant column in following tables. DBA_PROCEDURES DBA_OBJECTS
0
votes
2answers
100 views

Help about Oracle licensing [closed]

I would like to know some details about oracle licensing. What is oracle processor licences? How does it differ from the other?We are planning for a dual processor server, do we need to purchase 2 ...
0
votes
2answers
3k views

maximum number of interval partitions in Oracle 11g

Does anyone know the maximum number of interval partitions permitted in Oracle 11g? didnt find anything in Oracle documentation -perhaps missed something. Thanks in advance!
1
vote
0answers
2k views

Export Oracle Database User data from client

I am working on client system. There is a database on server contains many users. I have the access to database with a userid and password. my userid is "ncon" and Database name is 'HYDPPD'. We are ...
1
vote
1answer
70 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
43 views

Cannot create directory with sysoper

My user has the two roles sysdba and sysoper. When I connect as sysdba I can sucessfully perform in sql plus: @> connect myuser/mypassword@myservice as sysdba; Connected. SYS@service> CREATE OR ...
1
vote
1answer
1k views

RMAN rename datafile created in an archivelog

I'm trying to restore an Oracle database (11.2.0.4.0) to a test system with a different file layout. The production database is being backed up using RMAN, no catalog. We do a full backup of all the ...
0
votes
1answer
678 views

How to interpret undo statistics in Oracle AWR report? [closed]

How can I use undo statistics in oracle AWR report for tuning Oracle? which indicators are important to identify that size of undo segments needs to be increased?
0
votes
0answers
81 views

Return a string from a pivot query

I have a query that uses around 6 or 7 tables and uses pivot to return a formatted string from this entire structure. Actually, today this query is dynamically generated via C#/Entity Framework and it ...
0
votes
1answer
48 views

Oracle to SQL Server

We have a table with 169 million rows in a Oracle 10g database, we need to move this table to a MS SQL Server 2012 database. Describe on the table: aaa NOT NULL NUMBER(38) bbb NOT NULL NUMBER(38) ...
0
votes
0answers
294 views

Exception in SQL client but not in stored procedure

I am using Oracle 11g and SQLDeveloper for this scenario. In this scenario, I am trying to simulate an exception within a stored procedure but it is not happening. My DB runs in GMT. My client ...
1
vote
1answer
423 views

Why don't HOST comands appear in the spool?

spooltest.sql: SPOOL test.log CLEAR SCREEN SET VERIFY OFF PROMPT Queries will appear in spool select sys_context('USERENV', 'INSTANCE_NAME') from dual; PROMPT Output from the HOST command will not ...
0
votes
0answers
4k views

Database Open but Oracle gives ORA-01033: ORACLE initialization or shutdown in progress

We are using 4 node Oracle 10GR2 RAC in Windows x64. 3 instances of our database crashed today and later started normally. I started them by connecting (Remote Desktop) to individual instances and ...
0
votes
0answers
119 views

Oracle Merge performance

I've a question about the merge performance in oracle. I work in the business intelligence area of a big company and its quite common to have to move millions of records one side to other. The other ...
2
votes
1answer
654 views

In Oracle AWR report how to use “Latch statistics” section in analysis

Latch statistics in AWR report contain following sections 1.Latch Activity 2.Latch Sleep Breakdown 3.Latch Miss Sources 4.Parent Latch Statistics 5.Child Latch Statistics How can I use these ...
0
votes
2answers
291 views

Backup Oracle DB using TSM server version 6.3.0

I'm new to Oracle and TSM server. I installed Oracle11gR2 and TSM server version 6.3.0 (linux Centos 6.4 OS). I did the configuration and everything works fine. so far I arrive to run the following ...
0
votes
1answer
178 views

Oracle Patch Update

We have an Oracle RAC production environment with primary and secondary DB. Our DBA has asked to update oracle version from 11.2.0.1.0(64 bit) to 11.2.0.3(64 bit) with patch 6880880,10404530,16803769 ...
0
votes
1answer
417 views

How to export oracle 11g database partially

I have a huge oracle test database whose size is more than 100 GB. I want to export only 1% of all the data and import it into my local database to have a local replica of that application. Is it ...
0
votes
2answers
116 views

Optional parameter in where condition

I have the following where condition: lr."dateEnd" >= to_date(?, 'dd.mm.yyyy') and lr."dateEnd" <= to_date(?, 'dd.mm.yyyy') All works fine. But the second unnamed parameter is optional. How ...
0
votes
1answer
325 views

Oracle, changed NLS_LANGUAGE and is now invalid and can't fix

I'm using oracle 12c, and changed the NLS_LANGUAGE variable to a invalid text using the manager at localhost:5500/em, after reboot the oracle db doesn't get up and Database Configuration Assistant ...
4
votes
1answer
53 views

Changes access method for non-correlated subquery

Oracle 11g R2 Unfortunately our application has per row security "features". We have a query that looks about like this: Bad, slow: SELECT someRow, someOtherRow FROM bigTableA a WHERE EXISTS ( ...
1
vote
3answers
156 views

ORA-12528 TNS Listner

When I try to connect the database using sql developer it gives the error below: ORA-12505 TNS:listner does not currently know of SID given in connect descriptor I have tried all the suggestions ...
3
votes
2answers
508 views

Perl DBI takes 60 seconds to connect to an Oracle database, every single time

I feel like I must be doing something wrong with my SQL connections. I've generally ignored it, but I'm finally going to try and get to the bottom of this. my $dsn = ...
0
votes
2answers
4k views

Trying to create a sequence in Oracle that starts with the max value of primary key of a specific table. Why does this not work?

Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work ? CREATE SEQUENCE reservation_sequence MINVALUE 0 START WITH (SELECT ...
4
votes
2answers
5k views

Shrinking the SYSTEM tablespace in Oracle

Our SYSTEM tablespace grew out of control because of the SYS.AUD$ table. We have truncated SYS.AUD$ but the datafile is still very big (~30G). Resize doesn't work because the file contains used data ...
1
vote
1answer
489 views

Oracle dead with unknown reason

Our oracle database dead from time to time.When i look into the log in the alert.It shows: Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xFFFFFFFEAA7DD0E0] [PC:0x8213123, ...
1
vote
1answer
103 views

assign “all values” instead of “null” in Conditional Where clause

I have to extract from a table based on user input. I have a table named Vehicle_Register with vehicle details and users are allowed to filter the data using vehicle type (car, bus, truck...). If no ...
0
votes
1answer
57 views

How to add a new target in Oracle Enterprise Manager 12 C

I just installed Oracle Enterprise Manager 12 C Grid Control from a ready made image that Oracle provides and now I am trying to add targets to it (monitoring targets i mean). It might be a stupid ...
0
votes
0answers
145 views

Issue with a where clause by using Oracle

I'm currently facing a problem while I'm adding a trigger to the database that I use at home. I'm using : Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production The problem ...
0
votes
0answers
130 views

How to generate TPC_DS queries for Oracle?

I have downloaded the DSGEN tool from the TPC-DS web site and already generated the tables and loaded the data into Oracle XE. I am using the following command to generate the SQL statements: ...
6
votes
3answers
4k 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 ...
0
votes
1answer
889 views

Oracle case insensitive passwords seemingly ignoring SEC_CASE_SENSITIVE_LOGON

I'm trying to change the SYS password of an Oracle 11g database to be case-insensitive for legacy application reasons. My research suggests all I need to do is set the SEC_CASE_SENSITIVE_LOGON flag, ...
1
vote
2answers
73 views

index for OR operator with different columns

What would be best index for this kind of query: -- id is the PK create table test3(id number,name varchar2(30) not null,lastname varchar2(30) not null); select id from test3 where id=:1 and ...
5
votes
3answers
526 views

Bad use of Oracle Tablespaces

I have a doubt about the use of oracle tablespaces. I've decided to define for each schema of the datababase two different tablespaces (one for data, and another one for indexes). The problem is that ...
0
votes
1answer
344 views

Is using dbms_utility.format_error_stack to filter errors in the exception block a bad idea?

I'm trying to handle two different exceptions could trigger in an external table that reads a CSV. The code works great, but I'm concerned I'm doing it wrong. SET SERVEROUTPUT ON; DECLARE records ...
0
votes
1answer
420 views

Why does Oracle store time in DATE column?

I have a table with a date column. I used JDBC to save a java.util.Date object into the table. When I run following query I get date aswell as a time value: select To_CHAR(departure_date,'DD-MM-YYYY ...
2
votes
1answer
493 views

Oracle Standby Database Techniques for faster application of redo logs

I am quite new with the concept of standby databases in Oracle and I am not really a DBA, so help me out. I have been reading a bit on the concept of Oracle standby databases and I am pretty much ...
0
votes
2answers
956 views

IMPDP Failing with ORA-00001: unique constraint in oracle

When I try to import a dump which contains data from several tables, the import fails with ORA-00001: unique constraint as there are several entries which are matching in the destination database, but ...
0
votes
1answer
103 views

performance difference between < and <=

I remember that there is a performance issue with SQLs that use "<" against "<=" because of statistics and/or histograms and/or the data itself, but I dont remember exactly the issue. I know ...
0
votes
0answers
366 views

ORA-01658 on index tablespace when trying to insert into empty table of new database

We are getting ORA-01658 (Error :ORA-01658: unable to create INITIAL extent for segment in tablespace MYDATA_LDATA_INDEX) in our application logs. This happens when application is trying to insert ...
1
vote
3answers
1k views

oracle crash with ORA-00942: table or view does not exist

we use oracle9i database. and since last week my oracle crash many times, after running for 30 hours more or less. we see errors repeat in my trc file and alert log : qmhGetHTTPError: Got unknown ...
0
votes
1answer
316 views

rownum won't return records above 100

I have this query: SELECT * FROM ( SELECT v.*, ROWNUM recnum FROM ( SELECT hostaddress, modulename, severity, escalation, classification, ...
0
votes
1answer
997 views

VS 2012 SSIS Data Flow Designer Source Assistant Error: [SSIS.Pipeline]: Component ", clsid {4CAC … Could not be created

I am creating my first SSIS package to support the E of our ETL from an external Oracle database to our internal SQL Server 2012 databse. Using Visual Studio 2012 and I have installed the Microsoft ...
1
vote
2answers
942 views

What are definer and invoker rights in database, Oracle and MysQL?

What are definer and invoker rights in database, Oracle and MysQL? I just don't get it
1
vote
0answers
595 views

whats the fastest way to copy data from table to new one?

Whats the fastest way to copy table "table_1" to new table called "table_2" ? I'd like the constraints copied as well as the data.
0
votes
1answer
48 views

Oracle Autodop disabled at the table level

I have a question related to Oracle auto DOP and parallel DML concepts. I have a scenario, if I set the Auto DOP set to 1 on the table and if I run parallel DML statements on that table through ...
1
vote
1answer
548 views

How to check Oracle database connection encryption type

How to check the Oracle database connection encryption type?(between Oracle database and other client applications)How do I assure between Oracle database and Toad the database connection is ...
1
vote
2answers
57 views

Trunc sytax problem

If any one could help me with the syntax issues which I am facing here. sqlshipped := ' and a.ORDS_COMPLETED_DATE between ' ||shippedfrom ||' and trunc(' ...