Oracle Database 12c. Please also tag oracle for search purposes.
0
votes
1answer
31 views
Impdp 12c takes table original size
I want to import one of my schema "vvs1" to r&d database with content=metdata_only. expdp works perfect but when i am going to import on r&d server it takes tables original size.
for example:-...
0
votes
1answer
22 views
Extract values from oracle pl sql cursor
I have a function that returns a cursor. When I execute
select t.id, t.f_rc(id) from my_table t
I get
id F_RC(TID)
1 < cursor >
2 < cursor >
3 < cursor >
...
How to expand ...
2
votes
1answer
33 views
Network line switch-over in Oracle Data Guard
We have a Primary database with a remote Standby which established using Oracle Data Guard. They can be syncing over a private Wireless Network and a Wired LAN (There are two options as network medium)...
0
votes
0answers
12 views
IMPDP ORA-01861: literal does not match format string
I exported schema thru expdp from one database and set the following:
set NLS_DATE_FORMAT=yyyy/mm/dd hh24:mi:ss
set NLS_TIMESTAMP_FORMAT=yyyy/mm/dd hh24:mi:ss
Tried to import datapump with same ...
1
vote
0answers
14 views
Oracle XML Database database invalid, how to validate/recompile … can i drop and recreate it. Please suggest
The installed components and their status,
Oracle Database Catalog Views 12.1.0.2.0 Valid
Oracle Database Packages and Types 12.1.0.2.0 Valid
Oracle XML Database 12.1.0.2.0 ...
0
votes
0answers
13 views
How to identify customer with same no of nominees and same nominees for all his loans
Let's say Customer 1 has loans L1 and L2. L1 loans has nominees N1 and N2. L2 also has nominees N1 and N2. This is a valid scenario.
Let say Customer 2 has loans L1 and L2 with (L1-N1,N2)
(L2,N2,N3,...
0
votes
0answers
17 views
Convert a query to a function
I have a query that counts nearby neighbors and looks like
select id, value /*stuff*/ from t start with ID=1 /*stuff*/ --returns result for id=1
union all
select id, value /*stuff*/ from t start ...
0
votes
1answer
29 views
Moving Oracle / Oracle datafiles to different file system on Linux
We have Oracle installed on /root, which is now running out of space due to increasing data in Oracle database. On the same machine we have another file system with free space.
I want to know the ...
0
votes
0answers
18 views
Archive destination DB_RECOVERY_FILE_DEST_SIZE full - Oracle
I always get the following error DB_RECOVERY_FILE_DEST_SIZE being used 100% in all our Oracle databases. As a workaround I tried to increase the destination size and delete archivelogs in RMAN until ...
0
votes
1answer
29 views
Is it feasible to escape ' (single quote) with ESCAPE clause, if yes, then how to do it?
I understand that we can use '' to escape a single quote in Oracle queries. However, I want to know if it is feasible to escape a single quote in the ESCAPE clause.
set up:
create table test_t ( x ...
1
vote
2answers
24 views
Difficulty Understanding Oracle DQL Procedures and Functions Coming from T-SQL
I am much more familiar with SQL Server, but I am doing a project now in Oracle. There is a concept from SQL Server that I am not sure how to accomplish in Oracle.
In SQL Server if I create a ...
0
votes
0answers
41 views
39001. 00000 - “invalid argument value” error using DBMS_DATAPUMP.add_file
I been trying to use the Oracle Data Pump PL/SQL API to move data to AWS Oracle RDS. My export job is failing on my local installation of oracle database. Local database is 12c and RDS is 11g. I been ...
0
votes
0answers
18 views
Why am I getting an error “Failed to add USERNAME/Username install user to %2% group” when installing Oracle 12c R2?
I want to install Oracle 12c Release 2. But, it returns an error Failed to add USERNAME/Username install user to %2% group. So, after installation I cannot connect to my database. How to fix it? I ...
2
votes
1answer
39 views
Is it possible to name an ACL?
I need to create some ACL's to allow TCP connections to some hosts. These hosts can only be accessed by their IP address. To make them easier to keep track of in the future, I'd like to be able to ...
0
votes
1answer
26 views
Oracle TKPROF Insert data doesn't match text results
I'm trying to make sense of the output from a SQL Trace from an Oracle 12.2 database on Windows. I've used TKPROF to interpret the the resulting trace files. This utility generates a text file output ...
0
votes
1answer
50 views
ASM Space Usage
Oracle Version: 12.1.0.2
I had about 100 GB freespace on my +ASM diskgroup on Friday.
When I came back on Monday, I see only 45 gigs. How can I find what consumed my space?
Or is there a SQLPlus ...
1
vote
0answers
41 views
What is the cardinality in this situation?
I need to find the cardinality for a few tables in an Oracle database. I have been given a word document that has a list of tables described with details as shown in the image:
I have been reading ...
0
votes
0answers
54 views
FATAL DBT-06604 The location specified for 'Data Files Location' has insufficient free space
I'm trying to install Oracle 12.c release 2 on Solaris 11.3 but when it's going to complete the Oracle Database Configuration Assistant Task, it gets error and suggest an action : "choose a 'Data ...
-3
votes
2answers
56 views
Oracle multi-tenant vs SQL Server [closed]
I am from SQL Server background and recently getting my hands dirty with Oracle 12c. I came across few features of Oracle called multi-tenant, Container Database and Pluggbale database. Can somebody ...
0
votes
1answer
33 views
How to Oracle DBLink encryption
I have the environment that I explain below and I need to encrypt the data that is sent between my two nodes, I have been reading but I can not find the right solution, I explain:
I have an Oracle ...
0
votes
1answer
16 views
Check all enabled trace events
Recently on a database I enabled trace for event 12842 using the below statement in order to trace Oracle error ORA-12842 : Invalidated cursor.
alter system set events '12848 trace name ERRORSTACK ...
2
votes
1answer
76 views
Oracle 12c - Slow query to tables ALL_CONS_COLUMNS and ALL_CONSTRAINTS
We have successfully upgraded Oracle 11g to Oracle 12c. So far it's been going well. But We noticed one problem, queries made for Oracle's constraint tables (ALL_CONS_COLUMNS and ALL_CONSTRAINTS) are ...
0
votes
1answer
36 views
Join table on one of the row
I have a table, say, Person with fields
id name
1 Jone
2 Jane
Got another table address
id person_id address_type
1 1 Permanent
2 1 Temporary
I want to select ...
0
votes
1answer
29 views
Internal error code, kksgaItLvl_Setup [closed]
A simple query fails with the error. Has anyone faced this issue or has any solutions?
SELECT MAX(some_value)
FROM some_table
WHERE some_id = 62328188199
AND some_name = '...
1
vote
1answer
23 views
oracle: deny naked sql statements
I'm looking for a solution in oracle 12c to deny INSERT, UPDATE and DELETE statements on tables.
I would only provide SELECT privileges and exec rights to execute procedures which INSERT, UPDATE or ...
2
votes
1answer
37 views
cannot create view in oracle 12c
I have a simple query like this:
select d.file_name, d.tablespace_name, s.segment_type, s.segment_name,
round(s.bytes/1024/1024,2) MB
from dba_data_files d
inner join user_segments s on d....
0
votes
1answer
51 views
is it possible to use utl_file_dir = '*' scope = spfile in 12c
Oracle 12c has deprecated utl_file_dir = '*' scope = spfile,
but I need to use this functionality. Is there any option to do it?
Is it possible to give permission using Java classes?
0
votes
1answer
40 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'...
1
vote
1answer
67 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
148 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
1answer
45 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
47 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
21 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
29 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
42 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
288 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
52 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
26 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
155 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
45 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
32 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
205 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
125 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 ...
0
votes
2answers
46 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
30 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
45 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
61 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
157 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
67 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
59 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, ...
