Questions tagged [oracle-12c]

Oracle Database 12c. Please also tag oracle for search purposes.

Filter by
Sorted by
Tagged with
0 votes
0 answers
8 views

Migrate Oracle database to new server

I am looking to migrate an Oracle database with 2 instances to a new server. Here is some quick info Old server: SLES 12.4 with Oracle 12.1.0.2.0 Standard Edition New Server: SLES 15.3 with Oracle 19....
user avatar
  • 1
0 votes
0 answers
34 views

Oracle - How to Set Table and Index Statistics on empty table in order to test out explain plans?

I have an empty table with some indexes. I would like to test out different queries on this table to verify the explain plan is behaving as expected. Normally I would insert a large amount of fake ...
user avatar
0 votes
1 answer
20 views

How get role inside another role oracle

I am trying to make a script that allows me to get the roles that are inside another role of all users
user avatar
0 votes
1 answer
37 views

understanding oracle pluggable databases

I'm learning a little bit about Oracle Database. As I understand: In previous versions of Oracle DB (versions <= 11ng), each DB should be installed in a separate server. Is this statement true? ...
user avatar
  • 103
0 votes
1 answer
12 views

2 node RAC: gv$encryption_wallet has wallet in different locations?

Linux x86_64 Exadata X8, Oracle 12.1.0.2, 2-node RAC. I created a wallet on node 1. In $TNS_ADMIN, sqlnet.ora contains "wallet_location=/u01/oracle/correct_dir" Node 2, I spaced creating ...
user avatar
0 votes
1 answer
14 views

Oracle client 12c , if i can't find uninstallation file, can i install 19c client on same server?

I have a server that has oracle 12 client installed but now have an application that references the same server that needs oracle client 19c installed in order to work with the service. I cannot seem ...
user avatar
  • 1
0 votes
0 answers
10 views

ODI scheduled load plans

I recently inherited on a data warehouse which uses ODI to integrate data. The mechanism is using scheduled ODI load plans. The problem is that in the ODI environment we have thousands of load plans, ...
user avatar
  • 101
0 votes
1 answer
37 views

Oracle - Big Table Archiving

Version: Oracle 12cR2 EE The table contains the following column types: NUMBER, CLOB, NVARCHAR2, TIMESTAMP, VARCHAR2 Table size: 95GB Table row count: 20M Table partitioned? No Indexes on table: there ...
user avatar
0 votes
0 answers
102 views

Set 'WE8ISO8859P15' character set for existing Oracle 19c DB while migrating 12c DB

I have a database in 12c (12.2.0.1) which I need to migrate to an existing live oracle 19c DB.(This DB already hosts a couple of live DBs’s) Character set of DB’s are as below: 12c -> ...
user avatar
  • 1
-2 votes
1 answer
41 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(...
user avatar
0 votes
0 answers
45 views

Oracle CTE recursive query in package doesn't compile

I have a query which does work alone, but when put into package, package fails to compile with error: [2022-01-19 19:21:30] 58:12:PL/SQL: ORA-00600: internal error code, arguments: [qctcrcol:dty], [1],...
user avatar
  • 155
0 votes
0 answers
141 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). ...
user avatar
  • 11
0 votes
0 answers
30 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 ...
user avatar
  • 101
0 votes
1 answer
696 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 ...
user avatar
0 votes
0 answers
50 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 ...
user avatar
0 votes
0 answers
13 views

ORA-3136 but the client address is the dbserver itself

I keep observing ORA-3136 in the alert log of an Oracle database where the client ip corresponds to the dbserver itself. The port always changes and it's not the 1521. The customer is asking what ...
user avatar
0 votes
2 answers
40 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 ...
user avatar
0 votes
0 answers
49 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 ...
user avatar
  • 1
0 votes
1 answer
26 views

Oracle 12cR2 User Can Select Table Without Privilege

I noticed an interesting thing today. Consider a system where the production and test databases are equipped with the same privileges. In the production database, a user can select the table under ...
user avatar
0 votes
1 answer
50 views

Oracle Forms 12c key next item not running on execute query

I am running oracle forms 12c I have a client_id, client name, and contact number fields where if I type client_id it gets the clients name and contact number and those fields get populated but when I ...
user avatar
0 votes
2 answers
39 views

