Stack Exchange Network

Stack Exchange network consists of 175 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

Questions tagged [oracle]

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.

0
votes
1answer
14 views

Can I separate user and system trace files in Oracle 11g?

In Oracle 10g, the destinations for system and user generated trace files were controlled by the background_dump_dest and user_dump_dest Oracle parameters. Starting in 11g these parameters are ...
0
votes
1answer
24 views

Why can i not call COUNT on a varray column in a select statement using oracle

I have a simple array type definition like this CREATE OR REPLACE TYPE "LIST_OF_NAMES_T" IS VARRAY(10) OF VARCHAR2 (100); Now I have a column items of type LIST_OF_NAMES_T in my table TABLE1 ...
0
votes
1answer
19 views

Best approach to run a simple query against all users

So, I could easily do this in SQL Server. I created a cursor, but to be even more simple, I just ran SP_MSFOREACHDB with a simple query: select top 1 * from Database..Table order by Date desc I ...
0
votes
0answers
12 views

How does LBAC (Label Based Access Control) work?

I'm having a hard time to understand how an LBAC works? What is the difference between label security and security policy? An example is much appreciated.
0
votes
0answers
14 views

Access tables from multiple schemas

I have two schemes with multiple tables. The queries in packages heavily reference schema names. Is it possible to remove schema names from the queries and oracle to identify the tables based on roles ...
-1
votes
0answers
12 views

Refer table from another schema

I would like to refer to multiple tables in another schema without schema prefix or altering the session. I have the below query - Select * from schema1.app_table There r multiple queries like these ...
-1
votes
0answers
17 views

oracle synchronous data capture replacement

We have been using the oracle Streams Synchronous Data Capture functions to capture all DML changes that occur in a single transaction. All logged rows would be grouped by the same SCN value. In ...
0
votes
0answers
37 views

Oracle: Replacing package body stuck

I have two schemas in my oracle database: XXX_MONITORING and XXX_MONITORING_CONFIGURATION. I had a script running on MONITORING_CONFIGURATION which is run with SQLPlus to update the database. I split ...
0
votes
0answers
27 views

Delete number of rows from a table and deleted rows to be inserted into another table [on hold]

