Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

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
2answers
26 views

Error messages while migrating from Oracle to MSSQL

I got these error messages when migrating from Oracle to MSSQL (Unfortunately, the error messages are in German languages) Verletzung der PRIMARY KEY-Einschränkung "DTR_PK". Ein doppelter Schlüssel ...
-1
votes
0answers
19 views

oracle server query [on hold]

I am new to sql developer and datawarehousing. We are given two tables and have to do star schema ind join using index nested LJ, selecting 50 Tuples at a time my INLJ CREATE OR REPLACE PROCEDURE ...
0
votes
1answer
20 views

Does canceling update operation perform automatic rollback

I ran into a bit of confusion. I'm under the belief that database operations are automatic - that is either the query succeeds or the transaction is automatically rolledback. For example - if you have ...
0
votes
1answer
13 views

Toad for Oracle: Procedures, Packages, Job, and DBMS_SCHEDULER

If I have about 10 queries which contain subqueries, would it be a best practice to contain all of them in one procedure? This script creates a small fraction of the data required for a report. DROP ...
0
votes
0answers
16 views

Automating SQL scripts in Toad for Oracle (13.1)

I'm not a DBA, so bear with me. I've scheduled tasks through Toad's automation designer, but this uses Windows task scheduler. It's using Oracle 12c. All I'm doing is updating (creating/dropping) ...
-1
votes
1answer
23 views

Oracle data file size

My client’s tablespaces are autoextend off and i add monthly or bimonthly 3 or 6 gb space for tablespaces. Can you advise me if there is a recommended datafile size for Oracle 11gR2 on Windows 64 Bits?...
0
votes
0answers
20 views

How to extend oracle database to a newly added hard disk

I'm a begineer to Oracle database admin role. Recently I have added a new hard drive to extend the capacity of an oracle database which runs on Linux. How to extend the oracle database to utilize new ...
1
vote
1answer
29 views

Patching after upgrade (windows 11gr2 -> 12cR2)

I have a following scenario where I had a patched oracle 11gR2 DB (applied Windows Bundle and OJVM patches) and then it was upgrade to Oracle 12cR2. 12cR2 was a clean installation (no patches applied, ...
1
vote
1answer
15 views

ORA-39097: Data Pump job encountered unexpected error -942

Would somebody know what is that error? This is during the impdp command and there is nothing imported. The full error is: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - ...
-1
votes
0answers
19 views

Changing multiple records with pattern in SQL [on hold]

I'm using ORACLE, and trying to change multiple records with a query like Linux command 'SED'. For example, there's two rows in a table. PROFILE -------------- JAKOB.G* JAKOB.T* In this case, I'...
0
votes
2answers
34 views

How to add time from a Date value to another without a varchar conversion?

Let's say I have 2 variables in a Stored Procedure, both are Dates Foo contains '13/10/2019 00:00:00' Bar contains '01/10/1914 16:33:11' I want to add Bar's time to Foo's date, so that I get a ...
0
votes
0answers
38 views

How can I import the fga_log$ table with impdp Oracle 12 R1

I am trying to import with impdp a save made as follows: expdp '"sys/mmm as sysdba"' full=y COMPRESSION=ALL DIRECTORY=LOGICS_EXPORTS COMPRESSION_ALGORITHM=BASIC LOGTIME=ALL CONTENT=ALL DUMPFILE=FULL-...
1
vote
1answer
25 views

Oracle Database 11g Failing to mount on windows server 2012

I have an Oracle 11g database installed on Windows Server 2012. It's failing to mount after shutdown. The reason why i want to mount it is that i want to enable Archive log mode, and i have been able ...
1
vote
0answers
37 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 ...
0
votes
1answer
21 views

Moving columns based in last analyzed date

I have to create a report to summarize some metric results over the last 12 months by account, each time a metric is processed is added to the account attainment with the last 12 months results. The ...
0
votes
2answers
47 views

Table function in oracle

I want to create a function that will return a table as a result. Now, I have written quite a big SQL that works, but as I need to push dates trough that SQL to the beginning, I need a funtion that ...
0
votes
0answers
17 views

Output should return only real values [closed]

A table has three columns A, B, C these columns contain values with combination of both NULL and non NULL record in all columns. a query is required to return only not NULL values from all columns. ...
0
votes
1answer
36 views

Get notification about invalid views

I have 40 views that are integrated/synced to a work order management system on a weekly basis. The views are based on GIS tables which are notoriously messy. Over time, the views can end up becoming ...
0
votes
1answer
46 views

Oracle - how to know what internal executable or program performs DML (SELECT,UPDATE,INSERT,DELETE)? [closed]

My goal is to bypass the query parsing when writing data (INSERT or UPDATE). Is it possible to use directly the executable beneath these commands?
0
votes
0answers
31 views

Options for syncing from a MASTER table to a COPY table?

I have a MASTER table and a COPY table in separate databases (both are Oracle 12c). I want to sync the MASTER table with the COPY table on a weekly basis. The goal is to keep the two tables exactly ...
1
vote
1answer
62 views

How to create a DB Link to a Database which has failover in TNS

Could you please help me to understand how my DB LINK creation sql look like to a database (RAC env, Oracle 12c) which have fail over option in tns entry? I read below post from Oracle-(not having ...
0
votes
1answer
24 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 ...
0
votes
1answer
23 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
60 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
41 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
54 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 ...
-1
votes
0answers
40 views

“lsnrctl start listener” takes a long time

when I try to login to database using sqlplus I get "no listener " error, and when I found some solutions and try to use command "lsnrctl start listener" it takes long time and then I could login to ...
0
votes
0answers
41 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
28 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
31 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
24 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
32 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
42 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
22 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
42 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
59 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
17 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
33 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
87 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
43 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
37 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
52 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
49 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
98 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
25 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, ...