The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
16 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
7 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
22 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
18 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
20 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
46 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
16 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
34 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
24 views

Theoretical 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
votes
0answers
20 views

Sql server scheduler is trimming clob data [closed]

We have a TSQL batch to move data from one oracle table to a table in sql server. When we run this batch manually, it works fine but when we run this batch by scheduler, clob data is getting trimmed. ...
1
vote
1answer
54 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 ...
-1
votes
0answers
16 views

New to DB Design - Conceptual Model for Parking App [[ Issues ]] : Chen vs Crow Notation & Multi-Agent Ambiguities [closed]

[[Intro]] Student entrepeneur here, --- Im sorry if some parts are not relevant or properly explained and will edit to make sure this complies with the forum standards [Im sure if my problem is ...
-1
votes
0answers
25 views

Replace a string to other string [closed]

I have a string O__R__A_C_L__E which needs to be modified, the output should be O_R_A_C_L_E.
0
votes
1answer
23 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
56 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
39 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
52 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
38 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
37 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
27 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
30 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
30 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
41 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
41 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
57 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
32 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
75 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
36 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, ...
1
vote
0answers
57 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
1answer
48 views

ORA-01031: insufficient privileges to a newly created user

I have installed Oracle 18C Enterprise Edition. I just created a new user with a prefix of c##, and when I tried to login through the newly created user it says: ORA-01031: insufficient privileges ...
0
votes
0answers
50 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
36 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
68 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
14 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 ...