Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
1answer
59 views

In Oracle 12c trying to create table with columns greater than 4000

New to Oracle and SQL. I have already followed normal procedure to allow for this size: startup upgrade alter system set MAX_STRING_SIZE ='EXTENDED' scope=spfile; @%ORACLE_HOME%\RDBMS\ADMIN\utl32k....
0
votes
2answers
47 views

sqlplus, command line parameters and ORACLE_HOME with spaces

I have noticed strange interaction between ORACLE_HOME and parameter substitution. Here is my test.sql prompt &1 prompt &2 prompt &3 prompt &4 exit The expected behavior: C:\...
3
votes
1answer
76 views

Enforcing a “disjoint or identical” constraint

Suppose that F is an Oracle table with two columns, X and Y, such that the constraint CONSTRAINT unique_xy_pairs UNIQUE (X, Y) is satisfied. For each value [x] of F.X, let S[x] represent the set ...
0
votes
1answer
35 views

Enforcing “one-to-one-or-more” vs. “one-to-zero-or-more”

Suppose that I have a mini-world consisting of three types of entities, A, B, and C, subject to the following constraints: each A is associated with 0 or more B's, and each B must be associated with ...
0
votes
0answers
25 views

Oracle - stop dbms_service

I need to stop read-write service on standby so is there a way to lock connection within a specific service or to set a service as read-only? Thanks in advance.
0
votes
1answer
22 views

How to get my result from Procedure

How can i get my out parameters from my procedure. My stl_mbc_cdr table has over 100 records and i cant see my results. declare call_charge VARCHAR(10); MSG_OUT VARCHAR(50); BEGIN FOR ...
1
vote
2answers
34 views

Do I Alter user quota in USERS tablespace?

I'm new to Oracle so please bear with me. After installing Oracle XE 11.2, I created user SALES and granted create session and create table to the user. Once I logged in with SALES I tried to create ...
0
votes
0answers
15 views

dbms_stats.gather_schema_stats - ORA-20011: Approximate NDV failed: ORA-12801: error signaled in parallel query [closed]

We are getting ORA-600 while stat collection, DB - Oracle 12.2 (CDB/PDB) OS - RHEL exec dbms_stats.gather_schema_stats('MYSCHEMA', options => 'GATHER AUTO'); (OR) BEGIN dbms_stats....
0
votes
1answer
64 views

Oracle related to count(*) syntax alternate options [on hold]

i am using a Stored Procedure which are using multiple count(*) in oracle which makes slowness of the server the same i have used in db2 using if exists() which works fine in db2 but the same syntax ...
0
votes
0answers
60 views

ORA-12516: when accessing another database via link

I need to bring some data (blobs) from another database. I created global links to enable bidirectional communication. The links work. When I try to bring some data from tables that have BLOB columns ...
0
votes
0answers
30 views

DDL change tracking [closed]

Is there currently a program that tracks changes in DDL source code in an automated way? Ideally I would be able to track any changes in the DDL and DML to be able to pinpoint any changes in DDL ...
1
vote
1answer
32 views

Access oracle database from control file

When I asked a client for a copy of his database he sent me a .ctl file, it seems it is a control file of an Oracle database. I have no experience in Oracle, I'm a SQL Server guy. I have installed an ...
0
votes
1answer
34 views

Installed Oracle XE 11.2. How do I create a database?

I'm new to Oracle, so please bear with me. I recently installed Oracle XE Release 11.2 on a Windows 2008 Server that we have. The installation was successful. During installation, I was asked to set ...
0
votes
0answers
8 views

LDAP: Empty binddn“” and Authentication type becomes simple from none [migrated]

Am Developing an application in Spring MVC & JPA with Spring Security. Now Integrating the OUD (Oracle Unified Directory) through LDAP. While User authentication the response from the LDAP in OUD ...
1
vote
1answer
41 views

Facing Error 'Missing expression' while trying to create a After insert trigger in PL/SQL

Hi everyone hope you're doing well. AS you might know by now I'm new to PL/SQL and I'm trying to write a trigger as you can see below : CREATE OR REPLACE TRIGGER T_Insert AFTER INSERT ON tag ...
2
votes
0answers
44 views

Incremental Schema Stats (With partitioned/Subpartition table) gathering is taking too much time in oracle version 12.2.0.1