How to make a restricted view in oracle?

I had to provide data for an application schema, but there is a requirement that the applications connecting using that schema user cannot query more than five rows at a time, the application queries ...
user avatar
  • 99
0 votes
2 answers
66 views

Oracle 12cR2 RAC - ORA-06508

Environment: DB Version: Oracle Database 12cR2 EE on Exadata RAC: True Problem: ORA-06508 on procedure. One of our users had a DBA role privilege, we don't want such a big privilege to be defined ...
user avatar
0 votes
0 answers
14 views

Can I log connection IPs coming to Oracle listener from TCP clients?

I am looking for an option to log client IPs that come to Oracle listener without enabling oracle audit or creating trigger. Is there an option for this? Ps I am on a windows host, but I am willing to ...
user avatar
  • 2,006
0 votes
1 answer
80 views

Oracle 12c - Logon Trigger According To Machine Information

I want to create a logon trigger. Trigger will do the following: It will block access to the database based on certain users and certain machine information. Then it will give an information message ...
user avatar
1 vote
0 answers
32 views

Extremely slow updates to small Oracle table from rails app (oracle r12 and r19)

I'm not sure if this is a rails issue, or a database issue, but as this seems to be a schema specific issue, I'm looking at it from the database side first. (Other customers with the same setup on ...
user avatar
-2 votes
1 answer
199 views

Netca command not found Oracle 12c (silent mod)

i'm new on the Oracle's world, the installation it was easy and good, but , I dont know where is the problem here.
user avatar
1 vote
1 answer
48 views

EXPDP - why is block estimate significantly larger than segment size?

I'm exporting one specific table for a schema which I thought was a relatively small 5.4 GB SQL> select bytes/1024/1024 MB from user_segments where segment_name='TABLENAME'; MB ---------- ...
user avatar
  • 137
0 votes
0 answers
281 views

Convert VARCHAR2 to UTF-8 in Oracle 12

I need to convert VARCHAR2 columns that store text in unkown character sets into a standard UTF-8 BLOB. The table has more columns, but the ones of interest to me are: ADDRESS POST ---------- ----...
user avatar
0 votes
1 answer
20 views

Producing JSON in Oracle 12c1

I need to produce a JSON document with several properties from a list of Oracle 12c1 production databases, and I was given read-only access in order to generate this data. The problem is these ...
user avatar
0 votes
0 answers
78 views

Export DataPump - ORA-01555 Snapshot too old

I'm trying to export a customer's production database (schema) for problem solving. During export, I'm getting the following: Estimate in progress using BLOCKS method... Processing object type ...
user avatar
  • 137
0 votes
1 answer
39 views

Collection with additional column to %ROWTYPE columns (two-way MINUS comparing two data sets)

I am looking for a way to create collection that would have one additional column that the source table/view columns referred by %ROWTYPE for simplicity. TYPE my_type IS RECORD ( mode varchar2(10), ...
user avatar
  • 155
1 vote
2 answers
136 views

Oracle simple count(*) SQL query takes "forever"

I'm encountering an "issue" (at least a strange/slow behavior) with a new installation of Oracle 12c on Windows Server 2019, with this query which takes at least 5 minutes to complete: ...
user avatar
  • 111
0 votes
2 answers
40 views

What do I need to put in pfile to change NLS_CHARACTERSET?

Im not an expert in databases. I am using docker Oracle 12c database for local dev environment. Its an official image from Oracle but they dont give any option to change the NLS_CHARARACTERSET during ...
user avatar
  • 127
0 votes
1 answer
66 views

How can i disable opatch job?

I have a docker 12.2 oracle db for localhoat dev. I don't care if it's patched regularly. I'm seeing constant errors In qopatch queries. I think its triggered by some batch job at startup and maybe at ...
user avatar
  • 127
0 votes
1 answer
32 views

Different response time because it does not use the indexes created with the same query and same tables from the web server to the Oracle database

I have a database with Oracle 12c (12.1), where when making a query locally and even from TOAD or another remote tool, it returns the response in milliseconds, reviewing the execution plan I can see ...
user avatar
-3 votes
1 answer
30 views

I tried running this script, but i kept getting not a group by error [duplicate]

select a.*, b.Start_Date, b.End_date, sum(b.cost) as total_Cost from Ess.TZeewrld a left join Ess.Wrldest b on a.account_number, = b.account_number, group by b.Start_Date, b.End_date;
user avatar
0 votes
0 answers
18 views

Query xml multinode syntax

I have a table containing an XMLTYPE column that I have to parse and display contained values one per line: Example 1 : <c2>VLAN1</c2><c2 m="2">VLAN2</c2><c2 m=&...
user avatar
  • 1
0 votes
1 answer
19 views

Can't the Password File In Oracle 12.2 C

I intend to to make password file, spfile and control files as part of my backup script, but i however i have been trying to get find the password file in the $ORACLE_HOME/dbs location but its not ...
user avatar
  • 19
0 votes
0 answers
28 views

SQL query to fetch data between 2 date columns from 2 different tables

I am trying to fetch data from two different tables which contain two date columns: ENCODINGDATETIME and CREATEDON. I need those records where days gap between the ENCODINGDATETIME and CREATEDON ...
user avatar
0 votes
1 answer
25 views

Combining conditional copies and literals in an INSERT INTO statement

I have a table with a primary key and two foreign keys. I am trying to copy data from the source of one of the foreign keys conditionally, and use a literal for the other foreign key, somewhat like ...
user avatar
  • 101
0 votes
2 answers
40 views

How to Escape dot(.) In a substitution variable

I have a variable called ERROR_DETAIL and inside my file file.sql I have this line: SELECT 'error detail : &ERROR_DETAIL ' FROM DUAL; Once executed sqlplus complains with the following error ...
user avatar
  • 11
0 votes
2 answers
200 views

How can I configure Automatic Memory Management for certain target values?

I am a little confused about the Oracle Database configuration. My server: Operating system: Windows Server 2012 R2 64bit RAM: 40GB Oracle: Oracle Database 12c (12.1.0.2.0) 64bit My aim: Oracle ...
user avatar
  • 11
0 votes
1 answer
177 views

Regex replace to ignore certain pattern at the beginning if exists and format the rest

How to use regex replace to ignore country code if it exists, I tried to use select REGEXP_REPLACE(phone_number,'^/(?:011.44.)([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})$', '\1-\2-\3') ...
user avatar
  • 113
-1 votes
2 answers
138 views

How do i recover oracle sys password for oracle 12c when login as sqlplus / as sysdba

I have got a DB Oracle 12c R2 and i am able to login as: $sqlplus / as sysdba But however my problem is that i dont even know what is the sys password used there just in case I need to do db recovery. ...
user avatar
  • 19
1 vote
1 answer
47 views

Oracle gather stats autotask being interrupted before maintenance window ends

a few months ago I managed to enlarge the maintenance windows for autotask on a pluggable database, this way: BEGIN dbms_scheduler.disable( name => 'WINDOW_NAME'); dbms_scheduler....
user avatar
3 votes
2 answers
212 views

Getting "ORA-04030: out of process memory" on medium insert with large pga and sga

Running on Oracle 12.1.0.2 AIX Power9 (yes, I know it's 2021, but that's the customer I've got) Executing a plsql block that includes 1000 inserts as plain text and commit in the end. begin insert ...
user avatar
  • 141
0 votes
0 answers
239 views

Problem whit instalatión of GridInsfrastructure DNS/NIS name service

When trying to install the Grid Infrastructure 12, I have the following problem: This is the description of error: DNS/NIS name service - This test verifies that the Name Service lookups for the ...
user avatar
1 vote
1 answer
94 views

To trim particular value in a column in Oracle sql

I have a regular expression select statement like below : SELECT REGEXP_REPLACE(nvl(l.text_1, l.text),'^'||21810||'=|\|' ||21810||'=', '|'||21810||'='||'B1') ...
user avatar
0 votes
1 answer
78 views

Oracle database 12C restore script

i'm trying to create backup restore script . in short my backup script creating new directory every day from the operating system level and then backup the database using the directory created i tried ...
user avatar
-1 votes
1 answer
50 views

Oracle Execution Plan - Outline Question

I'm spending the last few days trying to learn some tips/tricks on tuning an Oracle query being run in Crystal Report. Obviously it has some performance issues. I suspect there are some deficiencies ...
user avatar
  • 147

1
2 3 4 5
19