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

2
votes
1answer
122 views

Split field values using specific character

Please help me with this. It is possible to split field values using a specific character? Here is my sample table value 10uF 2K 1.0uF 200UF I want it to split by this: value ...
3
votes
2answers
40 views

Query not recognize alias [duplicate]

I have the following query, the alias it's called DELAY, works fine, but when I try to run the first query adding AND DELAY < 0, the alias is not recognized. I tried to create the alias QUERY AS ...
0
votes
1answer
3k views

User with dba privs can't login to APEX, but can from SQL*Plus

I'm using Oracle 11gR2 XE. A user with dba privileges can't log in to the APEX interface, but can from the command line with sqlplus. I just created user: CREATE ROLE administrators; GRANT dba TO ...
1
vote
2answers
2k views

Why am I able to query a remote database without a dblink?

While researching a security concern, I discovered that users are able to query a remote database without the need of a dblink. However, this was only true for databases that had no value for ...
1
vote
1answer
91 views

Service privileges for Patch Download? (Oracle 11gR2)

I first asked this question on stackoverflow, but someone pointed out that this might be the appropriate site. Hope I am doing this right. To perform tests in advance of an upgrade from Oracle 11gR2 ...
0
votes
0answers
71 views

Clob (Swift message) data in Oracle table

I have swift data in clob column and want to insert this data in multiple columns in different table. create table test_swift (id number , block4 clob ); insert into test_swift values (123, ...
0
votes
1answer
107 views

select row where a couple of column values is different [closed]

I have a table A with thoses columns : date, ip, created_by. I would like to display date, ip and created by of rows where for one ip it exists different created_by. Thank you I explain more : ...
2
votes
0answers
53 views

Procedure to update tables when the difference of values is less than 10% [closed]

I use Oracle database. I have 2 tables. Table1 with the following details: Table-1 emp.no | salary 1 | 2000 2 | 3000 3 ...
3
votes
2answers
149 views

Stored procedure using cursors [closed]

I am attempting to create a procedure using cursors to list the average salary of all employees on a per department basis. I am using three tables, defined as emp (eid, ename, age, salary) works ...
3
votes
3answers
136 views

How to select a row based on the maximum value of a column, when ties are expected

SUBSCRIBER DATE TIME VALUE aaa 23-04-2015 04:04:07 10 aab 23-04-2015 12:04:32 5 bbb 23-04-2015 01:04:05 20 bbb 23-04-2015 ...
2
votes
1answer
163 views

Migrating Oracle 9i database to MySql database on a different Windows Server

I need to migrate my Oracle 9i database with all its tables from windows server 2003 to MySql on another Windows Server 2012. The oracle database is connected to Visual Studio web project. I have ...
1
vote
1answer
323 views

Is there any way to use “like” operator during oracle table partitioning

I am using Oracle 11g database and I have 45 million records. I want to partition the table, but if it's possible for partitioning to support the like operator then it will be much helpful for me. In ...
2
votes
1answer
517 views

How to download Oracle Database 11g release 2 for Mac OS

I have been trying to download Oracle Database 11g release 2 for mac os but in the given link by oracle I can only see Linux x86 and Linux x86-64. Is this ok to use this Linux file for installing ...
1
vote
1answer
124 views

Oracle - Using index on unique constraint, what the deal?

Using Druid to generate SQL script for Oracle, I have seen a difference between the version 3.10 and 3.13 and I would like to understand what going on. SQL generated by Druid 3.10 : CREATE TABLE ...
0
votes
1answer
4k views

Oracle Enterprise Manager - Where do I find the error logs?

I'm not familiar with oracle management. The problem is when I try to connect to the enterprise manager through the Web browser I get this error: In english: "Internal Error has occurred. Check ...
2
votes
1answer
20k views

How to format sql-plus-spool-file to *.csv with all columns in one line and row-content with linebreaks as one field?

I am not a professional, sorry. But perhaps you can help me anyway ... I have a table with 27 columns and within the rows data with linebreaks. When I try to export selected rows with sql-plus ...
2
votes
1answer
57 views

Variable Substitution

I am working in TOAD for Oracle and am trying to use variable substitution in my coding. I have been able to get variable substitution to work in the code body pressing F5: Define Value = '1.5' ...
0
votes
1answer
864 views

Printing comments or statements when executing script to a output file using TOAD/ORACLE

Coming from a SQL SSMS enviorment, I would like to duplicate their 'Print' Command which allows you to print commands or variables while executing a query. I am looking for this feature to help ...
1
vote
1answer
223 views

Is it possible to create a view in one database from another database in oracle when databases are different machine?

Lets say I have a oracle database db1(actually user) in machine A and another database db2 in machine B. Is it possible to create view in db2 from db1.If possible how ?
1
vote
1answer
3k views

Oracle 11.2.0.3 to 11.2.0.4

I'm planning to upgrade 3 instances of Oracle (all same Oracle Home) from 11.2.0.3 to 11.2.0.4. I'm going through the installer and I get to the question about Upgrade or install just the database ...
1
vote
0answers
154 views

Oracle Rac With Scan Error

I have installed Oracle RAC with ASM on Oracle Linux 5.8. My database version is 11.2.0.1. I have 2 node in my plan and all of them are active. I set scan in my DNS server but I can not connect to ...
0
votes
1answer
41 views

Formatting a DATE type in an Oracle database to only accept MMDD

Is there a way to limit the inputs into a DATE type in an Oracle database to only accept inputs formatted with only the day and month (not including the year)?
0
votes
0answers
66 views

CALL Procedure(Function) = Trigger?

I have discovered some strange behavior in Oracle 11g, and I'm wondering if anyone has an explanation for it. The gist is that when I use a function that contains a commit/rollback/savepoint within a ...
0
votes
1answer
170 views

Why is my PL/SQL procedure so slow?

I have this sub-procedure in a fairly large program, and it is taking hundreds of times longer than comparable sub-procedures. Is there any way I could improve efficiency here? None of this ...
0
votes
1answer
128 views

Is possible do an online snapshot backup , without archive logs at Oracle Database?

Using Oracle Database Standard Edition 11r2 . I'm looking a way to backup the database with zero downtime , using a snapshot of datafiles (LVM snapshots) and without need backup de archive logs. ...
0
votes
1answer
124 views

Oracle selective redo log shipping

We are planing to setup an Oracle 12c RAC with a Logical standby in the DR site. Since data consistency is extremely important, we our planning to setup redo log shipping to be in SYNC mode. Our ...
0
votes
0answers
133 views

Getting milliseconds elapsed during a stored PL/SQL procedure

I've been using sysdate to time procedures, but a lot of my procedures are very small procedures that get called thousands of times. Is there a way to get milliseconds elapsed instead of seconds? I ...
2
votes
2answers
3k views

ORA-12899: importing a .dmp file + character set conversion

I'm working on a django project on Ubuntu 10.04 with Oracle Database Server , so I've installed: Oracle Database 10g xe universal Rel.10.2.0.1.0 cx_Oracle-5.0.4-10g-unicode-py26-1.x86_64 When I ...
0
votes
1answer
47 views

Group-by in an Oracle database

on an Oracle database. If I make an: select id,id_operator,denumire,cod_fiscal,loc_den,str from rap where is_punct_lucru=1 and id_operator in ( select id_operator from rap where ...
1
vote
1answer
46 views

What does Oracle XE perform during installation?

I have oracle11g2 installed on my noteobook. I then installed the Oracle Database 11g XE. Now a .dll which uses the oracle11g2 client does not work anymore. First I recogniced that I had now two ...
0
votes
1answer
52 views

Apply operation on all schemas

I have an Oracle instance, with several schemas (all starting with the same prefix). PATT_A PATT_B PATT_C ... All of them have the same table, and I'd like to update all of them in one shot (with ...
2
votes
2answers
278 views

sqlplus (Oracle XE) doesn't start on CentOS 7

I followed the steps of this document: https://docs.oracle.com/cd/E17781_01/install.112/e18802/toc.htm#XEINL121, but sqlplus doesn't start. Even if I used the command: "sqlplus /nolog". The other ...
0
votes
0answers
1k views

Oracle trigger after insert error ORA-01403: no data found

I'm trying to make a trigger that select a value inserted in other table and concat with other value in an insert on other table that's my code CREATE TABLE "SEX" ( "ID" NUMBER, "CS" ...
0
votes
2answers
101 views

How to get the second string in the ORA-02296 error to show something meaningful

Whenever I run the following script*: ALTER TABLE ANIMAL.MONKEY MODIFY TAIL_COUNT NOT NULL; I receive the following error: ORA-02296: cannot enable (ANIMAL.) - null values found I have a ...
2
votes
1answer
225 views

Oracle user locks (dbms_lock): guaranteed order of requests granted?

I was wondering if Oracle guarantees that the sequence of dbms_lock.request granted is the same as the sequence in which they were requested. In my tests it always was the case, but I couldn't find ...
0
votes
0answers
260 views

Partitioning existing table in Oracle

(Oracle) I'm trying to do an exchange partition on an empty partitioned table to fill it with data from a non-partitioned table. I'm getting the following error and can't find the cause. I'm not 100% ...
0
votes
2answers
1k views

What is the privilege for account unlock in Oracle [closed]

I want to understand the privileges better, but I'm not able to find some useful info using Google. I thought that I needed ALTER USER privilege to be able to unlock user and change password. But ...
6
votes
3answers
33k views

What are the causes and solutions for mutating table errors?

I understand mutating table errors are caused by a design flaw or problematic query. An old query was recently put into production which throws a mutating table error. Our DBA solved the problem but ...
0
votes
1answer
247 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
1answer
81 views

sqlplus connect without typing the @local_SID when only have one instance

Every time I want to connect with a user in Sqlplus I have to specify the database|sid name too, SQL>connect scott/tiger@orcl I have installed another database for other purpose but I removed ...
12
votes
2answers
76k views

How do you show SQL executing on an Oracle database?

How can you show the SQL that is currently executing on an oracle db? Extra information that would be useful would include user, session id etc.
0
votes
2answers
144 views

DBMS_REDEFINITION don't copy data?

Is there an option to use Oracles online table redefinition feature but only copy a subset of the data from the source table? I mean - the source table has 2M records and I want to only copy those ...
1
vote
1answer
387 views

Understanding of schema and tablespaces - recommendation for a later seperation [closed]

DLDR: I would like to get a confirmation of my understanding of tablespaces and schemas (see image) a recommendation how to organize schemas and tablespace to make a later separation easy. ...
0
votes
1answer
4k views

Copying a table in SQL Server database to Oracle database

I am new to database management. I need to copy a particular table "Attendance" in SQL Server database to Oracle database. There is no connection between the two databases. Also, "Attendance" is ...
0
votes
0answers
276 views

(Oracle PL/SQL) How to capture multiple error lines in exception section?

begin some SQL...; exception send_SMS('SQL error: '||SQLERRM); end; This sometimes reports: ORA-02063: preceding line from DBLINK_NAME How to capture the preceding error lines in a PL/SQL ...
0
votes
3answers
4k views

I'm trying to use a variable in Oracle PL/SQL, but I'm getting an error. What's wrong here?

I come from an SQL Server background and I'm trying to figure out how to use Oracle PL/SQL variables. I'm trying to declare a variable and then select that variable. It sounds simple, but I am doing ...
0
votes
3answers
238 views

Will an update in my trigger trigger itself? Oracle PLSQL

Here's my PLSQL trigger: CREATE OR REPLACE TRIGGER ROOT_CHANGING_TRG BEFORE UPDATE OF ROOT_ID,PARENT_PHYS_ID ON UNIQUE_PHYSICIAN FOR EACH ROW DECLARE var_root number := :new.ROOT_ID; var_par number ...
3
votes
1answer
124 views

ORA-00845 after deleting files accidentally at /dev/shm

I have two instances (ORA1 and ORA2) of Oracle 11g at the same machine. One is starting the other cannot startup anymore. What happend: First my disk was full, so I accidentally deleted all ...
3
votes
2answers
2k views

Is index performance on a TIMESTAMP(6) column impacted when converting it to a number

If I have an index on a TIMESTAMP(6) column, am I forced to use this column as a TIMESTAMP(6) to get the benefits of the index? Can I convert this column to a number with second precision ...
2
votes
1answer
129 views

Why does DBMS_SESSION.session_trace_enable create multiple files?

I have a trace on a session configured like this: execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''TEST1'''; DBMS_SESSION.session_trace_enable (waits => TRUE, ...