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
0answers
11 views

ORA-00997: illegal use of LONG datatype error when query from oracle to mysql over dblink

I created a db link from Oracle (19c) to MySQL (8.0). My MySQL table's columns datatypes are text (I mustn't change the datatype). There are 126 columns in the table (It must be such). When I try to ...
0
votes
1answer
12 views

Cannot recover Pluggable Database

hello i new to rman and im trying to recover A pluggable database with "recover pluggable database " before i issue a recover pluggable database command i do these following steps: 1.backup ...
0
votes
1answer
14 views

what does the number in the "A_ROWS" column of the execution plan shows for operation "Index scan"?

I have a query as you can see below : select /*+gather_plan_statistics*/ * from mi_dimcustomer t where t.CUSTOMER_NUM = 321937 There's a Unique Index (IDX1_DIMCUSTOMER) on Customer_Num column of ...
-1
votes
1answer
36 views

Which sql statement is more efficient (Oracle SQL)?

Create a query to get the customer with the most numbers of orders - am wondering which one is a more efficient query? SELECT c.customer_id, c.first_name || ' ' || c.last_name as Name, count(...
0
votes
0answers
12 views

Issue with configuring Oracle 21 XE DB after removing Oracle 18 XE DB

I had installed Oracle 18 XE DB in my linux machine. Now I have uninstalled it and installed Oracle 21 XE DB in the same machine. The issue is when I run the below command to configure Oracle 21XE DB, ...
-4
votes
0answers
18 views

Oracle XE 21c startup not working linux

SQL> startup ORA-01506: missing or illegal database name
0
votes
1answer
13 views

RMAN Duplicate with new SID / Path

We are running Oracle 12c SE. I need to duplicate an instance from the RMAN backups. However, this instance will be on a different server, with a different SID. And, the pathnames have the SID in ...
0
votes
1answer
25 views

Can I convert a local user to a common user in Oracle?

I have a local user in a pluggable database and it has data, objects etc. under its schema. Can I easily convert this user to a common user without losing its objects etc.? (Oracle Database 19c)
-2
votes
1answer
45 views

LEFT JOIN Performance Issue

I have a view, which has a column that comes from query Without this query my count(*) against that view executes in less than 2 seconds, whereas it takes 2 days for the view with above extra column ...
0
votes
1answer
24 views

Adding new datafile to tablespace but no datafile being created

I recently faced the following error: ORA-01688: unable to extend table RATEL.MV_IN_GPRS_SPLIT partition SYS_P54817 by 128 in tablespace RATEL" while loading data into the database. To resolve ...
-2
votes
0answers
15 views

Oracle 11gR2 Single Instance with Grid Inf. - Change Server IP

Server: Oracle Linux Server 6.10 DB Version: 11.2.0.4.0 with Grid Inf. Database Files: ASM srvctl stop database -d db_unique_name (as oracle) srvctl stop listener (as oracle) .$GRID_HOME/bin/crsctl ...
0
votes
0answers
55 views

ORA-12152: TNS: unable to send break message - Only LISTENER restart works

i have a strange problem with our Oracle 12.1.0.1.0 on our VM Windows Server 2016. I create a daily batch script whichs creates a dump file (via EXP) and execute some sql statements (via SQLPlus). ...
0
votes
1answer
29 views

which process has start my db?

I have to restart a database, but it seems, it has not been start by the good process: ps -ef | grep smon | grep mydb oracle 54621 1 0 2021 ? 00:10:55 ora_smon_mydb . oraenv ORACLE_SID = [oracle] ...
0
votes
2answers
50 views

Oracle SQL missing keyword

Recently started with Oracle sql and I really can't understand what I'm doing wrong Select phones From employees left join (SELECT Employees.manager_id ,COUNT(employees.employee_id) From employees ...
0
votes
1answer
27 views

Oracle date or timestamp PARTITION columns

Is there a query I can issue to tell if a PARTITION column is a date or timestamp. For example this should retun timestamp CREATE TABLE ts ( num_val NUMBER(6), ts_val TIMESTAMP) PARTITION BY RANGE (...
0
votes
0answers
21 views

Why database link cant fetch all rows from view?

recently I moved to use oracle 12c instead of 11g using oracle Linux server , I have a DBLink that select data from another oracle 11g view , when I select * from using the link a number of rows are ...
0
votes
1answer
46 views

Three "Nested loop join" in the Execution plan despite using two inner joins in the main query

I have a query as you can see below : with cte as (select customer_num from vmi_segment_customer_relation where effective_date = to_date('12/30/2021', 'mm/dd/yyyy') and segment_id = ...
0
votes
0answers
13 views

Oracle Incrementally Updated Backups - does "recover copy of database" makes all datafiles consistent?

I would like to use Oracle Incrementally Updated Backups on 19c. Using this script to achieve it: RUN { BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'mybackup' DATABASE; RECOVER COPY OF ...
0
votes
1answer
56 views

Oracle 12c to 19c Internal Schema Export & Import

I want to exp & imp operations for APEX users from Oracle Database 12cR1 to 19c. Data Pump Utility can't do that. When I try to export by using following parfile and command: DUMPFILE=APEX.dmp ...
0
votes
0answers
47 views

Can an Oracle Database request a system crash (One Instance Crash)?

I am Junior Database Administrator at a Bank and there was an issue that caused the Oracle Database Instance one to crash (We have two instances). So the system rebooted itself and everything is ...
0
votes
0answers
20 views

Reimplement wm_concat as a user defined aggregate

We have an application running on Oracle 11 that is using wm_concat. We want to migrate it to Oracle 19. As wm_concat is no longer provided in Oracle 19, we considered rewriting all queries using it, ...
0
votes
1answer
52 views

Getting warnings while generating query Execution plan in Oracle , PLSQL developer

These are the steps I follow to generate a real Execution Plan in PLSQL developer : 1)Using this hint in my main query /+gather_plan_statistics/ select /*+gather_plan_statistics*/ from mi_factcustomer ...
2
votes
1answer
86 views

Return a resultset as JSON text—using pretty formatting

In Oracle 21c, it looks like we can return a resultset as JSON text: SELECT json_object(*) from dual; result: {"DUMMY":"X"} That works in db<>fiddle: Question: Is there a ...
0
votes
1answer
35 views

Do we need additional license to dump goldengate trail logs to kafka?

We have a Oracle (12c) GoldenGate license only (we do not have an oracle goldengate for big data). We need to transfer the gg trail logs to kafka then to hdfs. Do we need to buy the oracle goldengate ...
0
votes
0answers
58 views

RMAN-08138: WARNING: archived log not deleted - must create more backups

I have a primary oracle database and a logical standby. Primary database information BANNER CON_ID ------------------------...
-1
votes
1answer
36 views

Example tablespcae in Oracle 11g

I need to know why example table space is created by Oracle 11g ? Does it effects the performance if it is full ? if yes how can we stop it being used ? Thanks
0
votes
0answers
24 views

How to load Java in Oracle Database using SQL Developer?

I'm running Oracle 19. I would like to load some Java classes using SQL Developer, but the option is greyed out. Is there any necessary grants to do that? Or is there any other way to do it via SQL ...
0
votes
0answers
77 views

Oracle 19c Installer hangs with endless Reading from the pipe output

I am attemping to install Oracle 19c on Windows 10 64 bit. No Oracle products have ever been installed on this machine. When I run setup.exe the cmd window launches, then the splash screen and then ...
-5
votes
0answers
46 views

Please someone help me to draw this ER DIAGRAM

How to draw an ER diagram . I don't understand that library card part.? Need answers as soon as possible 🥺
1
vote
1answer
51 views

Recursive update parent and children

I have a single table 'category' and once I want to disable a category, I have to check if it´s a parent or a child. If parent the parent_id is null. If not it has a respective parent_id meaning it´s ...
0
votes
0answers
20 views

How to keep two database environment tables in sync by creating scripts using procedure

I have one table in the DEV environment and the same table was in SIT environment too. But I have added a few columns in the DEV environment table that I need to sync in SIT environment also. So, I am ...
0
votes
0answers
48 views

Oracle startup method [closed]

Can you please tell me how to determine how the database was started? With SRVCTL or SQL * Plus? When the database was started by SQL * Plus, after restarting the server it does not automatically turn ...
0
votes
2answers
24 views

Oracle parent child move failed

I have two tables, a parent and child table, which have referential integrity declared between two tables and try to move both the tables so I can delete rows. I start with the child table and the ...
0
votes
0answers
16 views

Oracle Index Clustering Factor

My question is about Clustering Factor (CF) of Indexes in oracle I have created a table as follows : Create table T2 as select c1,c2,c3,c4,c5,c6 from T1 order by c1,c2,c3,c4,c5,c6; Create Index IDX ...
0
votes
2answers
28 views

Oracle 12cR2 - After Logon Exception

Logon trigger as below: create or replace TRIGGER USER3.MY_TRIGGER AFTER LOGON ON DATABASE BEGIN IF SYS_CONTEXT ('USERENV', 'SESSION_USER') IN ('USER30') THEN IF SYS_CONTEXT ('USERENV', 'HOST') NOT IN ...
0
votes
0answers
40 views

Optimize the query to dba_free_space

In my query for check the free space of data file, I need to join with dba_free_space but it was too slow. I try to see what in dba_free_space: just 1200 records but need 11 mins to execute. Some ...
0
votes
1answer
37 views

What The Equivalent Of Oracle Schema Isolation In AzureSQL And PostgreSQL?

The database choices for my new application are Azure SQL and PostgreSQL. It is SaaS and each client will need to be isolated from each other for security reasons. This has to be done at the ...
0
votes
0answers
15 views

NO delete from old backup

Does anyone have any idea why my old backups are not deleted although I have correctly configured the backup retention for 1 day thank you for your help
0
votes
0answers
27 views

Oracle 11g Windows lost SYS password

I inherited an old Oracle DB and the previous IT person did not pass along the SYS password. It is Release 11.2.0.1.0 on Windows 2016. My OS user is in the ora_dba group but I am still unable to ...
0
votes
0answers
26 views

How to determine the host cpu utilization of an Oracle PDB

In a "non-containerized" Oracle Database I can use the following statement to determine the host cpu utilisation: SELECT value FROM v$sysmetric WHERE metric_name = 'host cpu utilization (%)...
0
votes
1answer
32 views

How to create a custom database with 32k block size via DBCA silent mode?

I am trying to create an Oracle 19c single instance (warehouse) with dbca silent mode. The problem is, when I try the command below, it gets a warning: "Ora-0058: DB_BLOCK_SIZE must be 8192 to ...
0
votes
1answer
38 views

Oracle SQL query: Best way to remove unwanted characters?

I want to remove all characters that are neither underscore, hyphen or alpha-numeric. Additionally, I don't want underscore or hyphen as the first character, so that needs to be removed as well. This ...
2
votes
1answer
33 views

How to list backups when not connected to target DB in Oracle RMAN

I'm trying to list backups in RMAN completed within a certain date range. This works fine when I can connect to the target database and recovery catalog. However, I'm assuming a situation where I am ...
0
votes
0answers
14 views

TLS Encryption Logging/Auditing in Oracle

I use Oracle only occasionally, but have heard that it is pretty easy to verify if your Oracle encryption is working (e.g. this post Verify if the client is connecting to the Oracle database using ...
0
votes
1answer
15 views

Drop view from package

I have a user called DEPLOY defined with DBA role. When logged on via SQL Developer the user can drop a view as follows: DROP VIEW APP_USER.VIEW_NAME_VW CASCADE CONSTRAINTS If I put the same ...
0
votes
0answers
23 views

DBA role is not applied

This worked yesterday, but now it won't. We've defined a user 'DEPLOY' on 'Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0' which will be used for deployments into each environment - DEV, ...
-1
votes
1answer
45 views

Execute Query only if a Oracle DB Table Exists

Hi Database Experts, Could anyone of you please suggest how I go about executing the following SQL Query against a Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 Database. I am running this ...
0
votes
1answer
29 views

Oracle generating hash_val from a clob

I have the following table, where I would like the value of hash_val to be generated from clob_val whether clob_val is inserted or updated. Can this be done perhaps by making the column hash_val a ...
0
votes
0answers
11 views

SQL code Compatibility: Migrating an Oracle RDS database to an Athena Trino Datalake

We are thinking of migrating an Oracle RDS database to Athena Trino Datalake. We would keep all database names, schemas, tables, and columns the same. Would SQL that runs correctly against the Oracle ...
0
votes
0answers
20 views

Oracle - one port for native network encryption traffic and one for non-encrypted

I need to expose my oracle database to a remote server, and want to require encrypted traffic for that machine (while not affecting my existing connections.) My thought is to use one port for ...

1
2 3 4 5
149