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.
7,619
questions
0
votes
1
answer
936
views
Why only ~half of RAM is used by Oracle on Linux server?
Why Oracle 11.2 would be using only around half of available RAM on RHEL5 box? I have 64GB but 32GB is free which really annoys me. Any memory not used is wasted memory in my books - especially on ...
CommunityBot
- 1
1
vote
3
answers
2k
views
sqlldr running slow after indexes created
let me explain this....
I had ran 1 session of sqlldr while I run indexes into a table for 6 fields... now the indexes are completed but the sqlldr running very slow it loads one file after each 30 ...
CommunityBot
- 1
0
votes
1
answer
392
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 ...
CommunityBot
- 1
1
vote
1
answer
15
views
Need a PL/SQL code to drop many partition in 1 single SQL statements
I have a requirement where we need to drop historical partitions which are not dropped .
Requirement is dropping multiple partitions in one step instead of dropping one by one.
like
ALTER TABLE sales ...
- 39.2k
0
votes
0
answers
14
views
Can't log into Oracle 19c database running inside a container from the host
I have a server running Oracle 19c inside a Docker container, and I would like the ability to log into the database from the host using sqlplus.
Inside the container, I am able to run this command ...
- 101
1
vote
1
answer
913
views
Oracle 18 on Windows with long hostnames doesn't talk to the local listener?
Note: Initially this was about installing Oracle 18c XE on a Windows machine with a long (> 15 chars) hostname. During our further tests we then noticed that Oracle 18c XE does not work properly at ...
0
votes
1
answer
1k
views
Executing a query with a variable in a loop
I have two select, how can I do the same using a loop and a variable in PL SQL Developer.
I have:
select 1 as nm from dual;
select 2 as nm from dual;
I need something similar:
DECLARE x NUMBER;
BEGIN
...
CommunityBot
- 1
0
votes
1
answer
1k
views
Unable to connect to an Oracle 12c Docker container
I'm working on a project with Oracle, and using a Oracle XE 11 container for testing. But recent developments have ended up with me running up against a brick wall: I need larger table names. ...
CommunityBot
- 1
1
vote
1
answer
1k
views
How to reclaim unused space in SYSAUX tablespace in Oracle?
I have 6gb file for SYSAUX table space in 18xe oracle database.
SYSAUX table space has 61% free space :
select
fs.tablespace_name as Tablespace,
(df....
CommunityBot
- 1
0
votes
1
answer
798
views
How to view databases and tables after 'imp' import using Oracle SQL developer
Maybe that's a wrong form of answer...
The problem is:
I've got an Oracle database dump file (very old, created with imp utility)
At last, I've imported it. The command was imp file=E:\Downloads\...
CommunityBot
- 1
1
vote
2
answers
1k
views
Oracle 19c Installer hangs with endless Reading from the pipe output
I am attemping to install Oracle 19c on Windows 10 64 bit.
No Oracle products have ever been installed on this machine.
When I run setup.exe the cmd window launches, then the splash screen and then ...
0
votes
1
answer
22
views
Oracle - How to find out who is causing LOCKED(TIMED) in user account?
I belive Oracle audit cannot track it since the user actually did not connect to the database. Or can audit help? I mean, can Oracle audit track without the user login in the database?
Someone or some ...
- 130
1
vote
1
answer
528
views
Oracle, Cursor_Sharing = Force and Queries with literal comparisons forcing FTS
I have the following scenario:
Oracle 11.2.0.3
A 3rd party application, that requires cursor_sharing = TRUE
A table (T2310) with a indexed field (C1000000161) that has no duplicated values
Result ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Oracle table is occupying too much space in disk
I have a simple table in Oracle. The table has 1 million records, with an average record size of 200 bytes. Adding column definition sizes, a record shall not have over 750 bytes.
According to a ...
CommunityBot
- 1
0
votes
1
answer
692
views
How to create multiple container databases in 12c R1?
As multiple databases can be created under the same/different Oracle home(s) in Oracle 11g on a host, please advise me on creating multiple container databases to create multiple pluggable databases ...
CommunityBot
- 1
1
vote
1
answer
2k
views
Why Oracle 19c Database EM Express Webpage is not opening in Microsoft Edge?
I have recently installed Oracle Database 19c on my Laptop which has Windows 10 OS. Now, I want to open the localhost:5500/em page in Microsoft Edge browser, but when I try to do so, the webpage does ...
CommunityBot
- 1
8
votes
2
answers
3k
views
Deferrable Foreign Key Constraints
What are the consequences of making a foreign key constraint DEFERRABLE INITIALLY IMMEDIATE instead of NONDEFERRABLE?
This answer on StackOverflow mentions the performance impact that comes with no ...
- 2,471
0
votes
1
answer
233
views
Query Over Oracle DB Link Requiring global_name = true
I'm running the following queries over a private dblink (Oracle Database 19c):
delete from myschema.mytable@mydblink where SERVER = (select host_name from gv$instance);
insert into myschema.mytable@...
CommunityBot
- 1
2
votes
2
answers
195
views
PostgreSQL equivalent to Oracle's ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)
There's a technique in Oracle SQL that can be used to simplify aggregation queries:
Aggregate on a particular column, but get information from a different column, using a simple calculated column in ...
- 1,417
0
votes
1
answer
228
views
Exclude Supplemental Logs from Import
I am trying to import tables from a production environment, to a test environment. The production environment has supplemental logs on a number of tables for golden gate replication. The ...
CommunityBot
- 1
3
votes
4
answers
4k
views
I don't know how to fix ORA-04091 mutating trigger error
I'm learning Oracle SQL, but when dealing with triggers I'm finding an error, that prevents me from updating values in that table.
The error is:
error ORA-04091: table USUARIO.LINEAS is mutating, ...
CommunityBot
- 1
1
vote
1
answer
63
views
When does Oracle still treat empty strings as NULL?
I was looking for the difference between VARCHAR and VARCHAR2 and learned, among other things, that Oracle’s VARCHAR2 treats empty strings as NULL, or the other way round. Also, apparently I should be ...
- 39.2k
0
votes
2
answers
26
views
How to update table keeping data available to users (Oracle)?
I have a table VISIT_HIST in an Oracle data warehouse. This table contains number of customer visits during every month and is rather big (~130 mn rows).
CREATE TABLE (
VALUE_MONTH DATE,
USER_ID ...
- 130
0
votes
1
answer
42
views
What is the optimal way to insert values in a table with index (Oracle)? [closed]
I have a table VISIT_HIST in an Oracle data warehouse. This table contains number of customer visits during every month and is rather big (~130 mn rows).
CREATE TABLE (
VALUE_MONTH DATE,
USER_ID ...
- 11
0
votes
0
answers
15
views
Why is it that when filtering by two collections, the execution time increases significantly and the query takes a very long time?
Given: A table with 6 million records. We need to collect some statistics for the quarter. 650 thousand lines fall into the quarter.
The data filtering parameters come into the procedure. Some of ...
- 101
1
vote
2
answers
38
views
Name of SQL programming style where KEYWORDS | CRITERIA are justified to centering line
Is there a name for this kind of SQL programming style?
--Courtesy of Markus Winand of modern-sql.com/
SELECT *
FROM (SELECT cities.*
, ROW_NUMBER() OVER(PARTITION BY country ORDER BY ...
- 1,417
3
votes
2
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: ...
CommunityBot
- 1
0
votes
2
answers
33
views
ignore column width in sqlplus
I'm getting the follow
SQLPLUS> create table source(aa nvarchar2(2), bb nvarchar2(100), cc nvarchar2(10));
insert into source values ('aa',null,'aa');
insert into source values (null,'aa','aa');
...
- 130
1
vote
1
answer
1k
views
Insert Into queries take too much time in Apache spark
I am using Oracle Database with Apache spark 2.3.0
In my code I am writing final output (after all the processing) in three tables using below spark command.
billableChargeKDF.write().mode("append")....
CommunityBot
- 1
0
votes
2
answers
3k
views
Oracle user created information
Is it possible to determine what user created another user's account in Oracle database?
I have two types of users in database:
Users having system and object privileges
Users having only system ...
CommunityBot
- 1
0
votes
2
answers
32
views
How do I set tab as a delimiter in sqlplus markup
I want to return sqlplus output as tsv. For that I use set markup csv. but it does not accept tab as delimiter, not \t and not actual tab.
error is
SP2-1660: Invalid option. Only a single character be ...
- 39.2k
1
vote
1
answer
37
views
Oracle ASM for data files - "not recommended" - why not
I've been supporting a lot of Oracle databases (not full-time; I'm a programmer 90% of the time) and I just happened to see in the Oracle 19 Linux Installation document that storing Oracle data files ...
- 39.2k
1
vote
1
answer
25
views
Oracle Database Snapshot?
Is there a proper snapshot function or anything analogue to it on Oracle Database (19c, 21c) on a schema or PDB level?
For example, I wanted to run some tests that will alter tables, procedures and ...
- 39.2k
0
votes
1
answer
271
views
Write Oracle proc/function to create dynamic views with changing column headers
I had similar requirement earlier but now i have been given a clear approach that needs to be implemented.
I need to write a procedure(preferably)/function, which would on basis of app_id passed as ...
CommunityBot
- 1
0
votes
1
answer
383
views
Can oracle dbca template be used cross versions
If you have a dbca template to create a database for version 12.1, will it also work for creating a database on 12.2 binaries?
Will it also work for creating a database on 11g binaries?
CommunityBot
- 1
-1
votes
0
answers
39
views
How to solve error ORA-00600: internal error code, arguments: [4194], [5], [7], [], [], [], [], [] in RAC node 2? [closed]
The instance on node 2 always go offline each 1 hour ,
I checked alert log file and I found this error
Errors in file /u01/app/oracle/admin/KAASH/bdump/kaash2_smon_15801.trc:
ORA-00607: Internal error ...
- 111
1
vote
1
answer
51
views
Patching Oracle Client 11g2 versions on SQL Server
We have a SQL Server 2014 running on a Windows Machine, with Oracle Client 11.2.0.1.0 running. This client is used to Allow SQL server connect to remote Oracle Data sources.
This is the directory for ...
- 11
0
votes
1
answer
583
views
Importing huge table exhausts UNDO extents in Oracle RDS (ORA-01628)
I'm attempting to do an impdp on RDS, Oracle 12c. I'm importing only one table for this particular impdp job but every time I try to import it, UNDO usage gets to about 50% and then the logs just say ...
CommunityBot
- 1
1
vote
1
answer
792
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 ...
CommunityBot
- 1
3
votes
1
answer
434
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 ...
CommunityBot
- 1
0
votes
1
answer
378
views
ORA-01653 Unable to Extend Table AGILE.A_DW_TXN_LOG BY 128 in Tablespace AGILE_DATA3
I am trying to import an oracle database from a dump file. While importing I am getting ORA-01653 Unable to Extend Table AGILE.A_DW_TXN_LOG BY 128 in Tablespace AGILE_DATA3.
I have tried to fetch the ...
CommunityBot
- 1
0
votes
2
answers
81
views
How to request enhancement to Oracle SQL Developer
Regarding Oracle's SQL Developer tool:
Is there a way to request that Oracle make an enhancement to the tool?
For example, I want to ask Oracle to add this functionality: Generate WITH clause from ...
- 1,417
0
votes
1
answer
35
views
How to request an enhancement to SDO_GEOMETRY / Oracle Spatial
I want to suggest a few ideas to Oracle about enhancements to SDO_GEOMETRY / Oracle Spatial.
Example:
Requesting a function to select vertices, including the multi-part numbers of SDO_GEOMETRY ...
- 1,417
1
vote
1
answer
817
views
Troubleshooting RMAN-03009: failure of delete command on d1 channel
I don't have many experience about RMAN backup. I'm running backup daily level 1 incremental backup and weekly Level 0 incremental. Backup files will kept at 2 difference locations (each location one ...
CommunityBot
- 1
-1
votes
2
answers
8k
views
How to set up a connection in Pentaho Kettle for Oracle using a SERVICE NAME instead of SID?
This error shows up when I try to connect to database string host:port/servicename.
- 1
0
votes
0
answers
29
views
What is difference between "Statement Cumulative Statistics" and "Statement Self Statistics" in reports generated by OraSrp tool
I have generated trace for single session in oracle database(Version 19c). Then used TKPROF and ORaSrp tool to read raw trace files.
What I observed for same sql statement OraSrp tool gives two stats
*...
- 21
0
votes
1
answer
231
views
TLS Connection Looking For Wallet in Default Location despite using MY_WALLET_DIRECTORY
I'm finding hints here and there that this is a known issue, but just wanted to ask here directly to confirm. I have a typical client-server TLS connection. We have multiple database instances server-...
CommunityBot
- 1
0
votes
0
answers
16
views
In Oracle SQL, How do I select an aggregate column from the result of a group by function? [duplicate]
SELECT *
FROM Clients c
JOIN Info i
ON i.id = c.info_id
JOIN (
SELECT
site_owner_id,
SUM(p.amount_paid)
FROM Site s
JOIN SecurityArrangement sa
ON sa.site_id = ...
- 23.8k
0
votes
0
answers
9
views
Oracle12c Compound update column trigger gives ora-06512 recursive SQL error on execution
This trigger is used to update the sales for empno 20
empno 20 sales value is the sum of the sales of all
employees under his supervision in dept 6
create or replace
trigger empno20upd
for delete or ...
- 973
0
votes
2
answers
263
views
Select and sum data from a oracle table containing duplicates
I have a table containing data where some are identical in given columns but different in other columns. I will try to describe this table as follwing:
table1 table2 ...
CommunityBot
- 1