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
1
vote
0answers
14 views

Oracle: Decreasing PCTFREE on large table

Question about Oracle Database Enterprise Edition version 19.10.0.0.0 running as a standalone instance. I have a table with billions of rows. The table has always had PCTFREE set to 10. It turns out ...
0
votes
1answer
20 views

Designing User & its Sub User Database Diagram Problem [duplicate]

There are some business category e.g. Travel Agency, Hotel, Car Rental etc. Roles: Management rolesSuper Admin, Staff Customer side roles are Owner, admin and sales counter May be etc. customer side ...
1
vote
3answers
44 views

Running Oracle's sqlldr on VirtualBox VM from my MacBook (client) machine

:) I'm not even CLOSE to a DBA, just an engineer that finds myself learning Oracle. Any help is much appreciated! Here's my question (which is a lot simpler than the sea of words which follow would ...
0
votes
0answers
20 views

Which blocking session is shown in Oracle 12c in V$SESSION, when wait event is “enq: TX - allocate ITL entry”?

We created a testcase on Oracle 12.1 as consequence of totally stucking application because of endless "enq: TX - allocate ITL entry" in a scenario with about 50 parallel processes. Each of ...
1
vote
0answers
10 views

ODI remove auto lock when object / datastore is opened

I was being stupid and enabled automatically lock object during edit / opened when I was asked the first time. Now I want to remove the automatically lock from GUI but I couldn't seem to find it. I ...
0
votes
0answers
20 views

Oracle 12cR2 - Upgrade Catalog Command Taking Too Much Time

I patched my database (Windows database bundle patch 12.2.0.1.201020 (31654782)). There are 6 instances on the server. After the patch, the UPGRADE CATALOG command I ran on RMAN for the first 5 ...
1
vote
2answers
44 views

Ramifications of Full SYSAUX - Oracle

What are the ramifications of having a full or almost nearly full SYSAUX tablespace (Oracle 19c)? There are plenty of sites discussing how to view the SYSAUX occupants, to reduce the size of SYSAUX ...
0
votes
0answers
39 views

Oracle 19c with SAP System - ORA-12154: TNS:could not resolve the connect identifier specified For Physical Standby

Oracle 19c + SAP system. On primary side I have a problem when transporting logs to Data Guard side. Outputs from primary side: SQL> select status, error from v$archive_dest where dest_id=2; ...
1
vote
1answer
25 views

Select all rows that have all of the following column values

I have the following tables Genres, Films, Directors. They have the following schema: CREATE TABLE GENRES( GID INTEGER PRIMARY KEY, GENRE VARCHAR(20) UNIQUE NOT NULL ); CREATE TABLE Films( ...
0
votes
1answer
21 views

How to check if a client can use windows authentication

My ORACLE (10g) sqlnet.ora has SQLNET.AUTHENTICATION_SERVICES=(NTS). I need to validate that no client is using Windows authentication to connect. What can I check next to make sure they don't ?
0
votes
1answer
44 views

Changing the grid listener port of an Oracle Database Appliance X8-2M

I need to add an extra Port: 1522 Listener Parameter File /u01/app/19.0.0.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/xxxx/listener/alert/log.xml Listening ...
1
vote
1answer
31 views

Getting the latest timestamp in Oracle

I am trying to create a report that has the latest member id in the first column and the old member id in the second column based on the timestamp in audits table below. Member ids are stored in ...
0
votes
1answer
18 views

SQL - Get the values of another column for values in a given column

I am working with a large data table. I want to check the individual items with their destination and the count of destinations if there are more than 1 destination for a given item. So in the below ...
0
votes
1answer
23 views

Restart ORACLE OHAS without restarting databases

looks like ohasd is getting to much memory (memory leak or something) that cause heavy swapping, is it possible to restart OHAS (till patch is applied) without bouncing databases? "crsctl stop ...
0
votes
1answer
25 views

User Permisssions White List vs Black List

I have Applications, Roles, Users, Permissions, Application_Permissions, User_Permissions, Role_Permissions, Application_Roles and Role_Permissions tables. So, permission can be defined at application ...
1
vote
0answers
46 views

How to improve Oracle Standard Edition's performance for testing? [closed]

There's a great post on StackOverflow about improving Postgres performance for testing. https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing/9407940#9407940 However, ...
1
vote
2answers
56 views

Query to find all dates between two specified dates using plsql

I have a table with below structure create table test_table ( customer_num NUMBER, card_number char(2), card_issue_date DATE, card_expire_date DATE ) Example data is: ...
0
votes
0answers
21 views

Create Oracle user with specific UID

Is there a way to create a user in Oracle 19c with a specific UID? We're going through a major upgrade and would like to preserve the UID's of our existing users.
5
votes
4answers
509 views

Database events but not triggers

This is a question regarding general DB inner workings, not particular to an implementation or paradigm, though answers for certain technologies are welcome. I am asking if there is a way to listen to ...
0
votes
2answers
28 views

Why does selecting an attribute of an Oracle object require an alias?

My GIS software has an Oracle 18c object type called ST_GEOMETRY. The object type has attributes: The attributes of the spatial type represent the following information: Entity: The type of ...
0
votes
1answer
16 views

Get nested procedure and sql execution oracle

In my Oracle 11g environment, I got performance issue with a procedure named : myProc(). In myProc() it's called to others proc named myChildProcA(), myChildProcB(),myChildProcC()......( ~10 child ...
0
votes
1answer
34 views

Oracle 11gR2 - Dynamic Registration

Database version: Oracle 11gR2 We want to use DYNAMIC_REGISTRATION_LISTENER parameter with off option. Our listener.ora file like this: LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (...
0
votes
1answer
18 views

Need help identifying how to make a function selectable using table() clause and a custom data type

I would like to be able to run the following code to select records: Desired Syntax select * from table(my_new_package.get_data(123)) I have been successful in creating code that compiles, but I am ...
0
votes
1answer
18 views

Exclude Supplemental Logs from Import

I am trying to import tables from a production environment, to a test environment. The production environment has supplemental logs on a number of tables for golden gate replication. The ...
0
votes
1answer
35 views

Error when using DBCA to create database Oracle XE 18c

I am trying to create a new database using dbca. Windows 10. Please help me to find solution. Here is the log
0
votes
1answer
23 views

drop datafile in RECOVER status

I have a datafile in a recover state: SQL> select status from v$datafile where file#=201; STATUS --------------------- RECOVER I'm not really sure how it was damaged, looks like something went ...
0
votes
1answer
31 views

How to set up Oracle 19c and/or RHEL so new files created using UTL_FILE have -rw-rw-r— (664) permissions on Linux

Working on getting a 19c installation working on RHEL 7.8. The database works, but we're having a problem with files created using the UTL_FILE package. They're being created with -rw-r--r-- (644) ...
2
votes
1answer
46 views

Replacing a specific string in oracle

I've a database value 'INS. Company Cancelled' where multiple values are separated by '.' I'm trying to replace 'INS' with 'INSEXP' my expected result is 'INSEXP. Company Cancelled' I've tried below ...
0
votes
0answers
40 views

How to block dedicated server requests in Oracle Database?

I have gone through some docs but no luck. Oracle database is always enabled to allow dedicated server processes. You can't control tnsnames.ora but you need to disallow clients to connect the ...
0
votes
1answer
34 views

Password column from sys.user$ is null in 18c, why?

Why the password column from sys.user$ is null? My version is 18c. I've made a test. SQL> alter user harry identified by 12345678; User altered. SQL> select name,password from sys.user$ where ...
0
votes
0answers
19 views

Oracle database, convert XMLTYPE table from basicfile lob to binary xml

Trying to import an Oracle EBS database (currently in a 19c staging database) into 19c Autonomous Data Warehouse, which won't accept XMLTYPE tables with "STORE AS BASICFILE LOB", but will ...
0
votes
0answers
20 views

Is it possible to select from procedure\function when the PDB is an argument without connect?

I have the following function in oracle 11g. Create or Replace function get_col_value( schema# in varchar2, table# in varchar2, column# in varchar2, rowid# in varchar2) Return varchar2 Is v_cmd ...
0
votes
1answer
34 views

Is it possible to give PDB name to a procedure and select from it?

The following example is not working, I just trying to show what I'm trying to do. Given table: -- this table is located at the CDB create table t1 (tab_name varchar2(35)) / -- this proc is located at ...
0
votes
0answers
25 views

Login oracle database with Microsoft Active Directory error

I have two machines, one (let's call it dbmachine) has the database and client installed of oracle 11g release 2, the other one is the domain controller (let's call it dcmachine) and has the client ...
0
votes
1answer
40 views

What process(s) writes to ALERT log file in Oracle?

Team: I have been searching for the process(s) which write to alert log file but couldn't find any reference to the documentation. I believe the background processes write to alert log. Not sure if it'...
1
vote
2answers
110 views

Oracle MV requires object type to be defined as FINAL?

I want to create an Oracle 18c materialized view (MV) on a table that has a user-defined datatype called ST_GEOMETRY: The MV would do a COMPLETE refresh on a schedule — on a local table. Some info ...
0
votes
1answer
27 views

How to select from pdbs with a cdb user?

I have several store procedures, which analyze oracle logs using log miner. These procedures run on CDB user C##ADMIN which created for this manner. Now I want to compare the logged values to the live ...
0
votes
2answers
34 views

The listener supports no services - 2 databases on oracle linux 7

I'm running two 12c R2 databases on linux oracle 7.9 I've installed the first database (CDB) and also a listener and both of them were running fine. Then, I've installed another 12c R2 database (CDB2) ...
3
votes
2answers
100 views

CUBE and calculating the number of dimensions

if a data presented with 4-dimension in which each dimension is dependent to hierarchical 3-level aggregate like (country, city, street), then we can summarize it into 4096 ways! we know for cube ...
0
votes
1answer
26 views

Why a job of dbms_job is BROKEN

I have Oracle job that uses dbms_job package. I see that it becomes BROKEN: Select job,what,BROKEN from dba_jobs; -------- ------------ ----------- 1 statspack.snap; Y I would like to know the ...
0
votes
1answer
21 views

UTL_HTTP call inside trigger leads to ORA-06502 when inserting from select

In order to allow a developer to make a SOAP call from an Oracle 11gR1 (11.1.0.7 PSU 24) instance using UTL_HTTP. He created a stored procedure : CREATE OR REPLACE PROCEDURE CALL_WS (URL IN VARCHAR2, ...
0
votes
0answers
31 views

Index not used in a remote query when values are supplied from a PL/SQL collection

I have a table with primary key column on a remote database: create table tab ( id primary key, pad ) nologging as select level id, lpad('x', 800) pad from dual connect by level <= 100000; If ...
0
votes
1answer
28 views

How to reset session context/variables in JDBC pooled connections - MySQL / Oracle

In my application I have multithreads that needs to access database and I am using apache.tomcat.jdbc.pool.DataSource as a JDBC connection pool. In some cases users execute stored procedures that ...
1
vote
0answers
26 views

Oracle Scheduler Job - Exporting of CSV File every 16:01

I have already executed the code for scheduler. However, I have a problem whenever I execute my scheduler. Can someone help me with this? I'll appreciate all your response. My procedure code: create ...
1
vote
2answers
39 views

Select failed from V$LOGMNR_CONTENTS when called from a procedure

Assuming the following user with these privileges granted: -- create admin user on CDB CREATE USER c##myadmin IDENTIFIED BY myadmin DEFAULT TABLESPACE system QUOTA UNLIMITED ON system ACCOUNT UNLOCK / ...
0
votes
1answer
25 views

How to quota a CDB only tablespace to a CDB user?

I can create a CDB tablespace like this: (all using: sqlplus / as sysdba) -- create unique table space for admin CREATE TABLESPACE admints DATAFILE '/path/to/admints.dbf' SIZE 20M AUTOEXTEND ON; I ...
2
votes
2answers
43 views

Select most occurrences of a varchar2 in Oracle

We need to group data in an Oracle database and must get a ProductNumber and a Description. The Description needs to be the one with the most occurrences inside the table. Both are a varchar2. See ...
0
votes
0answers
18 views

Oracle Enterprise Manager Cloud Control 13.4 - Package Deployment Stuck

We upgraded our Cloud Control from 13.3 to 13.4. I passed as well this step with "Simple Installation" choice, using SYSMAN. I have a user named as CCUSER1. When I want to skip this page ...
0
votes
1answer
29 views

“user does not exist ” when calling DBMS_LOGMNR.START_LOGMNR

I have a CDB user called "MYADMIN" and I'm trying to make it connect to log miner. -- enable calling admin username on CDB ALTER SESSION set "_ORACLE_SCRIPT"=true / -- create ...
2
votes
3answers
88 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 ...

1
2 3 4 5
142