When I am gathering schema stats with below options, it is taking too much time: EXEC dbms_stats.gather_schema_stats ( ownname => 'SCHEMA', granularity => 'AUTO', CASCADE=> true, ...
1
vote
0answers
40 views

Start dbms_services on standby database

I'm developing a trigger which starts and stops services when the pdb is open. The point is that my trigger doesn't stop the service if I execute the ALTER PLUGGABLE DATABASE ALL SAVE STATE; with ...
2
votes
1answer
84 views

Facing error while trying to create a function using 'ACCESSIBLE BY' clause in PL/SQL

Hi everyone hope you're doing well I'm new to oracle PL/SQL and I'm trying to write a function using 'ACCESSIBLE BY' clause but I receive errors . this is my function create or replace function ...
-2
votes
2answers
56 views

Can't startup databse when connected as new user

I have created a new user and I want to do startup the database, but this problem appears: I gave permission but without success
0
votes
0answers
24 views

Oracle Sequence using trigger for partitioned groups of data [duplicate]

Create table T1 ( PK NUMBER ,Grouping Varchar2(50) ,SequenceID Number ); commit; SELECT * FROM T1; PK GROUPING SEQUENCEID ---- ---------- --------- 1 ABC 1 2 ...
1
vote
1answer
35 views

After Inserting , Update / Insert into another table in pl/sql trigger

I am working on PL/SQL trigger i have two table TAG Table SR.NO ID TAG BRAND_ID ------ ---- ----- ---------- 1 10 ABC 99 MY_TAGS Table SR_NO TAG COUNT ...
1
vote
1answer
27 views

Data redaction with regex in Oracle

I would like to redact data with regex in oracle database. I know the procedure but I get unpredictable results. I use the following script to apply redaction to a certain column with card numbers. ...
2
votes
1answer
131 views

Facing error while trying to run dynamic pl/sql

Hope you're doing well I'm new to PL/SQL and I'm trying to write a dynamic PL/SQL. What I've written so far is : declare sql_stmt varchar2(100); v_student_id number := 1; v_new_stname ...
-4
votes
0answers
42 views

Redo Log File Oracle [closed]

Using SQLPLUS tool write and execute the SQL statement with two methods Data Dictionary and Dynamic Performance views. That will give us the count of LOG files.
1
vote
1answer
39 views

Facing error 'invalid SQL statement' while working with cursor variables in PL/SQL

Hi everyone hope you're doing well. I'm new to PL/SQL and I'm trying to learn 'cursor variable' . I have made and example for myself and everything looks correct and I have no idea why it does not ...
0
votes
1answer
45 views

Oracle Custom Sequence of a partitioned group (Insert trigger)

Please help, need an insert trigger to insert the group_ID (next sequential number) of a partitioned group. Much like a dense_rank of repeating group of names. When a new record is created, if the ...
2
votes
1answer
44 views

configure new linked server from SQL Server 2016 to Oracle 12c

I'm having difficulty creating a Linked Server in SQL Server 2016 to talk to a remote Oracle database. I have installed Oracle Database Client 12c. First I installed InstantClient and then later ...
0
votes
1answer
52 views

How to build a query that gets its values from a file

I'm trying to build a query that uses the values in a file select Epm,Emp_num,Emp_Addr From Dir_Rimco_Table where Emp in (Dir_Emp_Rsed.txt) Constraints -I can't create a new table -I can't modify ...
0
votes
0answers
22 views

cx_Oracle high memory use

I am using cx_Oracle with python to select a dataset of about 200,000 rows. I am having memory issues with my application, so I am memory profiling. The memory use for these lines is over 1G, however,...
0
votes
2answers
35 views

Split the results of a query into parts

I got this question in an exam, the teacher asked us to raise the employers' salaries using the following rules using a cursor: An extra 4% for the first quarter with the highest salary. An extra 6% ...
0
votes
0answers
29 views

Everyone can't use the linked server

I'm having an issue with a linked server on MSSQL Sever 2016. I created a link to an Oracle server, TKCS. It uses the Oracle OLEDB drivers installed by the Oracle client (11g). On the security tab ...
0
votes
1answer
23 views

Change LAST_NUMBER value in a sequence (NEXTVAL)

My question seems easy but i haven't found a solution. I'm using the nexval sequence in a insert SQL script but we have errors because the LAST_NUMBER value haven't been updated and its value is not ...
1
vote
1answer
24 views

Prefix scale for UTL_MATCH.JARO_WINKLER_SIMILARITY

This is a repeat of a previous question from several years ago that did not receive an answer. Oracle has a string matching package, UTL_MATCH, which contains the subprogram JARO_WINKLER_SIMILARITY. ...
0
votes
1answer
35 views

Two databases same server with different time zones

I want to have two databases on same server but want the data in each to reflect the time zone of the client. Do I need to adjust database parameters or can this be handled with client (JDBC or OCI) ...
1
vote
2answers
46 views

How to store time values (without date parts) in Oracle? [closed]

I have to decide how time values without date or timezone parts (like java.time.LocalTime) are stored in an Oracle database. My options are Oracle's DATE data type (and "ignoring" the date part) or ...
1
vote
0answers
55 views

Is there a name for this type of data structure?

I'm working with a database that has a structure that few of my colleagues have encountered. The database is a fairly standard Oracle SQL database, but it has a number of tables called xxx_param, ...
1
vote
1answer
48 views

How many users and objects can be in an Oracle Database? [closed]

I wonder if anyone know what can be the biggest existing Oracle databases in the world and how many users (DBA_USERS), objects (DBA_OBJECTS) they can contain? Is there a scenario when a database ...
1
vote
0answers
39 views

Cannot get the new table's FK-constraints in an AFTER ALTER trigger

I'm trying to create a trigger for AFTER ALTER event on schema's tables which would work with the new FK-constraints, here's my test code: CREATE OR REPLACE TRIGGER Test_Trigger AFTER ALTER ON SCHEMA ...
5
votes
2answers
524 views

PL/SQL function to receive a number and return its binary format

I'm trying to write a function to receive a number and return its binary format. This is what I've developed so far and it's ok for inputs : 4,8,16 but it does not return correct binary format for ...
1
vote
2answers
45 views

Facing error while executing function inside the package in PL/SQL

Hi everyone hope you're doing well. I'm new to oracle and I'm reading about the concept of 'Package' and 'Package body'. I have a function as you can see below CREATE OR REPLACE Function ...
-2
votes
1answer
49 views

Oracle: Sending query result through email? [duplicate]

I'm extremely new to oracle and I've been tasked to send the result of a certain SELECT query through email and have it regularly scheduled as well. I've tried looking at other posts as well but I ...
-1
votes
0answers
31 views

Is there any script for oracle listener to restart it

Is there any script that could restart listener when dedicated connections reach to more than 500? Best regards
1
vote
1answer
39 views

How to automate basic ddl scripts execution and management [closed]

Looking for suggestions (A Script, third party tool, software etc.) which can help automate some of the administration tasks around execution of DDL scripts across environments. Database is usually ...
0
votes
2answers
35 views

creating view on “oracle view”

I am using oracle 12c. We have an oracle view ALL_SCHEUDLER_JOBS. As we will loosing the access to this table, I create a view on it as below : the below function is placed inside a package called ...
0
votes
0answers
88 views

Oracle 19c GI Install on OL7 : ASM disks stamped via AsmLib not getting discovered via GridSetup.sh

I am trying to install GI 19c on Oracle Linux 7 (required for 19c), and have used AsmLib to try to achieve device persistence (owner,group) for ASM devices/disks. Prior to that I used udev rules for ...
2
votes
0answers
91 views

Insert statements completion increase over time [closed]

here's my situation: I recently setup an Oracle 18c instance on a virtualized Microsoft Windows Server 2016 (4 cores, 16GB RAM, seperate disk for running oracle and it's datafiles) running a (more or ...
1
vote
2answers
51 views

Issue Moving data from 11g to 18c

I have an rman database backup from Oracle 11g XE which i am trying to import into oracle 18c XE database using below commands rman target / shutdown abort; startup nomount; SET DBID ...
0
votes
1answer
41 views

ORA-01017 when connecting remotely as sysdba

Good morning, While installing a database for Oracle Entreprise Manager Cloud Control (OEMCC) I can't login as sysdba, neither localy using sqlplus SYS@MYSID as sysdba nor remotely using the OEMCC ...
2
votes
1answer
51 views

How can i select columns from two tables using REF

I'm new to Oracle's PL/SQL i'm using 11g. I have 2 Type Objects each one of them with has a table and third table where i keep references of this 2 objects, how can i retrieve values from objects ...
0
votes
1answer
27 views

Insert iterated values from one table to another in OracleDB

I'm pretty new in Oracle universe and I need help. I have to iterate through Users Table and insert these 3 specific rows into Task_Group Table. The hard thing to do is to check if these rows even ...