Feed aggregator
multi-table insert
Tom:
I was interested in using the multi-table insert feature in 9i but am stumped by the restriction that the subquery can't contain a sequence. I use a sequence to assign a Primary Key in one of the tables that is the target of the multi-table i...
Categories: DBA Blogs
How to find the begining of path and end of path
Hi,
I would like to know if there is anyway it return the beginning of the hierarchy and end of hierarchy for the code below.
<code>
WITH o AS
(
SELECT 'A' as obj,
'C' as link
FROM dual
UNION ALL
SELECT 'C',
...
Categories: DBA Blogs
bulk operation on multilevel collection -
Example 3-22 Using UPDATE to Insert an Entire Multilevel Collection
INSERT INTO region_tab (region_id, region_name) VALUES(2, 'Americas');
DECLARE
v_country nt_country_typ;
BEGIN
v_country := nt_country_typ( country_typ(
'US', 'Uni...
Categories: DBA Blogs
Audit records no longer being captured
Hi all, I want to ask about auditing database.
So I have a problem like this. I have a table that I have audited. Example we called it table x. Audit data in table X is recorded until the 14th. However, on the 15th onwards, no audit data is record...
Categories: DBA Blogs
Difference Between IMPDP Method
Hello Team
a while ago, I had a problem with a DB import. I managed to get it, but there's a question still remain in my head
1. what is the difference between
<code>
expdp system/system schemas=ieulive directory=dir1 dumpfile=dump1.dmp logfi...
Categories: DBA Blogs
Listagg Vs Stragg
Tom:
I was benchmarking stragg with listagg and results below, is that stragg is better than listagg? Please let me know your thoughts on this.
<code>drop table t purge;
create table t as
select *
from ( select *
from all_objects
...
Categories: DBA Blogs
Difference between select * and select a.* with alias on a table
Hi Tom,
What is the difference between using "select * from table1" and "selelct a.* from table1 a".
Observed that while doing the select statement
"select a.* from table1 a" locks the data for update (like if we use the statement from PLSQL dev...
Categories: DBA Blogs
BLOB Columns In tables
Hi Tom,
This is a general question about the design of the tables in a DAILY use database. The users will uploading and downloading pdf files from a web app daily. What i want to ask is it a good idea to create my tables with BLOB columns and savi...
Categories: DBA Blogs
CLOB to XMLTYPE out of memory issue
I am copying XML data from Operating System file into CLOB and then casting as XMLTYPE for extracting information into relational database.
This solution is working for years but I received a business requirement to process big XML files ranging fro...
Categories: DBA Blogs
Materialized View On Commit Refresh failure
Hi Team,
Thanks a lot for all the support you have provided so far :)
I have created an On Commit Materialized view MVA recently on tableA. Primary Key M View log is used here.
Occasionally this refresh fails with ORA-12034 error.
Since thi...
Categories: DBA Blogs
explain plan for index scan
Hi Tom,
i have updated the plan.i was unaware how to submit the further information on the questions asked, thats why put the questions again.
this is with reference to question asked at below link
https://asktom.oracle.com/pls/apex/asktom.sear...
Categories: DBA Blogs
Full Table Scan on join query
Hello, Ask TOM Team.
I have two tables: TABLE1 (parent) and TABLE2 (child). TABLE1_ID (FK) on TABLE2 table has an index. When I run a join query, I got a table access full. There are ~4M rows in the result set.
<b>select * from user.table1 t ...
Categories: DBA Blogs
SQL Versus ASMCMD
This is a set of scripts to help on Oracle ASM Monitoring
Reviewing the SQL looks rather complicated especially when trying to find information for specific Disk Group.
Use one command line to retrieve data or create complex SQL to do the same?
Size_MB is Bytes/1e6
Alloc_MB is Space/1e6
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
SQL> @asm.sql
System
ASM File Name / Volume Name / Device Name Size_MB Alloc_MB File Type Creation Date Created?
------------------------------------------------------------------------------- ------------------ ------------------ ------------------ -------------------- --------
+ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.311.990946061 0 1 PARAMETERFILE 31-OCT-2018 06:47:40 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Backup.313.990988403 43 50 CONTROLFILE 31-OCT-2018 18:33:22 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Current.257.990939051 0 8 CONTROLFILE 31-OCT-2018 04:50:50 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Current.258.990939051 0 8 CONTROLFILE 31-OCT-2018 04:50:50 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/current.256.990939051 43 50 CONTROLFILE 31-OCT-2018 04:50:51 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AUDIT_TBS.264.996655365 32,212 32,247 DATAFILE 04-JAN-2019 08:42:44 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AUDIT_TBS.319.996656095 1,074 1,076 DATAFILE 04-JAN-2019 08:54:55 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/AVAIL.277.990939537 701 703 DATAFILE 31-OCT-2018 04:58:56 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_ORDER.271.990939353 32,212 32,247 DATAFILE 31-OCT-2018 04:55:53 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_ORDER.316.992686897 315 317 DATAFILE 20-NOV-2018 10:21:37 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EUR_PDA.321.1007139241 11,035 11,037 DATAFILE 01-MAY-2019 16:54:01 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_IE.270.990939353 3,821 3,823 DATAFILE 31-OCT-2018 04:55:53 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_UK.269.990939199 33,521 33,555 DATAFILE 31-OCT-2018 04:53:18 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/EZFETCH_UK.279.990939547 6,946 6,948 DATAFILE 31-OCT-2018 04:59:06 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_ESS.284.990939589 105 107 DATAFILE 31-OCT-2018 04:59:49 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAS_OPSS.285.990939591 126 128 DATAFILE 31-OCT-2018 04:59:50 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAS_UMS.287.990939593 703 705 DATAFILE 31-OCT-2018 04:59:53 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_IAU.289.990939595 63 65 DATAFILE 31-OCT-2018 04:59:55 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_MDS.282.990939585 734 736 DATAFILE 31-OCT-2018 04:59:45 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.290.1010082787 32,212 32,247 DATAFILE 04-JUN-2019 18:33:07 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.292.1010082785 32,212 32,247 DATAFILE 04-JUN-2019 18:33:04 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.314.1010082781 32,212 32,247 DATAFILE 04-JUN-2019 18:33:00 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.318.1010082769 32,212 32,247 DATAFILE 04-JUN-2019 18:32:48 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_SOAINFRA.326.1013130689 13,342 13,344 DATAFILE 09-JUL-2019 01:11:28 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_STB.288.990939595 10 12 DATAFILE 31-OCT-2018 04:59:54 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/FMWEURPD12_WLS.286.990939591 63 65 DATAFILE 31-OCT-2018 04:59:51 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/IFW_DT_TBS.280.990939551 12,885 12,887 DATAFILE 31-OCT-2018 04:59:10 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/IFW_IDX_TBS.281.990939585 1,074 1,076 DATAFILE 31-OCT-2018 04:59:45 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/KOMPASS.274.990939395 34,360 34,361 DATAFILE 31-OCT-2018 04:56:35 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/KOMPASS.324.1028374119 13,627 13,629 DATAFILE 30-DEC-2019 11:28:39 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/MBSYNC_UK_MCD.291.990939597 25,166 25,200 DATAFILE 31-OCT-2018 04:59:57 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.267.990939163 34,360 34,361 DATAFILE 31-OCT-2018 04:52:42 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.278.990939539 33,580 33,589 DATAFILE 31-OCT-2018 04:58:59 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PDA_STG.325.1014480263 6,107 6,109 DATAFILE 24-JUL-2019 16:04:23 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/PERFSTAT.320.1001228979 2,961 2,963 DATAFILE 26-FEB-2019 07:09:39 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.260.990939059 34,341 34,361 DATAFILE 31-OCT-2018 04:50:59 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.268.990939163 9,026 9,028 DATAFILE 31-OCT-2018 04:52:43 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSAUX.275.990939395 32,212 32,247 DATAFILE 31-OCT-2018 04:56:35 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/SYSTEM.259.990939059 11,996 11,998 DATAFILE 31-OCT-2018 04:50:58 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS1.261.990939059 34,360 34,361 DATAFILE 31-OCT-2018 04:50:59 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS1.283.1041158311 4,295 4,297 DATAFILE 23-MAY-2020 10:38:30 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS2.263.990939063 34,360 34,361 DATAFILE 31-OCT-2018 04:51:03 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/UNDOTBS2.323.1041061103 34,359 34,361 DATAFILE 22-MAY-2020 07:38:23 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/USERS.262.990939059 7 7 DATAFILE 31-OCT-2018 04:50:59 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/WEBMDATA.272.990939357 27,259 27,281 DATAFILE 31-OCT-2018 04:55:57 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/WEBMINDX.273.990939393 1,074 1,076 DATAFILE 31-OCT-2018 04:56:32 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/ORCL_SID_PHX1RAC.312.990949397 0 1 DATAGUARDCONFIG 31-OCT-2018 07:43:17 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_1.293.990939821 1,074 1,076 ONLINELOG 31-OCT-2018 05:03:41 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_10.300.990939985 1,074 1,076 ONLINELOG 31-OCT-2018 05:06:25 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_11.301.990940017 1,074 1,076 ONLINELOG 31-OCT-2018 05:06:56 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_12.302.990940049 1,074 1,076 ONLINELOG 31-OCT-2018 05:07:28 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_13.303.990940081 1,074 1,076 ONLINELOG 31-OCT-2018 05:08:01 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_14.304.990940083 1,074 1,076 ONLINELOG 31-OCT-2018 05:08:02 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_2.294.990939853 1,074 1,076 ONLINELOG 31-OCT-2018 05:04:12 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_21.305.990940085 1,074 1,076 ONLINELOG 31-OCT-2018 05:08:04 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_22.306.990940115 1,074 1,076 ONLINELOG 31-OCT-2018 05:08:35 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_23.307.990940179 1,074 1,076 ONLINELOG 31-OCT-2018 05:09:38 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_24.308.990940241 1,074 1,076 ONLINELOG 31-OCT-2018 05:10:40 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_25.309.990940273 1,074 1,076 ONLINELOG 31-OCT-2018 05:11:12 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_26.310.990940273 1,074 1,076 ONLINELOG 31-OCT-2018 05:11:13 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_3.295.990939915 1,074 1,076 ONLINELOG 31-OCT-2018 05:05:15 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_4.296.990939917 1,074 1,076 ONLINELOG 31-OCT-2018 05:05:17 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_5.297.990939919 1,074 1,076 ONLINELOG 31-OCT-2018 05:05:19 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_6.298.990939951 1,074 1,076 ONLINELOG 31-OCT-2018 05:05:51 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/group_9.299.990939953 1,074 1,076 ONLINELOG 31-OCT-2018 05:05:53 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/FMWEURPD12_IAS_TEMP.317.991419415 18,822 18,824 TEMPFILE 05-NOV-2018 18:16:55 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/NA_ARCHIVE_TEMP.322.1007140587 10,737 10,740 TEMPFILE 01-MAY-2019 17:16:26 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/TEMP.315.991419415 34,359 34,361 TEMPFILE 05-NOV-2018 18:16:54 Y
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/dr1ORCL_SID.dat 0 1 DATAGUARDCONFIG 31-OCT-2018 07:43:17 N
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/snapcf_ORCL_SID.f 43 50 CONTROLFILE 31-OCT-2018 18:33:22 N
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/spfileORCL_SID.ora 0 1 PARAMETERFILE 31-OCT-2018 06:47:40 N
------------------ ------------------
736,613 737,115
------------------ ------------------
Grand Total: 736,613 737,115
71 rows selected.
SQL>
================================================================================
$ asmcmd lsdg -g ORCL_SID_DATA
Inst_ID State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
1 MOUNTED EXTERN N 512 512 4096 1048576 786492 83442 0 83442 0 N ORCL_SID_DATA/
2 MOUNTED EXTERN N 512 512 4096 1048576 786492 83442 0 83442 0 N ORCL_SID_DATA/
================================================================================
$ asmcmd ls -lsh ORCL_SID_DATA/*/*
Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/:
CONTROLFILE UNPROT FINE MAY 26 22:00:00 Y 16K 2635 41.2M 48M Backup.313.990988403
CONTROLFILE UNPROT FINE OCT 31 2018 Y 2K 1 2K 8M Current.257.990939051
CONTROLFILE UNPROT FINE OCT 31 2018 Y 2K 1 2K 8M Current.258.990939051
CONTROLFILE UNPROT FINE MAR 11 16:00:00 Y 16K 2635 41.2M 48M current.256.990939051
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAFILE/:
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G AUDIT_TBS.264.996655365
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 131073 1G 1G AUDIT_TBS.319.996656095
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 85625 668.9M 670M AVAIL.277.990939537
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G EUR_ORDER.271.990939353
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 38401 300M 302M EUR_ORDER.316.992686897
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1347073 10.3G 10.3G EUR_PDA.321.1007139241
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 466433 3.6G 3.6G EZFETCH_IE.270.990939353
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4091905 31.2G 31.3G EZFETCH_UK.269.990939199
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 847873 6.5G 6.5G EZFETCH_UK.279.990939547
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 12801 100M 102M FMWEURPD12_ESS.284.990939589
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 15361 120M 122M FMWEURPD12_IAS_OPSS.285.990939591
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 85761 670M 672M FMWEURPD12_IAS_UMS.287.990939593
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 7681 60M 62M FMWEURPD12_IAU.289.990939595
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 89601 700M 702M FMWEURPD12_MDS.282.990939585
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G FMWEURPD12_SOAINFRA.290.1010082787
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G FMWEURPD12_SOAINFRA.292.1010082785
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G FMWEURPD12_SOAINFRA.314.1010082781
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G FMWEURPD12_SOAINFRA.318.1010082769
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1628673 12.4G 12.4G FMWEURPD12_SOAINFRA.326.1013130689
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1281 10M 11M FMWEURPD12_STB.288.990939595
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 7681 60M 62M FMWEURPD12_WLS.286.990939591
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1572865 12G 12G IFW_DT_TBS.280.990939551
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 131073 1G 1G IFW_IDX_TBS.281.990939585
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4194303 32G 32G KOMPASS.274.990939395
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1663489 12.7G 12.7G KOMPASS.324.1028374119
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3072001 23.4G 23.5G MBSYNC_UK_MCD.291.990939597
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4194303 32G 32G PDA_STG.267.990939163
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4099073 31.3G 31.3G PDA_STG.278.990939539
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 745473 5.7G 5.7G PDA_STG.325.1014480263
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 361473 2.8G 2.8G PERFSTAT.320.1001228979
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4192001 32G 32G SYSAUX.260.990939059
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1101825 8.4G 8.4G SYSAUX.268.990939163
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G SYSAUX.275.990939395
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1464321 11.2G 11.2G SYSTEM.259.990939059
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4194303 32G 32G UNDOTBS1.261.990939059
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 524289 4G 4G UNDOTBS1.283.1041158311
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4194303 32G 32G UNDOTBS2.263.990939063
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4194177 32G 32G UNDOTBS2.323.1041061103
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 801 6.3M 7M USERS.262.990939059
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3327489 25.4G 25.4G WEBMDATA.272.990939357
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 131073 1G 1G WEBMINDX.273.990939393
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/:
DATAGUARDCONFIG UNPROT COARSE MAY 19 03:00:00 Y 4K 3 12K 1M ORCL_SID_PHX1RAC.312.990949397
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/ONLINELOG/:
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_1.293.990939821
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_10.300.990939985
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_11.301.990940017
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_12.302.990940049
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_13.303.990940081
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_14.304.990940083
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_2.294.990939853
ONLINELOG UNPROT COARSE MAY 26 21:00:00 Y 512 2097153 1G 1G group_21.305.990940085
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_22.306.990940115
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_23.307.990940179
ONLINELOG UNPROT COARSE MAY 26 20:00:00 Y 512 2097153 1G 1G group_24.308.990940241
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_25.309.990940273
ONLINELOG UNPROT COARSE MAY 26 20:00:00 Y 512 2097153 1G 1G group_26.310.990940273
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_3.295.990939915
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_4.296.990939917
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_5.297.990939919
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_6.298.990939951
ONLINELOG UNPROT COARSE MAY 26 21:00:00 Y 512 2097153 1G 1G group_9.299.990939953
+ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/:
PARAMETERFILE UNPROT COARSE MAY 26 22:00:00 Y 512 17 8.5K 1M SPFILE.311.990946061
+ORCL_SID_DATA/ORCL_SID_PHX1RAC/TEMPFILE/:
TEMPFILE UNPROT COARSE MAR 11 16:00:00 Y 8K 2297601 17.5G 17.5G FMWEURPD12_IAS_TEMP.317.991419415
TEMPFILE UNPROT COARSE MAR 11 16:00:00 Y 8K 1310721 10G 10G NA_ARCHIVE_TEMP.322.1007140587
TEMPFILE UNPROT COARSE MAR 11 16:00:00 Y 8K 4194177 32G 32G TEMP.315.991419415
DATAGUARDCONFIG UNPROT COARSE MAY 19 03:00:00 N 4K 3 12K 1M dr1ORCL_SID.dat => +ORCL_SID_DATA/ORCL_SID_PHX1RAC/DATAGUARDCONFIG/ORCL_SID_PHX1RAC.312.990949397
CONTROLFILE UNPROT FINE MAY 26 22:00:00 N 16K 2635 41.2M 48M snapcf_ORCL_SID.f => +ORCL_SID_DATA/ORCL_SID_PHX1RAC/CONTROLFILE/Backup.313.990988403
PARAMETERFILE UNPROT COARSE MAY 26 22:00:00 N 512 17 8.5K 1M spfileORCL_SID.ora => +ORCL_SID_DATA/DB_UNKNOWN/PARAMETERFILE/SPFILE.311.990946061
================================================================================
$ asmcmd ls -lsh ORCL_SID_DATA/*/*/*
Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G AUDIT_TBS.264.996655365
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 131073 1G 1G AUDIT_TBS.319.996656095
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 85625 668.9M 670M AVAIL.277.990939537
CONTROLFILE UNPROT FINE MAY 26 22:00:00 Y 16K 2635 41.2M 48M Backup.313.990988403
CONTROLFILE UNPROT FINE OCT 31 2018 Y 2K 1 2K 8M Current.257.990939051
CONTROLFILE UNPROT FINE OCT 31 2018 Y 2K 1 2K 8M Current.258.990939051
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G EUR_ORDER.271.990939353
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 38401 300M 302M EUR_ORDER.316.992686897
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1347073 10.3G 10.3G EUR_PDA.321.1007139241
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 466433 3.6G 3.6G EZFETCH_IE.270.990939353
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4091905 31.2G 31.3G EZFETCH_UK.269.990939199
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 847873 6.5G 6.5G EZFETCH_UK.279.990939547
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 12801 100M 102M FMWEURPD12_ESS.284.990939589
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 15361 120M 122M FMWEURPD12_IAS_OPSS.285.990939591
TEMPFILE UNPROT COARSE MAR 11 16:00:00 Y 8K 2297601 17.5G 17.5G FMWEURPD12_IAS_TEMP.317.991419415
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 85761 670M 672M FMWEURPD12_IAS_UMS.287.990939593
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 7681 60M 62M FMWEURPD12_IAU.289.990939595
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 89601 700M 702M FMWEURPD12_MDS.282.990939585
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G FMWEURPD12_SOAINFRA.290.1010082787
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G FMWEURPD12_SOAINFRA.292.1010082785
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G FMWEURPD12_SOAINFRA.314.1010082781
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G FMWEURPD12_SOAINFRA.318.1010082769
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1628673 12.4G 12.4G FMWEURPD12_SOAINFRA.326.1013130689
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1281 10M 11M FMWEURPD12_STB.288.990939595
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 7681 60M 62M FMWEURPD12_WLS.286.990939591
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1572865 12G 12G IFW_DT_TBS.280.990939551
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 131073 1G 1G IFW_IDX_TBS.281.990939585
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4194303 32G 32G KOMPASS.274.990939395
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1663489 12.7G 12.7G KOMPASS.324.1028374119
DATAGUARDCONFIG UNPROT COARSE MAY 19 03:00:00 Y 4K 3 12K 1M MBEURINP_SPMBRAC.312.990949397
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3072001 23.4G 23.5G MBSYNC_UK_MCD.291.990939597
TEMPFILE UNPROT COARSE MAR 11 16:00:00 Y 8K 1310721 10G 10G NA_ARCHIVE_TEMP.322.1007140587
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4194303 32G 32G PDA_STG.267.990939163
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4099073 31.3G 31.3G PDA_STG.278.990939539
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 745473 5.7G 5.7G PDA_STG.325.1014480263
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 361473 2.8G 2.8G PERFSTAT.320.1001228979
PARAMETERFILE UNPROT COARSE MAY 26 22:00:00 Y 512 17 8.5K 1M SPFILE.311.990946061
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4192001 32G 32G SYSAUX.260.990939059
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1101825 8.4G 8.4G SYSAUX.268.990939163
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3932161 30G 30G SYSAUX.275.990939395
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 1464321 11.2G 11.2G SYSTEM.259.990939059
TEMPFILE UNPROT COARSE MAR 11 16:00:00 Y 8K 4194177 32G 32G TEMP.315.991419415
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4194303 32G 32G UNDOTBS1.261.990939059
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 524289 4G 4G UNDOTBS1.283.1041158311
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4194303 32G 32G UNDOTBS2.263.990939063
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 4194177 32G 32G UNDOTBS2.323.1041061103
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 801 6.3M 7M USERS.262.990939059
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 3327489 25.4G 25.4G WEBMDATA.272.990939357
DATAFILE UNPROT COARSE MAY 26 01:00:00 Y 8K 131073 1G 1G WEBMINDX.273.990939393
CONTROLFILE UNPROT FINE MAR 11 16:00:00 Y 16K 2635 41.2M 48M current.256.990939051
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_1.293.990939821
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_10.300.990939985
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_11.301.990940017
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_12.302.990940049
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_13.303.990940081
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_14.304.990940083
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_2.294.990939853
ONLINELOG UNPROT COARSE MAY 26 21:00:00 Y 512 2097153 1G 1G group_21.305.990940085
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_22.306.990940115
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_23.307.990940179
ONLINELOG UNPROT COARSE MAY 26 20:00:00 Y 512 2097153 1G 1G group_24.308.990940241
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_25.309.990940273
ONLINELOG UNPROT COARSE MAY 26 20:00:00 Y 512 2097153 1G 1G group_26.310.990940273
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_3.295.990939915
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_4.296.990939917
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_5.297.990939919
ONLINELOG UNPROT COARSE MAY 26 22:00:00 Y 512 2097153 1G 1G group_6.298.990939951
ONLINELOG UNPROT COARSE MAY 26 21:00:00 Y 512 2097153 1G 1G group_9.299.990939953
__ATA.cmd.push(function() {
__ATA.initDynamicSlot({
id: 'atatags-26942-5ecded202cec1',
location: 120,
formFactor: '001',
label: {
text: 'Advertisements',
},
creative: {
reportAd: {
text: 'Report this ad',
},
privacySettings: {
text: 'Privacy settings',
}
}
});
});
Machine Learning for Oracle Professionals
In this webinar, Oracle ACE Director Craig Shallahamer will introduce you to the world of applied Machine Learning from an Oracle Professional (DBA/Developer/Manager) perspective. This includes understanding what ML is, why use it and why now.
He will demonstrate how to create an automated anomalous performance detection system. He'll use industry standard Python with its ML libraries and Jupyter Notebooks. You will be able to download and do everything he does in this webinar!
If you have ever wondered how ML can be applied in an IT environment, you don't want to miss this webinar.
Categories: Development
automatic partition naming
hi , is there any way to give a patern name to automatic partitions when using this DBMS_REDEFINITION.start_redef_table package.
thank you by advance.
Categories: DBA Blogs
Assigning values into table type variable within loop results in error - PLS-00382: expression is of wrong type
Please refer the script I attached with this question -
There are records which are supposed to be stored in table type variable as a result of cursor loop on table - test_mgr_hierar5.
The table type is expected to hold values - 177603,157564,15...
Categories: DBA Blogs
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
This is just weird. It works fine on 12.1. On version 19 it requires the "workaround" used in foo3.
Categories: DBA Blogs
ORA-02298: cannot validate (APEX_200100.WWV_FLOW_DATA_SESSION_FK) - parent keys not found
Hi,
Today I upgraded one of our Oracle APEX environment from 19.2 to 20.1. The upgrade was successful except that I found the following error in the installation log:
<code># Error: ORA-02298: cannot validate (APEX_200100.WWV_FLOW_DATA_SESSION_F...
Categories: DBA Blogs
Formating SQL commands output from within RMAN
Is it possible to format SQL interface commands output in RMAN .. at least some basic like >linesize< ?
Regards Dejan
Categories: DBA Blogs
Create MVW takes 15 mins. Refresh of same MVW takes 12 hours (or mostly failes)
We have a materialized view, which has undergone some tuning. When creating the new MVW, it took 15 mins.
The MVW is then refreshed using a shell in which many MVWs are refreshed in order. As part of the refresh, to make it faster, the indexes on ...
Categories: DBA Blogs
Pages
