Questions tagged [oracle-12c]
Oracle Database 12c. Please also tag oracle for search purposes.
939
questions
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....
- 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 ...
- 155
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
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? ...
- 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 ...
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 ...
- 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, ...
- 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 ...
- 29
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 -> ...
- 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(...
- 9
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],...
- 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).
...
- 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 ...
- 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
...
- 29
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 ...
- 1
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 ...
- 19
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 ...
- 29
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 ...
- 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 ...
- 29
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 ...
- 101
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 ...
- 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 ...
- 29
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 ...
- 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
...
- 29
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 ...
- 137
-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.
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
----------
...
- 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
---------- ----...
- 101
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 ...
- 101
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 ...
- 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), ...
- 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:
...
- 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 ...
- 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 ...
- 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 ...
- 125
-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;
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=&...
- 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 ...
- 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 ...
- 11
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 ...
- 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 ...
- 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 ...
- 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') ...
- 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. ...
- 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....
- 19
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 ...
- 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 ...
- 13
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')
...
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 ...
- 11
-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 ...
- 147