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.

Filter by
Sorted by
Tagged with
0
votes
1answer
31 views

Oracle 12c how to purge the FGA audit records before a certain date

please I need to know how to purge the FGA audit records before a certain date, I have an Oracle 12c R1 database in which the audit with FGA is implemented, the tables of this are in a tablespace ...
1
vote
1answer
27 views

How to see resumable timeout of another session in oracle?

How I see the setting of timeout about resumable of another session? I've found the function dbms_resumable.get_timeout, but there is no way of inform some parameters about a specified session.
0
votes
0answers
62 views

ORACLE MULTIPLE TABLE INDEXES ON GROUP BY

I have a VIEW with multiple tables, it has a group by statement containing multiple columns from each table. The problem is access to the view takes 4,5 seconds and ideally should take less than 1. ...
0
votes
2answers
50 views

How can the TNS connect identifier passed to SQL*Plus be accessed from the run script for reconnecting?

I wonder how the TNS identifier can be used within a SQL script. In this example $ sqlplus MY_USER/pswd@TNSID @script.sql par1 par2 I'd like to get TNSID as a string in order to use it from script....
0
votes
1answer
61 views

How to improve performance for table JOINS involving millions of rows

I am dealing with a performance issue for a very simple structure involving simple queries. Consider two tables called TBL01 and TBL02. TBL01 has a master-detail relationship with TBL02. The primary ...
0
votes
0answers
73 views

Export SCHEMAS and TABLES at the same time

My task is export schemas sch1, sch2 Also need to export tablespaces ts88, ts89 from schema sch3 How can I do it as one operation? Because, if I run different operations : DIRECTORY=dir ...
0
votes
1answer
30 views

Oracle - How to issue desc command in a procedure within package?

It's possible to see only the parameters of a especified procedure into a package? For example a procedure into dbms_scheduler, something like this: desc dbms_scheduler.create_job It's possible? ...
0
votes
1answer
45 views

How does one install Oracle XE for local development use and enable automatic startup?

I installed Oracle 18c XE on Oracle Linux 7 for local development purposes and everything functions as expected. The script that I wrote to install and configure Oracle XE may be found at https://gist....
0
votes
0answers
32 views

Different free space between EM and query

I'm using oracle database 12c. EM Express shows different free space from my query. In EM, it shows that I have 99MB free space on appdf1. But when I run this query: SELECT Substr(df....
0
votes
1answer
29 views

How can I change default location for tablespaces

In Oracle, how can I find out where datafiles for a tablespace are created by default if I do not provide a full path for the datafile but just the name? And how can I change this default location?
-1
votes
1answer
40 views

snapshot too old : roll back segment number with name too small error doubt

recently, i have been facing snapshot too old error. So, i tried to understand the why this is happening. Link to the snapshot too old article Still, i have some doubts regarding the error. If i ...
-4
votes
1answer
47 views

Monthly to Daily Partitions in Oracle

I have a table with monthly partitions on it. I was asked to change those monthly partitions to daily partitions. For that, can I do even if the data exists ? What prerequisites do I need to take care....
0
votes
1answer
24 views

“Server not open” / “Drop tablespace” catch-22 situation

I am getting the "server not open" error in SQLPLUS, and the equivalent on Oracle SQL Developer, which says "Oracle Initialization or shutdown in progress". Fair enough. The fix for this is as ...
0
votes
2answers
79 views

What is the purpose of the NOLOGGING clause on Oracle database TABLES and INDEXES

I was wandering what is the practical purpose of using the NOLOGGING clause on tables and indexes. For what I know from the Oracle documentation, we can prevent the database from generating redo logs ...
0
votes
1answer
116 views

Why do I keep getting ORA-01033: ORACLE initialization or shutdown in progress when my database is open? (12c)

I'm running Oracle on a Windows server (maybe that is why it is so buggy?). It was working fine for a bit, but lately I've been having an issue. I keep getting the ORA-01033: ORACLE initialization or ...
0
votes
0answers
34 views

Sql Oracle 11g. Want to find the what are all the values are modified in a table?

I have ten attributes in a table. I want to find what are all the datas are being modified and which are all the fields??? Could you please help me out
0
votes
0answers
20 views

SSMA - Sysdb - Tables Missing

Based on my test, when I use SSMA for Oracle.8.4.ExtPack.exe to install, after the installation, I can see the table ssma_oracle.bcp_migration_packages and other tables are not in the sysdb database, ...
-1
votes
1answer
38 views

What is difference between select count(1) from table and select count(*) from table [duplicate]

What is difference between select count(1) from table ; and select count(*) from table ; Here my table contain 5rows and 5column
1
vote
3answers
88 views

View: Ignore a left join if it is not used?

I have a web service (GIS feature service) that references a view called gis_sidewalks_vw. create table gis_sidewalks ( id number(10,0), last_edited_date date ); insert into gis_sidewalks ...
-1
votes
1answer
50 views

with clause sql statement maximum limit in oracle 12c [closed]

with s as (select distinct s.empno, s.deptnumber, s.age, s.salary from emp.employee details s ) ...
0
votes
0answers
76 views

optimization tips recommended for this query

