For my readers who are preparing the ocm 11g exam, the environment just changed (From 13th May 2013 onwards)
Instead of using OEM 10g, you will be using OEM 11g.
The upgrade exam is still using OEM 10g and DB 11gR1 (!) but I did not care installing OEM 10g and I prepared with OEM 11g.
I wrote about not using DAY OF MONTH and DAY OF WEEK simultanously in how to cron
The correct method is to use
15 14 15 05 * /tmp/run-my-job
But… I wrote this five years ago. Hmmm ! Not that correct then since it would run every year
Ok, periodically I check for jobs are scheduled to run a specific date only
$ crontab -l|awk '$1!~/#/&&$3*$4' 15 14 15 05 * /tmp/run-my-job
I have 9 more days to remove this before it runs for the fifth time
To delete large number of rows, for instance rows with date until 2010, you can issue this simple statement.
SQL> DELETE FROM T WHERE C<DATE '2011-01-01'; 1'000'000'000 rows deleted Elapsed: 23:45:22.01 SQL> commit;
This is perfectly fine. The table remains online, other users are not much affected (maybe they will not even notice the lower IO performance).
It will generate quite a lot of UNDO, and you will need enough space for archivelog and a large undo tablespace and a large undo retention setting (to prevent ORA-01555 snapshot too old).
If your table is like 100G big, you do it during week-end, you have 500Gb Undo and 250G free space in your archive destination, you will be fine. Well. Maybe.
There are workarounds where you create a new table then rename etc… but this is not the scope of this post and you will need to validate your index / foreign keys / online strategy with the application guys.
Another way to decrease runtime pro statement and undo requirement pro statement (but increase overall elapsed time) is to divided it chunks, for instance to delete 100’000’000 rows each night during 10 days.
SQL> DELETE FROM T WHERE C<DATE '2011-01-01' AND ROWNUM<=100000000; 100'000'000 rows deleted Elapsed: 04:11:15.31 SQL> commit;
Or if you want to delete in much smaller chunks to accomodate your tiny undo tablespace, you could try
BEGIN LOOP DELETE FROM T WHERE C<DATE '2011-01-01' AND ROWNUM <= 1000; EXIT WHEN SQL%ROWCOUNT = 0; COMMIT; END LOOP; END; /
This will run longer than a single transaction, but it is quite usefull if your undo tablespace is too small. Also if you abort it (CTRL-C or kill session), you will not lose all progresses (but you lose on integrity/atomicity) and your KILL SESSION will not last for ever. With a single transaction, your session may be marked as killed for hours/days…
With large table scans, sometimes the estimated total work is far beyond reality
SQL> select message from v$session_longops where target='SCOTT.EMP'; MESSAGE ------------------------------------------------------------ Table Scan: SCOTT.EMP: 7377612 out of 629683 Blocks done
The total work is the Oracle estimation :
SQL> select blocks from dba_tables where table_name='EMP'; BLOCKS ---------- 629683
This may differ quite a lot from the segment size, for instance if the table is not very often analyzed :
SQL> select blocks, sysdate, last_analyzed from dba_tables where table_name='EMP'; BLOCKS SYSDATE LAST_ANALYZED ---------- ------------------- ------------------- 629683 2013-04-21_09:21:47 2007-10-13_21:40:58 SQL> select blocks from dba_segments where segment_name='EMP'; BLOCKS ---------- 7749888
I have customized my very long ops query to deal with very long waits.
col target for a20 set lin 150 pages 40000 termout off alter session set nls_currency='%'; col PCT_DONE for 990.00L jus r col time_remaining for 999999 select lo.target,lo.sofar,seg.blocks, lo.ELAPSED_SECONDS*seg.blocks/lo.sofar-lo.ELAPSED_SECONDS TIME_REMAINING, 100*lo.sofar/seg.blocks PCT_DONE from dba_segments seg, v$session_longops lo where lo.units='Blocks' and lo.totalwork>0 and (lo.time_remaining>0 or lo.time_remaining is null) and regexp_substr(lo.target,'[^.]+') = seg.owner and regexp_substr(lo.target,'[^.]+$') = seg.segment_name /
In previous posts (e.g. Export to Excel) I wrote about using HTML format to export to Excel.
Let’s do it for real, let’s dive into the .xls file format and learn how to generate dynamic excel from Unix!
1) create one time your excel file manually. With graphs, colors, sounds, up to you. Or Word, Powerpoint or whatever (minimum MS Office 2007)
2) save as excel 2007 or later format (.xlsx)
this is called the Office Open XML format. It is neither OpenOffice nor OpenSource. It is XML and license restriction may apply.
3) transfer the excel file to your favorite platform
4) unzip the excel file (yes, you read it correctly, unzip the .xlsx file)
$ unzip /tmp/DynamicExcel.xlsx Archive: /tmp/DynamicExcel.xlsx inflating: [Content_Types].xml inflating: _rels/.rels inflating: xl/_rels/workbook.xml.rels inflating: xl/workbook.xml inflating: xl/styles.xml inflating: xl/worksheets/sheet2.xml inflating: xl/worksheets/_rels/sheet1.xml.rels inflating: xl/worksheets/_rels/sheet2.xml.rels inflating: xl/drawings/_rels/drawing1.xml.rels inflating: xl/theme/theme1.xml inflating: xl/worksheets/sheet1.xml inflating: xl/drawings/drawing2.xml inflating: xl/charts/chart1.xml inflating: xl/drawings/drawing1.xml inflating: xl/sharedStrings.xml inflating: docProps/core.xml inflating: docProps/app.xml
5) now substitute the data with some script output (for instance select * from v$backup_redologs). Here I am substituing all datas from row r=2
cd xl/worksheets tr -d '\r' < sheet1.xml | sed 's,<row r="2".*,,' > head sqlplus -s -L / as sysdba <<'EOF' > body set feed off pages 0 lin 2000 longc 2000 long 2000 SELECT XMLELEMENT ( "row", xmlattributes ((rownum+1) AS "r", '1:2' AS "spans", '0.2' AS "x14ac:dyDescent"), XMLELEMENT ("c", xmlattributes ('A' || (rownum+1) AS "r", '1' AS "s"), XMLELEMENT ("v", d-date '1899-12-30')), XMLELEMENT ("c", xmlattributes ('B' || (rownum+1) AS "r", '2' AS "s"), XMLELEMENT ("v", c))) x FROM ( SELECT TRUNC (next_time, 'DD') d, COUNT (*) c FROM v$backup_redolog WHERE next_time BETWEEN TRUNC (SYSDATE - 90) AND TRUNC (SYSDATE) - 1 / 86400 GROUP BY TRUNC (next_time, 'DD') ORDER BY 1); EOF tr -d '\r' < sheet1.xml | sed -n 's,.*</sheetData>,</sheetData>,p' > tail cat head body tail | tr -d '\n' > sheet1.xml rm head body tail
6) recreate zip file
$ cd ../.. $ zip -r /tmp/DynamicExcel2.xlsx * adding: [Content_Types].xml (deflated 78%) adding: docProps/ (stored 0%) adding: docProps/core.xml (deflated 51%) adding: docProps/app.xml (deflated 53%) adding: _rels/ (stored 0%) adding: _rels/.rels (deflated 60%) adding: xl/ (stored 0%) adding: xl/_rels/ (stored 0%) adding: xl/_rels/workbook.xml.rels (deflated 71%) adding: xl/workbook.xml (deflated 42%) adding: xl/styles.xml (deflated 56%) adding: xl/worksheets/ (stored 0%) adding: xl/worksheets/sheet2.xml (deflated 45%) adding: xl/worksheets/_rels/ (stored 0%) adding: xl/worksheets/_rels/sheet1.xml.rels (deflated 39%) adding: xl/worksheets/_rels/sheet2.xml.rels (deflated 39%) adding: xl/worksheets/sheet1.xml (deflated 81%) adding: xl/drawings/ (stored 0%) adding: xl/drawings/_rels/ (stored 0%) adding: xl/drawings/_rels/drawing1.xml.rels (deflated 39%) adding: xl/drawings/drawing2.xml (deflated 58%) adding: xl/drawings/drawing1.xml (deflated 61%) adding: xl/theme/ (stored 0%) adding: xl/theme/theme1.xml (deflated 79%) adding: xl/charts/ (stored 0%) adding: xl/charts/chart1.xml (deflated 85%) adding: xl/sharedStrings.xml (deflated 22%)
7) Check it
In Oracle Universal Installer and OPatch User’s Guide it is documented that The first Oracle home is named the “DEFAULT_HOME” and registers itself in the Windows NT Registry.
Remember, NT means New Technology
There is apparently a Home Selector that is a part of the installation software, maybe something like D:\oracle\product\11.2.0\client_1\bin\selecthome.bat. Sometimes. Not sure
But there is no DEFAULT HOME in the registry.
PS C:\> gci HKLM:\SOFTWARE\ORACLE Hive: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE Name Property ---- -------- KEY_agent12c1 ORACLE_HOME : D:/oracle\core\126.96.36.199.0 ORACLE_HOME_NAME : agent12c1 ORACLE_GROUP_NAME : Oracle - agent12c1 NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252 KEY_oracle_sysman_db_12_1_0_2_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.db.discovery.plugin_188.8.131.52.0 0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_db_12_1_0_2_0_discovery_Home0 ORACLE_GROUP_NAME : Oracle - oracle_sysman_db_12_1_0_2_0_discovery_Home0 KEY_oracle_sysman_emas_12_1_0_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.emas.discovery.plugin_184.108.40.206.0 2_0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_emas_12_1_0_2_0_discovery_Home0 ORACLE_GROUP_NAME : Oracle - oracle_sysman_emas_12_1_0_2_0_discovery_Home0 KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.oh.agent.plugin_220.127.116.11.0 0_agent_Home0 ORACLE_HOME_NAME : oracle_sysman_oh_12_1_0_1_0_agent_Home0 ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_agent_Home0 KEY_oracle_sysman_oh_12_1_0_1_ ORACLE_HOME : D:\oracle\plugins\oracle.sysman.oh.discovery.plugin_18.104.22.168.0 0_discovery_Home0 ORACLE_HOME_NAME : oracle_sysman_oh_12_1_0_1_0_discovery_Home0 ORACLE_GROUP_NAME : Oracle - oracle_sysman_oh_12_1_0_1_0_discovery_Home0 KEY_OraClient11g_home1 ORACLE_HOME : D:\oracle\product\11.2.0\client_1 ORACLE_HOME_NAME : OraClient11g_home1 ORACLE_GROUP_NAME : Oracle - OraClient11g_home1 ORACLE_BUNDLE_NAME : Enterprise NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252 OLEDB : D:\oracle\product\11.2.0\client_1\oledb\mesg ORACLE_HOME_KEY : SOFTWARE\ORACLE\KEY_OraClient11g_home1 MSHELP_TOOLS : D:\oracle\product\11.2.0\client_1\MSHELP SQLPATH : D:\oracle\product\11.2.0\client_1\dbs KEY_OraGtw11g_home1 ORACLE_HOME : D:\oracle\product\11.2.0\tg_1 ORACLE_HOME_NAME : OraGtw11g_home1 ORACLE_GROUP_NAME : Oracle - OraGtw11g_home1 NLS_LANG : AMERICAN_AMERICA.WE8MSWIN1252 ORACLE_BUNDLE_NAME : Enterprise MSHELP_TOOLS : D:\oracle\product\11.2.0\tg_1\MSHELP SQLPATH : D:\oracle\product\11.2.0\tg_1\dbs ORACLE_HOME_KEY : SOFTWARE\ORACLE\KEY_OraGtw11g_home1 KEY_sbin12c1 ORACLE_HOME : D:\oracle\sbin ORACLE_HOME_NAME : sbin12c1 ORACLE_GROUP_NAME : Oracle - sbin12c1 ODP.NET remexecservicectr remaining_time : 120000 SYSMAN
How do I set my Oracle Home?
Actually if you enter a command like “lsnrctl start”, the OS will search in the PATH for lsnrctl and determines the Oracle Home name accordingly.
Therefore, the only thing you must do to change your default Oracle Home is to set the PATH environment variable. Only then your LSNRCTL START will find the right binary and right parameter file to start your listener.
When building a materialized view, you may want to postpone the loading to a later phase, for instance you install a new release, and the refresh happends every night.
BUILD DEFERRED allow you to build an empty materialized view and refresh it later. But this may still takes ages.
SQL> create materialized view mv1 build deferred as select count(*) c from emp,emp,emp,emp,emp,emp,emp; Materialized view created. Elapsed: 00:00:17.28 SQL> select * from mv1; no rows selected
No data collected, but still a long time (17sec here, but much worst in real life)
A workaround is to use ON PREBUILT TABLE on an empty table, just add a few WHERE 1=0 in your subqueries
SQL> create table mv1 as select * from (select count(*) c from emp,emp,emp,emp,emp,emp,emp where 1=0) where 1=0; Table created. Elapsed: 00:00:00.04 SQL> create materialized view mv1 on prebuilt table as select count(*) c from emp,emp,emp,emp,emp,emp,emp; Materialized view created. Elapsed: 00:00:00.15 SQL> select * from mv1; no rows selected Elapsed: 00:00:00.00
Much faster !
I have not seen DBGSQL very often. But today again, a duplicate in RMAN was failing with, amoung other errors, sqlcode 911
RMAN> duplicate target database to DB02 until time "to_date('2013-01-29_00:00:00','YYYY-MM-DD_HH24:MI:SS')" nofilenamecheck ; DBGSQL: TARGET> select 2013-01-29_00:00:00 from sys.dual DBGSQL: sqlcode = 911 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 02/01/2013 17:58:23 RMAN-05501: aborting duplication of target database ORA-01861: literal does not match format string
I have not found anything useful except bug Bug 9351175 which is fixed in 11.2 (and I have 22.214.171.124/aix), but I could workaround the problem with
Because I do like to have readable timestamp (with seconds) in my RMAN logs, I set NLS_DATE_FORMAT in my spfile
alter system set nls_date_format='YYYY-MM-DD_HH24:MI:SS';