Questions tagged [oracle-12c]
Oracle Database 12c. Please also tag oracle for search purposes.
951
questions
-1
votes
0
answers
8
views
How would I create a sql query that measures the length of both the last name & first name in a table and then groups the count of the records?
I want to know the count of employee records by length of last_name (aliased len_last) and length of the first_name (aliased len_first) in a specific table. But, only show those records where the ...
1
vote
0
answers
18
views
Is it safe to sync Oracle 19c to 12c via dblink?
Source database: Oracle 19c
Target database: Oracle 12c, via dblink
Is it safe to sync tables from a higher version to a lower version?
I will create a materialized view on the target with scheduled ...
- 11
0
votes
0
answers
35
views
query to know list of the queries executed by all users from the last one hour with Username , SQL query and its execution time
I would like to know the list of the queries executed by all users from the last one hour with Username , SQL query and its execution time details.
Please share the query to get those details in ...
- 131
0
votes
1
answer
46
views
Offline install of Oracle 12c on Oracle Linux 8 without yum or GUI access
I am trying to install Oracle 12c on Oracle Linux 8. I have the relevant files (linuxamd64_12102_database_1of2 and linuxamd64_12102_database_2of2), but the installer uses a GUI, which I can't do (I ...
0
votes
0
answers
5
views
How to copy and paste a map in ODI
I want to copy an existing map in ODI Studio and paste it in the same folder. However the Paste option is grey so I cannot paste the copied one.
Please advice. Thx
- 1
0
votes
1
answer
36
views
Upgrade 12c client to 19c client
Does Oracle 19c client upgrade 12c client if installed or does 12c client have to be uninstalled prior to 19c installation?
- 1
0
votes
0
answers
26
views
sqlplus is not working, it is frozen
When I type
sqlplus / as sysdba
It simply gets frozen, the cursor stays there, and I can type more character but after pressing enter nothing happens. Neither CTRL + C. My pmon service is running, ...
0
votes
1
answer
40
views
how to identify what is causing the CPU Wait status ORACLE
I'm new to oracle and I have a problem because CPU Wait rises and I can't identify what is causing it
0
votes
0
answers
20
views
PL/SQL get the session ID (SPID) during a stored procedure on Oracle 19c
I'm trying to get the session ID (SPID) during a stored procedure on Oracle 19c.
In Oracle 12c the following query was enough:
select s.sid, p.spid, substr(s.username,1,20) MY_SCHEMA, s.terminal, p....
- 1
0
votes
1
answer
25
views
Proper way to setup oracle database for TCPS SSL in local machine
I am using oracle database 12c and would like to setup TCP SSL connection to be able to connect using the same machine where the database is hosted. So, connection would be from database machine to ...
0
votes
0
answers
13
views
Best approach to calculate PCTFREE for tables with high chained_row_ratio after ANALYZE TABLE xxx LIST CHAINED ROWS
On two different databases (Oracle 12.1, 19.11) I've seen in V$SESSTAT a high ratio of "table fetch continued row" of at least more than 10% and I'm searching a good approach to calculate a ...
0
votes
1
answer
17
views
ORA-02270 - can FK in Oracle be defined on VARCHAR2 column?
SQL is more than words:
CREATE TABLE "currency" ("id" NUMBER(10) NOT NULL,
"currency" VARCHAR2(50) NOT NULL,
"name" VARCHAR2(1020) DEFAULT NULL NULL,
PRIMARY ...
- 416
0
votes
2
answers
525
views
export multiple tables using expdp with query condition
I have requirement to export multiple tables using datapump job and the ultimate goal is that we are doing a migration from on-Prem . They have something like 6TB of AUD data and we are not required ...
- 50
0
votes
1
answer
60
views
Is it possible to set Audit_trail parameter to both DB and OS in Oracle database
In Oracle database ,we can direct audit records to either db , os or in xml file by using the below command:
ALTER SYSTEM SET AUDIT_TRAIL= DB SCOPE=SPFILE;
But is it possible to direct audit records ...
- 1
0
votes
0
answers
10
views
estimate total number of updated/locked rows in a transaction by SID
How can a DBA estimate the total number of updated/locked rows in the current transaction knowing the SID? I want to monitor the progress of a long operation in a 3rd-party software.
- 223
0
votes
0
answers
22
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
56
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 ...
- 157
0
votes
1
answer
43
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
50
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
60
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
28
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
94
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
65
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
366
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
45
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
99
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
338
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
81
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
2k
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
57
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
18
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
61
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
70
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
30
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
123
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
56
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
93
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
19
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,068
0
votes
1
answer
131
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
62
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
383
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
107
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
591
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
22
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
168
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
51
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
465
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
74
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
136
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
40
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 ...
- 135