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
0answers
6 views

How to increase the value of a column in a table by a constant by consulting another table?

I have prepared the database of the college given as create table depts( deptcode char(3) primary key, deptname char(70) not null); create table students( rollno number(2) primary key, name char(50),...
1
vote
0answers
37 views

Tool to migrate data to another database with different object names [on hold]

We are working on a project to migrate our Oracle database to SQL Server. There are many off the shelf products that seem to do this such as Microsoft SQL Server Migration Assistant & Navicat ...
0
votes
0answers
18 views

Oracle - clean LOB files - recovering disk space

I have a friend who has a website and asked me for help. I often use MySQL databases but never Oracle databases. And unfortunately he has an Oracle database, so I can't find a solution. The available ...
0
votes
0answers
29 views

SSIS Slow Data Transfer between Oracle and SQL Server 2016

I am trying to design a process that transfers data from Oracle to SQL Server 2016 by using SSIS. It is done with the aim of removing productive processes from an SQL Server 2000 instance, where the ...
-1
votes
1answer
32 views

Month on Month count to determine variance of table growth

Good Afternoon Team, Hoping you can help me . Every month i get a count of records associated to a table. What i would like to do is month on month calculate the % variance (between jan vs feb, feb ...
0
votes
0answers
19 views

Data Loss issue while migrating data from Source(Oracle) to Data Warehouse Staging Database(SQL Server)

Project Overview Loading data from Oracle Database to Datawarehouse Staging Database(SQL Server). Using ETL process we are loading data from Oracle to SQL Server. Staging Database in SQL server is an ...
0
votes
1answer
34 views

Oracle function based index with empty function name

I'm trying to understand this syntax in the creation of an index in a database I'm using: CREATE INDEX IDX ON SQ.MYTABLE (('ID_COLUMN1'), ('ID_COLUMN2'), ('ID_COLUMN3')); After searching for a ...
1
vote
2answers
36 views

RMAN restoring datafile out of ASM

Why is RMAN restoring the datafiles out of ASM and how to restore a backup into ASM? Here is one output. The tablespace triscaltb was restored in /u01/app/oracle/product/12.1.0/dbhome_1/dbs/...
1
vote
3answers
45 views

How can I drop a view only if it exists in Oracle?

I would like to perform a DROP VIEW IF EXISTS... command on an Oracle DB as it can be done on a MySQL database. How can this be achieved?
0
votes
1answer
26 views

how the database calculate the text size? [on hold]

i have a table stored in file with 2Mb size, when I moved it to the oracle DB, it became around 100 Mb size or more! why? how the DB calculate the text or number size
1
vote
0answers
52 views

Strange sql behavior - last character is removed in the Select statement, but actual data has different length

We have a table with a column name A with type nvarchar(23). following query will always return 23 which means that the actual length of all records are 23. select length(trim(req.A)), count(*) ...
0
votes
1answer
37 views

Migrate a remote oracle database (11g) into a docker oracle XE database (11g)

I am brand new to Oracle and I am trying to migrate an existing VM that has deployment scripts run on it every release to use a docker container instead. Then I can run the release scripts and tag a ...
0
votes
1answer
33 views

Oracle DDL Trigger error - RDSADMIN.RDS_DDL_TRIGGER [on hold]

On Oracle 11g, While calling a procedure through web application, which creates a table inside to store some data temporarily. I cannot change the mechanism as proc is already in production. I am ...
-1
votes
0answers
38 views

How to query by specific hours each day during a week? [on hold]

I have wrote my code as follow. However, it doesen't work(obviously not). select * from my_table where date between to_date('2019-08-09', 'yyyy-dd-mm') and to_date('2019-08-09', 'yyyy-dd-mm') and ...
0
votes
0answers
38 views

Creating a table from the existing table along with the constraints in oracle

I have table emp and I want to create a duplicate table from the emp table. I tried the below query, which is copying all the table columns, data and the not null constraints. create table emp_backup ...
0
votes
2answers
24 views

ASM - How the asm search the disks and where is this parameter from?

I'm a begginer in Oracle ASM and when I will create a diskgroup the ASM shows me this parameter: Where is this parameter from? I don't remember set any parameter with this name. I know that usually ...
-2
votes
1answer
83 views

Fetch small number of rows fast

I'm not sure if my SQL has a problem or the problem lies with Oracle. We have a function on a web application that needs to display a table with a large number of entries (in the millions). However, ...
-1
votes
1answer
25 views

Is there a possibility of data corruption/lost when inserting large blob data from one table to another table in the same database?

I am trying to determine if data corruption/lost is possible when moving large data from one table to another in the same schema. I am new to programming and database is not my forte. Anyway I've ...
1
vote
1answer
50 views

Oracle - loop through list of databases to check status [on hold]

I have a table with database names and connection strings. Do you know any method to loop through the list of database names (and/or the connection details) and test database connectivity (just need ...
0
votes
0answers
42 views

Migrating from windows-1252 to UTF-8 character set

I'm thinking of migrating my database (oracle) from windows-1252 to UTF-8 by doing the following: export all data (backup) truncate all tables run alter database character set import data back Is ...
0
votes
1answer
23 views

Prevent COUNT() Subquery using window functions

A developer colleague of mine has asked me to optimize statements for him. I have had a brief introduction to current level SQL technology and functionality and have the feeling that especially the ...
2
votes
2answers
64 views

How to “flatten” multiple rows with same ID (Oracle 11g) by concatenating **multiple** fields per row?

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4). I'd like to "flatten"/"merge" all rows with the same ID, pretty much as is well delineated here: https://www.w3resource.com/mysql/...
-1
votes
0answers
23 views

Query slow on the sharded table

I created a sharding db env on oracle 12c and 19c. I found a strange problem: The select query on the sharded table is very slow(17s to 300s) even there is only 10 rows in the table, but it's only 0....
0
votes
1answer
36 views

Oracle - Why create several service_name in service_names parameter?

SERVICE_NAMES specifies one or more names by which clients can connect to the instance. The instance registers its service names with the listener. When a client requests a service, the listener ...
1
vote
1answer
22 views

Help defining a chained one-to-many relationship tables

Sorry if the title is confusing but I couldn't think of a way to properly word this question without laying out what I am trying to do. Basically I have 3 tables and I'm trying to figure out how to ...
1
vote
0answers
40 views

Migrated DB from 12c to 19c with IMPDP failed

I have migrated a DB from 12c to 19c with IMPDP and EXPDP. While importing I have seen some errors. Below is an extract of the errors. Errors During Import Processing object type SCHEMA_EXPORT/TABLE/...
0
votes
1answer
61 views

How to alter database hour and date in Oracle?

How can I alter the date and hour of the database? I don't want to alter the hour and date from the server, only in database.
1
vote
3answers
55 views

Compare 2 columns in 2 different tables with non uniques values

I want to compare and check whether the values in columnA of table A exist in column A of Table B. But the columnA consist of duplicate values in both the table.
1
vote
1answer
86 views

Why is OracleDB locking up in Docker?

I am trying to set up an Oracle database in Docker, and once the database is up and running, I can exec into sqlplus just fine, but if I try to connect with another application like Oracle SQL ...
0
votes
0answers
16 views

How to refresh the URI type data : insert into temptable values (SYS.URIFACTORY.getURI('URL'));

How to refresh the URI type data and reload to table , whenever the website url got refresh. My complete code: Created table with URI TYPE create table vignesh3(url URIType) loaded data ...
0
votes
2answers
36 views

Joining 2 tables based on Dynamic criteria

Hoping you can help me. I have 2 Tables (Table PS & Table A). What i am trying is the following ; Depending on the ACCOUNT_NUMBER & Id_Type of table A, search the Table PS on the following ...
0
votes
1answer
39 views

Can't connect to Oracle 12c Release 1 in docker container from host

I pulled the Oracle 12.1.0.2 docker image from the Oracle Container Registry (container-registry.oracle.com/database/standard:12.1.0.2) and created a container as per the instructions, leaving the ...
0
votes
1answer
29 views

How to Join two tables with the join being regexp_replace [closed]

Need your help, I have two tables “temp” and “temp 1” The temp table I am deriving a column called numbers. I am wondering how do you join table temp1 onto temp and joint on SELECT regexp_replace(...
-1
votes
0answers
19 views

How to quercy exactly 1 year date differences with LEAD and partition by in oracle sql

I am quite beginner in oracle sql, and i am trying to solve some competitive tasks in my freetime. I have come upon one query that i am struggling with. The task is to get the same occurences of ...
0
votes
0answers
8 views

Oracle Fusion Middleware vs. MySQL - DB Init Param Prerequisite failure [migrated]

I'm setting up an FMW sandbox environment on my laptop to play around. It needs a database as repository, I though MySQL would be simplest. A battled a few problems I sorted out already, but I'm stuck ...
1
vote
0answers
23 views

Oracle 12C silent installation - response file

I am installing Oracle 12c using a response file. The installer exits after a while, with the following error message: ERROR: Cannot add user to application access ACL. No mapping between account ...
0
votes
1answer
28 views

How to recreate Oracle db from .ora and .dbf?

I have a number of files left from a previous Oracle db and I would like to create a new one from the files. The files include: init_locdb.ora 16 .dbf files spfilelocdb.ora log files .ctl Could you ...
1
vote
3answers
69 views

Extract a number from nvarchar column

Hoping you can help me. I have a column in a table called indicator. (Data type: NVarchar(80)) Within this column I have some numbers the length of this number is 6 characters in length. The total ...
0
votes
0answers
21 views

Create range from one date

So, I have a specific problem, that I can't solve without a function. Here's a simple code I wrote which if can be made to work, will solve my bigger issue. with dates as ( select year_start, day ...
0
votes
1answer
27 views

How to Calculate Month over Month User Retention based on already active users (not based on user signup date)?

I have a Table which tracks User activity (i-e user started a session at what time ?). This table contains the data from Dec 2018 Till now. I need to calculate monthly retention (not based on signup ...
0
votes
0answers
24 views

What gets executed immediately after logging into an Oracle database?

When I login with SQL*Plus, without executing a command, I always get the following error: $ sqlplus db_user/db_pass@localhost/XE SQL*Plus: Release 18.0.0.0.0 - Production on Wed Aug 7 10:33:03 2019 ...
1
vote
2answers
37 views

Equivalent of Oracle RR date format in SQL Server

Having a bunch of SQL insert scripts generated in Oracle, I have the requirement to adjust them to be usable with T-SQL in Microsoft SQL Server. The Oracle export has date formats like this: to_date(...
0
votes
0answers
26 views

In Oracle, how can I tell if I'm currently in a read only transaction?

How can I tell if I'm currently in a read-only transaction that was started by set transaction read only name 'tran1'?
0
votes
0answers
20 views

Oracle Dataguard old sequences

After having set up a dataguard environment with Oracle 12c I notice that when I execute the following command in the primary it shows me the archivelogs: select sequence#, first_time, next_time, ...
0
votes
0answers
18 views

Interpreting explain plan timing, A-Rows, and E-Rows with nested loops

I got the below output from an explain plan on a very poor-performing query: ---------------------------------------------------------------------------------------------------------------------------...
1
vote
1answer
22 views

Oracle 12.2.0 installation NLS_CHARACTERSET QUESTION

Im creating fresh database 12.2.0 on linux(RH7.6) but not with GUI (dbca). Im installing it from scripts and which in file i can set parameter NLS_CHARACTERSET to EE8MSWIN1250 in pre-install process ...
0
votes
1answer
20 views

oracle decreasing vs increasing column varchar2 performance

I know it seems stupid question but why increasing the column of a table tend be fast but decreasing it takes time depend on the size of the table? Is it because while decreasing oracle check each ...
1
vote
2answers
21 views

Free space of tablespace deleting table content (rows)

I have a theorical question, last week we have found this error in one database. The error has been given by our app when trying to save some WS data. ORA-01691: unable to extend lob segment RS2....
0
votes
2answers
34 views

How to fetch Package DDL in Oracle 8i

I want to fetch a Package DDL in Oracle 8i. 8i doesn't support DBMS_METADATA.get_ddl. Can it be done using export?
0
votes
3answers
45 views

Automatically Add 30 min to logout time column SQL

I have a table with id, login, and logout. id -> number login -> date logout -> date The employee has to login but they do not have to logout. The logout time will just be + 30 min added to the ...