Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

-1
votes
1answer
19 views

TImestamp format in Oracle DB

Query: select date,TO_CHAR(date,'dd/MM/yyyy HH24:mm:ss') from user.table where rownum<10; 02-08-18 05:21:02.301000000 PM 02/08/2018 17:08:02 Problem is minutes in above time are different 'mm'...
0
votes
2answers
28 views

SQL Developer: choose the tablespace where a new table will be stored

When creating a new table, is it possible to specify the tablespace where the table is going to be stored?
0
votes
1answer
27 views

unable to launch Oracle Universal Installer , There was an error trying to initialize the HPI library [on hold]

New to Oracle Database world, trying to install Oracle 11g on Fedora 28, getting below error, please help. [oracle@fedora28 database]$ ./runInstaller Starting Oracle Universal Installer... Checking ...
2
votes
4answers
308 views

How do you SUM a column without having its name?

I'm stuck in a way that how to do sum on a column let's say first column of a table (result from another query) without knowing the column name with something like column position id. it's something ...
0
votes
0answers
11 views

Error importing Workspace in Oracle APEX 4.0.2

I'm trying to move some Oracle APEX workspace. Source Oracle APEX and destination are on the same version 4.0.2 One of the workspace give this error during import (others are working): Execution of ...
0
votes
0answers
23 views

Accessing to Enterprise Manager: 500 internal server error

I installed yesterday Oracle 12c. After installation I accessed normally to the Enterprise Manager requesting https://localhost:5500/em The problem: after restarting Windows I get this error on my ...
0
votes
1answer
33 views

Error granting SELECT on view that uses ALL_OBJECTS

I'm in the middle of testing Oracle 12c for our application. I have something similar to this view in a particular schema, which we'll call MY_OWNER_SCHEMA for this question: CREATE OR REPLACE VIEW ...
1
vote
0answers
27 views

Removing REM INSERTING and SET DEFINE OFF from SQLcl Output

I ran below script to get data from Oracle, and then load to Postgres with psql. SET SQLFORMAT INSERT SET FEEDBACK OFF SET TERMOUT OFF SPOOL asset_file.sql SELECT * FROM FINANCE.ASSET; SPOOL OFF ...
0
votes
1answer
49 views

DISTINCT / Unique

