AskTom - Hot Questions
How frequent should i backup Archive logs
i take complete database backup once daily with following command
<code>
backup as compressed backupset database plus archivelog;
delete noprompt obsolete;
</code>
now i am planning to backup archive log every 5 mints with following command
<code> backup archivelog all; </code>
my archivelog deletion policy is 1 'times to DISK', so there will not be duplicate backups of same log files.
i am doing this because i could not do complete recovery of one of my database due to missing archive log.
my question is that, is this a good approch ? or there is any better way to be able to do complete recovery ?
my RMAN settings are as folows
<code>
configure controlfile autobackup on;
configure backup optimization on;
configure retention policy to redundancy 5;
configure archivelog deletion policy to backed up 1 times to DISK;
configure retention policy to recovery window of 7 days;
</code>
How to find child or parent from data with only ID and Level
Hi Tom,
I have this data
<code>select 1 LineNumber, 1 mylevel, 10 id from dual
union all
select 2 , 2 , 11 from dual
union all
select 3 , 3 , 13 from dual
union all
select 4 , 3 , 14 from dual
union all
select 5 , 4 , 15 from dual
union all
select 6 , 2 , 20 from dual
union all
select 7 , 2 , 30 from dual
union all
select 8 , 3 , 31 from dual
union all
select 9 , 4 , 33 from dual
union all
select 10 , 3 , 32 from dual
union all
select 11 , 3 , 34 from dual
union all
select 12 , 4 , 35 from dual
union all
select 13 , 5 , 36 from dual</code>
I'm look for a way to get
- the child ids recursively of the id = 30.
- the parent id of the id = 30
Cannot read external table or read using utl_file.get_line on Windows 10 with Oracle 18c
I just upgraded to Oracle 18c XE from 11g. I have an external table that worked fine on 11g, but I keep getting the following errors on 18c.
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11604: no directory object specified for log file
The directory does exist and I have the correct grants.
<code>SELECT *
FROM all_directories
WHERE directory_name = 'MYDIR';</code>
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
SYS MYDIR C:\Users\sneuf\OneDrive\PLSQL\OracleDirectory 1
<code>SELECT *
FROM all_tab_privs
WHERE table_name = 'MYDIR'
AND grantee = 'C##_SNEUF';</code>
GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE GRANTABLE HIERARCHY COMMON TYPE INHERITED
SYS C##_SNEUF SYS MYDIR EXECUTE YES NO NO DIRECTORY NO
SYS C##_SNEUF SYS MYDIR READ YES NO NO DIRECTORY NO
SYS C##_SNEUF SYS MYDIR WRITE YES NO NO DIRECTORY NO
I'm pretty sure I'm missing a grant somewhere, but I can't figure out what.
Here is my table:
<code>CREATE TABLE C##_SNEUF.CHECKING_TBL_EXT2
(
DB_KEY NUMBER,
CHECK_NUM VARCHAR2(10),
TRANS_DATE TIMESTAMP (6),
DESCRIPTION VARCHAR2(100),
DEPOSIT_WITHDRAWAL VARCHAR2(1),
AMOUNT VARCHAR2(12),
MEMO VARCHAR2(200)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY MYDIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
BADFILE MYDIR: 'checking.bad'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
LDRTRIM
MISSING FIELD VALUES ARE NULL
(
DB_key CHAR,
check_num CHAR(10),
trans_date CHAR(21) DATE_FORMAT DATE MASK 'MM/DD/YYYY HH24:MI:SS',
description CHAR(100),
deposit_withdrawal CHAR(1),
amount CHAR(12),
memo CHAR(200)
)
)
LOCATION
( MYDIR: 'checking.csv'
)
)
REJECT LIMIT UNLIMITED ;</code>
Thanks, Steve
How to dynamically transpose data into with changing column headers with or without pivot/pivot xml?
This question is most common with no clear solution.
I have 2 scenarios for dynamic pivoting and the solution requested could be using pivot xml(tried, but extracting is a task)/dynamic sql execution/arays if possible we could use..
Scenario 1:
In the first case,I i need to pivot the data where data source is same table. However the pivot needs to be dynamic as the columns header would keep changing as per column app_id. So if app_id=1. The column header would be A,B,C,D, If app_id=2, column would be CDEF and so on. Also each set of value has an id. So for id, 120 and app_id=1 , column A,B,C,D, would display the values and so on.
The current sample data has only 2 app_ids, but there could be many more, so app_id and labels would kepe changing thus i need to write a dynamic query.
Table is DATA_1
<code>ID label value app_id
--- ----- ----- ------
120 A Alpha 1
120 B Beta 1
120 C Class 1
120 D Delta 1
120 C Alpha 2
120 D Beta 2
120 E Class 2
120 F Delta 2</code>
And expected output would be something like this.
P.S. Data is dummy and app_ids, and ids would keep getting inserted and removed with changing column headers,so a dynamic solution is needed.
<code>SELECT * FROM data WHERE ID = 120 AND app_id = 1;
app_id A B C D ID
------ ------ ----- ----- ----- -----
1 Alpha Beta Class Delta 120
SELECT * FROM data WHERE ID = 120 AND app_id = 2;
app_id C D E F ID
------ ------ ----- ----- ----- -----
2 Alpha Beta Class Delta 120</code>
Scenario 2:
Here were had the data in one table. Now we have separate table containing the labels and current table with values.
SO in table from scenario one we will focus on id, app_id and value columns only.
Select app_id,id,value from data_1;
The labels will come from another table DATA_Labels in column Header_Name:
<code>APP_ID SEQ HEADER_NAME
1 1 A
1 2 B
1 3 C
1 4 D
1 5 E
1 6 F
1 7 G
1 8 H
1 9 I
1 10 J
2 1 P
2 2 Q
2 3 R
2 4 S
2 5 T
2 6 U
2 7 V
2 8 W
2 9 X
2 10 Y</code>
So, for labels we would use header_names. ANd Data_1 and Data_Labels could be joined on basis of app_id.
In case the values exceed column headers, we can assume that the headers would reach say maximum 20. And in case the header/name is not available, it could put some default header name like COL11,COL12 if the value stretches upto there.
I did lot of research but most solutions are too complex and confusing. Any leads would be appreciated.
Operations preserving row order
Hi Tom, a fan of your work.
Have a question: are there any operations in Oracle preserving row order?
For example, can I expect that
<code>
select * from (select tag from test order by tag)
</code>
Will return in sorted order?
Or if a pipelined table function produces a dozen rows in certain order, can I use
"select * from table(f())"
to see them in the same order?
Will a cursor read rows from a pipelined function in the same order they are piped?
Basically, looking for exceptions to the general rule "any operation destroys row order".
How to extract specific tags from a clob colum storing XML
I have a clob column that as different tags in it, like the example below, I am trying to get the comments tag of all the rows, one of them is returning null, I am assuming it is because it has the word "comments" more than once, this is the query I am using:
<b>select d.d1_activity_id, dbms_lob.substr(d.bo_data_area, dbms_lob.getlength(d.bo_data_area), 1) as DCLOB, extractValue(xmlparse(contentt d.bo_data_area),'comments' ) AS comnt from d1_activity d where dbms_lob.instr(d.bo_data_area,'comments') > 0
</b>
This is an example of the data we have in that column:
<code><comments>C2M Test Exchange Meter</comments><instructions>C2M Test Exchange Meter</instructions><replyToExternalSystem>D1YS</replyToExternalSystem><retryDetails><numberOfRetries>0</numberOfRetries><isToDoEntrySuppressed>false</isToDoEntrySuppressed></retryDetails><contactDetails/><connectNewDevice>D1CN</connectNewDevice><oldDeviceId>061840493997</oldDeviceId>
<isFieldActivityCompleted>D1NO</isFieldActivityCompleted><isAppointmentNecessary>N</isAppointmentNecessary><appointmentWindow/><comments>C2M Test for M-Exchange Orch to PragmaCad</comments><instructions>C2M Test for M-Exchange Orch to PragmaCad</instructions><isMeasurementFound>D1NO</isMeasurementFound><replyToExternalSystem>D1YS</replyToExternalSystem><retryDetails><numberOfRetries>0</numberOfRetries><isToDoEntrySuppressed>false</isToDoEntrySuppressed></retryDetails><allowParentTransition>true</allowParentTransition><overrideRestrictions>D1NA</overrideRestrictions><fieldWorkSystemAddress><address1>3456 BOWDEN CIR W</address1><address4>15305034560000><193954</address4><crossStreet>6249</crossStreet><city>JACKSONVILLE</city><county>DUVAL</county><postal>32216</postal><country>USA</country><state>FL</state><geocodeLatitude>0.000000</geocodeLatitude><geocodeLongitude>0.000000</geocodeLongitude></fieldWorkSystemAddress><contactDetails/>
<updateSpecificActivity>D1YS</updateSpecificActivity><updateableItems><comments>Editing comments</comments><instructions>Editing comments</instructions><startDateTime>2020-10-27-00.00.00</startDateTime></updateableItems><isAppointmentNecessary>N</isAppointmentNecessary><appointmentWindow/><allowParentTransition>true</allowParentTransition><replyToExternalSystem>D1YS</replyToExternalSystem><retryDetails><numberOfRetries>0</numberOfRetries><isToDoEntrySuppressed>false</isToDoEntrySuppressed></retryDetails>
</code>
Extracting data elements from Json within PL/SQL
I am building a package procedure that will accept a string of json as the input. In SQL, I can select a single value from the json object, but, using select x into var from ... in pl/sql throws an error:
<code>
ORA-06550: line 10, column 12:
PL/SQL: ORA-19200: Invalid column specification
</code>
<code>
with jdata as (
select treat('{"practitionerId":12345,"gender":"F"}' as json) as jrow
from dual
)
select j.jrow.practitionerId
from jdata j;
declare
p_input_json varchar2(32767) := '{"practitionerId":12345,"gender":"F"}';
p_practitioner int;
begin
with jdata as (
select treat(p_input_json as json) as jrow
from dual
)
select j.jrow.practitionerId
into p_practitioner
from jdata j;
end;
/
</code>
Several hours of searching docs and ye olde internet, I cannot find a method of extracting scalars from json in pl/sql. Can you point me in the right direction?