Create Or Replace Trigger EMP_DTS_TRG BEFORE INSERT OR UPDATE OR DELETE ON EMP_DTS FOR EACH ROW DECLARE CURSOR C1 (C_DLETE_TIME DATE ,C_EMP_ID VARCHAR2(20),C_EMP_NAME VARCHAR2(20),C_EMP_ROLE VARCHAR2(...
1
vote
1answer
27 views

Grant all the privileges to a user over all other user's objects in Oracle

I would like to know how to grant all the privileges to a user over all other user's objects in oracle. Greetings and many thanks
-3
votes
0answers
41 views

why Case count distinct not returning correct values [on hold]

I am trying to get a count of employees based on a case statement with count. I want the code to return a true if the condition defined is true otherwise it should return a null or zero but its ...
1
vote
1answer
35 views

Is there any function like checksum in Oracle

I need to calculate the checksum of some columns. The built-in function checksum is used to calculate the checksum in Microsoft SQL Server. Is there any built-in function checksum in Oracle?
0
votes
1answer
44 views

Oracle uses a lot of memory

OS: Windows Server 2012 DB: Oracle11g-r2 When you restart or turn on the server, everything works perfectly, the memory is normal, the number of sessions, too. But after 2-3 days of continuous ...
2
votes
1answer
34 views

START WITH and CONNECT BY condition in Oracle SQL

I have a question on the START WITH and CONNECT BY condition (in Oracle 12c): This is my Oracle SQL: SELECT employee_id, lpad(' ', level*2-1,' ') || last_name, manager_id, level, ...
0
votes
0answers
17 views

Stranges character when install cloud control 13c R2 [on hold]

I am trying to install Oracle Cloud Control on a server with Red Hat Enterprise 7.5 to access it by ssh (ssh -X oracle @ipserver) and when I run ./em13200_linux64.bin it shows the installation window ...
0
votes
0answers
22 views

audit policy on a table does not log expected results

Running on Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production. Attempting to test auditing with the following steps: Created a new user, MYUSER. CREATE TABLE mytest ( id ...
0
votes
0answers
30 views

Switching between dblinks depending on date

The database was divided in two, one with information up to 2016 and the other with information from 2017 onwards. The DBA created two dblinks: @old.com (<=2016) and @new.com (>2016). I have some ...
0
votes
1answer
17 views

Oracle AWS RDS supermaster user cant grant select on some tables

I'm dealing with some sort of issue beyond my Oracle knowledge. A customer wants to execute some queries to detect issues, locks, etc in a Oracle RDS database. I assume this queries needs database ...
0
votes
1answer
20 views

Why does the system generated TNSNAMES.ORA have two entires, one with a full connect descriptor and another with just a network address?

My TNSNAMES.ORA file was auto-generated when I installed my database. It generates with two entries for my database however. One entry looks "complete" with a full connect descriptor, while the 2nd ...
0
votes
0answers
35 views

Some question about FBI and Index on unique column [on hold]

Anyone can explain to me these issues: Which function-based-index(FBI) should I create and not? I heard from someone active in this forum that we should avoid create FBI as RTRIM, TO_CHAR, UPPER, ...
0
votes
1answer
31 views

Sqlplus not using NLS_LANG env variable

I am trying to login to an Oracle 10g database vía sqlplus. In the nls_session_parameters I need NLS_DATE_LANGUAGE in American, but I always have Spanish. In the database server my NLS_LANG is ...
1
vote
1answer
23 views

Added a new listener to my 'listeners.ora' file. How can I connect directly to it with SQLPlus? (skipping tnsnames.ora for now)

I updated my listeners.ora file to include a new listener on port 1523. I then started the listener with lnsrctl start. I'd now like to connect to the database on that port via SQLPlus without ...
0
votes
1answer
36 views

ORA-16706: Data Guard broker worker process not available

Database version is 12.1.0.1. We have one primary database and two physical standby database and dataguard also configured. Dataguard sync is fine working between primary and physical standby.(No lag)...
-1
votes
0answers
48 views

Oracle client install not working properly [closed]

I've been given a new laptop (yay!) but have to reinstall everything (boo!) I'm trying to setup the Oracle 32-bit full client on Windows 10 Enterprise. I'm installing 32-bit since that is what is ...
-1
votes
1answer
48 views

Get the average of unique city to city distances

I tried to obtain the desired result with the following query: Select city1, city2, Avg(distance) from table group by city1, city2; ...but this SQL statement is resulting ...
0
votes
1answer
27 views

Migrating triggers, functions and procedures from Oracle to PostgreSQL

We run a Java based application which uses Oracle as its backend. While they are rather simple and don't overly use Oracle specific functions, we have lots of triggers, function and procedures written ...
0
votes
1answer
27 views

Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared

I recently installed an Oracle 12c in Oracle Linux 7.6, it was previously in Windows. In the first database restored, some functions are with compilation errors. The query below informs you that ...
0
votes
1answer
30 views

Oracle DB union two select statements which return multiple rows

I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more than ...
1
vote
0answers
25 views

Cannot access remote table after parallel/insert direct load txn

This is the Oracle error message I'm getting ORA - 12840. We just upgraded from 10i to 12c version and some of the processes aren't working as they did before. I get this error when I execute a CREATE ...
1
vote
1answer
48 views

Why Logical reads are high

I have a table with 999 rows in 24 blocks.Ran a sql query for the first time which does a full table scan.From the execution statistics it shows 87 LIOS I'm confused why LIOS is high it's like Oracle ...
0
votes
1answer
67 views

ORA-27102: out of memory

We're facing an Out Memory Problem with an Oracle database SE 12c Release 1 (deployed as a Docker container) ... The only information that I have is this one: Caused by: java.sql....
0
votes
0answers
25 views

Oracle Forms 6i database compatibility

I have a forms & reports 6i running with a oracle 10g database. We're considering upgrading the database to 11gR2 or 12cR2, but I cannot find documentation providing database matrix compatibility ...
-2
votes
1answer
51 views

Can PostGIS or Oracle Spatial and Graph scale to hundreds of terabytes of data, or should I use a NoSQL option like DynamoDB? [closed]

I'm going to have a very large data set (a few hundred terabytes big eventually), but it is very well structured, relatively simple data involving lat, long points (which is why I want some GIS ...
0
votes
2answers
55 views

Select the oldest records in a table in Oracle [closed]

please, how can I select the oldest records in a table in oracle? and if it is possible to show them by date. Thank you
0
votes
0answers
22 views

Oracle database configuration failed [closed]

what should I do to complete installation ?
2
votes
0answers
33 views

Oracle impdp error when importing data

I get this error ORA-02289: sequence does not exist when I try to import a schema to my database, could you give me some recommendation? Import Command: impdp '"sys/mmm as sysdba"' directory=...
0
votes
0answers
35 views

fix oracle 11.2 asm performances (bad disks repartition)

i've a single Oracle 11.2 node with really bad performances and i figure out that there's a lun always running at 100% IO. This is the "DATA_0000" lun configured in asm in this way SQL> SELECT name, ...
1
vote
1answer
41 views

connect to Oracle Cloud from SQLPlus and Instant Client

I want to try to connect with SQLPlus to Oracle Cloud. for regular oracle I can connect from remote machine as follows: IP = 1.2.3.4 PORT = 1521 ServiceName = "ORCL1" Username = "username" Password ...
0
votes
1answer
42 views

What's the difference between aud$ and dba_audit_trail tables in Oracle database?

I'm studying about audit in Oracle database and when I configure the audit using audit_trail = db the Oracle stores in the two tables
1
vote
2answers
38 views

How to copy database schema in the same database for testing purposes?

I need to copy... by any means, a schema of my database in that same database, let's say, I have SC1 and I have to duplicate it as SC2 without the data contained in SC1 (in the same database). How ...
0
votes
1answer
42 views

utlrp.sql fails to execute

I just created a docker image of Oracle 11g ee following this tutorial https://vitalflux.com/how-to-install-oracle-11g-ee-on-docker/ and loaded some tables inside. The process worked fine and no ...
1
vote
1answer
56 views

Unlock user shell script for Oracle

We have so many user ID locks from the front-end users. We need to provide a shell script to the user which they can run on OS level and unlock the ID themselves when the DBA is not present. The sys ...
1
vote
1answer
60 views

Oracle 11G Clob column with where clause

I am using a query to get data from a CLOB column. I need to add a WHERE clause to it to only get the rows where Error = 0 from the CLOB column. The query I am using atm is: select lt.T_ID, lt....
1
vote
0answers
38 views

oracle slow query with diferent user

I have a question with an oracle query that takes almost 20 minutes when it is executed from a schema and yet when it runs as sys users it runs in 1 second maximum ... how is this possible? ... I do ...
0
votes
1answer
21 views

How can I search for a hex string in oracle?

There's a database record that is incorrect. The name field displays like this in a browser ( this is incorrect ): Thcˇodore And when I look at the record via SQL results, I see: Thc\xCB\x87odore ...
0
votes
0answers
35 views

Is it possible to implement automation for Oracle and DB2 with Jenkins and or Ansible?

I'm curious if anyone has successfully used Jenkins and/or Ansible for automating routine tasks for Oracle and DB2 databases. From what I've looked up online options seem very limited. So far ...
-1
votes
1answer
23 views

How to use mariadb instead of sqlplus using sqlnet configuration files

currently I can access a database on a remote server using Oracle's instant client "sqlplus" The connection is dependent on a couple of configuration files in: $ORACLE_HOME/network/admin which are ...
0
votes
1answer
17 views

$X Tables Do Not Exist?

In troubleshooting why a database application is working infinitely better on test than production, I ran across this phenomenon: I cannot query the X$ tables in production. SQL> desc x$ksppi ...
0
votes
1answer
20 views

error writing input to commandERROR: Invalid username and/or password

This is the error I have been getting on my RMAN notifcations on my backup jobs for the last day or so. The title is exactly how the message is formatted. ~~~End Step Output/Error Log~~~ Error Log:...
1
vote
0answers
32 views

Using conditions to return specific rows

From the following table i am trying to retrieve specific rows based on a criteria +----+--------------+------+ | ID | UPDATED_DATE | TYPE | +----+--------------+------+ | 1 | 01/13 | P | |...