We’re rewarding the question askers & reputations are being recalculated! Read more.

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

SQLcl: set color in sqlprompt (ANSI/VT100 Control sequences)

This is my login.sql SET sqlprompt "_user'@'_connect_identifier > " SET sqlformat ansiconsole SET serveroutput on SET lines 3000 How to set the sqlprompt to color red for Prod (and green color ...
-1
votes
1answer
22 views

How to know needed UNDO_RETENTION for a specified query of long duration?

I'd like to know whether there is some way to know the undo_retention needed for a specified query with long duration that need keep the consistence. In other words, I'd like to know how long my ...
0
votes
0answers
22 views

Schema size in Oracle

I have this query - SET SERVEROUTPUT on; DECLARE input_owner NVARCHAR2(128) := 'APP USER'; segment_size_blocks NUMBER; segment_size_bytes NUMBER; used_blocks NUMBER; ...
0
votes
0answers
18 views

Oracle 11gR2 can I delete datafiles to reduce tablespace?

I have the following doubt, maybe a little silly, if I have a tablespace for example TBS_MYDATA of 155 GB, this tablespace is composed of 5 datafiles of 31GB each, in turn, each of these datafiles ...
-5
votes
3answers
60 views

Oracle trigger with checking day routine doesn't work correct [on hold]

CREATE OR REPLACE TRIGGER SYS.Restict_user AFTER LOGON ON DATABASE BEGIN IF(TO_CHAR(SYSDATE, 'DAY')) = UPPER('SUNDAY') OR (TO_CHAR(SYSDATE, 'DAY')) = UPPER('SATURDAY') THEN ...
1
vote
1answer
658 views

Oracle losing connection ( ORA-03114 ) after some time not using the software

After some time idle, if users try to use the our system, we receive the error: ORA-03114 - not connected to Oracle. I know there's a parameter called SQLNET.EXPIRE_TIME. the recomendation is ...
0
votes
2answers
35k views

ORA-01830: date format picture ends before converting entire input string

I am trying to run the query on an Oracle database select to_date(trunc(sysdate) || '13:00:00','DD-MON-YY HH24:MI:SS') from dual; and get the error ORA-01830: date format picture ends before ...
0
votes
1answer
18 views

Oracle SQL query - select or rows without all ids in other tables

I need to delete customers if customer not have any accounts or all of accounts not have any operations (no records in id_customer, id_account_sender, id_account_receiver in operation table). example ...
0
votes
3answers
41 views

Oracle 12c and Jobs running one hour apart after changing time for hourly use

I have an Oracle 12c database in which I have detected that the scheduled jobs are running one hour apart after the change of time on the server, checking in the database I see that the SYSTIMESTAMP ...
0
votes
1answer
147 views

How could we start Oracle database in Oracle Exadata which's celldisk has proactive failure?

I am trying to open exadata machine but it has error. That EXADATA  Oracle Sun Fire X4720 M3 SAS, Oracle 11gr2 RAC database installed. I was examining celldisks. When i gave command list griddisk I ...
-1
votes
1answer
103 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 ...
3
votes
1answer
161 views

SQLcl history (up-arrow) rewrites my queries

Version sql -version SQLcl: Release 18.4.0.0 Production I type this: SQL> exec dbaspace.long_ops; SID % Done Start Time Rem [s] Elapsed Message ==== ======= =================== =====...
1
vote
1answer
102 views

oracle slow query with diferent user

I have a question with an oracle query that takes almost 20 minutes when it is executed from a schema and yet when it runs as sys users it runs in 1 second maximum ... how is this possible? ... I do ...
0
votes
0answers
35 views

Research paper related to Database administration with good citations = [on hold]

I wanted to ask that if this is possible to let me know about some research paper related to database administration which has got very good number of citations. I have done research on my own ...
-1
votes
1answer
42 views

Oracle database - how to switch instance

I'm totaly new in oracle, so sorry for my probably stupid question... I have two instances on oracle server, when I'm connecting to the server via sqlplus in default opens connection to instance X but ...
0
votes
1answer
278 views

PL/SQL Procedure: limit number of INSERTS

I have a procedure that is supposed to take an employee from a certain table and insert it into another table that records who is in a leadership role for a certain period. The thing is, there can ...
0
votes
1answer
40 views

Oracle Tablespace not using 100% of available space

About a year ago, we added a new tablespace to our Oracle 11g RAC database and we are now using all of the space and are running out of room occasionally. I check on the space available with this ...
2
votes
1answer
43 views

RMAN - How to add date in backup tag

I know that the default of the RMAN is to add the date in the following format: TAGYYYYMMDDTHHMMSS, But if I wanted something like this: BKP_FULL_11112019. How I do this? I tried using %T but it does'...
0
votes
4answers
53 views

Alternative to Triggers

I am working on an oracle database and want to record the exact time of any transaction happening on a specific table in the schema. Using triggers on that table is out of question as the client do ...
0
votes
1answer
31 views

Cannot connect database as sys as sysdba using sqlplus

I am new to oracle server 18c (currently taking online course). My problem is sql*plus where I tried to use sqlplus then sys as sysdba (it did not work) and the error appeared below. However, if I ...
1
vote
1answer
34 views

can I do a restore controlfile from autobackup out FAST RECOVERY AREA?

I know that I can issue the command restore controlfile from "location_here". But there is some config that can I do for issue the command restore controlfile from autobackup even whether the ...
0
votes
1answer
127 views

Getting sum result for last year divided quarterly

I am trying to get financial data grouped by each quarter of last year and also grouped by the last three weeks. Also, if it is possible, I want to have the type as a separate column with the type as ...
0
votes
2answers
119 views

Simulate pipelined order by in oracle 11g

I have been working with an application that is integrated with spring and Hibernate 4.X.X and its transaction is managed by JTA in Weblogic application server. After 3 years, there are about 40 ...
1
vote
3answers
71 views

Are there any reasons why a table wouldn't show in sys.dba_tables? (Oracle)

I have 2 queries SELECT * FROM sys.dba_indexes WHERE Table_Name = 'XDB$RESOURCE' and SELECT * FROM sys.dba_tables WHERE Table_Name = 'XDB$RESOURCE' Are there any reasons why a table wouldn'...
0
votes
1answer
250 views

Oracle 11gR2 Enterprise manager problem

I joined a company as a fresher DBA and in testing server there is weird problem. My listener is running very well and developers can connect remotely to database but whenever i tried to start ...
0
votes
2answers
111 views

Connection denied from Java application

I try to connect from my little Java class to Oracle11g database and connection was refused whit SQL error. From Client (SQL Developer) connection works. This is the ERROR when I try to run java ...
0
votes
1answer
36 views

How To Create A New bash_profile And Connect New Database To It In Oracle [on hold]

OS -> Linux DB -> Oracle 11gR2 Normally my .bash_profile file looks like this: TMP=/tmp; export TMP TMPDIR=$TMP; export TMPDIR ORACLE_HOSTNAME=abcde.localdomain; export ORACLE_HOSTNAME ...
0
votes
1answer
25 views

Oracle JSON ADT in Collection

Why is the table() operator not working? create or replace type tmp_011040_t2 as table of (JSON_OBJECT_T) NOT PERSISTABLE; declare l_objects tmp_011040_t2; begin l_objects := tmp_011040_t2(); ...
-1
votes
2answers
91 views

Is there something similar to PgPool-II for oracle

I am running apps in kubernetes, which for even one app, it may have multiple containers; thus, the jdbc pool won't work. Is there a layer similar to PgPool-II for oracle database? Thanks in advance
0
votes
1answer
41 views

How to remove these duplicate records in an Oracle Database 12c?

I have these two tables: messages(id primary key, message_date, created_at, ...) user_messages(user_id, message_id references messages(id)) I have some duplicate rows in messages: select user_id, ...
0
votes
1answer
29 views

How to combine subqueries?

There are two subqueries select a set according to certain criteria, such as select id, dt_beg, dt_end from ................... where .............. it is necessary to select data from the ...
1
vote
2answers
5k views

Swap utilization is high on Oracle server

How do I reduce swap utilisation on a Linux server which is running as an Oracle 11gR2 Server? Currently it shows above 80% in Enterprise Manager. How do I identify which processes use swap memory ...
0
votes
0answers
31 views

ORA-01466: unable to read data - table definition has changed

What happen when is done a import from a export with this erro? I have a table that every day is altered the structure during the consistent export (flashback_time=systimestamp) and I always receive ...
-2
votes
1answer
19 views

No data in Partitions columns [closed]

Data got inserted in Table but it is showing null columns (Last_Analyzed,Num_Rows,Blocks,Sample_size) in partition section of the Table in Oracle 12c
1
vote
3answers
127 views

Partial local index on partitioned Oracle table

We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added. We need to do selects on this table, over a period of the last five days, and ...
0
votes
0answers
25 views

Difference in converting varchar2 column to number in Oracle 11 and 12

I have a table tblA ( ID varchar2(5), DESC varchar2(200) ) which includes data: '1', 'One' '2', 'Two' My query is something like: select anotherTbl.ID, T.txt from anotherTbl, (select 0 as ...
-1
votes
2answers
119 views

ORACLE Table insertion

I want to insert data into below table, but I got an error. How do I solve this? CREATE OR REPLACE TYPE TEST_TYP FORCE IS OBJECT ("id" VARCHAR(5000 NULL) NOT FINAL; CREATE OR REPLACE TYPE TEST_TAB ...
0
votes
0answers
29 views

Oracle Express 18c install is hanging

Trying to Install Oracle Express 18c on Windows 10/x64 machine. Constantly hangs on "Creating Oracle Listener". Looking in the install log file does not show any error. Log files are huge, but can ...
0
votes
1answer
86 views

error writing input to commandERROR: Invalid username and/or password

This is the error I have been getting on my RMAN notifcations on my backup jobs for the last day or so. The title is exactly how the message is formatted. ~~~End Step Output/Error Log~~~ Error Log:...
1
vote
0answers
22 views

Are LOBs also backed up if I take dump of all partitions of table individually?

I am taking export of a large table in oracle through datapump. I am taking export of all partitions one by one. The table has a LOB column as well. Are LOBs also backed up with partitions? Because ...
-1
votes
1answer
255 views

Oracle Database is taking too much waiting time

Yesterday I have found our database was taking too much wait time. It is a single instance Oracle 12c with around 55gb of SGA memory and 12 CPU cores. On Enterprise Manager, I have found 5 ADDM ...
0
votes
1answer
175 views

How to migrate entire database to new physical machine [closed]

I'm in need of migrating my entire database to a new VM set up in a remote physical server. I'm using Oracle 11g enterprise edition, in noarchivelog mode which is running in Windows environment. What ...
0
votes
2answers
117 views

Summarizing Pay Checks Based on Employee's Job Code

What I'm trying to do is summarize an employee's earnings for each job code they worked in for the year. Employees are assigned a job code based on an effective date. I'm trying to line up the job ...
0
votes
0answers
68 views

Oracle 12c Datapatch Error

We have an old Oracle 12c database server that is not up to date (version 12.1.0.1.0). Unforunately a full version upgrade is out of scope right now, but I was hoping to at least apply the latest ...
1
vote
0answers
32 views

Does the target database need to have the same structure of the source database before to use IMPDP?

When I do a import from another database have I need the same estruture of the source database in the target database? For example, if my source database use ASM and my target database no, what have ...
0
votes
1answer
34 views

Extend Trigger Script after Create on Schema (grant select on table,sequence and view) [closed]

can anyone help me to extend this script below? I would also need a trigger for views and sequences CREATE OR REPLACE TRIGGER JDTEST.DO_GRANT after CREATE on jdtest.schema declare l_str ...
-1
votes
1answer
251 views

INSERT and UPDATE in Trigger

I have created a trigger so I could insert every row in a backup table CREATE OR REPLACE TRIGGER xyz AFTER INSERT ON table_1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO table_2 (...
0
votes
1answer
40 views

How to query Last inserted row for each group in oracle 10

I have two tables about users where I want to join two tables and show the latest remarks of each user bellow is table I want to query the latest remarks for each user (1 remark per user) bellow ...
6
votes
2answers
3k views

How do I delete a file that has been locked by UTL_FILE?

I wrote a flakey PL/SQL script that crashed out and forgot to close the file handle (type UTL_FILE.file_type). Now I can't delete the file. Short of restarting the instance or rebooting the box, is ...
-4
votes
2answers
1k views

use the Union condition inside the Case block in Oracle

How can we use the Union condition inside the Case block in Oracle. Trying the below code but is not working. SELECT NVL(SUM(DECR(BUDGET, 'AcKnOw%eDg3h1dD3N$4mH3RE')),0) AS budget INTO v_budget FROM ...