I am running a query that I want to return only the distinct patron_id. SELECT pa.patron_account_id, pa.patron_account_name, pce.email, e.event_code AS EVENTCODE, sum(po....
0
votes
5answers
47 views

How could I fix this warning?

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 20 13:08:07 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Enter user-name: system Enter password: ERROR: ORA-28002: the password will ...
0
votes
1answer
39 views

Users running ANSI scripts are having problems with special characers [on hold]

Long story short. How can I fix users environments, to make them run our scripts using ANSI encode? The problem is, we send them scripts to run on their databases using ANSI encode. But some of ...
1
vote
1answer
20 views

Question on Oracle Public DB link inside Procedures

Scenario : A procedure 'XYZ' is created with DB account 'SCOTT'. The procedure uses a public DB link to connect to another database. We have another user 'EVE' who has access to execute procedures ...
0
votes
1answer
13 views

Variable Assignment and Usage in Oracle

Why does this fail in SQL Developer against an Oracle database and what would the an appropriate way of achieving the same? DECLARE v_pNbr NUMBER := 100150; begin select * from TARGETTABLE ...
-3
votes
0answers
43 views

Why we need to rebuild indexes in oracle [on hold]

Why we need to rebuild indexes and how come query cost get improved after rebuilding indexes?
0
votes
1answer
17 views

How do I check the connection timeout in Oracle?

I need to check Oracle 12c timeout for an active connection. I don't have access to the database itself but I do have some creds. Is there a table to query to get this information? Thank you!
0
votes
0answers
27 views

Migrating Oracle 10g on Solaris Sparc to Linux RHEL 5 VM

if i will rate my oracle expertise i would give it 3/10. i just started learning oracle, solaris and linux 2months ago and was given this task to migrate. yes our oracle version is quite old and might ...
0
votes
0answers
24 views

TEMP Tablespace Removal

Oracle Version: 12.1.0.2 We have a discrepancy between our PROD and DR TEMP tablespace data files. PROD = 30G + 30G + 30G data files DR = 30G + 30G + 30G + 21G + 2G + 1G + 15G data files I ...
0
votes
1answer
12 views

how to replace dbms_reputil.from_remote in oracle 12.2.0.1 since All objects removed except REPLICATION_IS_ON, _OFF and _ON

how to replace dbms_reputil.from_remote in oracle 12.2.0.1 since All objects removed except REPLICATION_IS_ON, _OFF and _ON In plsql program CREATE OR REPLACE TRIGGER EVENT_ALLOWABLE_STATUS_S ...
0
votes
1answer
19 views

Question about alerts in Oracle 11.2

I inherited an Oracle 11.2 server. I wanted to see if whomever configured it before me had any alerts configured, perhaps to notify on audit events. Logging on as sysdba, I issued this command per ...
0
votes
1answer
16 views

Help - SQL query // Extracting from XML // getting error if no data found

I am building a query, where I am extract values from XML. I am getting output if the xml has values. But if no xml found am getting error I need help to fill those columns with "yet to get" or ...
-2
votes
0answers
16 views

query DB by detecting the pattern [closed]

I want to query my oracle db based on the search criteria specified in the text box. e.g. for a table class (roll no, Student Name), Subject_enrolled (Roll no, Sub_Name, Teacher_id), dept(teacher_id,...
0
votes
1answer
22 views

sqlplus command not found after installing oracle 12c

I have going at it for the last 12 hours... I installed Oracle 12c on rhel7, the first installation didn’t go well so Installed a second one without changing the first installation; I created a new ...
0
votes
1answer
27 views

RMAN or Fresh install

I'm trying to reduce the time to install Oracle on a pipeline we have. At the moment, it's installed using dbca in silent mode with a database template file. The majority of the time seems to be ...
0
votes
1answer
37 views

Different execution plans and joins using synonym or table

Edit: We found the leading cause. If you join tables with the same name but in different schemas you can get this result. Same with joining two tables using the same alias (whyever this works at all)....
0
votes
0answers
26 views

Error while importing database using impdp Oracle 11g

While importing the database using impdp I saw this in the log file . Cannot set an SCN larger than the current SCN. If a Streams Capture configuration was imported then the Apply that processes the ...
0
votes
0answers
11 views

unixODBC - [IM002][unixODBC][Driver Manager] Data source name not found and no default driver specified

Folks, We installed unixODBC-2.3.7 on Aix 7.1 to connect to Oracle 11g. After setup odbc.ini and odbcinst.ini files and tried to connect with isql, we had the following error: # isql -v ORACLE [...
0
votes
2answers
38 views

ORACLE Table insertion

I want to insert data into below table, but I got an error. How do I solve this? CREATE OR REPLACE TYPE TEST_TYP FORCE IS OBJECT ("id" VARCHAR(5000 NULL) NOT FINAL; CREATE OR REPLACE TYPE TEST_TAB ...
1
vote
0answers
22 views

Incremental load from SQL Server through Oracle into Teradata [closed]

I have data in SQL Server but in that table there is no data column. How we can load data through Oracle using HIGH WATER MARK, based on Id column we can move the data into Teradata. How can we do ...
1
vote
1answer
51 views

How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0

I am running following command to dump my database ./expdp username/password@//host:port/database directory=/home/ubuntu/oracle/instantclient_12_2 dumpfile=dumpfilename.dmp content=METADATA_ONLY and ...
-1
votes
2answers
37 views

Repeat row in the result based on the value of a column

FIRST_COL VAL_COL A 2 B 3 C 4 I need output as below. FIRST_COL VAL_COL A 2 A 2 B 3 B 3 B 3 C 4 C 4 C ...
0
votes
1answer
48 views

unable to start ORACLE database

My application is running on Oracle Database which hosts 3 database instances. All of sudden one of my database instance is not running. when connected through sysdba it says "connected to idle ...
0
votes
1answer
30 views

ORA-24155 - Why am I getting this error when creating a chain rule

When trying to create a new chain rule, I receive ORA-24155 stating that the the rule I'm trying to create is not in the ruleset which doesn't make sense. I stop and drop all jobs/chains and then ...
1
vote
0answers
19 views

ORA-12514 for Oracle Devsuite 10g and Oracle DB 11g on the same host

I have oracle devsuite 10g (forms, reports, etc...) on the same host as Oracle database 11g... Devsuite was installed prior to Oracle DB 11g. When I go in Oracle DB 11g and launch sqlplus sys as ...
1
vote
0answers
24 views

Oracle Warning in Alert Log Regarding PGA_AGGREGATE_LIMIT

Introduction During the startup process of our Oracle instances, we are encountering the following error message in the alert log: WARNING: pga_aggregate_limit value is too high for the amount of ...
-1
votes
1answer
29 views

Column ambigously defined [closed]

Am having issue executing the below. line 1: ORA-00918: column ambiguously defined SELECT user_id, user_no,dat_sys,dat_post from rec_txnlog LEFT JOIN rec_bctl ON user_id = user_no ORDER BY 2 ...
1
vote
0answers
19 views

What is meant by Orphan Lock and Orphan Sessions?

I have came across this term Orphan locks and Orphan sessions and I really have no idea what that is. I have tried to search it on Google and I still haven't got any useful info/results regarding what ...
0
votes
2answers
33 views

How space is compared in where condition

I've come across below query where it is column being compared the space character. SELECT * FROM company WHERE emp_name > ' ' -- space char AND emp_id = 123 AND active_flag = 'Y'; ...
1
vote
0answers
17 views

using postgresql foreign wrapper and replication at the same time for oracle external table

We need to replicate an oracle table into a postgresql database, now we are doing that with etl jobs and programatically but the frequence of the updates are to high for this approach. I was thinking ...
2
votes
1answer
33 views

Tables with MD_ prefix were found in my Oracle DB - is there any info about them?

Recently, during up-gradation of my Oracle DB, I have found many tables whose names are starting with MD_, which are empty. Is there any info about them? Can I drop them?
0
votes
3answers
45 views

Partial local index on partitioned Oracle table

We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added. We need to do selects on this table, over a period of the last five days, and ...
0
votes
1answer
21 views

Backup data base in oracle 12C

We have 12c database. we had create a pdb. Now i want to take a backup through RMAN. Could you please share the steps to configure this backup. I know to configure backup in 11g..For 12c Please share ...
-1
votes
2answers
40 views

Oracle SQL subquery error “too many values” [closed]

select student.student_id, attendence.absent_time from student, attetndence where ( select student_id, sum(absent_time) from attetndence group by student_id; ) > 10 Group by student....
1
vote
1answer
42 views

How to export user with tables and tablespace oracle

I'm a newbie to Oracle World. I have to export, from computer A, tables and their data, to computer B. So, my question is: is there a way to export user, tablespace, tables and their data with one ...
-1
votes
1answer
32 views

Migrate View from Oracle to PostgreSQL (with extractvalue() function)

I'm migrating a database from Oracle to PostgreSQL and have a problem with several Views. I can't find an equivalent function for extractvalue() and xmltype()... NVL(extractvalue(xmltype(METADATA), '...
1
vote
1answer
32 views

What is the function that determines the maximum value in a series?

Say I have a table with columns C and D (among others but these are the relevant ones) with the date type. I am instructed to update the values in C to be 90 days before its corresponding value in D, ...
4
votes
1answer
132 views

Does any RDBMS parallelize or otherwise optimize UNION queries?

For an example data set with rows fulfilling both subqueries: SELECT h.batch, li.id as line_item, d.prusr as price, 0 as advertised FROM schema.lineitem li INNER JOIN schema.detail d on d.line = li....
0
votes
0answers
38 views

Oracle to SQL Server Conversion help: sys_connect_by_path, listagg, regexp

I've been tasked with converting a process from Oracle to Sql Server 2012. I've converted most of the query over to SQL Server but am having a hard time translating over these queries to SQL Server ...
0
votes
0answers
24 views

Way to link to Oracle DB from SQL Azure DB

We currently get data from our Oracle DB by creating it as a linked server from our on-premise SQL server database. We are considering moving to the Azure SQL and would still require data from on -...
1
vote
0answers
27 views

Index to facilitate joining a table against itself to gather the last observation

I have a discrete step timeseries table where I insert about 1M rows per "month end" with the identifier being the last day of the month (2018/02/28 or 2018/08/31). I try to keep as many months as ...
1
vote
3answers
44 views

Why are global temporary tables truncated when the connection is closed?

I'm a relatively new Oracle database developer coming from SQL Server. I noticed that global temporary tables (created with ON COMMIT PRESERVE ROWS) lose their contents when the session times out, ...