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

Automatically purge dropped tables in Oracle

I am using an Oracle 18c database in a build system. One of my builds is for a JPA/Hibernate project, which is configured as "create-drop". This means it will create all tables during startup and ...
0
votes
0answers
17 views

Data replication between Oracle and Postgres

Is there a way to replicate the DML changes that happen in a Postgres table into a table in Oracle database? My Postgres database is RDS in AWS environment. So I am unable to use the extension ...
0
votes
1answer
21 views

Oracle security script during database refresh/restore activities [on hold]

I am looking for oracle shell scripts that are used by DBA as part of security provisioning during the refresh/restore activities . Is anyone done similiar thing like DB provisoning ,user access ,and ...
-1
votes
1answer
33 views

Monitoring of Oracle Databases

Can I monitor all databases on different hosts with single cloud control? Do I need to install agent on each server? Do I need any license ?
0
votes
0answers
19 views

Import Schema dump into multiple schemas

I have a production schema and for upgrade they have decided to use 3 schema instead of one. For deploying this into production I have to take dump of schema A and import into 3 different schema with ...
0
votes
0answers
22 views

Is there any way to group only syntax errors from exceptions given by oracle server?

Basically the exceptions thrown by oracle are in the form, say for example 'ORA-01756','ORA-00907' etc. I would to know if there is a way to filter out just syntax errors may be something like a list ...
0
votes
1answer
25 views

percentage of count in Oracle

I'm trying to get percentage of payments according to status every day. I found a MySQL query which throws an error at Oracle when I use '=' inside SUM parentheses: "ORA-00907: missing right ...
0
votes
1answer
44 views

Enable archivelog

I'm trying to enable archivelog mode in Oracle database. For that I shutdown the services and used startup mount. Instead of database getting mounted, I'm getting error ORA- 12528 TNS - All instances ...
2
votes
1answer
41 views

alter database backup controlfile vs cp Linux file

We have the following explanation in the documentation: Back up the control file to a binary file (duplicate of existing control file) using the following statement: ALTER DATABASE BACKUP ...
0
votes
2answers
39 views

Detect if the family record is incomplete to avoid partial deletion

MS SQL SERVER and ORACLE. I have a table Acct table that has acct_id and parent_acct_id attribute. Parent_acct_id on children account points to acct_id on parent account. If no parent, then ...
0
votes
1answer
23 views

RMAN log file with date and name in Oracle 11gR2 in Linux

I have a script and I want to create my log file with datetime and with unique name. It's my script: ....... ....... rman target/ LOG='/u01/backup/logs/rman.log' <<EOF ....... ....... ....... ...
0
votes
2answers
28 views

Convert Text Column Web Service Data to Columns

I have a database table which stores web transactions for a fundraising donation page. One of the columns in the table stores a bunch of concatenated data in the format 'data1=value1 data2=value2 ...
0
votes
2answers
60 views

Variables in Oracle Triggers

Context Every few weeks the database guy at work backs up the production database and then several weeks later imports it into the development database. I don't know why it takes so long between ...
0
votes
1answer
25 views

Using of delete obsolete in Oracle 11gR2 RMAN on Linux

I'm using crontab for automatic backup (full backup). It works 2 times in a day (8 AM and 23 PM). In my script I use delete obsolete but I have a question. I want to delete old backups without last 2 ...
0
votes
1answer
45 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 ...
0
votes
0answers
38 views

Schema size in Oracle [closed]

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
1answer
28 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 ...
-1
votes
1answer
35 views

How to know needed UNDO_RETENTION for a specified query of long duration? [on hold]

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 ...
1
vote
1answer
67 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 ...
3
votes
1answer
165 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 ==== ======= =================== =====...
0
votes
3answers
47 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
42 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 ...
0
votes
1answer
34 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 ...
3
votes
1answer
49 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'...
-5
votes
3answers
61 views

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

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 ...
0
votes
0answers
37 views

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

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
vote
1answer
38 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
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(); ...
0
votes
1answer
42 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
39 views

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

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
4answers
58 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
0answers
34 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 ...
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 ...
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 ...
0
votes
0answers
30 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 ...
1
vote
3answers
72 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'...
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
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 ...
1
vote
0answers
73 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 ...
0
votes
1answer
41 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 ...
0
votes
0answers
33 views

Case inside case in oracle query

Can anyone help me? I want to use query using CASE WHEN with logic where time less than 03:00 PM on Monday until Friday will get value start from tomorrow 03:00 PM until today 03:00 PM SELECT ...
0
votes
0answers
29 views

Why expdp does not include identified by statement?

I have the following error when I do a impdp (from a pdb in a 12c to 11g. I also used the parameter version=11.2.0) IMPDP LOG: ORA-01938: IDENTIFIED BY must be specified for CREATE USER Failing sql ...
1
vote
0answers
15 views

Can multiple process pull off the same record from an Oracle FIFO queue?

My understanding of Oracle AQ (Advanced Queuing) is that no two (or more) processes can pull off (drain/consume) the same record of an Oracle FIFO queue. Is this correct or is some code needed to ...
1
vote
0answers
33 views

How to know what function called before in nested function in Oracle PL/SQL [closed]

I have some functions and need to know which function is called before a certain function. Are there any ways to find out? I have heard about the UTL_CALL_STACK package that is supplied with Oracle. ...
2
votes
1answer
61 views

ORA-00904: Invalid Identifier when selecting from view [on hold]

I am attempting to view a view with: select * from DISCOUNT_AMOUNTS; ERROR at line 1: ORA-00904: "SALE_GIFT_VOUCHER_AMOUNT_AMOUN": invalid identifier I then check the view to see what is there in ...
1
vote
2answers
47 views

Export SQL query output to a file in a scheduler job

I need to export data to a csv file in a scheduler job. Here are the commands: SPOOL "\\AServerName\Output\RMS_DataFileSizes.csv" REPLACE; SELECT /*csv*/ TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS ...
0
votes
0answers
31 views

Oracle 11.2.0.1.0 showing “no matching authentication protocol”

I have installed the above oracle version (personal edition) for my developmental purpose. But when I try to login through sqlplus, always it throws me the error no matching authentication protocol. ...
-1
votes
1answer
43 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
43 views

How to get TNS connect strings (like tnsping) to check a dblink via a session?

I would like to know, if there is any chance to get the currently used TNS connect string, that will be used for a DB-Link from a db-server. I'm connected to a schema on a database located on any ...
0
votes
1answer
41 views

It's a good practice always restore controlfile before restore the database?

I'm beginner in Oracle, but I'd like know if it's a good practice always restore the control-file of a backup-set before restore the database. I ask this because always when I restore the control-file ...