Questions tagged [oracle]
All versions of Oracle database. Add a version-specific tag like oracle-11g-r2 if that context is important in the question. Do not use for Oracle products such as applications or middleware or other database products owned by Oracle, like MySQL or BerkeleyDB.
1,235
questions with no upvoted or accepted answers
11
votes
1
answer
9k
views
DBeaver launch script from file
I'm trying to find a good alternative to SQL Developer for performing queries and scripts on Oracle instances.
I find DBeaver to be an interesting tool, it has a lot of great features, BUT it seems ...
- 111
9
votes
1
answer
1k
views
Oracle Ref Partitioning: Deadlock due to child table row migration
My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc.
ORDERS table is LIST partitioned on STATUS column and all ...
- 265
8
votes
2
answers
121
views
Changes access method for non-correlated subquery
Oracle 11g R2
Unfortunately our application has per row security "features".
We have a query that looks about like this:
Bad, slow:
SELECT someRow, someOtherRow
FROM bigTableA a
WHERE EXISTS (
...
- 1,559
6
votes
2
answers
3k
views
Case insensitive search of CLOBs in Oracle 12
Question: What is the most performant way to do a case-insensitive search in a CLOB column?
I've simplified the query to easily reproduce the problem. The filedata table can have millions of rows. ...
- 105
5
votes
1
answer
1k
views
SQL Developer Data Modeller and Data Dictionary question
I'm not a DBA but I am a (mostly!) competent SQL report writer. Can I trouble you DBAs for some pro-knowledge, please?
I can connect SQL Developer to Oracle XE's data dictionary without any problem, ...
- 51
4
votes
1
answer
7k
views
Oracle Database Server TNS Listener Remote Registration Vulnerability (CVE-2012-1675)
Some of our Internal DBs Oracle installed in Linux and Windows servers have been highlighted with the issue Oracle TNS Listener Remote Poisoning (CVE-2012-1675).
This security alert addresses the ...
- 41
4
votes
0
answers
501
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 ...
- 61
4
votes
2
answers
230
views
Alert on Table size
I need to create an alert on my application table when it reaches 98% of its threshold. I wrote an SP which checks the rows and logs the information into a log file which was monitored by alert system ...
4
votes
0
answers
2k
views
Oracle: built in SQL pretty-print?
Oracle has some packages that will dump objects in SQL format, such as dbms_metadata.set_transform_param. One option that can be set is to "pretty print" the SQL.
Is there anything in Oracle that ...
- 819
4
votes
1
answer
2k
views
How do I identify the remote db agent name to use in create_database_destination on Oracle 11gR2?
I am trying to setup DBMS_SCHEDULER in Oracle 11g to run a remote database job.
I have a remote Oracle 11g R2 database on unix and a local one on Windows.
I read that you can install the oracle ...
- 183
4
votes
1
answer
248
views
Determine weekend based on country
I've a table called BOND which have columns BOND NAME, COUNTRY of ISSUE and ISSUE DATE
BOND Country ISSUE_DATE
===========================
B1 INDIA 20-AUG-2017
B2 ISRAEL 18-AUG-2017
How ...
- 193
3
votes
0
answers
80
views
Optimizing queries using indexes
I'm doing an exercise, where we have to improve several queries:
(30%) Give me FH and FC per month, filtered by the aircraft model (e.g., "777").
(30%) Give me ADOSS, ADOSU per year, ...
- 131
3
votes
0
answers
2k
views
Difference between To_Number (Null) and Cast (Null as Number) in Oracle
There is a difference in behavior between these two variants to create a view with a null number column and then a table from that view: One fails and one succeeds.
At the same time there is no ...
- 76
3
votes
1
answer
2k
views
check if value exist in another table before insert SQL
I have table: " daysoff " and table: " rdv "
the rdv table looks like this :
temps_rdv
etat_rdv
ID_RDV
12-10-2022
reservee
2
the daysoff table looks like this :
ID
DATE_OFF
...
- 41
3
votes
1
answer
758
views
How to list backups when not connected to target DB in Oracle RMAN
I'm trying to list backups in RMAN completed within a certain date range. This works fine when I can connect to the target database and recovery catalog.
However, I'm assuming a situation where I am ...
- 31
3
votes
1
answer
1k
views
Autotask 'auto optimizer stats collection' and scheduler jobs logging
We have a DB hosting Peoplesoft schema, with more than 33.000 tables. Since
december Oracle has left more than 18 thousand of them with no statistics
renewed:
SQL> select to_char(last_analyzed,'...
- 31
3
votes
1
answer
15k
views
Why create a Materialized View Log with Primary Key or RowID, etc?
I have a table with 600million rows. I want to create a materialized view that filters it down to 50 million records but also uses REFRESH FAST ON COMMIT. To use REFRESH FAST I need a MATERIALIZED ...
- 149
3
votes
2
answers
982
views
How do I run a database heavy query over jdbc without timing out (Oracle)?
For one of our applications (Pentaho Data Integration), we need to run queries through the oracle jdbc. Some of these queries are computation expensive and can take an hour or more to return any rows ...
3
votes
1
answer
1k
views
Why would a query return oracle.sql.NUMBER@[hex value] in place of a numeric value?
I'm seeing some odd behavior in an Oracle 11gR2 database. When I select from a particular table, using SQL Developer, certain rows of the result set include non-numeric results in a NUMBER column (...
- 171
3
votes
1
answer
480
views
What might be the cause of regular periodic slow transactions on an OLTP system?
I have a two node Oracle RAC cluster with that runs an oltp application against it. I have both a production and a duplicate test environment with the same setup. Upon running stress tests on the ...
- 279
3
votes
0
answers
2k
views
Getting email notification when jobs are created under user_schema rather than sys?
I log in as sys and set the smtp for scheduler:
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute('email_server', '10.1.1.15');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', '[email protected]')...
- 31
3
votes
1
answer
288
views
UTL_MATCH.JARO_WINKLER_SIMILARITY - how can I set the prefix scale?
I'm using UTL_MATCH's Jaro Winkler similarity function and it seems to be performing well. However, I would like to adjust the prefix scale according to the situation. Is this possible? Is it possible ...
3
votes
1
answer
134
views
Oracle replicat is abending when oracle is having special char while loading to teradata
I am doing replication from oracle to teradata where oracle is having below charter in one column.
->->->
ACSII value for this char is 26 .
I need to replace it with null . In the charmap we have ...
- 131
3
votes
3
answers
7k
views
ORA-01157: cannot identify/lock data file 201
SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;
*
ERROR at line 1: ORA-01157: cannot identify/lock data file 201 - see
DBWR trace file ORA-01110: ...
- 194
3
votes
0
answers
1k
views
ORA-28545 (Net8) error when connecting Oracle 12c on Centos 7 to SQL Server 2014
I have installed Oracle 12c on Centos 7 and I wanted to connect SQL Server 2014 using freeTDS. So, I installed the following:
FreeTds.
unixODBC.
Next, I configured ODBC on the Linux server so that, ...
- 169
3
votes
2
answers
148
views
Causing ORA errors in a program using JDBC. How can I find what's causing them?
So I'm building a java app to interface with the company's database. Whenever it causes an ORA error, it just prints something like the following to system output:
ORA-00911: invalid character
Is ...
3
votes
0
answers
649
views
plsql procedure gets invalidated
procedure involving dblinks gets invalidated regularly .we need to recompile every time.How to find what is causing the problem
in my case
procedure p1()--resides in db1
is
begin
....
select col1 ...
- 45
3
votes
1
answer
5k
views
sqlplus is not starting
I have a strange error on a Red Hat machine.
InstantClient is installed in and ORACLE_HOME is set to /opt/oracle/product/112030_cl_64/cl/
The user which installed the client was oracle:oinstall. My ...
- 131
3
votes
0
answers
947
views
Mismatched Oracle drivers - need upgrade (10.2.0.1 to 10.2.0.5)
I was forwarded here by recommendation from Stack Overflow ......
We are running Oracle Enterprise software on various client and DB servers and we are experiencing a problem (outlined here) where ...
- 51
3
votes
3
answers
26k
views
Create Oracle DB LINK to a database with no DB_DOMAIN
I'm trying to create a database link between this two DBs.
My local DB is 11.2.0.3.
SQL> select * from global_name;
GLOBAL_NAME
-------------------------------------------------------------------...
- 667
3
votes
1
answer
4k
views
12c Grid Agent installation issues - Shell path is incorrect or not defined
I am installing 12c Grid Control agent on my server via Enterprise manager.
The targer server is a UNIX server with AIX OS on it. I add target host manually, enter authentication information, host ...
- 131
2
votes
0
answers
28
views
Can I audit all actions of a database role?
I know from AskTom that we can audit all actions of a particular user account, e.g.:
create audit policy test_audit_policy
actions all
when q'!sys_context('userenv', 'session_user') = '...
- 193
2
votes
0
answers
137
views
How set default collation in Oracle by default BINARY_CI for new Tables?
I wrote script to change all tables default collation:
BEGIN
FOR rc IN (
SELECT * FROM ALL_ALL_TABLES WHERE owner='SchemaName' AND TABLE_NAME NOT IN ('__EFMigrationsHistory')
)
...
- 21
2
votes
0
answers
111
views
Prevent appending schema name to function-based index calls
When a function-based index created using
CREATE INDEX I_FN_TEST
ON T_TEST (TESTPKG.TESTFUNC (TEST_COLUMN));
Oracle appends the current schema name to the TESTPKG call, making it CURRSCHEMA.TESTPKG....
- 21
2
votes
0
answers
61
views
How to derive a LOCK ID from a user lock handle?
I have a lock handle from dbms_lock.allocate_unique() but I would like to get the Lock ID from the handle. By ID I mean the value of SYS.DBMS_ALLOCATED_LOCK.LOCKID and V$LOCK.ID1. From what I can see, ...
- 666
2
votes
1
answer
414
views
ORA-00957 View of JOINed tables with same column names
Is there a way to create a view of two joined tables so that
all columns of both tables are in the view
and there is a column of the same name in both joined tables
and the columns are not explicitly ...
- 523
2
votes
0
answers
265
views
Audit SELECT on a particular column
How do I do audit SELECT on one column in the table?
The column in question is a BLOB column so most likely these blob columns are accessed using some dbms_lob.
Table Emp:
column
Type
Emp_Id
Number
...
- 81
2
votes
0
answers
485
views
SQL*Plus fail instead of prompting for substitution variable
Imagine a file test.sql with the following content
SELECT '&myvar' AS greeting FROM dual;
When we run that file in sqlplus. We will be prompted for the value of the variable &myvar since it's ...
- 121
2
votes
0
answers
2k
views
Oracle BI 12C (12.2.1.4.0) Configuration Assistant Error (Adding default service instance) on Windows Server 2016
I'm installing Oracle BI Pub 12c (12.2.1.4.0) on Windows Server 2016, all steps were successfully passed but on "Add Default Service Instance" it throws below error
Not sure what to do now, ...
Naman
2
votes
0
answers
433
views
Whether or Not to Use DataPump with Oracle GoldenGate
We are setting up GoldenGate to keep 3 of our Oracle databases in sync. Regarding configuration:
We plan to have an instance of GoldenGate running on all 3 nodes. Each node will run an extract on (...
Lance Allison
2
votes
2
answers
2k
views
Oracle 12c Database Config Manager very slow at creating a new database
I have installed Oracle 12c on a number of machines (Windows 10, 2012, 2016 mostly) before and created several databases on them, using the Database Config Manager, all with no real hiccups.
However ...
- 201
2
votes
0
answers
517
views
Incremental Schema Stats (With partitioned/Subpartition table) gathering is taking too much time in oracle version 12.2.0.1
When I am gathering schema stats with below options, it is taking too much time:
EXEC dbms_stats.gather_schema_stats (
ownname => 'SCHEMA',
granularity => 'AUTO',
CASCADE=> true,
...
- 21
2
votes
0
answers
2k
views
How to solve problem when oracle apex does not let me do anything with my created schemas even though I should have privileges?
I have a problem with with latest version of APEX, I have installed it on my pc, i logged in as and admin created workspace with schema, but when I log in to that workspace as a user with admin's ...
- 21
2
votes
0
answers
333
views
How to monitor progress of Oracle Materialized View
I have created a materialized view in Oracle 12.1 database. The master table has materialized view log created, so that I can do a fast refresh.
I have executed the refresh using DBMS_MVIEW.REFRESH, ...
- 141
2
votes
0
answers
64
views
Does Azure SQL natively support being the subscriber in transactional replication where Oracle is the publisher?...and if PostgreSQL is the publisher?
Is there a native way to setup transactional replication where an Azure SQL database is the subscriber and the publisher database is in Oracle?
What if the publisher database is in PostgreSQL?
If ...
- 35.1k
2
votes
0
answers
1k
views
How do I do an ODBC connection to ORACLE from SSIS/ Visual studio?
Despite that OLE Db and ADO.NET are newer than ODBC potentially, I've heard that ODBC connections to Oracle are potentially faster within SSIS.
So --- I'm trying to set up a simple ODBC connection to ...
- 1,651
2
votes
0
answers
3k
views
Oracle 11g Select query returns NO ROW SELECTED but the table actually has data
In my production, There is a table named ABC under X schema. I have granted SELECT privilege on ABC table to another user named Z. When I execute the query
SELECT COUNT(*) FROM X.ABC;
as user Z it ...
2
votes
0
answers
3k
views
Removing REM INSERTING and SET DEFINE OFF from SQLcl Output
I ran below script to get data from Oracle, and then load to Postgres with psql.
SET SQLFORMAT INSERT
SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL asset_file.sql
SELECT * FROM FINANCE.ASSET;
SPOOL OFF
...
- 121
2
votes
1
answer
4k
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 ...
- 17.4k
2
votes
0
answers
2k
views
ORA-01031: insufficient privileges solution oracle
Create or replace procedure myprocedure
is
stmt varchar2(1000);
stmt2 varchar2(1000);
begin
stmt := 'create global temporary table temp(id number(10))';
execute immediate stmt;
...
- 401