Questions tagged [oracle-12c]

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

Filter by
Sorted by
Tagged with
-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 ...
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 ...
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?
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 ...
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 ...
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(...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
-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 ---------- ----...
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 ...
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 ...

1
2 3 4 5
20