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

4
votes
1answer
431 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 ...
2
votes
1answer
208 views

Does a DDL in Oracle affect other transactions?

I understand that DDL statements in Oracle have an implicit commit before and after the statement, which obviously affects the current transaction. However, does a DDL statement in any way affect ...
2
votes
4answers
1k 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
126 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
3answers
90 views

What is the instance name of the box I'm logged into?

I'm at the linux prompt of a computer running Oracle 11. Without connecting to the database, how can I determine what is the instance name of the Oracle server running on this box?
0
votes
2answers
5k 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!
0
votes
1answer
68 views

Access and filter in oracle plan

I came to a execution plan where oracle choose to access the index but instead of using the columns as predicate it only filter by them. My question is what are the reasons that can cause that(all ...
0
votes
2answers
4k views

Where .dmp file is restored after import in oracle?

I am new to oracle and I have abc.dmp file at c:\abc.dmp location. I want to import this abc.dmp file. I am using the following command. c:\>imp system/mypassword file=c:\abc.dmp full=y I am ...
1
vote
2answers
18k views

Inserting data into a table with one value as clob and blob

My table has one clob column and one blob column. I need to insert data into it through insert statements. The length of data to be inserted is more than 4000 chars. When I do export of insert ...
2
votes
1answer
814 views

Historical SQL Monitoring

Is there a built-in Oracle feature that extends Real-Time SQL Monitoring to historical activity? I am specifically interested in getting wait events aggregated at the SQL operation level, not just ...
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
81 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
2k 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 ...
1
vote
1answer
175 views

Authenticating to Oracle REST data services as admin

i'm working on an android app connected to a database, the database has more data than any tablet can handle, so after some research i found many solutions, most of them consisted of implementing a ...
0
votes
0answers
137 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
1k 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?
-1
votes
1answer
549 views

Shutdown immediate displays an error

During my shutdown for the database, there is ora error message return and it won't let me shutdown my database. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit ...
0
votes
1answer
61 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
460 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
662 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
160 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 ...
3
votes
5answers
22k views

Oracle 12c create user

I have some problem with creating user in Enterprise Oracle Database System. A could'n create user using command like create user name identified by password; because of exception: ORA-65096: ...
0
votes
1answer
60 views

What is meant by the RMAN Duplicate “noncurrent point in time”?

When doing a RMAN DUPLICATE from backup connected to the target, how current does the duplication make the database when no until time is set? The closest thing I can find in the documentation is the ...
0
votes
1answer
162 views

Oracle 11g max login fail attempts workaround

My problem with database starts with situation where I cant really modify anything in database. My project specialist has limited time to help me. Here is the thing: My user in Oracle database has ...
0
votes
2answers
177 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
743 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 ...
4
votes
1answer
59 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 ( ...
0
votes
1answer
530 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 ...
1
vote
1answer
94 views

SQLPlus Padding in Query Results

I have several Oracle servers that exhibit a behavior I do not understand. I have identified a two node RAC in which one node exhibits the behavior, but the other node does not. RAC is not involved ...
3
votes
2answers
739 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 = ...
1
vote
3answers
193 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 ...
1
vote
1answer
329 views

Compare Number of Rows of Tables

I have two or more tables different by structure and I want to compare their numbers of rows, which I get with the count-star query: select count(*) from table_a For two tables I would like to see ...
2
votes
1answer
2k views

Oracle 11g installation stuck at installation

I'm trying to install oracle 11g on fedora by following this tutorial but it stuck after I click Next in step 8. The screen does not go away even after several hours. There's no log file in ...
0
votes
2answers
409 views

How to print the information of all tables using Oracle?

I'm studying DDBB and we are using Oracle. Using too much tables can be really difficult for me because I have to remember table1.content2, table3.content1, etc. So because of that, I would like to ...
1
vote
1answer
126 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 ...
1
vote
1answer
773 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, ...
0
votes
1answer
85 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
1answer
760 views

I'm trying to run an oracle database in virtual box under oracle linux

I'm trying to run a oracle DB in VirtualBox and access it from my host mac OSX , i am using port forwarding to access my guest, I can telnet my guest on the port, however when i try to connect from ...
0
votes
1answer
228 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
215 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: ...
2
votes
1answer
1k 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
0answers
5k 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 ...
1
vote
2answers
113 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 ...
0
votes
1answer
5k views

PLS-00049 bad bind variable when compiling a trigger

I'm trying to implement a dedupe trigger to a table. CREATE OR REPLACE TRIGGER USER.TBL_ACTION_DEDUP BEFORE INSERT ON USER.ERRORTABLE REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF ...
5
votes
3answers
919 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 ...
1
vote
2answers
8k views

Create Oracle DB LINK to a database with no DB_DOMAIN

I'm trying to create a database link between this two DBs. My local DB is 11.2.0.3. SQL> select * from global_name; GLOBAL_NAME ...
0
votes
1answer
574 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
2k 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
1answer
341 views

Export synonyms content to tables

I have a distant database to replicate having huge data but I have access only to synonyms and I would like to use Oracle export/import tool to import these data into tables of local database. What ...
0
votes
1answer
1k 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 ...