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.

Filter by
Sorted by
Tagged with
0
votes
1answer
347 views

How to convert the JSON date with to_date function

How to convert the specific string Aug 6, 2018 3:17:11 PM with the function to_date? That string is a result of a Java parsing mechanism for JSON. I would like to be able to recover it directly from ...
0
votes
1answer
35 views

How can I get all data from a given table since my last run?

In querying a table in Oracle Database, is there any way to reliably and precisely get all rows that have changed (via inserts or updates) since the last time I checked? My tables typically have a ...
0
votes
1answer
118 views

Copy DBF files from production to development environment

I'm not an expert. I'm a Software Developer but got trapped in doing this DBA work. We have a development environment and we would like to copy the DBF files from the production environment to the ...
0
votes
1answer
417 views

Calculate Count Distinct without grouped columns in Oracle

I am trying to extract a few records and I ran into the "not a single group" error. This error can be resolved by removing the (count(distinct dc.DUCT_FID)) as NO_OF_WAYS column expression. However, I ...
3
votes
2answers
112 views

Causing ORA errors in a program using JDBC. How can I find what's causing them?

So I'm building a java app to interface with the company's database. Whenever it causes an ORA error, it just prints something like the following to system output: ORA-00911: invalid character Is ...
1
vote
1answer
468 views

Oracle Wallet Connection setup for SSIS

How to configure SSIS package to connect to oracle using oracle wallet? Specifically for using OLEDB to connect to oracle and load data to oracle tables.
0
votes
1answer
30 views

“ORA-03146: invalid buffer length for TTC field” after upgrade from Oracle 12 to 19c

We are experiencing problems with updating a table record BLOB column. We get the "ORA-03146: invalid buffer length for TTC field" error. We basically are saving e.g. PDF attachments to the ...
0
votes
1answer
129 views

SAP BO Freehand SQL date formatting (Oracle db)

My original query worked perfectly via SQL Developer: select * from [table] where DT_CURRENT_DT = '15-OCT-18' However when ran via freehand SQL in SAP BO I got no results. After running the query ...
0
votes
0answers
12 views

Why does json_equal use so much PGA memory and how to avoid it?