I have these queries select decode(sum(txn_amount),'',0,sum(txn_amount)) as txn_amount, count(txn_id) as txn_num, currency_code, (select rpt_descr from rptcodesmtb where rpt_code = txnmtb.txn_type ...
0
votes
0answers
38 views

Query to count the number of different departments that a student is studying a subject from

The database schema are as follows: depts(deptcode(primary key), deptname) students(rollno (primary key), name , deptcode (references depts(deptcode))) faculty( fac_code(primary key), ...
0
votes
0answers
55 views

How to insert from one table to another from trigger

So i have an after insert trigger to insert some data into a temp table, and i want to add to the end of the trigger the ability to insert the rows from the temp table back to the original table. This ...
0
votes
1answer
39 views

Chronological list of statements from Oracle trace

I'm using a trace in Oracle (11.2) to elucidate the behavior of a client application, and would like to view a list of all of the SQL statements that it submits, in the order in which they were ...
0
votes
1answer
59 views

Isn't global temporary table global?

I've doing some tests in SQL Server and Oracle. When I created a temporary table in SQL Server I don't needed grant select on the temporary table for the specified user. Any user get to query the ...
1
vote
1answer
52 views

How to securely store the password for an Oracle DB link?

Having a system consisting of two linked Oracle DBs, we currently have the password for the dblink stored in a table, in order to be able to change it (session issues) and to re-created the dblink in ...
0
votes
3answers
107 views

Integration: Keep two systems in sync

I have a GIS system with 40 tables, ranging from 1,000 to 60,000 rows per table. The tables are the system of record for assets in a municipality. The GIS assets in the tables get integrated to a ...
1
vote
1answer
26 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, ...
1
vote
0answers
62 views

oracle 11g select distinct issue

I have a big table (61mil rows) that has an indexed not-null column ID. I want to select the distinct values of that column. I expected the select distinct id from bigtable ; to run fast, making ...
0
votes
0answers
139 views

exceeds the maximum allowed database size when export database oracle 18c

I have worked on Oracle Database 18c Express Edition, while I try to export database schema by using expd command, I have an issues ORA-12954: The request exceeds the maximum allowed database size ...
1
vote
2answers
71 views

Suppress some output from PL/SQL script in Toad for Oracle

Question: In PL/SQL for Oracle running in Toad, can I suppress script output except for certain lines I want to see? e.g. I don't want create table, insert record, drop table. I do want my own ...
0
votes
1answer
70 views

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
15 views

What is the default column name given to a TABLE() type with a single column

CREATE OR REPLACE TYPE MISSING_KEYS AS TABLE OF VARCHAR2(256); SELECT [¿what goes here?] as missing_key FROM TABLE (MISSING_KEYS('A','B','C','D')) What is the default column name that should be in [¿...
0
votes
1answer
20 views

update records that are there in .csv file which will be given as input [closed]

I have .csv file with list of Ids, using those IDs in file- giving them as input, I need to update the record in table, I have around 4000 Ids so 4000 update statements are not possible, need a ...
-1
votes
1answer
23 views

Validation of Column value data to Other table column value [closed]

I have 2 Tables, 1st is the source table which contains City name and 2nd Table is staging table also contains City name and I want to validate the city name for each row in 2nd table is valid or not ...
3
votes
1answer
104 views

How to get Oracle_Home directory?

I'm not asking "where is my home directory", or "how do I find it". I am pretty sure I don't have an Oracle_Home directory and I want one. What is the minimum software package that I should install to ...
1
vote
0answers
19 views

Oracle 12c - Installing SW with an Active Directory Account on RHEL7

My client insists on installing Oracle using an Active Directory Account as the SW owner instead of creating a local oracle service account on RHEL7. I'm opposed, but willing to be flexible to meet ...
1
vote
3answers
50 views

How to select from a table where in a list and then diff that against the same list?

Here is what I need: I have a LIST of keys: A B C D A TABLE keys that contains: A B **... and millions of other entries** ( but `NOT` `C` or `D` ) I want to see what is in the list AND NOT IN the ...
0
votes
0answers
49 views

BI- Directional Golden gate integrated capture (oracle - oracle ) fails [ recovery scn values not set ]

I see " ERROR: Extract EXT1 is not ready to be upgraded because recovery SCN values are not set " with integrated capture, Appreciate your inputs and pointers on the below issue w.r.t integrade ...
1
vote
2answers
366 views

Database Table/Column rules [closed]

We are making a new database design where we are setting some rules upfront. I dunno how much it makes sense to have these rules in place, Each table and column name should be meaningful. It should ...
1
vote
2answers
67 views

Can sqlplus fetch more than one row at a time?

We have a task running every day dumping results of a query into a CSV-file -- as the stdout of sqlplus running a query. The number of records is many millions (don't ask), and the job takes over two ...
0
votes
1answer
52 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
29 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
43 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 ...
2
votes
0answers
40 views

Oracle DISTINCT with NULLable columns

There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest,...
3
votes
1answer
36 views

Query Performance Help

A brief history. I have an oracle sql query which I'm looking to pull data when a certain preference is enabled. In this query I had a date range to filter the data to a specific range. This was ...
0
votes
1answer
37 views

deadlock detected during oracle expdp

I have an Oracle 11g and I wanted to export all my shcema objects, with this command: expdp as '/ as sysdba' directory=DIR dumpfile=DWH%U.dmp logfile=DWH.log filesize=40000m parallel=32 schemas=DWH ...
0
votes
0answers
44 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
102 views

Help needed to tidy up deprecated (and mal-functioning) SQL code [closed]

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4). I've inherited a chunk of SQL which apparently uses quite a lot of deprecated stuff and, importantly, "sometimes" yields wrong results (...
0
votes
0answers
24 views

merge statement taking 30mins for 1777820 rows? how can i optimize it

merge into apl_scp_direct_dispatch t Using (select siteid, item, bucketid,band_qty from i2_scp_inventory_bands@DB_DMSAPIL_LINK where band_type = 'DEPBAND' and band_level = 2 ) s ON ( s....