Oracle Database 12c. Please also tag oracle for search purposes.
-1
votes
1answer
19 views
TImestamp format in Oracle DB
Query:
select date,TO_CHAR(date,'dd/MM/yyyy HH24:mm:ss') from user.table where rownum<10;
02-08-18 05:21:02.301000000 PM 02/08/2018 17:08:02
Problem is minutes in above time are different 'mm'...
0
votes
1answer
33 views
Error granting SELECT on view that uses ALL_OBJECTS
I'm in the middle of testing Oracle 12c for our application. I have something similar to this view in a particular schema, which we'll call MY_OWNER_SCHEMA for this question:
CREATE OR REPLACE VIEW ...
0
votes
1answer
17 views
How do I check the connection timeout in Oracle?
I need to check Oracle 12c timeout for an active connection. I don't have access to the database itself but I do have some creds. Is there a table to query to get this information?
Thank you!
0
votes
0answers
24 views
TEMP Tablespace Removal
Oracle Version: 12.1.0.2
We have a discrepancy between our PROD and DR TEMP tablespace data files.
PROD = 30G + 30G + 30G data files
DR = 30G + 30G + 30G + 21G + 2G + 1G + 15G data files
I ...
0
votes
1answer
12 views
how to replace dbms_reputil.from_remote in oracle 12.2.0.1 since All objects removed except REPLICATION_IS_ON, _OFF and _ON
how to replace dbms_reputil.from_remote in oracle 12.2.0.1 since All objects removed except REPLICATION_IS_ON, _OFF and _ON
In plsql program
CREATE OR REPLACE TRIGGER EVENT_ALLOWABLE_STATUS_S ...
0
votes
0answers
13 views
SQL Server 2012 to Oracle 12c migration
I am very new to Oracle world and I am trying to migrate a SQL Server 2012 database to Oracle 12 using SQL Developer. Looks like I am able to migrate the database successfully as I got a popup saying "...
0
votes
1answer
27 views
RMAN or Fresh install
I'm trying to reduce the time to install Oracle on a pipeline we have. At the moment, it's installed using dbca in silent mode with a database template file. The majority of the time seems to be ...
0
votes
1answer
36 views
Different execution plans and joins using synonym or table
Edit:
We found the leading cause. If you join tables with the same name but in different schemas you can get this result. Same with joining two tables using the same alias (whyever this works at all)....
1
vote
1answer
51 views
How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0
I am running following command to dump my database
./expdp username/password@//host:port/database directory=/home/ubuntu/oracle/instantclient_12_2 dumpfile=dumpfilename.dmp content=METADATA_ONLY
and ...
1
vote
0answers
24 views
Oracle Warning in Alert Log Regarding PGA_AGGREGATE_LIMIT
Introduction
During the startup process of our Oracle instances, we are encountering the following error message in the alert log:
WARNING: pga_aggregate_limit value is too high for the
amount of ...
0
votes
1answer
21 views
Backup data base in oracle 12C
We have 12c database. we had create a pdb. Now i want to take a backup through
RMAN. Could you please share the steps to configure this backup.
I know to configure backup in 11g..For 12c Please share ...
0
votes
1answer
30 views
No privileges on tablespace 'USERS' [duplicate]
I am creating to the user after create table, but not insert the data why?
Then display the message NO PRIVILEGES ON TABLESPACE 'USERS'
1
vote
1answer
42 views
How to export user with tables and tablespace oracle
I'm a newbie to Oracle World.
I have to export, from computer A, tables and their data, to computer B.
So, my question is: is there a way to export user, tablespace, tables and their data with one ...
0
votes
2answers
23 views
PL/SQL - Member Function: Link directly to object without parameters
I want a table generated from a table function in oracle.
Like this:
+--------------------------------------------------------------------+
| CUSTOMERS ...
1
vote
1answer
70 views
oracle 12c alert log error
I am using Oracle 12c Standard Edition Release 12.2.0.1.0. Everything works perfect. But while I was checking the alert log file I saw the following message several times.
Error Message
An internal ...
2
votes
1answer
25 views
How can I configure RMAN to delete crontrol files automatically?
I am using Oracle Release 12.2.0.1.0 Standard Edition on ODA (Oracle Database Appliance, running linux). My instance "xyz" is running in archivelog mode. RMAN performs backup and unnecessary datafiles ...
-1
votes
0answers
24 views
Cause of Oracle database size increase
We have a warehouse management database with Oracle since 2017, until then it was SQL.
the usual database size is 40-50 GB when it was SQL, since we moved to oracle, the size has been increasing ...
0
votes
2answers
44 views
Select strings with line breaks
How can I select strings that have line breaks in them?
I can think of these possibilities when it comes to line breaks in strings:
create table test1 (
id number(4,0),
f1 varchar(25));
...
0
votes
1answer
28 views
Select rows with line breaks: Unexpected result
I'm trying to find rows in a table where there are line breaks.
I found some sample code that lets me do this:
where dump(f1) like '%,10%' and Not (dump(f1) like '%100%')
However, I'm getting an ...
1
vote
1answer
29 views
User Quota Default Oracle
I am sure I should either know this or have been able to determine this, but I am investigating an issue with user allocated space quotas and am wondering how to determine the default (if there is one)...
1
vote
1answer
58 views
Unexpected results in query with rownum condition [closed]
I have found old query in application that behaves unexpectally after Oracle update. I have found out that problematic part of this query is condition:
rownum >= 1
When I apply this condition, ...
4
votes
0answers
143 views
RMAN-06169, but only when backing up via OEM 13c
When running a Level 1 or Level 0 backup via OEM 13c, the following RMAN error causes the run to fail. (The RMAN script is being ran with proper sysdba credentials from OEM 13c.)
RMAN-06169: could ...
3
votes
1answer
64 views
Select where number range does not overlap
I have two tables that contain records about road construction activites:
table_a is the master list.
table_b is a legacy list.
For each road, in each year, I want to select the records from table_b ...
0
votes
0answers
58 views
Oracle - Is it possible for the results of a transaction not to be visible to a subsequent transaction?
We're having a strange problem with a flaky integration test. This is using Java/JDBC against an Oracle 12 database. The test does this, in pseudocode:
transaction 1 {
insert into order (id, ...
0
votes
0answers
52 views
Password for Oracle Database 18c user “system” [duplicate]
I recently upgraded from Oracle 12c to Oracle 18c. When using SQLPLUS in 12c I logged in using "*
username: system
password: mypassword
But when I try to login in 18c using same it shows ORA-...
1
vote
2answers
60 views
SQL case statement not executing 'else' [closed]
I am on Oracle 12.1.0.2.0
I have a query that I wrote utilizing a CASE statement. I have 2 "WHEN" statements and one "ELSE" statement. The query is working as expected except for the "ELSE" statement. ...
0
votes
1answer
29 views
Change month partition to day partition with current data in Oracle 12c
I have sales table and currently sales table have interval partition by month.
I changed partition type to month to day;
ALTER TABLE sales set interval (NUMTOYMINTERVAL(1, 'DAY'));
However current ...
2
votes
1answer
47 views
Migration of table from MySQL to Oracle using GoldenGate
I have a table of size 120 GB in MySQL 5.7 which I need to migrate to Oracle 12c. I am using Oracle GoldenGate for initial load of the table.
I tried with initial load of full table but it consumed ...
3
votes
1answer
107 views
Oracle 12 Startup upgrade throws error 443
Please follow the logic of my attempts at upgrade before suggesting a fix. The error 443 is not the main problem here.
we are upgrading from oracle 11.2.0.4 to 12.2.0.1 on a Solaris 11.3 server '...
4
votes
0answers
156 views
“Unable to enlist in the transaction” with Oracle linked server from MS SQL Server
We have a setup where we use MS SQL Server with a linked Oracle server. Our database has views on the Oracle database. We have in-house written software (I'm the lead developer) which uses this setup.
...
2
votes
3answers
73 views
Select the greatest value from each column, even if one of the values is null
I have a road_age_test table:
create table road_age_test (
surface_year int,
base_year int
);
insert into road_age_test (surface_year, base_year) values (10,20);
insert into road_age_test (...
1
vote
1answer
58 views
Wrong query results on column ORA_ROWSCN with bind variables and NVL (Oracle 12c)
In the process of migrating our DB from Oracle 11 to Oracle 12c (12.2.0.1.0), we discovered that the same query gives different results on the two instances.
The query is:
VARIABLE myId NUMBER;
...
0
votes
1answer
75 views
What is the best way to determine, without an Oracle support contract, the release date of an Oracle Database 12c patchset?
This is regarding a set of databases that I'm working with (I'm not a DBA, I just verify various settings and check for patching). The DBA is unable to provide the information I need.
What I run is ...
0
votes
0answers
50 views
Moving undo tablespace data file to new location in 12C
We have moved undo tablespace datafile to new location as bellow:
ALTER DATABASE MOVE DATAFILE '/oracle/app/oracle/oradata/ETATD/undotbs01.dbf'
TO '/TD-Data/table_space/undo_datafile/undotbs01.dbf';
...
0
votes
0answers
40 views
Oracle APEX. New installation. Unable to create Work Space: PROVISION_COMPANY
Newly installed Oracle Database 12c. While creating the first Workspace in Application Express (APEX) 5.1, I've got an error:
"Error provisioning XXXXXXXXXXXX.
ORA-20001: Request 1824748984765804 ...
0
votes
0answers
35 views
Oracle: Automating copies of database from Prod to Dev
I have been tasked with coming up with a way to automate a regularly scheduled copy of production to development on the first of each month for each application environment we run.
On the surface, ...
3
votes
1answer
198 views
Is it possible to create an alias/shortcut/makro/function for a collection of keywords?
In Oracle 12c, it's possible to use AUTO_INCREMENT functionality by using the keywords/phrase GENERATED ALWAYS AS IDENTITY. This seems cumbersome/clunky to me, given that in other dialects of SQL that ...
0
votes
1answer
22 views
How do I get a list of values for column x GROUPed BY column y?
I have the following table structure and data (using Oracle DB 12c):
CREATE TABLE authors (
aid NUMBER(38) GENERATED ALWAYS AS IDENTITY CONSTRAINT authors_pk PRIMARY KEY NOT NULL,
fname ...
0
votes
1answer
44 views
Unable to apply order by clause on distinct select in Oracle
The query is:
select distinct( Name )
from Students
where marks>75
order by Substr(Name,-3,3),ID asc;
This shows an error message:
order by Substr(Name,-3,3),ID asc
*
ERROR at line 4:
ORA-...
0
votes
2answers
48 views
Do I need to update Oracle Client?
How does Oracle maintain Oracle Client software? I don't find Patch Updates for the Client Software of Oracle.
0
votes
2answers
33 views
How to switch between Oracle 11g and 12c on same linux
I have oracle11g and 12c installed on same machine and few test cases that need to be run on both versions.
Can power up both database simultaneously and run test paralally.
Do you know any document ...
-7
votes
1answer
47 views
i want output as shown in below image [closed]
max_unit_price=maximum of unit price product wise
difference=max_unit_price-individual unit price
0
votes
1answer
163 views
Can not install oracle apex on GNU/Linux (db 12c)
I have to install oracle apex on oracle database 12.2.0.1.0, database is on GNU/Linux server.
I followed instructions from this pages:
https://oracle-base.com/articles/misc/oracle-application-express-...
2
votes
1answer
79 views
Recommended settings for a small table with high update rate [closed]
I have a small table which can be explained as follows:
create table mytab (
primary_key_column number,
a number,
b number,
c number,
d number,
e number,
f number
);
...
0
votes
1answer
46 views
Moving DataFile to Another Location ORACLE
I'm having some problems moving DATAFILE from one location to another due to the shortage of storage space. I've managed to change the path file of control file via command(linux). Now I would like to ...
5
votes
1answer
117 views
Same table alias for two different tables (Oracle)
This example has the same table alias used for two different tables. I don't understand why this is allowed by Oracle, and if allowed, how the results make any sense.
create table Table_A (x number);...
0
votes
0answers
28 views
Duplicate Active Database - Dataguard Oracle 12C
Folks,
Good evening.
I am performing the configuration of a DataGuard in Oracle 12c.
At the time of performing duplicate active database, the process has been aborted, reporting the error below.
I ...
2
votes
0answers
25 views
Upgrading Oracle ODBC driver from 11 to 12 causing code to fail
In the process of replacing our servers which involves updating the Oracle ODBC drivers from 11 to 12. We have a process that executes on these servers that calls a stored procedure. The procedure ...
0
votes
1answer
75 views
RMAN validate incremental level 1 backups?
I am somewhat new to RMAN, and have taken an Oracle class on it, but I am stumped on validating my backups when using level 0 and 1; in order to save space, I am considering doing a level 0 (full) ...
0
votes
0answers
14 views
Oracle 12c Cloning PDBs from disk
In Oracle 12c is it possible to clone/duplicate a PDB into a different CDB using a disk backup?
So if I take a backup to disk as follows, can I then clone from this backup into a new PDB in a ...