I have an Oracle 19c database and a schema with two tables (let's call them L and R). They have the same structure: Two VARCHAR2 columns I'll call T and J. The second one has an IS JSON STRICT ...
0
votes
1answer
39 views

Displaying number before decimal points in oracle

I have a logic where I want to calculate and display numbers based upon some operations. The operations are below V_CALPERCENT nvarhcar(100), v_yearMSA1 nvarchar(100), So I want to calculate ...
2
votes
2answers
4k views

Writing Oracle BLOB field to raw file in a Cursor or For loop result empty file

I am stuck at this for a while and had been Googling for answer but can't seem to find any.... I have an Oracle 9i table with BLOB field that I need to extract the BLOB field into a raw file on the ...
3
votes
1answer
4k views

sqlplus is not starting

I have a strange error on a Red Hat machine. InstantClient is installed in and ORACLE_HOME is set to /opt/oracle/product/112030_cl_64/cl/ The user which installed the client was oracle:oinstall. My ...
0
votes
1answer
93 views

Oracle 12cR2 / ORA-04031 - Unable to allocate xx bytes of shared memory (Shared Pool) (Hard Parse & Parallel Queries)

Sometimes I'm getting ORA-04031 error on my DWH database. The last one like this: "ORA-04031: unable to allocate 56 bytes of shared memory ("shared pool", "unknown object", &...
0
votes
1answer
104 views

Oracle JSON ADT in Collection

Why is the table() operator not working? create or replace type tmp_011040_t2 as table of (JSON_OBJECT_T) NOT PERSISTABLE; declare l_objects tmp_011040_t2; begin l_objects := tmp_011040_t2(); ...
11
votes
7answers
24k views

“ORA-24454: client host name is not set” when connecting from Ubuntu instance on AWS EC2

Some background: I am attempting to connect to an Oracle database. I have a username, password, IP address, port, and service name. I only have access to this database from an Ubuntu EC2 instance on ...
0
votes
1answer
19 views

how to get the difference between 2 tables in 2 different schema's in Oracle? [duplicate]

I am using Oracle 11G. I have a table called Tickets in the schema called 'tickets1" I have the same table structure in another schema called 'tickets2' I want to find out What data is the same ...
2
votes
1answer
9k views

How to reduce sysaux tablespace size with Audit Trail Unified enabled

I have Oracle 12c Standard instance with dev database with enabled Audit Trail Unified. And now SYSAUX tablespace has size 32Gb, but all data is only 9Gb. When I tried to reduce size of SYSAUX I ...
0
votes
2answers
1k views

Pass multiples values to script into sqlplus in Oracle

I want to execute the same sql script passing one text every time but I don't know how to it. I want to do it into sqlplus. With this select I have the values but I don't know how to pass this ...
0
votes
2answers
1k views

Cloning the database to another server with raw data files

I'm trying to clone the database of server 1 to server 2 with raw data files. I'm using Linux 7 and Oracle 11g R2. I have the instance running with same SID in Server 2 but file structure is different....
0
votes
1answer
31 views

Data deleted with sqlplus still showing up in SQL Developer

I wrote a script to run with SQLPlus which deletes the previous seven days of data in my Oracle XE database. I logged into the database with SQLPlus, ran the script, and it showed me the correct rows ...
0
votes
1answer
78 views

Oracle 11gR2 can I delete datafiles to reduce tablespace?

I have the following doubt, maybe a little silly, if I have a tablespace for example TBS_MYDATA of 155 GB, this tablespace is composed of 5 datafiles of 31GB each, in turn, each of these datafiles ...
3
votes
1answer
45 views

Ordering CONNECT BY siblings

I have an hierarchical query that uses CONNECT BY: select wonum, parent, classification, classstructureid, division, worktype, status, glaccount, fircode, ...
0
votes
1answer
2k views

Oracle 11 IMPDP Table Exists but gets Does Not Exist Error

I'm running in to a weird problem with IMPDP and Oracle DB version 11.2.0.4.0 where I have a table in the destination schema that does indeed exist, but IMPDP thinks it doesn't. See this entry from ...
0
votes
1answer
41 views

Does ' /*+parallel(<Specific Table >,4)*/ ' have better impact on query performance in comparison with simple parallel hint '/*+parallel(4)*/'?

I have three tables with below structure: create table customer_info (customer_num number, customer_firstName varchar2(100), customer_lastName varchar2(100), branch_code varchar2(...
1
vote
1answer
28 views

How to convince Oracle to create optimal execution plans in SQL queries with bind variables?

An attempt to parameterize a query in Oracle using bind variables and OR :B1 IS NULL or NVL(:B1,col1) in a WHERE clause leads to suboptimal execution plans. For example, I'd like to write SELECT like ...
1
vote
1answer
19 views

How to enable INMEMORY JOIN GROUP in Oracle 12.2

I have read official Oracle documentation on INMEMORY JOIN GROUP and run code from their tutorials. However I unable to successfully create INMEMORY JOIN GROUP. Installed database is Oracle 12.2 ...
-1
votes
1answer
77 views

Oracle 11gR2 - Export - Import (expdp, impdp) How To Exclude A Table Partiton And This Table's Indexes

I have a table partition (135GB) and index partition on this table (total=150GB) and I want to exclude owner schema of this table and indexes with excluding this table partition and it's indexes. What ...
1
vote
1answer
23 views

Is it possible to duplicate a standby database on the fly?

Can I duplicate a standby database on the fly? Currently, I'm getting this error: RMAN-03002: failure of Duplicate Db command at 01/08/2021 01:55:58 RMAN-05501: aborting duplication of target ...
0
votes
1answer
28 views

RMAN windows client

can you please explain me - when I run : rman target sys/<sys_PWD>@SRC_DB auxiliary sys/<sys_pwd>@TRGT_DB from MY WINDOWS PC - will it copy data from SRC_DB to TRGT_DB through MY WINDOWS ...
8
votes
1answer
915 views

Oracle Ref Partitioning: Deadlock due to child table row migration

My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc. ORDERS table is LIST partitioned on STATUS column and all ...
0
votes
2answers
283 views

Connection denied from Java application

I try to connect from my little Java class to Oracle11g database and connection was refused with a SQL error. From client (SQL Developer) connection works. This is the ERROR when I try to run java ...
19
votes
3answers
18k views

What does NVL stand for?

What does NVL stand for? I'm talking about the Oracle and Informix (perhaps some others too) function used to filter out non NULL values from query results (similar to COALESCE in other databases).
2
votes
0answers
55 views

How to configure partial replication of Oracle DB

Suppose we have 2 identical Oracle 11g DB schemas: PRODUCTION and ARCHIVE. The goal is to keep in PRODUCTION the minimal amount of data required for online operations, and use ARCHIVE to store all the ...
0
votes
2answers
816 views

SQLplus run without admin account [closed]

I have a weird problem. I login to a pc using non admin user. Then I installed sqlclient 64 bit for oracle 11.2. Installation went fine, no errors. Then i tried to login using sqlplus from command ...
1
vote
4answers
3k views

Query to find users with passwords same as their username in Oracle

I'm writing an Oracle audit script where I need to find users in a database who have set their passwords to be the same as their username, e.g. dbaadmin/dbaadmin. The query would need to get the list ...
0
votes
0answers
14 views

How to reset failures count to zero when using dbms_jobs?

Is there some way to reset the failure count column to 0 in dba_jobs view? Sometimes a job fail and after fix it I need to wait the next scheduled execution, during this time I'd like to reset to 0 to ...
-1
votes
1answer
29 views

Oracle: Cannot connect to the database using DbForge or any other client, but can use SQL*Plus when I SSH [closed]

I am unable to connect to my DB using a client like Toad or DbForge but I am able to use the database when I SSH into the server and do SQL*Plus Do I need to enable something like a service or ...
4
votes
1answer
2k views

How do I identify the remote db agent name to use in create_database_destination on Oracle 11gR2?

I am trying to setup DBMS_SCHEDULER in Oracle 11g to run a remote database job. I have a remote Oracle 11g R2 database on unix and a local one on Windows. I read that you can install the oracle ...
1
vote
0answers
48 views

SQL*Plus fail instead of prompting for substitution variable

Imagine a file test.sql with the following content SELECT '&myvar' AS greeting FROM dual; When we run that file in sqlplus. We will be prompted for the value of the variable &myvar since it's ...
2
votes
1answer
504 views

Oracle 12c Database Config Manager very slow at creating a new database

I have installed Oracle 12c on a number of machines (Windows 10, 2012, 2016 mostly) before and created several databases on them, using the Database Config Manager, all with no real hiccups. However ...
1
vote
1answer
1k views

Query on SCAN in RAC

I have a two node RAC (Oracle 12c) on linux. I have 5 SCAN listeners configured. Currently, if I do a srvctl config scan I notice that out of the 5 scan listeners, one scan is running in one node ...
3
votes
2answers
73 views

SQL SERVER equivalent for Oracle DBA_USERS

I'm working on conversion of Oracle queries to MSSQL queries. Following is query for Oracle - SELECT USERNAME FROM DBA_USERS WHERE USERNAME = 'TESTUSER'; But in MSSQL, DBA_USERS is invalid object is ...
2
votes
1answer
2k views

Unable to get output variable of Oracle procedure using Sql linked server

I have created a Heterogeneous connection between SQL Server and Oracle and tried to call a oracle procedure from SQL Server.I got below error when i tried to execute the procedure. OLE DB provider ...
0
votes
3answers
3k views

How can we get the table and the column name of known data value?

I have a value (like 'IT department'), based on this I want to get the table and column name in which this value exists/present.
0
votes
2answers
905 views

Select Column Values from all the tables of database ORACLE

I am trying to fetch column value from all the tables of database where column value matches. Table A CAMPUS_ID 1 Table B CAMPUS_ID 1 Expected Result TABLE VALUE A 1 TABLE VALUE B ...
0
votes
1answer
61 views

How can I use COUNT in the same query with ROUND() and AVG()?

I need to write a query where I need to first count the tickets sold for spectacles in a theater, then calculate the average number of tickets sold in each theater and to be round it to two decimal ...
0
votes
1answer
32 views

Explicit usernames in references to objects

Much of our code -- both in external programs and in stored procedures -- uses explicit usernames, when referring to various DB-objects: SELECT * FROM prod.object or prod.StoredProcedure(.....) This ...
0
votes
0answers
27 views

Oracle writes to SSD drive are really slow

My customer recently ported their database to a new server that has an SSD drive. This new server uses Windows 2016 Server OS, and the SSD drive model is a DELL PERC H740P Mini The good news is the ...
0
votes
1answer
723 views

Using JOINS in QUERY clause in EXPDP

I have a par file that is using JOINS in QUERY clause. But it is not working. directory=expdp DUMPFILE=Test_SIT_Query_1table_%U.dmp encryption_password=oracle parallel=8 logfile=TEST_SIT_Query_table....
0
votes
1answer
58 views

Scripts for setting the environment for Oracle Database Multitenant

When working on shared database servers with several Oracle Container Databases installed in different homes and different versions it can be hard to find the pdb you are looking for quickly. We used ...

1
2 3 4 5
141