Questions tagged [oracle-12c]
Oracle Database 12c. Please also tag oracle for search purposes.
736
questions
1
vote
1answer
22 views
Generate hierarchical path
I have a classstructure table:
create table classstructure (classstructureid number(8,0), classificationid varchar2(25), parent number(8,0));
insert into classstructure(classstructureid, ...
0
votes
0answers
30 views
+50
Oracle 12c query performance varies based on schema/user
I have a strange (to me) performance issue. Based on the schema that runs the query I received very different performance. Here's my setup.
Oracle 12c DB
SAMPLE schema/user
WIDGET table (...
0
votes
1answer
31 views
Is Oracle 12.2 backward compatible with Oracle 12.1
Current Oracle DB I am using is 'Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit' and we have a use case for using 'Oracle 12.2.X.X.0'. We want to use the elongated object name ...
0
votes
1answer
21 views
Oracle Password File Version
When trying to perform certain user security actions, I get the following error:
ORA-46953: The password file is not in the 12.2 format.
How do I migrate a 12.1 password file to 12.2? Or, is this ...
0
votes
2answers
33 views
Grant schema permissions effective for everything existing and everything that will exist
I'm looking to do what this answer explains but in an Oracle environment:
https://dba.stackexchange.com/a/125400
Essentially what I'm looking to do is
"GRANT schema permissions that are effective ...
0
votes
0answers
17 views
Is possible to send email alert / notification for long running query in OEM
Due to long running queries our batch failed sometimes so we requested to identify if any query is running more than 5 min then it should be killed and alert / notification to the team.
Is this ...
1
vote
1answer
22 views
IORA-00904: Invalid Identifier when converting Radians to Degrees
I am using this simple query in Oracle SQL Developer to basically convert a value in Radians to Degrees but I get error at the function itself.
SELECT Degrees(1.571) as ANGLE FROM DUAL;
Error: Ora-...
0
votes
1answer
44 views
How to run INSERT INTO AS SELECT in parallel on Oracle?
I have a very large table AUD$ that I want to copy it to another empty one with the same structure AUD_BACKUP.
INSERT INTO TEST_USER.AUD_BACKUP
SELECT * FROM SYS.AUD$;
COMMIT;
How do I use ...
1
vote
2answers
30 views
Startup failing with ORA-14694 after running utl32k.sql in upgrade mode
Here are the steps
Creating the db on Oracle 12c (12.2.0.1.0)
Creating the admin views and tables
Starting the db in upgrade mode and running utlk32.sql
Starting up is failing with these errors ...
0
votes
0answers
102 views
Install Oracle 12c on Oracle Linux 6.7 port in use
I have attempted to install Oracle 12.1.0.2.0 on Oracle Linux 6.7 multiple times without success. I have installed the database software only with the configuration assistant and am attempting to ...
0
votes
0answers
29 views
Backup and restore with RMAN under Oracle 12c / Redhat 7 [closed]
I am looking for backup and recovery information using the RMAN tool.
I am implementing an RMAN backup procedure with Oracle 12c.
Oracle recommends to multiplex certain files. Which files are part of ...
0
votes
2answers
38 views
Error During IMPDP in oracle database 12c
I create Dump file with EXPDP command in oracle database 11gR2 with operating system Oracle Linux 6.7
EXPDP Command:
expdp hmis/pass directory=MY_DATA_PUMP_DIR dumpfile=12c_db.dmp logfile=12c_db.log ...
0
votes
0answers
22 views
Search string into dbms_xplan.display_awr output
I want to search into the result of this query.
Select
cursor(select * from table(dbms_xplan.display_awr(sql_id,
plan_hash_value,null,'all +outline +peeked_binds'))) "PLAN"
from gv$sql
where ...
0
votes
1answer
17 views
How to migrate external procedure from Oracle Listener to Postgres
Consider that I'm using C or java external procedure called from my Oracle 12.2 Listener (EXTPROC listener parameter).
That Oracle database will be migrated to Postgres.
My question is how to migrate ...
-1
votes
2answers
48 views
Select data based on XML attributes
My table contain two columns: Branch and data
Here's an example:
Branch having value finance
Data having value xml packet i.e.
<field id="0" value="2200"/>
<field id="2" ...
0
votes
1answer
56 views
Oracle 12c create manual script for automatic startup on RHEL7
I need to create a script to start and shut down a 12c database in rhel7 automatically when the operating system starts and shuts down, it turns out that I have tried the common options with dbstart ...
0
votes
1answer
31 views
Oracle Linked Server Long Preemptive_COM_Cocreateinstance Waits
I have a Linked Server using the OraOLEDB.Oracle provider from the 11g Oracle Client and it was working fine for a while. However, an Oracle 12c Heterogeneous Connection was setup recently. This ...
0
votes
1answer
29 views
Load balancing on oracle (12r2) 2 node RAC is not working
Any help/pointers would be greatly appreciated.
I have a 2 node RAC (Oracle 12r2) on linux,
CRS and all its services are up.
Both the RAC instances are up.
scan listener resolves to 3 ip's.
But ...
-2
votes
1answer
90 views
Fetch small number of rows fast
I'm not sure if my SQL has a problem or the problem lies with Oracle. We have a function on a web application that needs to display a table with a large number of entries (in the millions). However, ...
0
votes
1answer
25 views
Prevent COUNT() Subquery using window functions
A developer colleague of mine has asked me to optimize statements for him. I have had a brief introduction to current level SQL technology and functionality and have the feeling that especially the ...
1
vote
0answers
75 views
Migrated DB from 12c to 19c with IMPDP failed
I have migrated a DB from 12c to 19c with IMPDP and EXPDP. While importing I have seen some errors. Below is an extract of the errors.
Errors During Import
Processing object type SCHEMA_EXPORT/TABLE/...
0
votes
1answer
72 views
How to alter database hour and date in Oracle?
How can I alter the date and hour of the database? I don't want to alter the hour and date from the server, only in database.
1
vote
1answer
92 views
Why is OracleDB locking up in Docker?
I am trying to set up an Oracle database in Docker, and once the database is up and running, I can exec into sqlplus just fine, but if I try to connect with another application like Oracle SQL ...
0
votes
1answer
92 views
Can't connect to Oracle 12c Release 1 in docker container from host
I pulled the Oracle 12.1.0.2 docker image from the Oracle Container Registry (container-registry.oracle.com/database/standard:12.1.0.2) and created a container as per the instructions, leaving the ...
1
vote
0answers
27 views
Oracle 12C silent installation - response file
I am installing Oracle 12c using a response file. The installer exits after a while, with the following error message:
ERROR: Cannot add user to application access ACL. No mapping between account ...
0
votes
1answer
84 views
RAC database fails to start
I encountered the below errors when i am trying to start the RAC DB/instances.
The Cluster/CRS is up but could not bring up the RAC DB instances.
Environment: 2 Node RAC (12cr2) on Linux 7.3
[...
0
votes
0answers
25 views
Oracle Dataguard old sequences
After having set up a dataguard environment with Oracle 12c I notice that when I execute the following command in the primary it shows me the archivelogs:
select sequence#, first_time, next_time, ...
1
vote
2answers
25 views
Free space of tablespace deleting table content (rows)
I have a theorical question, last week we have found this error in one database. The error has been given by our app when trying to save some WS data.
ORA-01691: unable to extend lob segment RS2....
0
votes
0answers
82 views
Oracle 12c RAC Cluster / CRS fails to start after reboot
Could someone shed some light to help fix the cluster/CRS startup failure
1)
/oragrid/app/grid_home/bin/crsctl start crs
CRS-4640: Oracle High Availability Services is already active
CRS-4000: ...
0
votes
0answers
28 views
Oracle 12.1.0.2.0 EM port 5500 not open
After Install Oracle 12.1.0.2.0 i can not access https://10.10.20.156:5500/em
netstat -a
not show port 5500
C:\Users\Administrator>lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0....
0
votes
1answer
35 views
Result Cache disabled in Oracle 12c Enterprise - BYPASS status
I have two database instances, development and production that are fairly identical. On production, result cache is working fine, in development it is not. The value for the database parameter ...
0
votes
0answers
46 views
Determine size of “FLASHBACK ARCHIVE” storage used
I am in charge of estimating amount of data to archive off-site for legal requirements for fairly large (10 GB per client) tables. I found out that off-site storage is fairly expensive, and so am ...
0
votes
1answer
23 views
Selecting next month first Friday when current month last day not equal to Friday
Hi I have my data as below.
Year Period Start_Date End_Date
2019 6 1-Jun-19 30-Jun-19
2019 7 1-Jul-19 31-Jul-19
Period will be the month number
start date and end ...
1
vote
0answers
36 views
Why did my index become unusable after setting MAX_STRING_SIZE to EXTENDED?
I run Oracle Database 12c Standard Edition Release 12.2.0.1.0, and followed the official documentation to set MAX_STRING_SIZE to EXTENDED, to be allowed to use more than 4000 bytes in a VARCHAR2 ...
0
votes
2answers
32 views
In Oracle multi-tenant architecture, is a PDB a container?
Below is an excerpt from Oracle document.
I just want to confirm, is it correct to consider a user-created PDB or a seed PDB as a container? It's a little weird to me. If this is true. Then ...
0
votes
1answer
19 views
Understanding of Oracle multi-tenant PDBs
I'm a new user to Oracle. The document states
The CDB root, also called simply the root, is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong.
and
A PDB is ...
0
votes
1answer
24 views
How to speed up Oracle to PostgreSQL data transfer
I have an Oracle schema that I am transferring to PostgreSQL with ogr2ogr.
This particular table has 5 columns with number(22) each. There are some 300M rows. In under 10 minutes I can transfer this ...
0
votes
2answers
69 views
Error when creating database using DBCA
I'm using Oracle Database 12c R2 on my Elementary OS 5.0. The install is complete without any error. Then I create listener and it started just fine.
When I create database using dbca it shows ...
0
votes
1answer
42 views
How to safely eject a tablespace oracle and move it to another machine
I've a tablespace created on a external hardisk with one big datafile. If there are no connections to it, is it safe to eject the disk and plug it into a new machine? And how to map the tablespace to ...
0
votes
1answer
38 views
script to spool all tables in a schema to xml format in Oracle 12c
I've written the following script, spooltables.sql, which I'm calling from SQLcl as $> @spooltables.sql
The aim is to loop through all tables in a given schema and output them to separate XML ...
0
votes
0answers
27 views
Don't echo a single command in SQL*Plus
If I have a script like this...
SET ECHO ON
SELECT 'I want the person reading the spool to see this whole query.'
FROM DUAL
WHERE interesting_logic_ = 'DEBUGGABLE';
SET ECHO OFF
PROMPT I only ...
1
vote
1answer
63 views
ORA-01652: Extend a tablespace in Oracle 12c, no DBA_DATA_FILES table
I know that there are other questions about this topic but reading and trying them they cannot solve my problem. Mainly because select * from DBA_DATA_FILES; shows me an error when executing, because ...
0
votes
0answers
33 views
Oracle Model Clause and For Iteration Constraints
I have a multidimensional model which I am trying to apply a FOR iteration against. However, I need to add a constraint to one of the dimensions in the FOR iteration (e.g. stop the MODEL from ...
0
votes
1answer
22 views
Insufficient privileges as a definer of package
I cannot run a procedure within a package with a user that also defined it unless I specify "authid current_user" for some reason. Here is my original code that produces insufficient privileges error
...
1
vote
1answer
30 views
Oracle Materialized View - Is it Safe to Use Only ROWID as Record Identifier?
this is a question related to Oracle 12c.
I'm currently trying to create MVs as data sources for Golden Gate replication. We need fast refresh because We have to replicate 60,000 rows + at one time ...
-1
votes
2answers
29 views
Getting the Max date/end date in Oracle query
HI all, I am new to Oracle. My table structure is as follows. I need to find the end date/effective to date for each line. If no new records are there then end date should be SYSDATE. What will be the ...
0
votes
0answers
11 views
Couldn't login to sql developer on oracle 12c
I am trying to login to SQL developer on Oracle 12c but couldn't do so. It shows following error.
Here is output of lsnrctl status
Here is my listener.ora
# listener.ora Network Configuration File: ...
0
votes
0answers
55 views
Loading data from CSV file to oracle table using sql loader
i want to load the data from CSV file to oracle table with this below condition.
if the table is empty then only the data should be loaded into table otherwise data should not be loaded.
is this ...
-1
votes
2answers
156 views
How to check sql query execution time in sql developer
I need to check any sql Statment exact execution time (how much time it will take to complete that sql Statment without running this query) in Oracle sql developer tools without running that sql ...
1
vote
0answers
30 views
Oracle procedure takes forever to compile if I use public synonym
I have a procedure that queries a table through a DB link. If my procedure uses a public synonym through the DB link, it takes forever to compile.
I'm creating the procedure in Oracle 12c. The DB ...
