tag:blogger.com,1999:blog-21706371493897360392024-03-10T20:23:38.036-07:00Simple Oracle Dbaby Piet de Visser, Oracle DBA + ACE-Pro PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.comBlogger54125tag:blogger.com,1999:blog-2170637149389736039.post-34119371485633903952023-08-13T07:00:00.004-07:002023-08-13T10:06:41.798-07:00Adding PDBs is Easy also in v23c.<p>TL;DR: Success! I've got several PDBs plugged in a CDB, all created "just from scripts", no "template-recovery". From here on it is all is Dead-Simple. Or is it.</p><div><br /></div><div>Spoiler: Finished. Success. But there is Always More ToDo.</div><div><br /></div><div><div>And... Need I state it: Do Not Try This at Work. (but if you Do, let me know how it went. I'm sure you can improve on my attempts...).</div></div><div><br /></div><div><br /></div><div><b>Background:</b></div><div><br /></div><div>In the first three "episodes", I created the CDB + Seed, added the "catalog", and added the more or less mandatory components. Now it is time to add (and maybe delete) some PDBs and see what is happening.</div><div><br /></div><div>To avoid the use of "templates-to-recover" (which, btw, is Highly Recommended, and much more efficient), I've been creating a database manually, from scratch, just using scripts. In all cases, I have tried to choose the "Minimal Effort" and. "Minimal Configuration Work". Minimal RTFM even (which is kinda risky... You Should Always RTFM, Always). </div><div><br /></div><div>My list of parameters is kept minimal, thus using defaults wherever possible, my typing effort was minimal, and the management of files (creation, placement, naming, deletion) is totally left to Oracle (using defaults and OMF wherever possible). </div><div><br /></div><div>As software I'm using the "Free Developer Release v23c". But since that is an early (preview) release, not all components are "in", and the option to "generate scripts" wasn't included in the DBCA from v23c yet. Hence I used scripts generated from a v21c as examples and edited them where needed.</div><div><br /></div><div><div>Because I'm still working with the list of generated scripts from DBCA-v21c (not the same version! hence use at own risk) I had to edit. In part-3, I eliminated a few obsolete items (intermedia was deprecated in 12c, and "removed" in 19c, how many years ago...). Then I consolidated everything up-to-but-not-including any PDB-creation in one Simplified script, script nr3. That worked well, resulting in 0 (zero) invalid objects. Check previous blogs for the details.</div><div><br /></div></div><div><div>Note: I Hate long command-lines and complicated scripts, and I especially Hate hardcoded things in scripts (like 10s of repetitions of a file-path with a version-number in it). We have $ORACL_HOME and $ORACLE_BASE and $ORACLE_SID, and we have them For a Reason.</div><div>On top of that, editing is Risky Business: Typos! - Apologies if I repeat that too often. </div><div><br /></div></div><div><br /></div><div><br /></div><div><b>Finally, Creating the PDBs:</b></div><div><br /></div><div>Most demos and examples show how to create the CDB and then add 1, single, PDB to it. I'm going to be a little more ambitions: I want 3 PDBs and by using OMF (Oracle Managed Files) and by avoiding FILE_NAME_CONVERT parameters, I don't want to worry about Anything on the file-system.</div><div><br /></div><div>Now from the "generated" create-scripts from DBCA v21c, there are three scripts remaining. Time for another Eyeball Mark-1 Inspection, and let's see what we can do and maybe improve.</div><div><br /></div><div><div>The v21c generated PDBCreation: </div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8pIGUkyI3Me7WkbpOK-ksOmb90i5tvGe9QalmNILGZaIVxh6ev4sA0DtkAvPhik51QQl07g1jypATNo6QhcaqDo0TH-lggi_HNntULBLs5igs59fESTdN4IdSvvlGVrj3tzMr6BodCaNFahPUwVkG6xulzI4Wq_eJZU5j_5AuFPICpxb8h2bms1r79vI/s1572/crdb4_1pdbcre.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="366" data-original-width="1572" height="150" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8pIGUkyI3Me7WkbpOK-ksOmb90i5tvGe9QalmNILGZaIVxh6ev4sA0DtkAvPhik51QQl07g1jypATNo6QhcaqDo0TH-lggi_HNntULBLs5igs59fESTdN4IdSvvlGVrj3tzMr6BodCaNFahPUwVkG6xulzI4Wq_eJZU5j_5AuFPICpxb8h2bms1r79vI/w640-h150/crdb4_1pdbcre.png" width="640" /></a></div><br /><div>Strangely, this one contains little relevant items. Fine. let's move on.</div><div><br /></div><div>Next would be the generated plug_freepdb1: </div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4g2c02q_nmZcqjnglRLBTRY40CLipXzaYeElL-dlgcfCX8rlY-m_bJAb8JyisW4lm8k_R9IOMM7H_UYecD_b4n8012Gx1Ch7vCq-T23AmHonrIoTr1meer0F0aUWtZXnJPtQg9XITp1rtJA6wYTtzq3Mx35A-lGj6aSBMhbjKpsFunZ1K7AW3OJvyhOc/s1484/crdb4_2plug.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1140" data-original-width="1484" height="492" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4g2c02q_nmZcqjnglRLBTRY40CLipXzaYeElL-dlgcfCX8rlY-m_bJAb8JyisW4lm8k_R9IOMM7H_UYecD_b4n8012Gx1Ch7vCq-T23AmHonrIoTr1meer0F0aUWtZXnJPtQg9XITp1rtJA6wYTtzq3Mx35A-lGj6aSBMhbjKpsFunZ1K7AW3OJvyhOc/w640-h492/crdb4_2plug.png" width="640" /></a></div><div><br /></div><div>This actually creates a PDB (yesss). </div><div>With the name "hardcoded" in the script, on no less then 6 lines, including in the file-name-convert. </div><div>The spool-command seems a double (is fixable?). </div><div>The select shows data- and temp-files (can go?).</div><div><br /></div><div>And then, Voila, we get to the Create, and Open, and Register of the first real Pluggable Database. That looks deliciously Simple, and it even announced itself to the listener straight away. That is the good part.</div><div><br /></div><div>Hmm, yeah. I can work with that. Will still try to improve on the script though.</div><div><br /></div><div><br /></div><div>Last script from the generated set was postPDBCreation_freepdb1: </div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhceu7MzZFZeo71uk8hTHNG24VyKQ07Y-r0URmQokOzlftoHyrHZJcpQJXw5_42uk2XQ7LOgUybAODfXMGZjdVKhYmwNyqKbPagUOBebxDuUGDYjQaGrTaO--_VMOoWDaQIOXvn4rkJlyNi-hRH_nzAqrQh-a9JQHeFV8y_cNwJBl19OCLT4EJ_j_KUZGI/s1472/crdb4_3postpdb.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1472" data-original-width="1464" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhceu7MzZFZeo71uk8hTHNG24VyKQ07Y-r0URmQokOzlftoHyrHZJcpQJXw5_42uk2XQ7LOgUybAODfXMGZjdVKhYmwNyqKbPagUOBebxDuUGDYjQaGrTaO--_VMOoWDaQIOXvn4rkJlyNi-hRH_nzAqrQh-a9JQHeFV8y_cNwJBl19OCLT4EJ_j_KUZGI/w636-h640/crdb4_3postpdb.png" width="636" /></a></div><div><br /></div><div>This one is funny. It tries to add a USERS-tablespace that already exists in the PDB$SEED (hence error!), with a hardcoded datafile-path *), hm. </div><div>That USERS-tablespace is set as default for users (Good Idea). </div><div>It then does a call to OPatch/datapatch (hardcoded 21c path and hardcoded names! what about using $ORACLE_HOME and $ORACLE_SID? ). </div><div>And it does a number of reconnects and selects, checks to report things to screen or spoolfile.</div><div>It has _only_ 11 lines with the PDB-name hardcoded in them.</div><div>OK. All Seems "Fixable" to me. </div><div><br /></div><div>Some "good news" as well: apart from the OPatch call, these scripts are "self contained". There is no call to perl-catcon or other callout. </div><div>All I would do is to simplify a little, for Readability + Portability. Maybe, use a DEFINE or some form of arg1 (or $1 or &1) to minimise use of hardcoded names and make the scripts more portable (I'm not obsessed, not obsessed, ...)</div><div><br /></div><div></div></div><div>After some more looking (on a larger screen), and some coffee, I concatenated the first two scripts and edited that into crdb4_pdb.sql:</div><div><br /></div><div>(pre and code for crdb4_pdb) </div>
<pre><code>/* -------------------------
crdb4_pdb.sql: create the first PDB(s), with minimal code + config..
notes:
- in future: define the pdb-name(s) once or pass as args.
---------------------------
*/
-- get the passwords
@accpws
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool crdb4_pdb.log append
-- found out this "bootstrap" was needed. MOS note: 2538591.1
host $ORACLE_HOME/OPatch/datapatch --verbose
-- let's do multiple plugs straight away.
-- future: create 42 plugs in a pl/sql loop? would that work?
CREATE PLUGGABLE DATABASE FREEPDB1 ADMIN USER PDBADMIN IDENTIFIED BY "&&pdbAdminPassword" ;
CREATE PLUGGABLE DATABASE FREEPDB2 ADMIN USER PDBADMIN IDENTIFIED BY "&&pdbAdminPassword" ;
CREATE PLUGGABLE DATABASE ORCL ADMIN USER PDBADMIN IDENTIFIED BY "&&pdbAdminPassword" ;
-- only see 2 of the 3 new ones, wonder why ...
select name from v$containers where upper(name) like 'FREEPDB%';
alter pluggable database FREEPDB1 open;
alter pluggable database FREEPDB2 open;
alter pluggable database ORCL open;
show pdbs
alter system register;
prompt ---- End of crdb4_pdbs. Please check, properties, files, listener etc... -----
spool off
</code></pre> <div><br /></div><div>Here is what I hope to achieve:</div><div>There are no file-paths at all, I'm not going to mess with files if I can help it.</div><div>The remaining commands are readable (even on 80x25 terminal) and straightforward understandable, I hope.</div><div>On testing, I (re-)found that OPatch/datapatch needs a "bootstrap" (MOS note: 2538591.1, known issue...). I'm doing that before any further PDB-creations.</div><div>I then create and open the PDBs.. no big deal actually, and I register them with the listener. The select-checks and the can wait (for next script).</div><div><div><br /></div><div>In my current script I am left with just 3 PDB names, which are "hardcoded" 2x each. That still makes 6 items to correct later. ;-(.</div><div><br /></div></div><div>That user-tablespace, the selects and checks from the postPDBCreation, I'll isolate in a separate check-script, to be run per PDB. One day, I'm hoping for a generic, portable script to "add 1 PDB" to a CDB, in the simplest fashion, for now, I'm in a hurry: I want my PDBs, to observe and play with...</div><div><br /></div><div>Bonus "feature": I _could_ try run these last script even from just-a-client when connected to the CDB$ROOT, but it would mean ignoring the datapatch and editing every "connect". Too Lazy at the moment. But good to know it can be done (yep, I checked, later, works fine, but ignoring datapatch, so maybe not recommended)</div><div><br /></div><div><br /></div><div><b>I'm impatient, Let's Do This:</b></div><div><br /></div><div>If you are like me, you might check disk-usage on "the server" and check the listener status, before and after. But was in a hurry...</div><div><br /></div><div>The datapatch part went down fine:</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgk1i3PAeekvTCFhOSbq9836_BJBwlqsftXQ8hbuLFUb4yitZ-FtdSkMPPU0gXl13V6jHfZXMZREnHKiyzOUCAMdOT2MHIrgDW3megoWHoOrbdvyjZsFzt8Q_AaBOTD8izUkV3ZD7M6e1kZCfiPoTSNKl7lnObcPAwyvabRWUMyeQxi4zW0OVJCLw0diG8/s1656/crdb4_30datapatch.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1434" data-original-width="1656" height="554" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgk1i3PAeekvTCFhOSbq9836_BJBwlqsftXQ8hbuLFUb4yitZ-FtdSkMPPU0gXl13V6jHfZXMZREnHKiyzOUCAMdOT2MHIrgDW3megoWHoOrbdvyjZsFzt8Q_AaBOTD8izUkV3ZD7M6e1kZCfiPoTSNKl7lnObcPAwyvabRWUMyeQxi4zW0OVJCLw0diG8/w640-h554/crdb4_30datapatch.png" width="640" /></a></div><div><br /></div><div><br /></div><div>And the creation + opening:</div><div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGutt7Oy4ll7_z9faKZVgj1ZdyOFt7KUQHOhITkr3NrKM6_dInS__WdoVxO0yVgrDdRU7CmrX6mOJzXF3wxxXKsikiirAN0ceY1In71YW6lWpfFhnMrlRuPeCGyPjZULrWim8vp7Gp03P4CJNubSFBLtLe-9sP2NBelIcnNCoGIR2FwFnLMX0sG4QwemU/s1646/crdb4_31cre_open.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1546" data-original-width="1646" height="602" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGutt7Oy4ll7_z9faKZVgj1ZdyOFt7KUQHOhITkr3NrKM6_dInS__WdoVxO0yVgrDdRU7CmrX6mOJzXF3wxxXKsikiirAN0ceY1In71YW6lWpfFhnMrlRuPeCGyPjZULrWim8vp7Gp03P4CJNubSFBLtLe-9sP2NBelIcnNCoGIR2FwFnLMX0sG4QwemU/w640-h602/crdb4_31cre_open.png" width="640" /></a></div><br /><div>You can see the creation of 3 PDBs, with not drama at all. One of them has the "classic" name ORCL, just because I can. I do now wonder if I can't find a way to leave out the admin-user part as well. I also left the original "select" statement from the generated script (hint: something about a name "hardcoded" maybe).</div><div><br /></div><div>We then witness the OPEN-ing of each PDB and if you run the script for yourself, the command "show pdbs" will display them, all of them this time. In the end there is also a "register" to let the listener know what was added.</div><div><br /></div><div>If you go back and check your filesystem and listener: Creating will have taken up about 3G of diskspace, and the listener will show the new PDBs as services. </div><div><br /></div><div>Checking from a "client machine" to connect over the listener:</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_Sf477_dP314T5T-wbH3hu5B6uEXVI7odklkjQNKeRJ4wH_XOf6kmNagcLrDnvCXqXqqc6C5aegBPYWpHdYuLNoWt66Q4JjR5MbyULQqJfwsu5GzdfYM2cLTzAb737jK0Va1HNB6RAj_kcPcPa7lPxv3L7eyHZpblaPBkjHmAZikxthIRMwcyrU_3Stk/s1518/crdb4_35client.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1376" data-original-width="1518" height="580" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_Sf477_dP314T5T-wbH3hu5B6uEXVI7odklkjQNKeRJ4wH_XOf6kmNagcLrDnvCXqXqqc6C5aegBPYWpHdYuLNoWt66Q4JjR5MbyULQqJfwsu5GzdfYM2cLTzAb737jK0Va1HNB6RAj_kcPcPa7lPxv3L7eyHZpblaPBkjHmAZikxthIRMwcyrU_3Stk/w640-h580/crdb4_35client.png" width="640" /></a></div><div><br /></div><div>Of course, from a client machine, I can connect to any of the open PDBs. Just to show how Easy.</div><div> </div><div><br /></div><div><b>Almost Done... </b></div><div><br /></div><div>We now have 3 PDBs and I can play with them. Nice. I got more or less what I wanted. We are Nearly There.</div><div><br /></div><div>Just a little bit of housekeeping to do: remember that postPDBCreation.sql? It did set the default to USER-tablespace, did OPatch and did a number of selects. I am going to respect the same commands and same order of events, more or less. But I have edited that to become a script to be run per-PDB, with the PDB-name as argument (the &1 in the oracle sql-script world):</div><div><br /></div><div>The script crdb4_postcheck.sql:</div><pre><code>
/* ---------------
crdb4_postcheck.sql pdb_name: set user-ts, do OPatch and run some selects
arg1 : PDBNAME to check, e.g. FREEPDB1 or ORCLPDB2
notes:
- adapted from postPDBCreate_freepdb1.sql, as genrated by dbca-v21c.
-----------------
*/
-- pick arg from cmdline.
define targetPDB=&1;
-- get the pwds, we need m
@accpws
-- stmnt and their sequence-order copied from generated postPDBCreate
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
spool crdb4_postchecks.log append
alter session set container="&&targetPDB";
prompt Adding + setting dflt tablespace USERS, If needed...
set echo on
CREATE SMALLFILE TABLESPACE USERS LOGGING
DATAFILE '/opt/oracle/oradata/FREE/freepdb1/users01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 20M MAXSIZE 1G ;
ALTER DATABASE DEFAULT TABLESPACE USERS;
-- next, the call to OPatch, added OH and SID
host $ORACLE_HOME/OPatch/datapatch -skip_upgrade_check -db $ORACLE_SID -pdbs &&targetPDB
-- queries, copied straight from genereated v21c original.
-- not sure if essential
connect "SYS"/"&&sysPassword" as SYSDBA
-- expect TRUE
select property_value
from database_properties
where property_name='LOCAL_UNDO_ENABLED';
connect "SYS"/"&&sysPassword" as SYSDBA
alter session set container="&&targetPDB";
select TABLESPACE_NAME
from cdb_tablespaces a,dba_pdbs b
where a.con_id=b.con_id and UPPER(b.pdb_name)=UPPER('&&targetPDB');
connect "SYS"/"&&sysPassword" as SYSDBA
alter session set container="&&targetPDB";
set echo on
Select count(*) nr_valid_DV
from dba_registry
where comp_id = 'DV'
and status='VALID';
-- go back to root.. why?, does exit straight after..
alter session set container=CDB$ROOT;
exit
</code></pre><div>And ... running that script, 3 times, Went Fine: the USER-tablespace came from the PDB$SEED, it was already there. If it is ever missing, the error will notify us. Setting the default tablespace is no problem, and running Opatch/datapatch works Fine. The last select, checking for DV (DataVault, installable per catols.sql) isnt present here because it wasnt included in the "generated scripts" for v21c. Something I could correct, but was too Lazy...</div><div><div><br /></div><div>Plus, I can now Rapidly provide dev/test systems (mostly containers) and customise them to my hearts content... </div><div><br /></div><div><div>Also: (in scope, but this blog is too long already): Dropping a PDB including datafiles, gives you back the disk-space immediately, which I think is quite nice.</div><div><br /></div></div><div><div>I've run most of my ppt-demo SQL scripts, and they work fine agains 23c! (I had not expected anything else). I can use this container in my next presentations and be one of the Uber-Hip crowd using v23c. How Cool is That?</div><div><br /></div></div><div><br /></div><div><b>I would say: This is Success !</b></div></div><div><br /></div><div>So in summary, I was able to create several PDBs, with minimal configuration and minimal typing-effort. Mostly due to the OMF parameters in my spfile and using as many defaults as I can.</div><div><p>My main "obstacle" was actually the "typing effort" in crdb3_com.sql: installing all the various components. And the testing and removal of typos to make the whole exercise look even more Simple.</p><p>I will now sagely, as a well behaved Oracle-DBA, wait for the actual GA (General Availability) of v23c. A GA which hopefully still comes with a DBCA that can generate scripts that I can read and alter. </p><p><br /></p></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsQmv2Ts-_RXTAPM-C-zQN0_KuuaPmFj7G6xXD15c-i9k6_PRA3QMzMqR8k1K5X6mtpNZ4lG71wZfXdBup3Td9_WZ8v-SS3-HPwzxq8mVLlu6dqV9Gq4tTbkr3aspC7erRx13y_p_v5JG6tembXySkKJcCsWpvOp6AM_G8iVai8YYUGhdXIaOB5mRetRg/s239/warning_triangle.jpeg" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="211" data-original-width="239" height="211" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsQmv2Ts-_RXTAPM-C-zQN0_KuuaPmFj7G6xXD15c-i9k6_PRA3QMzMqR8k1K5X6mtpNZ4lG71wZfXdBup3Td9_WZ8v-SS3-HPwzxq8mVLlu6dqV9Gq4tTbkr3aspC7erRx13y_p_v5JG6tembXySkKJcCsWpvOp6AM_G8iVai8YYUGhdXIaOB5mRetRg/s1600/warning_triangle.jpeg" width="239" /></a></div><div><br /></div><div><b>Warning</b>: writing this up took me much more time then anticipated. Actually creating the system was easy. But tweaking the scripts to be 0-error, and Portable, and chasing down some of the nitty-gritty details took waay too much time... Anyway, it was interesting enough to do it. Maybe it will be useful knowledge one day.</div><div><br /></div><p><b>The List of "Always More ToDo"...</b></p><p>I wanted a quick-create, .. reading + investigating turned out 4 waaay to long blogposts... And ... There is always more. The list got so long it would merit a separate blog+discussion.. Here are some of the items you can think about, or maybe I'll do some writeups later.</p><p>Experiment with Read-Only Oracle-Home. Ensure maximum integrity of the software-tree. Separate Software from Data was always a good concept.</p><p>Experiment with mounted volume and then store the databases, and the admin- and the admin-output (diag!) on a mounted-volume. That could make the container much smaller still. (This would be my Prio if I was deploying via Containers...?)</p><p>Completeness.... I'd really like to see the scripts generated by v23c to make sure I got all the components and activities Correct: complete and in the right order etc... Up to now, this is just a hobby-project with a lot of Guesstimation involved.</p><p>Directory-names: I dont like the overly-long generated names for PDB-subdirs. I'll have to investigate the "simplest possibly" way to use FILE_NAME_CONVERT...</p><p>The UNDO=Local setting merits some investigation. Can that save me more effort+space? What is best/easiest, and when? For the moment, I'll just point you to Tim "Oracle-base" Hall for <a href="https://oracle-base.com/articles/12c/multitenant-local-undo-mode-12cr2" target="_blank">reading up on the topic</a>.</p><p>Housekeeping, files: My "disk system" got full a few times during these experiments. There are MAX_SIZE clauses for data, audit and diag, and those Will be Useful. Alternatively: Check your subdirectories under the "diag" tree, and the output-log-dir for catcon. Especially when providing "dev-/test-containers" to developers, you dont want your system to run into "mysterious errors" due to disk-full problems.</p><p>Housekeeping, drop PDB: Demonstrate how dropping a PDB frees up disk-space, Automatically. Lazy-me is Really Happy with this (OMF-)feature. But dropping a PDB leaves an empty subdirectory. Not a big problem, but I'd rather have that cleaned anyway. Requires another trick (script).</p><p>Script-beautifying: Evidently. Also, the original (generated) scripts do a lot of (re)connects and spools.. I can still make my scripts cleaner by removing seemingly useless "connect" and housekeeping stmnts. (I like small, clean scripts...).</p><p>LockAccount: The crdb3_com.sql now contains a copy of the generated code. On close inspection: this is identical, repeating code. Make it a call to a script.</p><p>Make a single, better, crdb4-script: a Simple script, taking the PDB-Name as input, should be able to add (or later remove..) a PDB, including the OPatch and some of the checks.</p><p>The OPatch, needed a bootstrap. Doesnt seem like a critical problem to me, but would not discard it. Easily Fixable, MOS note: 2538591.1. Best add that to crdb3_com at some point (after CDB, but before 1st PDB).</p><p>Test a patch or an upgrade: This will Inevitable need to be done sometime...</p><p>On perl: I'm still not very happy with the perl-stuff as it is yet another component, and yet another dependency. The scripts have a hardcoded path to the oracle-provided perl-executable in them. And another, different-version of perl is found in /usr/bin, which apparently the scripts don't trust or don't want you to use. It makes sense, but it is a bit cumbersome.</p><p><br /></p><p><b>But in the end: Happy! </b></p><p>At least I got my kick of creating a Database from Scratch using scripts. I got the learnings, now documented in a set of scripts. And my scripts seem Portable enough to be able to create a v23c DB on just about any (linux)platform (VMs, containers) even without the templates.</p><p>If anyone wants a copy: copy/paste from the blog seems to work, or drop me a message for the repository.</p><p>------------ end of part 4 -------------</p><div><div>Disclaimer etc. ... nah. </div><div>OK. Find disclaimer at the bottom of the <a href="http://simpleoracledba.blogspot.com/2023/08/minimalistic-creation-of-oracle.html" target="_blank">starting-post.</a></div><div><p>And remember what I wrote earlier: <b>Dont Do This At Work</b> (or... not yet).</p><div><br /></div><div>*) Footnote: </div><div>On the use of hardcoded-file-path-name for database-files (do I have to say this..?): How often did you end up with one or two files (crucial, needed datafiles) in a rogue location because of forgotten or badly-replaced filenames in re-used scripts ? Folks have overwritten files because of this, folks have ended up with "windows file-path-names" in the DBS-directory of a linux-systems bcse of this. (do I have to do a blog on this with bad-examples... )</div></div><div>Minimise hardocoded paths, please.</div><div><br /></div></div><div><br /></div><div>-------- the Real End of Part 4 ------- Maybe parts 5, 6 etc., but Later --------</div>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-23047292978824449252023-08-10T04:32:00.009-07:002023-08-14T09:26:43.278-07:00Create a Database v23c - Installing components Manually<p>TL;DR: I simplified the assistant-generated scripts from v21c, I learned a few things, and it all still worked. We are slowly Getting There in v23c </p><p>Spoiler: still a long-ish script, but it worked Fine.</p><p>Note: Aug 2023, still using the Free Developer Release of 23c.</p><p><b><br /></b></p><p><b>Background:</b></p><p>Still on my quest of trying to <a href="http://simpleoracledba.blogspot.com/2023/08/minimalistic-creation-of-oracle.html" target="_blank">create a database, manually-scripted,</a> without using (recovering) "the template". But with minimal effort, minimal scripting, minimal typing (even minimal RTFM, which is never a safe idea...). In parts 1 and 2. Earlier I explained how+why I am using a set of scripts generated by the previous version, v21c.</p><p>It was more work than I expected. In the next stage I am going to get angry with "too many, too complicated and too-hardcoded scripts". But it seems the old-ish code Works Fine against the New Version. Legacy ;-)</p><p>Again: Keep in Mind that I am using the "Free Developer Release v23c". No guarantee this will work correctly, and nog guarantee this will still work when v23c comes out for General Availability.</p><p>I can't wait to get a G.A version and check if the new DBCA still agrees with my approach.</p><p>But... So far it is going Well.. I got to the stage where "Catalog" is created in the CDB and in the SEED with little problems. Here is how:</p><div><br /></div><div><b>The components (list taken from v21c)</b></div><div><br /></div><div>When I check the generated script for creation of a v21c database, it shows the following:</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTrOVydznk5i8bVhkWxEJrKtOX3Adr3jl5wE7pW5342Tit5TxIt6yvz5xUM2dXdDGjV8dEyRj_Wsh3J8Wyj72oFMYfAz_cWWuuvugplPgA_J8FdKtpenp4xM_RQkyeWVOR5gGxMeWSQTfziPLpGgNGw9sPYN1_gv-BF8uXDa0zRAwnPL1uNqrwRgqVnJ0/s1476/crdb3_freesql.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="962" data-original-width="1476" height="418" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTrOVydznk5i8bVhkWxEJrKtOX3Adr3jl5wE7pW5342Tit5TxIt6yvz5xUM2dXdDGjV8dEyRj_Wsh3J8Wyj72oFMYfAz_cWWuuvugplPgA_J8FdKtpenp4xM_RQkyeWVOR5gGxMeWSQTfziPLpGgNGw9sPYN1_gv-BF8uXDa0zRAwnPL1uNqrwRgqVnJ0/w640-h418/crdb3_freesql.png" width="640" /></a></div><br /><div>Mind the long-ish list of items after "CreateDBCatalog" <a href="http://simpleoracledba.blogspot.com/2023/08/minimalistic-creation-of-database-v23c.html" target="_blank">(covered in part-2)</a>. Those scripts install the "additional components" that the DBCA wanted me to install. That list looks familiar: that same list has been there since waay back. I guess I'd better run (most of) those scripts on v23c as well...</div><div><br /></div><div>The first script, JServer.sql, looks like:</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQgAH7BmZrDP157Ba62wtvDTyXtueVvJ60KZJC-IaziOSxZnX0IfuqYvvq_OeD-sxRvoYB6N36DYwrjHfjbZbYBinPH4BCEPOqksbybooGJl-cBRChhBwSE4COZxElxeoAvKH16Hlm4-KcSYhAvCt_tmqfIu8l2p8R10KfOWMfKl79GsiHV0Qbvs2qgfM/s1478/crdb3_jserver.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1026" data-original-width="1478" height="444" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQgAH7BmZrDP157Ba62wtvDTyXtueVvJ60KZJC-IaziOSxZnX0IfuqYvvq_OeD-sxRvoYB6N36DYwrjHfjbZbYBinPH4BCEPOqksbybooGJl-cBRChhBwSE4COZxElxeoAvKH16Hlm4-KcSYhAvCt_tmqfIu8l2p8R10KfOWMfKl79GsiHV0Qbvs2qgfM/w640-h444/crdb3_jserver.png" width="640" /></a></div><br /><div>As you can see, similarly long commands as we have seen in the "CreateDBCatalog" in part-2, except that I already put in $ORACLE_HOME to simplify. And by opening the scripts one after another, they all look more or less similar (and equally hard to read on first attempt) Note: I Hate long command-lines and complicated scripts - apologies if I repeat that too often.</div><div><br /></div><div>On first attempt, they ran fine after just editing the file-paths to adjust to 23c, Good sign. But I wanted to do a little more Inspection using Eyeball Mark-I on a large-screen (e.g. have a good look at the subject...). So I concatenated them all into one big script and viewed them on another editor, to better horizontally compare the various lines.</div><div><br /></div><div>The following of the generated scripts were very similar: </div><div>JServer.sql </div><div>context.sql </div><div>ordinst.sql and interMedia.sql (huh?)</div><div>cwmlite.sql (olap), </div><div>spatial.sql </div><div>... are all very much alike (but not quite). They contain a lot of repeating, identical-looking calls with generally only two understandable differences: The -b <logname> and the end of the line the "script + options" are different. </div><div><br /></div><div>That makes sense, in multi-tenant, those components are installed via catcon.pl in both CDB$ROOT and PDB$SEED.</div><div><br /></div><div><br /></div><div><b>Some remarks on the scripts:</b></div><div><br /></div><div>ordinst and interMedia: Deprecated, checked them anyway. Those are now just stubs, and can probably be removed from the list of scripts to run. So I deleted them from the calling-script.</div><div><br /></div><div>CreateClusterDBViews: Doesn't use the -c (or -C) option, but is otherwise identical. Apparently, leaving out -c and -C runs the scripts against both CDB$ROOT and PDB$SEED. It runs both catclust and catfinal (which is effectively empty), thereby sort-of signalling the "End of Catalog stuff".</div><div><br /></div><div>LockAccount: Does a stop/start of PDB$SEED and then runs a block of PL/SQL in both CDB$ROOT and PDB$SEED to presumably lock-down default accounts. OK. We can keep that code as is for the moment (would v23c contain more dflt-accounts, more items to lock or protect...?).</div><div><br /></div><div>PostDBCreation: Generates the spfile and does the classic utlrp. OK, that should fix the "invalid objects" we had after doing the catalog in part-2.</div><div><br /></div><div><br /></div><div>Then a bit of a surprise, good and bad: The last three scripts are: </div><div><br /></div><div>PDBCreation: Only contains a spool-append command to a logfile.</div><div><br /></div><div>plug_freepdb1: Creates an Actual PDB (yesss, finally...), with the name "hardcoded" in the script, and file-name-convert directives included. I'd like to tweak this stmnt to at least get rid of hardcoded names + paths.</div><div><br /></div><div>postPDBCreation_freepdb1: Adds a user-tablespace with hardcoded datafile-path (hm), it does an OPatch, and does a number of selects to report things to spoolfile.</div><div><br /></div><div>I am going to keep the PDB-related scripts (the last 3) for later, and first run the scripts to add all the (mandatory or recommended?) components to CDB and SEED. </div><div><br /></div><div><br /></div><div><b>To Simplify...</b></div><div><br /></div><div>Already I had them all concatenated into one, so I decide to consolidate the scripts up-to, but not including, any Create-PDB work. I'll keep everything in one single script, call it crdb3_comp(onents).sql. And I cannot resist to simplify it just a little.</div><div><br /></div><div><div>I am also going to keep the spfile-creation under my own (manual, but scripted) control, putting a "comment" in front of that command, for the moment (yes, I understand the risk.. some script may want to set parameters to spfile/scope-both).</div><div><br /></div><div>The idea for crdb3_comp.sql is born.. now just had to do a bit of editing+testing.</div><div><br /></div></div><div>The main changes I made are reduction of hardcoded file-paths and smaller commands over-all, for readability and editability. I then removed a lot of lines that did just spool-append. I'm keeping everything the main logfile. There will also be the logfiles from catcon.pl which I directed to /tmp.</div><div><br /></div><div>The resulting script, crdb3_comp.sql:</div><div>
<pre><code>
/* -----------------
crdb3_comp.sql : install basic components before creating more pdbs
notes:
- wasnt happy with the long commandlines, still not happy
- after utlrp: 0 invalids. Good Sign.
- why repeated connect? to verify un/pw ?, or Need for fresh session??
- option -a is undocumented (Martin berx Berger traced: probably GUI/windows related)
--------------- */
-- get passwords
@accpws
-- one main logfile for this script...
spool crdb3_comp
-- Wanted shorter commands to Run CatCon.. Hence define rcc
DEFINE rcc="$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -v "
prompt first JServer
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
host &&rcc -b initjvm -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/javavm/install/initjvm.sql;
host &&rcc -b initxml -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/xdk/admin/initxml.sql;
host &&rcc -b xmlja -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/xdk/admin/xmlja.sql;
host &&rcc -b catjava -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/catjava.sql;
prompt next is context
-- keeping the connect, as each "script" originally did a fresh connection
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
host &&rcc -b catctx -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" -a 1 $ORACLE_HOME/ctx/admin/catctx.sql 1Xbkfsdcdf1ggh_123 1SYSAUX 1TEMP 1LOCK;
host &&rcc -b dr0defin -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" -a 1 $ORACLE_HOME/ctx/admin/defaults/dr0defin.sql 1\"AMERICAN\";
host &&rcc -b dbmsxdbt -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/dbmsxdbt.sql;
prompt ordinst and interMedia: stubbs removed, product no longer avaiable.
prompt next is cwmlite.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
host &&rcc -b olap -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" -a 1 $ORACLE_HOME/olap/admin/olap.sql 1SYSAUX 1TEMP;
prompt next is spatial.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
host &&rcc -b mdinst -c 'PDB$SEED CDB$ROOT' -U "SYS"/"&&sysPassword" $ORACLE_HOME/md/admin/mdinst.sql;
prompt next is CreateClustDBViews.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
host &&rcc -b catclust -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/catclust.sql;
host &&rcc -b catfinal -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/catfinal.sql;
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
prompt next is lockAccount.sql
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;
BEGIN
FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
'SYS','SYSTEM','SYSRAC','XS$NULL') )
LOOP
IF item.AUTHENTICATION_TYPE='PASSWORD' THEN
dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' password expire account lock' ;
ELSE
dbms_output.put_line('Locking: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' account lock' ;
END IF;
END LOOP;
END;
/
alter session set container=pdb$seed;
BEGIN
FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
'SYS','SYSTEM','SYSRAC','XS$NULL') )
LOOP
IF item.AUTHENTICATION_TYPE='PASSWORD' THEN
dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' password expire account lock' ;
ELSE
dbms_output.put_line('Locking: ' || item.USERNAME);
execute immediate 'alter user ' ||
sys.dbms_assert.enquote_name(
sys.dbms_assert.schema_name(
item.USERNAME),false) || ' account lock' ;
END IF;
END LOOP;
END;
/
alter session set container=cdb$root;
prompt next is postDBCreation.sql
SET VERIFY OFF
host $ORACLE_HOME/OPatch/datapatch -skip_upgrade_check -db FREE;
connect "SYS"/"&&sysPassword" as SYSDBA
prompt note on spfile: I prefer to do my spfile manually,
prompt and at the moment in dflt location
prompt I might consider moving it out of OH later.
-- create spfile='/opt/oracle/dbs/spfilefree.ora' FROM pfile='/opt/oracle/admin/free/scripts/init.ora';
connect "SYS"/"&&sysPassword" as SYSDBA
host &&rcc -b utlrp -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/utlrp.sql;
select comp_id, status from dba_registry;
shutdown immediate;
connect "SYS"/"&&sysPassword" as SYSDBA
startup ;
spool off
prompt Done crdb3_comp. Please Verify: errors, invalids objs etc...
</code></pre></div><div>That is the script I did the actually run with.</div><div>(feel free to copy, I checked: Chrome, Firefox and Opera.. allow copy-paste. One day I'll get around to using proper code-windows in blogger..)</div><div><br /></div><div>Now... Guess how many invalid-objects? .... None in the CDB.</div><div><br /></div><div>And in the PDB$SEED, where we still had 600+ Invalid objects after stage 2... :</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrB0pj3CgutxeBM5HdzO23x5qw4_K_E8Sakpxd16Dm-Of4j_YqwO0ENgxtSKn5Ji3mLKgxIEqJfPG-_8I7y3WfDukD2vHioeSH1_nN9kweQ_oE36NNdFqssILbf8oiJSuxTq4i7lhISS0hZcVreinidMne7hIqy5wa5oVrdidqJ1sgxoxGSsmjkTj_RLY/s1312/crdb3_zero_invalid.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1032" data-original-width="1312" height="504" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrB0pj3CgutxeBM5HdzO23x5qw4_K_E8Sakpxd16Dm-Of4j_YqwO0ENgxtSKn5Ji3mLKgxIEqJfPG-_8I7y3WfDukD2vHioeSH1_nN9kweQ_oE36NNdFqssILbf8oiJSuxTq4i7lhISS0hZcVreinidMne7hIqy5wa5oVrdidqJ1sgxoxGSsmjkTj_RLY/w640-h504/crdb3_zero_invalid.png" width="640" /></a></div><div><br /></div><div>Really Happy so far. Off course, I have no idea if v23c has or will introduced additional components or scripts to run at Create-Time. I havn't even checked the <a href="https://docs.oracle.com/en/database/oracle/oracle-database/23/multi/creating-a-pdb-from-scratch.html#GUID-CCA938BD-52BE-422C-86AF-576D3E02B96B" target="_blank">fairly long page of RTFM</a> in all its details on this yet....</div><div><br /></div><div>First, I want my Databases! </div><div>With Maximum-Lazyness-Default, minimal effort and Minimal-scripting (but Readable and Portable scripts, Please...).</div><div><br /></div><br /><div><b>So far So Good.</b></div><div><br /></div><div>All in all, it looks like the generated scripts from v21c are still working. This combination of CDB + Seed should now be ready for PDB-creation. I will of course try to minimalise and simplify that create-process, just a little.</div><div><br /></div><div>Next blog will describe the creation + running of crdb4_pdb.sql in which I finally manage to create 2 PDBs. It worked. But I wasn't quite happy with the result, yet.</div><div><p>------- End of Part 3 ------</p><p>Disclaimer: Don't Try this at Work.</p><p>But if you seriously plan to try this at work: <a href="http://simpleoracledba.blogspot.com/2023/08/minimalistic-creation-of-oracle.html" target="_blank">Read The Disclaimer underneath Part 1.</a></p></div><div>PS: Special Thanks to <a href="https://berxblog.blogspot.com/" target="_blank">Martin "berx" Berger</a> for digging down the catcon.pl -a option. </div><div><br /></div><div>----- Real End of Part 3 ----</div><div><br /></div>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-21406762151047755492023-08-08T11:33:00.002-07:002023-08-08T11:33:40.727-07:00 Minimalistic creation of a Database v23c - adding catalog.<p>TL;DR: Creating the "catalog", using a script from v21c as example. Beware: v23c Can Differ !</p><p>Spoiler: It ends with Optimism.</p><p><br /></p><p><b>Background:</b></p><p>Still on my quest of trying to create a database, scripted, but with minimal effort, minimal scripting, minimal typing. Because the create-assistant from "Free Developer Release 23c" doesn't provide an option to generate scripts yet, I took the set of scripts generated by the DBCA from a v21c as example.</p><p>Warning: This is part 2 of what could now become a 3 or 4-part series... <a href="http://simpleoracledba.blogspot.com/2023/08/minimalistic-creation-of-oracle.html" target="_blank">(here is Part 1)</a></p><p><br /></p><p><b>Next: Catalog.sql</b></p><p>Next step is to run the "catalog" scripts. From the generated scripts (which are from version v21c!!), I see that the first script to run after the basic "create database" is the CreateDBCatalog.sql.</p><p>This is a script that uses catcon.pl to run four components:</p><p>catpcat.sql - a fancy utility to run the old catalog and catproc</p><p>owminst.plb - known as workflow, apparently wm goes into CDB</p><p>pupbld.sql - known pub stuff for SQL*Plus, CLI tools</p><p>pupdel.sql - didn't know this one yet, but OK, seems a logical addition</p><p>The Challenge here, even in very old versions of DBCA, was always the adaptation of the many hardcoded file-paths. The DBCA would assume you only wanted to run the scripts on the one server where you generated them. Porting those scripts to anther server was a recipe for problems...</p><p>The original CreateDBCatalog, generated from DBCA v21c looked like this:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiF7nrj7RsVJALQhotPsJMuT6VvmrhvJ4CmJaa0J4lGQI0YqjTddx4bWuzX8ehykb-hLZWuFZzHpkhaeE0HuFtGFIGgzU279TnILRQJGd8qJuPEz1I_-0jPiW_6ZLwInHK1EQ2M5bEaRFuuhvGH4cAcrHCQBonW9Ve4pUlw07gGUkBzZAUNyIcP3ZulNsU/s1576/crdb_cat_orig.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1576" data-original-width="1474" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiF7nrj7RsVJALQhotPsJMuT6VvmrhvJ4CmJaa0J4lGQI0YqjTddx4bWuzX8ehykb-hLZWuFZzHpkhaeE0HuFtGFIGgzU279TnILRQJGd8qJuPEz1I_-0jPiW_6ZLwInHK1EQ2M5bEaRFuuhvGH4cAcrHCQBonW9Ve4pUlw07gGUkBzZAUNyIcP3ZulNsU/w598-h640/crdb_cat_orig.png" width="598" /></a></div><br /><p>Good luck deciphering that on a 80x25 terminal, but ok...</p><p>I could not resist trying to simplify a little: I added $ORACLE_HOME where appropriate, and I redirected the logs to /tmp (good riddance, and Yes I still read logfiles. Sometimes..). This made the scripts a _little_ more simple and portable but I still think they should be made more readable, Simpler. Might do Later.</p><p> After some editing version looked like this (copy-able code, I hope...):</p>
<pre><code>-- crdb2_cat.sql : call scripts to generate "catalog" etc..
-- adapted from CreateDBCatalog.sql, as genrated by dbca-v21c.
-- get the pwds, we need m
@accpws
set echo on
set verify off
connect "SYS"/"&&sysPassword" as SYSDBA
spool crdb2_cat.log append
alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;
-- oracle-home missing for catpcat? supposedly catctl.pl catches that ?
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -u "SYS"/"&&sysPassword" -n 2 -icatpcat -c 'CDB$ROOT PDB$SEED' -a -d $ORACLE_HOME/rdbms/admin -l /tmp rdbms/admin/catpcat.sql;
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -v -b owminst -U "SYS"/"&&sysPassword" $ORACLE_HOME/rdbms/admin/owminst.plb;
-- make sure system pwd is correct
alter user system identified by "&&systemPassword" ;
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -v -b pupbld -u SYSTEM/&&systemPassword -U "SYS"/"&&sysPassword" $ORACLE_HOME/sqlplus/admin/pupbld.sql;
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -v -b pupdel -u SYS/&&sysPassword -U "SYS"/"&&sysPassword" $ORACLE_HOME/sqlplus/admin/pupdel.sql;
-- again, make sure system pwd is correct, got error here ?
alter user system identified by "&&systemPassword" ;
connect "SYSTEM"/"&&systemPassword"
set echo on
spool /opt/oracle/admin/free/scripts/sqlPlusHelp.log append
host $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -v -b hlpbld -u SYSTEM/&&systemPassword -U "SYS"/"&&sysPassword" -a 1 $ORACLE_HOME/sqlplus/admin/help/hlpbld.sql 1helpus.sql;
spool off
</code></pre>
<p>Running this script took +/- 2hrs on my 5yr old, trusty MB-Air but only just >1hr on the heavy, 10yrs old, MB-Pro (which is still overall faster, and has more CPUs). I always end up doing a lot of testing on that old thing.</p><p>But the result was OK. No alarming warnings in the *.log and *.lst files.</p><p>----- intermezzo -----</p><p>Checking for compile errors... </p><p>The CDB$ROOT had 1 invalid object, a trigger on the sqlplus-help system. I can live with that "error", for the moment. The PDB$SEED has 600+ INVALID objects. That may need investigation. But I know there are some calls to utlrp in the next set of scripts. And Don't Forget: This is a generated script taken from v21c, and ported to a "Free Developer Release v23c". Which means that We are effectively testing the Big Red Flagship product, for Free.</p><p>----- end of intermezzo -----</p><p><br /></p><p><b>Did it Work: Yes (but...?)</b></p><p>What worries me a little is that during the fairly long runtime of these scripts, I did some <a href="https://docs.oracle.com/en/database/oracle/oracle-database/23/multi/creating-and-configuring-an-oracle-database.html#GUID-807DE711-C82C-4BB2-8C31-5EE89CA71349" target="_blank">RTFM on Create-Database v23c</a> (remember, folks, Always RTFM). I stumbled across the need to run <b>catcdb.sql and catcdb.pl.</b> I checked: that script isn't called anywhere in the generated scripts. But a quick glance in catcdb.sql and the catcdb.pl scripts show that catcdb.sql does _approximately_ (my interpretation!) the same as the generated CreateDBCatalog: it invokes catalog, catproc, owminst, and pupbld. Also, catcdb.sql seems intended for interactive usage (it asks for passwords at the commandprompt! )</p><p>For the moment I'm going to assume that CreateDBCatalog (intended for automatic-running) and catcdb.sql (plus catcdb.pl) are equivalent, where the latter is meant for purely for interactive usage. (this needs more RTFM-ing, or maybe some reader already Knows..)</p><p>In future, I hope the DBCA for v23c will soon allow me to "generate scripts". That way I can (should) be able to get a more Quality Assured version of these scripts.</p><p><br /></p><p><b>File Sizes, possibly important:</b></p><p>Also, while running the "catalog" script(s), I noticed the datafiles growing bit by bit, so after several tests, I decided again to help a little by resizing them proactively. (I already knew the the resize-command works straight after the create-database, see earlier blog). I begin to appreciate the construction whereby you can specify sizes on "create time", and the SEED is stated to inherit from the CDB, and the PDBs then inherit from the SEED. It is just that I dont like to add too many items to my "minimalistic" create scripts yet. If I hardcode 1G size now, that may bite me in the tail later...</p><p>If you consider creating manually (which I probably would not recommend) and if you also want to limit size (for example to keep your container-footprint small), then study this part, check/search for the "tablespace_datafile" clauses and their cascading effect on ROOT and SEED. And try to aim for small datafiles (even if I would prefer tablespaces of type "bigfile", but that is another discussion).</p><p>Additionally, I hate it when an UNDO tablespace grows to several 100s of M, but cannot quickly be shrunk down due to the infamous ORA-03297 (grey-bearded DBA's will add this number to their hate-list, with 1555 and 12154 etc..). </p><p>Note to Oracle: Can we fix that? Can I request EASY UNDO-RESIZING, for the next release (v25d or whatever) ? </p><p>In a real world, there is always someone who will (+/- correctly) say: But Disk is Cheap Now... Yes, that is true. but I still don't like to waste resource. </p><p><br /></p><p><b>So Far So Good.</b></p><p>I seem to have survived up to this stage. </p><p>Next, I will go over on the various components that the DBCA (when it was v21c and earlier) normally would install in the CDB, followed by some post-creation steps (stage 3, let's call it crdb3_..) and then I will hopefully, finally, add 1 or 2 PDBs (crdb4 ?). All strictly "scripted" of course, and from scripts that I hope are small, Simple, Readable and Portable (to linux/unix systems).</p><p><br /></p><p>------- End of Part 2 ------</p><p>Disclaimer: Don't Try this at Work.</p><p>But if you seriously plan to try this at work: <a href="http://simpleoracledba.blogspot.com/2023/08/minimalistic-creation-of-oracle.html" target="_blank">Read The Disclaimer underneath Part 1</a>.</p><p>------ Real End of Part 2 ------</p><p><br /></p><p><br /></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-60701173152862167222023-08-07T11:39:00.005-07:002023-08-13T23:39:00.554-07:00Minimalistic Creation of an Oracle Database, v23c<p> TL;DR: To create a v23c database "manually", with Minimal effort and minimalistic scripts, here is how I did it.</p><p>Spoiler: It Worked, in the end.</p><p>Note: Aug 2023, still using the Free Developer Release of 23c.</p><div><br /></div><p><b>Goal: </b></p><p>The aim is to be Lazy, to type minimal code, and to keep scripts as simple as possible, but still create a complete database: a Container + at least 2 PDBs. And do this for version 23c, for which the Free Developer Release 23c is available for download since early 2023.</p><p>I would like to use "simple commands" that can be easily and flexibly used by Developers, Dev-supporting DBAs and SysAdmins.</p><p>My resulting scripts should be usable to create a database quickly on Any (linux) platform where Oracle can be installed.</p><p><br /></p><p><b>Background: </b></p><p>When I tried to create a database with the developer-release of version 23c, I first noticed that the trusty "db create assistant", the DBCA, would only allow me to use a template. The Assistant didn't offer the option to "generate scripts" anymore. I liked those scripts, both as examples and for inspiration/research (what is in there...? what happens ? is sql.bsq still used...?). As v23c comes together for General Availability (GA) the <a href="https://forums.oracle.com/ords/apexds/post/creating-database-from-scratch-createdb-sql-etc-9055" target="_blank">DBCA-assistant </a>will be made "complete" again (or so Gerald and Loïc +/- promised). Not to be stopped, I went back to my notes since versions 11.2 and 19.3, from when I created the databases all by myself, and I tried to apply it to 23c. Here is "The Report".</p><p><br /></p><p><b>More on how I approached it: I took some old examples.</b></p><p>To depart, I used the generated scripts from a v21c assistant, where the assistant still allowed me to do the "generate scripts". These scripts were my examples on how to install and complete all the (required and more) components. I would definitely recommend to simplify those scripts a little, if that is possible (Oracle?).</p><p>But my first obstacle was, dont laugh:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqu5rkcB1ZbAQx5qHOVSJFHhaKbpOIX1SJ9zoO0V3EQ5vlH8qvAj-DZZYiesyAAD98wznqqknwy-tdISO1Va-JKkPLL61pWsodKJE0qXauzguqMo-DGC22gq_Gr4AIVZX-9JaSsd2KaGOPV_J-IvJsLjS2lkGRsUud0ONRjdKLIJMLghgPBx_4A9KLGFc/s1612/crdb_sid_free.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="602" data-original-width="1612" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqu5rkcB1ZbAQx5qHOVSJFHhaKbpOIX1SJ9zoO0V3EQ5vlH8qvAj-DZZYiesyAAD98wznqqknwy-tdISO1Va-JKkPLL61pWsodKJE0qXauzguqMo-DGC22gq_Gr4AIVZX-9JaSsd2KaGOPV_J-IvJsLjS2lkGRsUud0ONRjdKLIJMLghgPBx_4A9KLGFc/w640-h240/crdb_sid_free.png" width="640" /></a></div><p>First I had to change all occurrences of ORCL, ORCLCDB and ORCLPDBn (the defaults from the iron-age) to use the names FREE and FREEPDBn, because "FREE" is the only name the early developer-release for v23c will accept as database name. No big deal. It is a Brave New World... feeling FREE (but yeah, limited to that one name...).</p><p>Note: This demonstrates, again, <b>the need for Simplicity</b>: the less text, the less (hardcoded) code, and the less editing you have to do, the better. You become more agile, more portable, less prone to typos, and more Robust.</p><p>To make a database useful, you need a minimum of additional scrips (think catalog, catproc, etc) and at least 1 PDB, but preferably more PDBs. Hence some more scripting and experimenting to do.</p><p><br /></p><p><b>So Let's Start this...</b></p><p>First, I composed a minimal init.ora file. After some trial and error, it contained this:</p><pre><code># the minimalistic init.ora file
# name is fixed for the 23c-FREE edition...
db_name='FREE'
# file-parameters, mimimum of information to where to place files.
control_files = '/opt/oracle/oradata/FREE/control01.ctl'
db_create_file_dest = '/opt/oracle/oradata/'
db_create_online_log_dest_1 = '/opt/oracle/oradata/'
db_create_online_log_dest_2 = '/opt/oracle/oraflra/'
diagnostic_dest ='/opt/oracle'
# needed
undo_tablespace = 'UNDOTBS1'
# for PDB, we need some filename-convert ? (apparently not)
# use the default! (experiment later, at own risk!)
db_block_size = 8192
# set these out of habit..
audit_trail ='db'
compatible ='23.0.0'
# isnt this the dflt yet in 23 ? yep, it is.
# enable_pluggable_database=true
# some memory params, all goes faster if you can give space, if you have..
# you can actually leave this out, dflts may also work?? needs test.
open_cursors = 300
processes = 300
pga_aggregate_target = 512m
sga_target = 1536m
</code></pre>
<p><br /></p><p>Notice there are very few references to file-paths, meaning very little to edit if you move to another box or system. And there are at the moment minimal memory settings, which is partly because the Developer Release 23c has some <a href="https://docs.oracle.com/en/database/oracle/oracle-database/23/xeinl/licensing-restrictions.html#GUID-5B066473-0502-4ECA-9DF8-DA645386EA2A" target="_blank">built-in limitations</a>. </p><p>With that minimum of parameters, with some experience from earlier versions+attempt, and by re-reading the Doc on Create-Database (always RTFM folks, <a href="https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/CREATE-DATABASE.html#GUID-ECE717DF-F116-4151-927C-2E51BB9DD39C" target="_blank">Always RTFM</a> ), I was able to use the following, simple, minimalistic create-statement to create my first v23c database:</p><pre><code>spool crdb1
-- pwds get "accepted" in separate file, file and pwds are reused.
@accpws
-- we need the pw file, in the dflt location.
host $ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=&&sysPassword force=y format=12
-- we need at least some directories to place the data.
<pre><code>host mkdir -p /opt/oracle/oradata/FREE
</code></pre><div><code>host mkdir -p /opt/oracle/oraflra/FREE
</code></div>
set echo on
connect / as sysdba
-- there must be an init or spfile, but mine is minimal...
startup nomount ;
-- minimal create stmnt
CREATE DATABASE FREE
EXTENT MANAGEMENT LOCAL
DEFAULT TABLESPACE users
DEFAULT TEMPORARY TABLESPACE temp
UNDO TABLESPACE undotbs1
ENABLE PLUGGABLE DATABASE
SEED
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M;
-- add a few checks..
select name, open_mode from v$database ;
show pdbs
prompt Create Database Done. Please Verify.
</code></pre><p>That was my script crdb1.sql: create the database.. from minimal init-content and a minimal create command. There are a lot of caveats and Warnings needed here (Don't Try This At Work ;-) ...), but it worked. A Good Start:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjuGsbv02WsVLVB_MT_MaTJ4Dkjm0cz1mnPurmOto8P5_juCoERzDAMhGicOlCQU7CKUKliYPHpdW-Gi8ipxOoziqy4Qpanh5Pd5KYJjWxSGWSaU6mxrKuLEtAgNkchpHHRaqXMn-3Pzetl76DMucQEX4_7ijdrc5WXKmAeVSwOZNK7PTxDePddpwsC4E/s1542/crdb_crdb1.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1420" data-original-width="1542" height="590" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjuGsbv02WsVLVB_MT_MaTJ4Dkjm0cz1mnPurmOto8P5_juCoERzDAMhGicOlCQU7CKUKliYPHpdW-Gi8ipxOoziqy4Qpanh5Pd5KYJjWxSGWSaU6mxrKuLEtAgNkchpHHRaqXMn-3Pzetl76DMucQEX4_7ijdrc5WXKmAeVSwOZNK7PTxDePddpwsC4E/w640-h590/crdb_crdb1.png" width="640" /></a></div><br /><p>Result: The CDB Container and the PDB$SEED are created. Process-wise, a ps -ef | grep $ORACLE_SID shows 59 processes already. </p><p>Me Curious, so immediately checking: lsnrctl status shows:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdJ0rNZedusVKUd9D0NBbU0Zh_tBX_skFL37Qy8mAukbpcROk4rn5z9wM2qnulkJt85iRDKs5NLmQpNNQD4uPhB6R6_Bd-CADyyh0pg81cfxWG4yI_l8591vAdosoLNDJsowT4Td52W3ko7hx-iiNaLsiJ_Ec_GpMCzO4RFONTxGoP-B2Lte5x-ZmLel4/s1444/crdb_lsnrctl_status.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="860" data-original-width="1444" height="382" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdJ0rNZedusVKUd9D0NBbU0Zh_tBX_skFL37Qy8mAukbpcROk4rn5z9wM2qnulkJt85iRDKs5NLmQpNNQD4uPhB6R6_Bd-CADyyh0pg81cfxWG4yI_l8591vAdosoLNDJsowT4Td52W3ko7hx-iiNaLsiJ_Ec_GpMCzO4RFONTxGoP-B2Lte5x-ZmLel4/w640-h382/crdb_lsnrctl_status.png" width="640" /></a></div><br /><p><br /></p><p>...And because of the LISTENER, we can already connect from a client-stack with SQLcl or SQL*Plus. Aha, that needs testing:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYALXr1mwVkWgWrZIPnqxepnRb2LHMbnOZwFFU_WfgJGg3xiAOa9nrRBTdObX7Yyki8QueWnfCb7JtvBYo7A1bujudvcX0bAIzj6jF4WBRNIKmNNlgcYuLxJS_V7DB87I5Ykg2Bk4by_6S3ZcOdkqhA98UvN70NKghuP0w93ShNfh1KtzvPzB62-BYO1A/s1446/crdb_1mbair.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="758" data-original-width="1446" height="336" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYALXr1mwVkWgWrZIPnqxepnRb2LHMbnOZwFFU_WfgJGg3xiAOa9nrRBTdObX7Yyki8QueWnfCb7JtvBYo7A1bujudvcX0bAIzj6jF4WBRNIKmNNlgcYuLxJS_V7DB87I5Ykg2Bk4by_6S3ZcOdkqhA98UvN70NKghuP0w93ShNfh1KtzvPzB62-BYO1A/w640-h336/crdb_1mbair.png" width="640" /></a></div><p>Yes. From the MB-air, I can already connect, just like that. And now double-check, connect from the "server": </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgicRfpiiNvJOw0xai3jt-2SUvDh05oKnoTn7c8tAtn-yKT3ymf4_iOlf-K-zVqG51GlTkb8Ow5PtHLInaUs0Inzd7tbYlsHBP2aEoWXNSxoVo6keeVMJAY-nZh73mwg5M_kH-x3SbZyRmYhEL9aPVy7o7Zvd6b6C76HnCo6Ih3qyLDRVHbGvZjZg1SMuM/s1294/crdb_1server.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="760" data-original-width="1294" height="376" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgicRfpiiNvJOw0xai3jt-2SUvDh05oKnoTn7c8tAtn-yKT3ymf4_iOlf-K-zVqG51GlTkb8Ow5PtHLInaUs0Inzd7tbYlsHBP2aEoWXNSxoVo6keeVMJAY-nZh73mwg5M_kH-x3SbZyRmYhEL9aPVy7o7Zvd6b6C76HnCo6Ih3qyLDRVHbGvZjZg1SMuM/w640-h376/crdb_1server.png" width="640" /></a></div><br /><p><br /></p><p>And indeed from the actual "server" (in reality a bash-shell inside a container), we get the same results: Same DBID. We are looking at a (minimalistic, not finished yet) CDB. Created very "easily" with minimal editing and scripting. And I can already connect over TNS.</p><p><br /></p><p><b>Looking around:</b></p><p>Inside the database there are no USER-views or DBA-views yet, only V$ views to "inspect the system".</p><p>The First Check:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjocAw1nVzjp2AT21iFOJghVTBRL8auURQEYsnbhJcpiAIzPn5h8QO7z4x_1XRgqmD0bjBGDIWy0cFw5mA8NwD8ToxLMLZoL2rHQ85qMGekJv1Fi7JnKp2aIY1QCYr07dCc0xP9u1TlnTokAtWtCMoMhN0r0dVp7WmJ_Xr4ZYLlXGCYZVDRw-T8wYBf6Zs/s1260/crdb_chk1.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1260" data-original-width="1060" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjocAw1nVzjp2AT21iFOJghVTBRL8auURQEYsnbhJcpiAIzPn5h8QO7z4x_1XRgqmD0bjBGDIWy0cFw5mA8NwD8ToxLMLZoL2rHQ85qMGekJv1Fi7JnKp2aIY1QCYr07dCc0xP9u1TlnTokAtWtCMoMhN0r0dVp7WmJ_Xr4ZYLlXGCYZVDRw-T8wYBf6Zs/w538-h640/crdb_chk1.png" width="538" /></a></div><br /><p>But wait until you see the filenames:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtEYzWrY4BcLV91MRGOEg1A39fl67bJs28uU1OKgxArMa3VEQdT0iDnHol1gErCWlcGSHQI5BNpWRK-H4mJKYvYoj8xVJCqPr41t2X4rMi4DrzZ9YLiCjHGyEONhgk3XT41y1yflcDrUfgN6pvTfLTD3mjVtK0Lzv7D63tqUhG4qsvcrWDdEyaTLWUObU/s1586/crdb_chk2.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1112" data-original-width="1586" height="448" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtEYzWrY4BcLV91MRGOEg1A39fl67bJs28uU1OKgxArMa3VEQdT0iDnHol1gErCWlcGSHQI5BNpWRK-H4mJKYvYoj8xVJCqPr41t2X4rMi4DrzZ9YLiCjHGyEONhgk3XT41y1yflcDrUfgN6pvTfLTD3mjVtK0Lzv7D63tqUhG4qsvcrWDdEyaTLWUObU/w640-h448/crdb_chk2.png" width="640" /></a></div><p>As was expected, due to the minimalistic (lazy) setting of parameters and the minimal typing effort, I have OMF files, and the Pluggable, the PDB$SEED, has gotten this horrendous subdirectory-name which makes memorising the file-paths impossible. I Really Like OMF, I dont mind about the crazy but readable filenames.dbf. I don't like the long subdir-names, but those are what you get if you, lazily, don't want to specify file_name_convert or other directives for the files of the PDBs (there is a reason, but that is out of scope for now).</p><p>I also tried to Increase the undo-tbs, just to see if I can... Resize command worked fine. Because I know some of the next operation will make my datafiles grow, I can already pre-grow the files. Pre-allocate space instead of grow incrementally, to make the work slightly more efficient. And thanks to OMF, I can also add bigger logfile-groups with Very Simple commands :</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGX2kAwnYkzYDlV7Pt-hQGpWgJWy9Rr-UWQ34mxreYt9khka2m3ZSPOCJ0RL5MoxQT_X5nwU1ZjsxfDFu5-JYI3XVFT8ystfFpDySNyK9oC2HTgiLEvqIRVvjRKwpoqpVQZp1ZuKHGs6lMys16KC07iTzfIY07W0Qcsz1hy0h5Bg_3akbyliXDx5sHrtg/s1134/crdb_logfiles.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="724" data-original-width="1134" height="408" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGX2kAwnYkzYDlV7Pt-hQGpWgJWy9Rr-UWQ34mxreYt9khka2m3ZSPOCJ0RL5MoxQT_X5nwU1ZjsxfDFu5-JYI3XVFT8ystfFpDySNyK9oC2HTgiLEvqIRVvjRKwpoqpVQZp1ZuKHGs6lMys16KC07iTzfIY07W0Qcsz1hy0h5Bg_3akbyliXDx5sHrtg/w640-h408/crdb_logfiles.png" width="640" /></a></div><p>Bigger logfiles and pre-grown datafiles might help the subsequent actions (catalog, components) to finish a little faster. (And if you are re-playing this : don't forget to switch-logfile, do a checkpoint, and drop the small-ish logfile group 2 as well...). </p><p><br /></p><p><b>Success ? Not complete yet, but Getting There!</b></p><p>So.. basic operations already work nicely: resizing of datafiles, and adding of logfiles. I can even do that work over a connect from a client which allows me to run some of my trusty old maintenance-scripts right from my laptop-client machine. </p><p>But since we don't have a PDB yet, we are not done, not by a long way. It looks like I will have to do a little more work: running the "catalog" scripts, adding the "required" components, and then finally creating one or more PDBs. </p><p><br /></p><p><b>More to come:</b></p><p>I will need to create + run at least three more scripts : </p><p><a href="http://simpleoracledba.blogspot.com/2023/08/minimalistic-creation-of-database-v23c.html" target="_blank">crdb2_cat.sql: to add the catalog (this was actually the slowest part)</a></p><p><a href="http://simpleoracledba.blogspot.com/2023/08/create-database-v23c-installing.html" target="_blank">crdb3_comp.sql: to add a number of essential "mandatory" components.</a></p><p><a href="http://simpleoracledba.blogspot.com/2023/08/draft-4-adding-pdbs-is-easy-also-in-v23c.html" target="_blank">crdc4_pdb.sql: to finally add some PDBs and show it All Really Works.</a></p><p><br /></p><p>Spoiler: It is going to Work. But some of it is for the next Blogs.</p><p><br /></p><p>---- End of Part 1 ---- </p><p><br /></p><p>Some Disclaimers, as ppl have asked (written 06 Aug 2023)</p><div><br /></div><p><b>My Server: </b></p><p>As platform, I used the container image provided by Oracle, which can be found here: container-registry.oracle.com/database/free, image id: 7c64410c08d5afc711a9da0cc7cfa6e4e66bc7ec4456c3ba5867823c8e40ef57</p><p>And I've ran this image via docker-desktop on a both an MBP and an MBAir, more or less as if it where a little Virtual Machine. Most of the actual work for this blog was done on a humble MBair from 2018. Kinda shows you really need Big Iron and Large Servers to run Oracle, right?</p><div><br /></div><p><b>The Scripts:</b></p><p>The original "generated scripts" were generated on a VM with Oracle v21 installed. I copied them off and edited them to fit in the (often hardcoded) version/name 23c. By editing and simplifying the generated scripts, I got to the results above. You may see more of the generated scripts and their 21c origin in the next episodes.</p><p><br /></p><p>Warnings, just in case: </p><p><b>Dont Do This At Work.</b></p><p>and<b> Use This at Your Own Risk.</b></p><p>The work and scripts and results in the above blog is all just for research purposes, and bcse I am a curious, slightly weird person. I am not suggesting you should do this for any Serious system, and Definitely Not with the pre-/developer release of 23c.</p><p>---- finally, the real end of part 1 ---</p><p><br /></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-51099569570182964292023-08-05T07:50:00.005-07:002023-08-07T00:25:38.912-07:00Oracle SQLcl Still Surprises... even when finishing work.<p> TL;DR You can use \q in SQLcl. That is Cool! </p><p>As stated before, The Mantra is: </p><p><b>Everything is SQL, and SQL is everything.</b></p><p>And you should collect sql-scripts and test them, use them...hence you need <a href="https://www.oracle.com/be/database/sqldeveloper/technologies/sqlcl/" target="_blank">SQLcl</a></p><p>I Strongly Recommend that you "store" your SQL Knowledge in (runnable) scripts. That way you don't have to go back and google (or AI-chat) to find how a certain query is done, or how to find the size of a set of objects. (on my laptop, typing SQL > @segsize abc% will find all segments by that name and list them with sizes, I've scripted that since the dawn of time to never have to type too much ....)</p><p>But that is not Today's little TIL.</p><p>So, I tend to have SQLcl installed on my client-machine (and SQLDeveloper, and even artifacts like SQL*Plus and sqlldr, exp, imp). And on my often-used jump-boxes, and bascially everywhere I can. You Need your SQL tools...</p><p>And TIL (Today I learned), by accident, this :</p><p><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxdYTYd9vxwAizkBWP0PywKfcuq8bnxJ6mO55GZi7bhCiHcpiec2yNhmClLQsAQyIbo2kbnqbjd-glJFCJnc-Ue5hi6qs_uQmTQAsc1373BEevNAq_d8Lek2HmDAlx04f5RZreF1v_bPiiQtUWYRWdyYX3h5Aiq_o7SjSYql62aEvD0lkBhRrQhWly5CA/s1506/Screenshot%202023-08-05%20at%2015.58.29.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="266" data-original-width="1506" height="114" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxdYTYd9vxwAizkBWP0PywKfcuq8bnxJ6mO55GZi7bhCiHcpiec2yNhmClLQsAQyIbo2kbnqbjd-glJFCJnc-Ue5hi6qs_uQmTQAsc1373BEevNAq_d8Lek2HmDAlx04f5RZreF1v_bPiiQtUWYRWdyYX3h5Aiq_o7SjSYql62aEvD0lkBhRrQhWly5CA/w640-h114/Screenshot%202023-08-05%20at%2015.58.29.png" width="640" /></a></div><p>For those who now blink: The image above shows that the SQLcl tool will accept the command "\q" (BackSlash and lowercase Kjuu).</p><p>And the \q will tell it to gracefully disconnect and exit.</p><p>For some of us, this is Very Convenient, as it is the same command used by a few other (DBA, geeky) tools that we use. I realise I probably have used \q several times on SQLcl without paying attention, as I tend to keep scripts "generic" as much as possible, and have a several versions of "the same script" for various purposes.</p><p>Having \q is just ... Convenient! </p><p>And who else knows what is in there. I already found that \d also works.</p><p>Hence: Tip of the Hat to the Jeff, Layla, Kris, and the entire team behind SQLcl.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjf47GQp96mFbUJppBL9zHciVntCxBSx3P3dQz49rZi6uHyUaePG_sxGziy5vMR45u4qcUuJjXcyDiuqY2JHaR_94ymhWdsjUO-uoszqCQk7qTqchZGhkgbszNMFc1FtNqcxXq5umFYHut_V-9Cle7Xix-q6i35xlU3HEa2x3CCpq4tm3Wl_AJA_yYfmlI/s1252/Screenshot%202023-08-05%20at%2016.42.09.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="522" data-original-width="1252" height="166" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjf47GQp96mFbUJppBL9zHciVntCxBSx3P3dQz49rZi6uHyUaePG_sxGziy5vMR45u4qcUuJjXcyDiuqY2JHaR_94ymhWdsjUO-uoszqCQk7qTqchZGhkgbszNMFc1FtNqcxXq5umFYHut_V-9Cle7Xix-q6i35xlU3HEa2x3CCpq4tm3Wl_AJA_yYfmlI/w400-h166/Screenshot%202023-08-05%20at%2016.42.09.png" width="400" /></a></div><p>If you got to this point and are still clueless why \q or \d is convenient, no worries. You miss very little...</p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-78827522685678499452023-07-25T02:25:00.001-07:002023-07-25T02:25:19.424-07:00SQL, Explain-Plan, Show me the Data, automated<p> <span style="font-family: "Helvetica Neue";">TL;DR: Find relevant info about the data behind an SQL: easy, and automated.</span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">OK, so you found the SQL-statement that seems to cause problems. From Statspack or AWR, or just from v$sqlarea. You got the stmnt, the sql_id, and probably the explain-plan(s) using dbms_xplan. In earlier blogs I also showed you how to check on the history of an sql_id (from dba_hist_sqlstat) and how to find the bind-variables (from v$sql_bind_capture).</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">How about we also do some inspection of the data in the tables and indexes? And how about we automate that job ? </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Take this (simple) SQL stmnt as an example: </p><pre><code>select e.ename, e.hiredate, d.dname, d.loc
from emp e, dept d
where 1=1
and e.deptno = d.deptno
and e.ename = :vc_ename
order by e.ename, d.dname; </code></pre><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">In practice, problem-queries may fetch data form 10+ tables but we'll keep this example small. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">We can use dbms_xplan to show the explain:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">
<code></code></p><pre><code>-----------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT ORDER BY | | 0 (0)|
| 2 | NESTED LOOPS | | |
| 3 | NESTED LOOPS | | |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | |
|* 5 | INDEX RANGE SCAN | EMP_ENAME | |
|* 6 | INDEX UNIQUE SCAN | PK_DEPT | |
| 7 | TABLE ACCESS BY INDEX ROWID | DEPT | |
-----------------------------------------------------------------
</code></pre>
<p></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">But we can take it a few steps further: we know the plan is stored in v$sql_plan. <b>So let's combine v$sql_plan with the information in dba_tables and dba_indexes:</b></p>
<code><pre>column obj_own_name format A30
column last_analyzed format A20
column est_rows format 999,999
column est_keys format 999,999
select p.object_owner || '.' || p.object_name || ' (T)' as obj_own_name
, to_char ( t.last_analyzed, 'YYYY-MON-DD HH24:MI:SS' ) as last_analyzed
, t.num_rows est_rows
from v$sql_plan p
, dba_tables t
where 1=1
and p.object_owner = t.owner
and p.object_name = t.table_name
and sql_id = '&1'
and p.object_type like 'TAB%'
order by p.position ;
select p.object_owner || '.' || p.object_name
|| case i.uniqueness
when 'UNIQUE' then
' (UNQ)'
else ' (IDX)'
end as obj_own_name
, to_char ( i.last_analyzed, 'YYYY-MON-DD HH24:MI:SS' ) as last_analyzed
, i.distinct_keys est_keys
from v$sql_plan p
, dba_indexes i
where 1=1
and p.object_owner = i.owner
and p.object_name = i.index_name
and sql_id = '&1'
and p.object_type like 'IND%'
order by p.position ;
</pre></code>
<p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">These queries will display the relevant statistics (number of rows and number of keys). Note that the statistics can be out of date, hence we also generated the count-queries:</p>
<pre><code>
OBJ_OWN_NAME LAST_ANALYZED EST_ROWS
------------------------------ -------------------- --------
SCOTT.EMP (T) 2023-JUL-23 06:38:30 14
SCOTT.DEPT (T) 2023-JUL-23 06:38:30 4
OBJ_OWN_NAME LAST_ANALYZED EST_KEYS
------------------------------ -------------------- --------
SCOTT.EMP_ENAME (IDX) 2023-JUL-23 06:38:30 14
SCOTT.PK_DEPT (UNQ) 2023-JUL-23 06:38:30 4
-- Generated count-queries ready to copy/paste
select ' SCOTT.DEPT :' || to_char ( count ('x' ), '9,999,999,999' ) from SCOTT.DEPT;
select ' SCOTT.EMP :' || to_char ( count ('x' ), '9,999,999,999' ) from SCOTT.EMP;</code></pre>
<p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">This will give you a quick insight </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Note that the dbms_xplan-advanced will provide you most of the same information, and more. But being paranoid, I can never resist to verify for myself. Sometimes the "statistics" are way off the mark, the last-analyzed may be outdated, or you may have overlooked a particularly large table in the from-clause, or some tables were possibly hidden by a view. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Warning: Careful with count-queries.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">The Count-queries are deliberately Not Executed Automatically: on some systems a thoughtless count can take minutes or even hours. But if you need them: copy/paste and run .... </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">In summary: </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">We combine the information in v$sql_plan with the available statistics, and we generate count-queries in case you need them. By automating this in a script, we save the copy/paste/typing effort, and we obtain a more or less standardized format of data. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">And all of this with fairly simple SQL statements...</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Remember, in a good RDBMS:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><b>Everything is SQL, and SQL is everything.</b></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Happy Selecting !</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-57814427291259216072023-07-22T06:59:00.007-07:002023-07-22T07:02:21.339-07:00Career Avice: Learn SQL.<p> <span style="font-family: "Helvetica Neue"; font-size: 19px;">TL;DR: knowledge of SQL is Mandatory. If you get to deal with data, you will benefit from knowing SQL</span></p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><span style="font-size: x-small;">Note: geeky SQL-based trips and tricks to manage your Oracle Database will resume shortly. This SQL-advertisement just happened to come along on twitter.</span></p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">Before you continue, read this twitter-thread:</p><p class="p3" style="color: #dca10d; font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;"><a href="https://twitter.com/parmardarshil07/status/1681570471475834882">https://twitter.com/parmardarshil07/status/1681570471475834882</a></p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">This tweet underscores my favourite statement:<span class="Apple-converted-space"> </span></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;"><span class="Apple-converted-space"><br /></span></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;"><b>Everything is SQL and SQL is Everything.</b></p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">Why?<span class="Apple-converted-space"> </span>Because IT is always about Data.<span class="Apple-converted-space"> </span></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">And the most natural way to process data is SQL</p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">Data is best found, kept, and manipulated with SQL.<span class="Apple-converted-space"> </span></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">Even the noSQL systems are implementing SQL-layers-access to facilitate retrieving content.</p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">More elaborated…</p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">First of all, SQL will help you find and retrieve data: SELECT: Select from table(s), with Joins on key-columns if needed, with Where-Clauses filters to obtain just the data you need, with Order-By to put the data in a sensible sequence, if only to put the most relevant data at the best-visible end.</p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">But if you choose to do so (and you should) SQL will also help you define your data-structures: Create-Table with relevant fields, some mandatory, and typed to ensure that a hire-date is in deed a valid date).</p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">Furthermore, SQL will help you define and enforce relations between tables: an employee should be attached to a valid, existing, department.</p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">And SQL will allow you to define Constraints to make sure the data is sensible: A price (generally) can not be negative, assignment-dates can not overlap.</p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">These conditions, rules, you can define (declare!) as constraints and no data-manipulation can (should be able to) overrule them.</p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">And this is only the short summary of the benefits of SQL…</p><p class="p2" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px; min-height: 23px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;">My whole blog is 90% based on SQL, and how to use SQL to control your data and your Database System.</p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-feature-settings: normal; font-kerning: auto; font-optical-sizing: auto; font-size: 19px; font-stretch: normal; font-variant-alternates: normal; font-variant-east-asian: normal; font-variant-numeric: normal; font-variation-settings: normal; line-height: normal; margin: 0px;"><br /></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-6098447299025364422023-07-14T01:34:00.007-07:002023-07-14T01:53:42.099-07:00Find and use Bind Variables from SQL statements.<p><span style="font-family: "Helvetica Neue";">TL;DR: You can see what the content of a bind-variable was in</span><span style="font-family: "Helvetica Neue";"> </span><b style="font-family: "Helvetica Neue";">v$sql_bind_capture</b><span style="font-family: "Helvetica Neue";">. You can then use that to generate re-play-scripts for queries. There are some caveats though.</span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Have a look at the "ADVANCED" option of dbms_xplan:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">select * from table </span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">( dbms_xplan.display_cursor ('sql_id',0, 'ADVANCED'));</span></p><div><br /></div><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Somewhere in the 100+ lines of output you will find the list of bind-vars and their (peeked) values. You could use those values to re-run the query with same or similar, or different values. Ideal for testing.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Intrigued as I was, I reasoned there had to be some place this information was stored, and I stumbled across <b>v$sql_bind_capture</b>. After a bit of experimenting, I ended up with two queries I could use in scripting:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">I can now define the variables for use in an SQL script:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">-- define variables</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">select 'Variable ' || replace ( bvc.name, ':', 'b' ) </span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> || ' ' || bvc.datatype_string ||';' </span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">from v$sql_bind_capture bvc</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">where 1=1</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">and sql_id = '&sql_id'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">and child_number = 0</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">order by position, child_number, name;</span></p><div><br /></div><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">And I can generate code to set the variables to the values stored:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">select</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">--bvc.*</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">-- ANYDATA.AccessTimestamp(bvc.value_anydata)</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">-- ANYDATA.Accessdate(bvc.value_anydata)</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> ' ' || replace ( bvc.name, ':', ':b' ) || ' := '</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> || decode ( substr ( bvc.datatype_string, 1, 3)</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , 'NUM' , nvl ( bvc.value_string, '''''' )</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , 'VAR' , '''' || bvc.value_string || ''''</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , 'DAT' , ' to_date ( ' || '''' || ANYDATA.Accessdate(bvc.value_anydata) || ''', ''YYYY-MM-DD HH24:MI:SSXFF'' ) '</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , 'TIM' , ' to_timestamp ( ' || '''' || ANYDATA.AccessTimestamp(bvc.value_anydata) || ''', ''YYYY-MM-DD HH24:MI:SSXFF'' ) '</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , bvc.value_string</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> ) || ' ;' from v$sql_bind_capture bvc</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">where 1=1</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">-- and child_number = 1</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">and child_number = 0</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">and sql_id = '&sql_id'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">order by position, name, child_number;</span></p><div><br /></div><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Notice the trickery needed to extract the date-values. Beware of your NLS settings here.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">With those possibilities, I am very close to re-running the query with either the original values or with values that I can edit into a script. This will allow me to replay queries and to test fixes.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">I short, once I know the sql_id, what I can now do (and largely automate!) is:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"> - find the offending SQL from v$sqlarea or v$sql.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"> - use the sql_id to find the types and values of the bind-variables.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"> - semi-automate the definition and the setting of the bind-vars.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"> - paste in the SQL and run it, and repeat-run it with various values for bind-vars or with edited varieties of the SQL.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">My script to do this is called mk_rerun.sql. I'll include the script below, and you can test if for yourself using sqlcl or sql*plus. The script takes 1 argument, the sql_id:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">SQL> @mk_rerun <sql_id> </span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">It will generate a script called rerun_<sql_id>.lst that you can edit and use for re-run. With this trick, it now takes me less then 1 minute to find + test the re-running of a problem-SQL.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">There are some caveats, and I want to mention two warnings:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Firstly, bind-variables are often shown as placeholders like :1, :2 etc.. and those are not valid variable-names in an sql-script. Therefore you need to edit the script to make variables start with a character. Hence I tend to stick a "b" in front of the variable-name. This changes :1 (invalid variable name) into :b1 (perfectly valid)</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Secondly, Date-variables don't work in sql-scripts. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Hence you need to cast them into dates : to_date ( value, format) </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">There are a few other quirks you might encounter when you try to automatically re-run SQL statements, but you are well on your way now.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Happy Scripting! </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">In case someone wants to reuse my script: mk_rerun.sql:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"></p><pre><code>set ver off
set feedb off
set head off
@set_nls
spool rerun_&1
-- try picking bind-vars from memory: define + assign
-- define variables
select 'Variable ' || replace ( bvc.name, ':', 'b' ) || ' ' || bvc.datatype_string ||';'
from v$sql_bind_capture bvc
where 1=1
and sql_id = '&1'
and child_number = 0
order by name, child_number, name;
prompt /* */
prompt /* -- now all varibles declared, will try to assign from cursor-0 */
prompt /* */
prompt BEGIN
select --bvc.sql_id
-- , bvc.child_number chld
--, bvc.name bind_variable
--, bvc.datatype_string datatype
--, bvc.value_string bind_value
--bvc.*
--ANYDATA.AccessTimestamp(bvc.value_anydata)
-- ANYDATA.Accessdate(bvc.value_anydata)
' ' || replace ( bvc.name, ':', ':b' ) || ' := '
|| decode ( substr ( bvc.datatype_string, 1, 3)
, 'NUM' , nvl ( bvc.value_string, '''''' )
, 'VAR' , '''' || bvc.value_string || ''''
, 'DAT' , ' to_date ( ' || '''' || ANYDATA.Accessdate(bvc.value_anydata) || ''', ''YYYY-MM-DD HH24:MI:SSXFF'' ) '
, 'TIM' , ' to_timestamp ( ' || '''' || ANYDATA.AccessTimestamp(bvc.value_anydata) || ''', ''YYYY-MM-DD HH24:MI:SSXFF'' ) '
, bvc.value_string
) || ' ;' from v$sql_bind_capture bvc
where 1=1
-- and child_number = 1
and child_number = 0
and sql_id = '&1'
order by name, child_number;
select 'END;'from dual ;
select '/' from dual ;
prompt set autotrace off
prompt /* -- */
prompt /* -- Paste formatted statement here, followed by semicolon... */
prompt /* -- */
prompt /* -- SQL goes HERE, with semicolon added! */
promp /* -- Will include explain to catch stmnt. */
prompt /* -- */
prompt /* -- use this file to edit + run stmnt with variables */
prompt /* -- SQL> ed rerun_&1.lst */
prompt /* -- SQL> @rerun_&1.lst */
prompt /* -- */
prompt /* first execute goes here, can use output from xplan to get SQL */
prompt /* please remove plan... */
SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR('<sql_id>', 0, 'BASIC' ));
-- and repeat with autotrace on
prompt
prompt
prompt set autotrace on
prompt set timing on
prompt set linesize 150
prompt set feedback on
prompt
prompt /* -- and 2nd run with autotrace on */
prompt /
prompt set autotrace off
prompt set timing off
prompt /* -- */
prompt /* -- use this file to edit and run stmnt with variables */
prompt /* -- SQL> ed rerun_&1<sql_id>..lst */
prompt /* -- SQL> @rerun_&1<sql_id>..lst */
prompt /* -- */
spool off
set feedb on
</sql_id></sql_id></sql_id></code></pre><p></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">End of script mk_rerun.sql</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-1530368876194181822023-07-12T01:09:00.002-07:002023-07-12T01:09:59.213-07:00DBaaS is Fine in Most Cases.<p><span style="background-color: white; font-family: arial;"><span style="color: #3c4043;">TL;DR: F</span><span style="color: #3c4043;">or 99% of use-cases a DBaaS (autonomous...), an RDS- or an MPV-service is sufficient. But in case I want to investigate further, </span><span style="color: #3c4043;">I am still very grateful to Oracle for exposing the V$ and X$ views. They allow me to inspect, understand, and troubleshoot my system at the next level</span></span></p><span style="background-color: white; font-family: arial;"><br style="color: #3c4043;" /><br style="color: #3c4043;" /><span style="color: #3c4043;">This blog post is partly in reaction to...</span><br style="color: #3c4043;" /><br style="color: #3c4043;" /><a href="https://www.google.com/url?q=https://twitter.com/samokhvalov/status/1678786986763755521&sa=D&source=calendar&usd=2&usg=AOvVaw2UTmFuB-SjyfNmvlzncj1I" style="cursor: auto;">https://twitter.com/samokhvalov/status/1678786986763755521</a><br style="color: #3c4043;" /><br style="color: #3c4043;" /><span style="color: #3c4043;">Firstly, I agree with Nikolay.</span><br style="color: #3c4043;" /><span style="color: #3c4043;">And when you need "backend access" or "root-level-control" on the system where you database is running: please build+manage your own servers. I understand the desire for control and access. It is the only way a real techie will "feel safe". But realise that when you have all that access: with great power comes great responsibility: You will have to secure, manage and maintain your platform yourself. Not all organisations are willing to do so, and even less are capable of attracting + retaining staff with the desired level of competence.</span><br style="color: #3c4043;" /><span style="color: #3c4043;">(and did I mention "Documentation" ??)</span><br style="color: #3c4043;" /><br style="color: #3c4043;" /><br style="color: #3c4043;" /><span style="color: #3c4043;">But the world is moving on....</span><br style="color: #3c4043;" /><span style="color: #3c4043;">And for the rest of us, there are the "managed services" like RDS and other MPVs. And we have to accept that those come with some restrictions.</span><br style="color: #3c4043;" /><br style="color: #3c4043;" /><span style="color: #3c4043;">For me, it took a while to get used to it, but now I'm ok with the RDS "level of service". F</span><span style="color: #3c4043;">or the following (nitpickity) reasons: </span><br style="color: #3c4043;" /><br style="color: #3c4043;" /><span style="color: #3c4043;">1. My customers often dont want to be bothered with the details of DBA-work. And they definitely dont want to deal with Oracle-sales ever again. RDS is the best example I know that "takes away the worries" of mundane DBA-tasks and liberates us from the sales-vampires.</span><br style="color: #3c4043;" /><br style="color: #3c4043;" /><span style="color: #3c4043;">2. A good RDBMS (Oracle, and to some extend Postgres) will expose enough of its functioning via SQL-interface so that I can diagnose problems.</span></span><div><span style="background-color: white; font-family: arial;"><br style="color: #3c4043;" /><span style="color: #3c4043;">3. As an application-developer, software provider, I will also try to limit my software and its complexity to avoid getting into situations where I "need backend access".</span><br style="color: #3c4043;" /><br style="color: #3c4043;" /><span style="color: #3c4043;">4. A good provider (Oracle, RDS) will have + provided assistance if needed. (this is not the place to write another long rant about the support-staff who are driven by the need to Close Tickets, rather than to solve problems, but you know who you are...)</span><br style="color: #3c4043;" /><br style="color: #3c4043;" /><span style="color: #3c4043;">To the RDBMS-vendors, if they care to listen, I would say : please expose your system, all of it, via the SQL-interface. <a href="https://en.wikipedia.org/wiki/Codd%27s_12_rules" target="_blank">The rules of Codd still apply, and Rule-4 is Especially applicable here.</a></span><span style="color: #3c4043;"> </span></span></div><div><span style="background-color: white; font-family: arial;"><br /></span></div><div><span style="font-family: arial;"><b style="background-color: white;">SQL is Everything, and Everything is SQL...</b></span></div><div><span style="background-color: white; font-family: arial;"><br /><span style="color: #3c4043;">And please provide the data via SQL. That way your customers / developers / users can find their data, and combine it in any way they see fit. Running graphs, dashboards with metrics-over-time are very useful, but at some point some geeky cstmer will want to query for himself and combine data in ways your dashboard didnt think of yet... Please Give them that SQL..</span><br style="color: #3c4043;" /><br /><br style="color: #3c4043;" /><span style="color: #3c4043;">To the providers of managed services (RDS, other MPVs), I would say: Expose! Provide as much dashboards and metrics as the RDBMS can expose. And provide the data via SQL: that way the consumer is free to select/slice/dice/present the data in any way they think they need.</span><br style="color: #3c4043;" /><br style="color: #3c4043;" /><br /></span></div>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-78706186496409758052023-07-11T04:47:00.005-07:002023-07-11T06:41:45.433-07:00The History of a Query<p> <span style="font-family: "Helvetica Neue";">TL;DR: The dba_hist_sqlstat view can tell you what has changed in the frequency or in the execution plan of a given query.</span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">We have this dashboard-query that suddenly became very slow. The qry is a sum (something) to indicate "how much work was done". Normally it uses an index and is lighting fast. This morning it seem to have suddenly slowed down.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Let's dig in and use this as an example on "How to Investigate".</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">We know what the query looks like, and using the v$sqlarea, we can find the SQL_ID: 7292jtjypdyvt.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Next is the DBA_HIST_SQLSTAT : this can tell you how an SQL performed over the snapshot-intervals. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">If you are using "statspack", you will find similar information in STATS$SQL_SUMMARY, but you will need to subtract the preceding value to find the difference since the previous snapshot.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">We start with this:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">select * from dba_hist_sqlstat sq</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">where sq.sql_id = '7292jtjypdyvt'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">order by sq.snap_id ;</span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Since v12, the DBA_HIST... views contain "delta" columns which save you the effort of subtracting. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">And we need to join to the snapshot view to find at what time each snapshot was taken. The query evolves into something like this:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">select to_char ( sn.end_interval_time , 'DDMON HH24:MI') as Time</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">, sq.executions_delta execs</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">, sq.buffer_gets_delta buff_gets</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">, rows_processed_delta nr_rows</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">-- , sq.*</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">from dba_hist_sqlstat sq</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">, dba_hist_snapshot sn</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">where sq.sql_id = '7292jtjypdyvt'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;"> and sn.snap_id = sq.snap_id</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;"> and sn.dbid = sq.dbid</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;"> and sn.instance_number = sq.instance_number</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">order by sn.dbid, sn.snap_id, sn.instance_number ;</span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">To place it all in perspective, I also tend to look at the elapsed-time per execution, and the buffer_gets per execution:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">elapsed_time_delta / executions_delta as sec_p_exe</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">But here I need to be careful, some decode is needed to avoid divide-by-zero.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">And another interesting item is the plan_hash_value: if the plan has changed over time, it means the optimiser has chosen another plan, which may or may not be advantageous.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">In my case, the inspection-query has turned into :</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEj1w3WOaySdWmb7d99DUIqIF8xGa8YF5A3bNz_YpiDG_2PD6cGfh1nEXcl2xr39ZvsJ4HsHj67YPl5LkNXtnoKvLGQVC4XYd20dPMfUzjMKpAzzCmw5NTlgztd6tFC8_QMv6sZGIUh3_no1Ql3ZLLS3dv2QPF_CyPeZSsSg3HAqUefL5jsFRnOEnFBWKN4" style="margin-left: 1em; margin-right: 1em;"><img data-original-height="1256" data-original-width="1900" height="424" src="https://blogger.googleusercontent.com/img/a/AVvXsEj1w3WOaySdWmb7d99DUIqIF8xGa8YF5A3bNz_YpiDG_2PD6cGfh1nEXcl2xr39ZvsJ4HsHj67YPl5LkNXtnoKvLGQVC4XYd20dPMfUzjMKpAzzCmw5NTlgztd6tFC8_QMv6sZGIUh3_no1Ql3ZLLS3dv2QPF_CyPeZSsSg3HAqUefL5jsFRnOEnFBWKN4=w640-h424" width="640" /></a></div><br />Or, in my case, I often run it from a script, that I have called <p></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">SQL> @sql_freq <sql_id> <enter></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjzdwybthwjFV3uNaMu60lL4TDg8iY1BKotuLvR4J3KTzKspAdclOVxgaXznhCXvIP4uYKrtVDtQIygxixdykAfOk5NLwvLKXcVHxrkIakNNary2pv2wV699xBQHG8I2nTOXt7tekJfmiG7YM8317yNJjTX4yccPvVxvPn0fKAFlo0md1Qw9kCatTlRbDU" style="margin-left: 1em; margin-right: 1em;"><img data-original-height="560" data-original-width="1268" height="282" src="https://blogger.googleusercontent.com/img/a/AVvXsEjzdwybthwjFV3uNaMu60lL4TDg8iY1BKotuLvR4J3KTzKspAdclOVxgaXznhCXvIP4uYKrtVDtQIygxixdykAfOk5NLwvLKXcVHxrkIakNNary2pv2wV699xBQHG8I2nTOXt7tekJfmiG7YM8317yNJjTX4yccPvVxvPn0fKAFlo0md1Qw9kCatTlRbDU=w640-h282" width="640" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><br />Let me discuss the relevant columns here:<p></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">TIME: Notice that my awr-snapshots are only 10min apart. I tend to set that interval narrow on critical systems or on systems under investigation.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">EXECS: notice that this SQL is fired 10404 times between 07:30 and 07:40. That is quite a lot of executes in a 10min interval. Busy time-window?</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">BUFF_GETS and GET_PX (gets per execute) : At 07:50, the nr of buffer-gets suddenly goes up. And the nr of gets-per-execute goes from 3 (very efficient) to 7000 (not so efficient). </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">SEC_PX: the nr of seconds per execute is still way below 0.5 sec. When rounded to whole-seconds, it ends up as zero-seconds. In some versions of the script, I display milliseconds, but when dealing with big/slow SQL, those numbers tend to become unpractically wide.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">NR_ROWS: Because this is a sum-query, each execute only returns only 1 row.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">G_PR: the nr of block-gets per row-returned. For some queries, I want to know the nr of blocks processed (gets) in relation to the nr of rows returned. In an OLTP system, you want the nr of blocks processed to be low. But if for some reasons the nr of rows is high, I can tolerate the processing of more blocks (e.g. bulk-shipments with a lot of items...)</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">PLN_HV: the Plan-Hash-Value. If this value changes, the execution-plan has changed over time. In this case, we we notice 3 intervals where the plan was indeed different. From the nr of Buffer-gets and the gets-per-row, we deduce that the "other plan" is probably a lot less efficient because it requires 7000 gets to return the result, whereas the "efficient plan" only needed 3 gets (a typical index-lookup) to return the result.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">To further inspect the SQL, and display the execution-plans used, you can use something like:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">select plan_table_output from table (dbms_xplan.display_awr('<sql_id>'));</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";">And there is a lot more to discover, for example:</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";">The DBA_HIST_SQL_PLAN contains details about the execution-plans, and DBA_HIST_SQLBIND can be used to hunt down the values of bind-variables from queries.</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";">It is all there for you to explore ... </span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";">In this particular case, the re-calculation of statistics for the relevant index was sufficient to "fix" this problem. At 09:00 the SQL had returned to its normal, efficient, PHV and 3 GET_PX.</span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-70282382667020364262023-07-07T06:54:00.004-07:002023-07-07T07:01:02.859-07:00Who uses this Index ?<p> <span style="font-family: "Helvetica Neue";">TL;DR: One more way to "monitor an index". Checking v$sql_plan can get you to the relevant SQL, tell you what component runs the query, and give you a lot of details. Read, experiment, and choose the way you want Your monitoring done.</span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">There are multiple ways to find out if an index is useful or not, and they all have pro- and con- arguments.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Firstly, a very blunt way is to <a href="https://oracle-base.com/articles/11g/invisible-indexes-11gr1" target="_blank">make the index invisible</a>, or even drop it, and just see what happens. This method has a downside: it can impact users hard+fast. </p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: Helvetica Neue;"><br /></span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Secondly, the official documentation will provide you with DBA_INDEX_USAGE, and a very good article on this by Tim -oracle-base-dot-com Hall is found <a href="https://oracle-base.com/articles/12c/index-usage-tracking-12cr2" target="_blank">Here (Do Read It!)</a>. This 2nd method is less intrusive, more subtle, but it is still more or less a Yes/No answer to the question. It does not give you an indication of how-many users/session/queries/users are hitting this index, or how-often.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">We are going to add yet another method: also not perfect, but maybe more suitable for some use-cases. Read on if you like to Dig In Deep....</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Take a look at <b>v$sql_plan</b> and at the AWR and Statspack equivalents: DBA_HIST_SQL_PLAN and STAT$_SQL_PLAN. If you filter by object_owner, object_name and object_type, you can see which SQL-statements have used your index in their plan, both in sql-cache and in AWR/Statspack.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">This view stores the "explained plan" of the SQL that resides in the shared_pool, and is also used by dbms_xplan.display_cursur and by (auto-)explain to display the explain-plans.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">At this moment, I am interested in any SQL that uses my index. And I can obtain the list of SQL_IDs like this: </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">select p.sql_id</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">from v$sql_plan p</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">where p.object_owner = 'SCOTT'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">and p.object_type = 'INDEX' ;</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">and p.object_name like 'EMP%'/* idx name(s) here */ </span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">;</span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">From the nr of sql_ids we already get an idea of how many and how often this index is used. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">To make sure you dont miss occurrences, you should also check the DBA_HIST and/or the STAT$ equivalent because not all SQL-stmtns will remain in the sql-cache and be visible via the v$ views.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Once you have the set of SQL_IDs, you can link them to <b>v$sql</b> or<b> v$sqlarea</b> to find out how often each of them is used, and by which users or programs. I tend to use something like this:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">select a.sql_id</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;"> , a.executions</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;"> , a.parsing_schema_name</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;"> , substr ( a.sql_text, 1, 25 ) as sqltxt</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">from v$sql_plan p</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;"> , v$sqlarea a</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">where p.object_owner = 'SCOTT'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">and p.object_type = 'INDEX'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">and p.object_name like 'EMP%' /* name(s) here...*/</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier;">and a.sql_id = p.sql_id;</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">For even more detail, you can also check to see if the SQL_IDs occur in the <b>v$active_session_history</b> views: those will tell you which sessions, e.g. which programs/user/jobs have been running the queries that used your index.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">By doing this little extra digging, you may obtain a much better view of who + when + what is using your index(es).</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">I would encourage you to use each of the methods described, and to "do your own digging", and then to make an informed decision on whether an index can be dropped or not.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">As I've often done, I'll make the point again: All this information is available to you in Views and Tables, and can be viewed with just SQL, and you are free to use the tool of your choice: SQLDeveloper, SQLcl, SQL-Plus, or any other adhoc-query tool at your disposal. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><b>Everything is SQL, and SQL is Everything.</b></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Go Experiment, Go Learn and Enjoy !</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-65093842897748008082023-07-04T03:49:00.009-07:002023-07-04T03:57:42.438-07:00Use SQL to find problem-statements<p> <span style="font-family: "Helvetica Neue";">TL;DR: There is a lot of interesting information in <b>v$sqlarea</b>, but beware of jumping to conclusions. </span></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">On a troubled system, one of the first things I look at is v$sqlarea.</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">Have a look: </p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">Select * from v$sqlarea s order by buffer_gets ;</span></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">This view has grown over time to include a lot of columns but I recommend you start by looking at the ones you (think you...) understand: executions, time, disk_reads.</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">Depending on what you (think you) know to be your bottleneck, you can order by buffer_gets, disk_reads, or simply by elapsed time. </p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">My starting point for investigating a system is often:</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">select sql_id</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">, executions</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">, elapsed_time as microsec</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">, elapsed_time / ( (executions +1) * 1000 ) msec_p_exec</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">, buffer_gets</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">, disk_reads</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">, substr ( sql_text, 1, 60) txt</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">from v$sqlarea s</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">where 1=1</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">-- and parsing_schema_name like 'APP_OWN%'</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">order by 4 desc /* heaviest on top */</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">, elapsed_time desc</span></p><p class="p2" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: courier;">, buffer_gets desc ;</span></p><div><span style="font-family: courier;"><br /></span></div><div><span style="font-family: "Helvetica Neue";"><br /></span></div><div><span style="font-family: "Helvetica Neue";">When run in SQLDeveloper, this puts the "slowest-per-execute" on top of the list. But by editing the query, I can quickly look for the most-buffer-gets, or the most-disk-reads as well. </span></div><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiebfgGuH2ZP4Yr79GTatgzeZNtaRIz9-P0JrZGGe-C_RvRY4R7NZvpR-aeYpAtk86jHNl8u_EPSC4Bl6kXF2YQ7n9Gpq_WkLjk1PJRObcLAf5PUdJQ1tMk68aBmTp0OB5sptZ92ir5j9l9E9SHNbw6BUVLhT_HN-3ecElvZy1XMN6U72ZMAPH4JWsUNB4/s1394/slowest_stmnt.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="976" data-original-width="1394" height="280" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiebfgGuH2ZP4Yr79GTatgzeZNtaRIz9-P0JrZGGe-C_RvRY4R7NZvpR-aeYpAtk86jHNl8u_EPSC4Bl6kXF2YQ7n9Gpq_WkLjk1PJRObcLAf5PUdJQ1tMk68aBmTp0OB5sptZ92ir5j9l9E9SHNbw6BUVLhT_HN-3ecElvZy1XMN6U72ZMAPH4JWsUNB4/w400-h280/slowest_stmnt.png" width="400" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><br /></div>In the screenshot above, it is clear that the first 3 lines, a PL/SQL block and two select-statements, are consuming the majority of the elapsed-time. Those are probably the pieces we should investigate.<p></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">Notice several things about this way of "finding heavies":</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">Depending on what I want to look for, I can add/remove columns to the select. Some lines are read-to-uncomment, in case I need them. In reality, the stmnt I have saved in a ready-to-use txtfile is much larger, but it would look really messy to put that up in a blog.</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">The +1 is my dirty-trick to avoid div-by-zero. Notice how several stmnts have Zero executes: those were still running while we did the sample.</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">When run in sqlcl or sql*plus, I remove the DESC from the order-by, to make sure my relevant stmnt appears at the bottom (e.g. I dont have to scroll back).</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">Some warnings are due: </p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">There are a lot of caveats to this quick-and-dirty method.</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">It will only find statements that are currently in the shared_pool, e.g. recent or often-used statements. </p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">The numbers are "averages", which may or may not be impacted by outliers. One slow stmnt, an accidental lock, or one user looking for "Everyone called Garcia" may impact the results. <i>Hence in interpreting the results: also check against your AWR or statspack, and check against common sense.</i></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">For example, if you know or suspect your database has an IO bottleneck, you should also order by disk-reads rather than by buffer_gets or by elapsed. And if the slowest SQL does not concur with the most disk-reads, you should question whether you really have an IO bottleneck.</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">Once you have identified a few relevant or "heavy statements", also go and verify that those statements actually are involved in relevant activity. Sometimes, the "load" is caused by something you didnt expect. (I have a funny story about an uptime-measuring script that impacted a running system, causing a hiccup every 15 minutes).</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">And next, once you think you have the <b>SQL_ID</b> of a problem-stmnt, you can use that sql_id do do further inspection.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">For example, you can dig into <b>v$sql</b> for child-cursors, or into <b>v$sql_plan</b> and <b>v$sql_bind_capture</b>. Lots of possibilities there, and there is always more to discover.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">At this point, I strongly encourage you to experiment and do some further research by yourself. You are welcome to Copy/Paste my code, but you learn much more from discovering things by yourself.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Happy Searching !</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-25729178650774131602023-06-28T23:11:00.001-07:002023-06-28T23:11:23.217-07:00Do you know Where you Are ?<p><span style="font-family: "Helvetica Neue";">TL;DR: Use the sqlprompt to identify the connected system.</span></p><p><span style="font-family: "Helvetica Neue";"><br /></span></p><p><span style="font-family: "Helvetica Neue";">Why? - </span><span style="font-family: "Helvetica Neue";">My most frequent mistake is to run a command in the wrong window or against the wrong database. Hence I often double-check my schema, database, server, container, etc... </span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">How? - Using SQL to find my relevant information (how else...) and to set the prompt accordingly.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">The simplest version looks like this:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">rem following defines prompt as user @ database</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">set heading off</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">set feedback off</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">spool sqlstart</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">SELECT 'set sqlprompt "' || user || ' @ ' ||global_name||' '</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> || ' > "'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">FROM global_name gn</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">/</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">spool off</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">@sqlstart.lst</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">set heading on</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">set feedback on</span></p><div><br /></div><div><br /></div><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">This little script can be used in either SQL*Plus or SQLcl and it will modify the prompt to show me the schema and the global_name of the database I am connected to. This trick is 25 years old, and still useful.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">So far so good. But in the world of pdbs, containers and often-cloned system, I needed something more advanced. In many places, the sandboxes, dev-clones and test-environments will have the same username, the same schemaname, and the same global_name or db_name, thereby preventing my cunning script to distinguish between those system.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Hence over the years my simple prompt-gadget turned into something like this:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">rem following defines prompt as :</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">rem user [schema] @ database @ host (env)</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">set heading off</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">set feedback off</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">spool sqlstart</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">SELECT 'set sqlprompt "' </span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> || user</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> || decode ( user</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , sys_context('userenv','current_schema') , ''</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , ' ['|| sys_context('userenv','current_schema') || ']'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> )</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> || ' @ ' || global_name</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> || ' @ '|| SYS_CONTEXT('USERENV','SERVER_HOST') </span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> || decode (SYS_CONTEXT('USERENV','SERVER_HOST') </span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , 'ip-nnn-nn-2-109', ' (PROD)'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , 'ip-nnn-nn-0-226', ' (ACC)'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , 'ip-nnn-nn-0-23', ' (SE)'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , '98b6d46dd637', ' (XE)'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , '98eac28a59c0', ' (23c-demo)'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> , ' (-chk env-)') </span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"> || ' > "'</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">FROM global_name</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">;</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">spool off</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">@sqlstart.lst</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">set heading on</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: courier; font-size: x-small;">set feedback on</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";">This version has a number of improvements:</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";">It will check for current_schema, in case you are connected via a proxy-user.</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";">it will check for server_host, which you can use to verify what server or (docker, k8s) container you are running from.</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";">it has a decode that you can edit to provide better information about the system you are connected to. </span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: Helvetica Neue;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: Helvetica Neue;">In my case, if the default value of "-chk env-" appears, this means I am connected to a (new) database that is not yet "known to this script". If I want to clarify what this new system is about, I can go into the script and add a line to the decode to identify this database.</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";">In this example, you can see that the last added line was to use the container (98ea....) to tell me that this is my latest 23c version.</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";">(I have messy versions of the script with long lists of decode...)</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";">In other versions, I have used v$database and v$instance to add more items to the prompt, and colleagues have used ANSI-escape codes to modify the colors of their terminal windows (red = danger... ).</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: "Helvetica Neue";"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: Helvetica Neue;">Nothing will stop you from modifying this script to provide the information that You find relevant.</span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-family: Helvetica Neue;"><br /></span></p><p class="p1" style="font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-35898583022012830022023-03-06T02:45:00.003-08:002023-03-06T02:53:51.473-08:00A few tips, as you dive into SQL<div style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-family: "Helvetica Neue";">In previous posts, I proclaimed "Everything is SQL" (link)<br /></span>And I re-called which tools you could use to explore the SQL from your database: SQL Developer and SQLcl (or whichever other SQL-client you prefer). (links...)<br />Now for some tips...</div><div style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /><br />Tip1: Stay Current, and skilled.<br />Get familiar with your tools and Stay Current: Check the download-sites and keep up with the latest version. You are going to live in the SQL-environment, hence you may as well keep up with current versions. <br />For SQL Developer you should probably explore the Web-Version as well.<br /><span class="Apple-converted-space">For SQLcl, the most important item for me is to keep my $SQLPATH correct, or modify it for the task/client at hand. When I do demos or presentations, I often use a special, Clean, version of my sql-scripts.</span></div><div style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /><br />Tip2: Collect and Save your scripts.<br />Collect and manage (github) your own set of tool-scripts and familiarise yourself with them.<span class="Apple-converted-space"> </span>In my case, I have my own scripts for day-to-day examinations, and for troubleshooting. I can quickly clone my private set form github onto just about any machine I get to work on (if not, I have a zipfile, and at times I use old fashioned uuencode to circumvent filters ...)<br /><br /><br />Tip3: Explore scripts from others. <br />There is a lot if useful stuff out there, and you should read some of it just for Inspiration. In the end, you will suffer the "not invented by me" syndrome, but it really helps to see what others did. I use the available tools from Oracle and others. <br />Oracle provides AWR, ASH, (or statspack for Standard Edition).<br />Carlos Sierra maintains <a href="https://carlos-sierra.net/category/sqld360/">sqldb360</a>. <br />And for the high-tech folks, there is also the <a href="https://github.com/tanelpoder/tpt-oracle" target="_blank">toolset from Tanel Poder</a>. <br />This topic merits a whole article by itself, but you can start by exploring the material from Tanel Poder and Carlos Sierra. Links...<br /><br /><br />Tip4: Always establish “Where You Are”.<br />Make sure that you are “looking at the problem” and not at some random database(-clone) running in a container on some developer-box. Many ppl seem to logon to the wrong DB (e.g. pre-prod instead of prod). Make sure you are looking at the correct database or instance.<br />Sounds trivial, but it is one of the most occurring "mistakes" I come across. You have to check and Double Check that you are Actually looking at the right database, and that the supposed problem you see really is the problem the cstmr is having issues with.</div><div style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br />Similar when you get mailed a Statspack or AWR report: double + triple check that is was from the correct database, and that the problem-you-want did occur at the time-interval of the report.</div><div style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br />The other classic is to run some disastrous stmnt in Production while you were assuming you were just connected to some Dev-copy.<br />Sorry to hammer on about this, but it has happened too often...<br />For this reason, my most used, and most valuable scripts are the ones called pr.sql (to set the prompt) and a number of varieties on it. I tend to customise it for the environment I look at. Possibly merits a separate blogpost (future link)</div><div style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></div><div style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br />So far the first set of Tips..<br />next post probably about pr.sql or some of the ready-to-use scripts out there.<br /></div><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-50078220945611722372023-02-28T01:17:00.003-08:002023-02-28T02:24:49.916-08:00Your Tools: SQLDeveloper and SQLcl<p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br class="Apple-interchange-newline" />In the previous post (link), I insisted: Everything is SQL, and SQL is Everything.</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">So if all the information you need is presented in the form of tables and views, and everything is SELECT-able, you should get familiar with SQL and with the tools to run SQL.</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">Your tools of choice should be SQLcl or SQL-Developer, or the SQL-Developer web variety, if you want to be totally hip <a href="https://www.oracle.com/database/sqldeveloper/" target="_blank">(downloads and information here).</a></p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;">You may also know that SQL*Plus is still available on Every Platform where the RDBMS itself is deployed.</p><p class="p2" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><span style="font-size: x-small;">And there are others... if you are used to other SQL-tools, you will find that most of them can connect to an Oracle database, and work just fine. For example TOAD or DBeaver will also do the job just fine (<a href="https://dbeaver.com/docs/wiki/Oracle/" target="_blank">some documentation here</a>).</span></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Running a query from SQLDeveloper mostly looks like this:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAr5bbF1ZDEhop9Cw_wm9ObUCJ3-M-VvP5ayxAFLQN7KKrhLMdxqAJ2kltxsiGR6bRDJa7fV88RHo6r40zvLfNFiMzd035r8jvF_dO2XiwGarETCshwEv4fRnqTTIwgzjrHnUkhwN1001E_7yRsoE70sZvYc-qC53cAurdLRLIRJrWIBCt4Ard0OiC/s1386/sqldev_scott.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1050" data-original-width="1386" height="485" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAr5bbF1ZDEhop9Cw_wm9ObUCJ3-M-VvP5ayxAFLQN7KKrhLMdxqAJ2kltxsiGR6bRDJa7fV88RHo6r40zvLfNFiMzd035r8jvF_dO2XiwGarETCshwEv4fRnqTTIwgzjrHnUkhwN1001E_7yRsoE70sZvYc-qC53cAurdLRLIRJrWIBCt4Ard0OiC/w640-h485/sqldev_scott.png" width="640" /></a></div><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">In there you see I am using SQL to join two views, and I am looking for statement executed by SCOTT, with the heaviest IO-stmnt listed on top. This information, and many other valuable data is available to query using just the tools and SQL that you already know.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">But as a DBA, I have a strong preference for using a CLI (Command Line Interface) next to my GUIs or other tools. The big advantage of a CLI is the capability to "script" your commands. Scripts will "store your knowledge" and make it repeatable, re-usable.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">In my case, I would use SQLDeveloper to do ad-hoc inspections, queries. But once a query or a report needs to be re-run more then once, when I think it is useful in future, I will create a script. From that point on, the query can be run from using its filename.sql from the CLI. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Example is the script "pr.sql" a very small script that I use everywhere and all the time: it sets the SQL-Prompt to tell me I which user, which database and which server I am connected to, just to make sure I am always typing at the correct prompt... </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;">SQL><span class="Apple-converted-space"> </span></span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><span class="Apple-converted-space">SQL><span class="Apple-converted-space"> </span></span></span><b><i><span style="font-variant-ligatures: no-common-ligatures;">connect scott/tiger@orclpdb1</span><span class="Apple-converted-space" style="font-variant-ligatures: no-common-ligatures;"> </span></i></b></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;">Connected.</span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;">SQL><span class="Apple-converted-space"> </span></span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;">SQL><span class="Apple-converted-space"> </span></span><span style="font-variant-ligatures: no-common-ligatures;"><i><b>@pr</b></i></span></p><p class="p2" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 18px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"></span><br /></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;">set sqlprompt "SCOTT @ ORCLCDB @ oracle-21c-vagrant > "</span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><br /></span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="s1" style="font-variant-ligatures: no-common-ligatures;"><span class="Apple-converted-space"></span></span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span style="font-variant-ligatures: no-common-ligatures;">SCOTT @ ORCLCDB @ oracle-21c-vagrant ></span><span class="Apple-converted-space" style="font-variant-ligatures: no-common-ligatures;"> </span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><span class="Apple-converted-space" style="font-variant-ligatures: no-common-ligatures;"><br /></span></p><p class="p1" style="font-family: Menlo; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Because "Everything is SQL", that script actually goes out and uses SQL to find the user, the database (or PDB) and the host this database runs on, and then sets the prompt accordingly. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">If you make a habit of creating and using scrips, then, over time, you will collect a nice set of scripts. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">The first good reason for using the CLI tools, SQL*Plus or SQLcl, is that those tools can run scripts quickly using the start command or the @ operator. If you stick with the GUIs, you will at some point get tired of copy-pasting your code-snippets of SQL-commands into the GUI. Running a script from the command-prompt takes less of a mouse-and-type effort.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">And once you are using the CLI, you should investigate the setting of the environment-variable $SQLPATH. (windows: %SQLPATH ). This variable can contain one or more directories where you can store your scripts and _always_ have them at your fingertips. Not to mention the useful scripts you might copy from others who have already gone there and done that.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">In Summary:</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"> - Download and get to know the tools.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"> - Explore the data, find your information.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"> - Consider creating your own set of scripts.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"> - Use environment-variables to always have the scripts available.</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Next blogs...: </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">Tips to avoid the mistakes I made, and still see others making</p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">And links to where you can find good re-usable scripts already built by others. </p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">(insert future links here..)</p><div><br /></div><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;">And... I told you: <b>"SQL is Everything and SQL is Everything"</b></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p><p class="p1" style="font-family: "Helvetica Neue"; font-stretch: normal; font-variant-east-asian: normal; font-variant-numeric: normal; line-height: normal; margin: 0px; min-height: 15px;"><br /></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-81728944103551958602023-02-22T12:27:00.008-08:002023-02-26T04:52:06.021-08:00With Oracle RDBMS: Everything is SQL<div>Everything is SQL, and SQL is Everyting.</div><div><br /></div>One of the things I really like about Oracle RDBMS is that it follows the <a href="https://en.wikipedia.org/wiki/Codd%27s_12_rules" target="_blank">“12-rules of Codd”</a> very well. <div><br /></div><div>And What I specifically like, All of the RDBMS information is accessible via SQL.</div><div><br /></div><div>In Oracle, “Everything is a Table or View”. </div><div>And thus, everything can be queried by SQL. </div><div><br /></div><div>If you need to observe or “diagnose” your Oracle Database, you can (and should) do so via SQL. And you will also learn a few things every time you delve into something. </div><div><br /></div><div> Disclaimer: I'm writing this bcse I am trying to convince some newbies and DevOps folks to learn just a little more about Oracle.... </div><div><br /></div><div> This blogpost is to show you how you can Get to Know Everything about your Oracle database by just using the SQL interface.
For example: I can ask what tables are in my current schema with a query on a view called "USER_TABLES" </div><div><br /></div><div><span style="font-family: courier; font-size: x-small;">SCOTT @ ORCLPDB1 > <i><b>select table_name from user_tables order by 1 ;</b></i></span></div><div><span style="font-family: courier; font-size: x-small;">TABLE_NAME </span></div><div><span style="font-family: courier; font-size: x-small;">-----------------------</span></div><div><span style="font-family: courier; font-size: x-small;">DEPT</span></div><div><span style="font-family: courier; font-size: x-small;">EMP</span></div><div><span style="font-family: courier; font-size: x-small;">LOG_STATS</span></div><div><span style="font-family: courier; font-size: x-small;">PT
PT_C</span></div><div><span style="font-family: courier; font-size: x-small;">PT_CC</span></div><div><span style="font-family: courier; font-size: x-small;">PT_CCC</span></div><div><span style="font-family: courier; font-size: x-small;">T</span></div><div><span style="font-family: courier; font-size: x-small;">T_C</span></div><div><span style="font-family: courier; font-size: x-small;">T_CC</span></div><div><span style="font-family: courier; font-size: x-small;">T_CCC</span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">11 rows selected. </span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">SCOTT @ ORCLPDB1 > </span></div><div><br /></div><div><span style="font-family: inherit;">And if you want to dig deeper: you can ask the database where it stores the actual datafiles, for example from v$datafile: </span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">SCOTT @ ORCLPDB1 > <b style="font-style: italic;">select name from v$datafile ; </b></span></div><div><span style="font-family: courier; font-size: x-small;">NAME </span></div><div><span style="font-family: courier; font-size: x-small;">------------------------------------------------------------- </span></div><div><span style="font-family: courier; font-size: x-small;">/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf </span></div><div><span style="font-family: courier; font-size: x-small;"><br /></span></div><div><span style="font-family: courier; font-size: x-small;">SCOTT @ ORCLPDB1 ></span></div><div><span style="font-family: courier; font-size: x-small;"><i><b><br /></b></i></span></div><div><span style="font-family: inherit;">And in that spirit, following notably rule nr 4 of Codd, all the data and metadata of your Oracle database can be found via SQL. </span></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;">I strongly encourage any DBA (and Developer, Devops, etc.. ) to explore the <a href="https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/static-data-dictionary-views.html#GUID-8865F65B-EF6D-44A5-B0A1-3179EFF0C36A" target="_blank">available documentation</a> on the DBA_% and V$... views. Everything you need to know about your database is There. And much more. All reachable with Select-statements. </span></div><div><span style="font-family: inherit;">Link to the official doc here ....</span></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;"><span style="font-size: x-small;">note: Compare Oracle to unix/linux. Those of you who are brought up with unix/linux will recognise this. In unix, “Everything is a File”. </span></span></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;">My fingers are itching to show more SQL and to show how you can inspect the darker corners of your database with some some nifty SQL-stmtns.</span></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;">Also, I want to make some points about tooling, issue some tips and some Warnings. With powerfull tools comes some responsability on using them correctly. </span></div><div>The more I think (write) about it, the more there is to elaborate upon.</div><div><span style="font-family: inherit;">Guess I have to do some more blogs...</span></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;">But my main message would be: Check the 12 rules of Codd, and explore some of the documentation about the internal views. There is your starting point.</span></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;"><b>For now, just remember: Everything is SQL ! </b></span></div>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-51495076859790224542022-10-11T02:18:00.002-07:002023-01-11T08:40:22.699-08:00You Should Speak at Conferences. #JoelKallmanDay<p> Conferences are Great - you should go (and Speak)</p><p>Instead of some geek-content, This is a call-to-speakers. The trigger for this post is two-fold: 1) the <a href="https://oracle-base.com/blog/2022/09/27/joel-kallman-day-2022-announcement/" target="_blank">Joel-Kallman-Day inititative from Tim Hall</a>, and 2) the fact that some IT conferences are still searching for relevant, new/local speakers.</p><p><br /></p><p>So..</p><p>Conferences are Great, and You Should go + Speak! </p><p><br /></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTj_whrLLmwIvlFee7-z3Hcym9sO53LgFOtaaxp_YCuLq4VRMFeMifzbVl-CG3iCnEA7_MH8vJdC7qce6Mfq98J8kjF7DT37g5ux52unn9wfXP3Fja7dzWxFrYSuM6oHh3pRIqUES7pyKH/s862/Screenshot+2021-06-10+at+18.51.11.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="862" data-original-width="778" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTj_whrLLmwIvlFee7-z3Hcym9sO53LgFOtaaxp_YCuLq4VRMFeMifzbVl-CG3iCnEA7_MH8vJdC7qce6Mfq98J8kjF7DT37g5ux52unn9wfXP3Fja7dzWxFrYSuM6oHh3pRIqUES7pyKH/w289-h320/Screenshot+2021-06-10+at+18.51.11.png" width="289" /></a></div><p><br /></p>Conferences are for getting out of the (home)office, to discuss topics, meet people, and learn new things, ideas, concepts, trends. And for the food+drink, although that varies from place to place.<p></p><p>And the best way to get to a conference is ... to do a Presentation.</p><p>Especially when I had to "ask my boss", it would always Greatly help if I could say: "I'm Speaking at XYZ, can you please budget the trip+time" (maybe more on that later).</p><p><br /></p><p>So... What to Speak about?</p><p><b>Here is one of my best 3-step tricks to find a topic:</b></p><p>1. Find the biggest Obstacle you had at work in the last 12 months (e.g. learning python, creating K8s pods, Designing your Datamodel, arguing with your architect..). That can be your Topic.</p><p>2. Now write down what you want to Tell / Warn / Laugh to others about (The Quirks of Python, Yaml/Ansible-syntax, how to manipulate the architect). This will be your "Message".</p><p>3. Turn that into slides notably with a clear Conclusion. Give your listeners a "Take Home Lesson" at the end. (I do max 25 slides, but your style may vary).</p><p>There you are!</p><p><br /></p><p>My reasoning behind this is multiple: </p><p>First, you will be a better speaker if you have a grain of Passion, a Mission. Something you Really Want to Communicate. And even better: something you have Experienced Yourself. </p><p>Secondly, you probably were not the only one with whatever challenge you had. Others will have been in the same situation and will recognise it. Those will be your Audience, and they will spark the discussion afterwards.</p><p>Thirdly: Because you are "On a Mission" to convey your learnings, you will be better motivated, and you will more easily overcome any stage-fright your may have.</p><p><br /></p><p>There is more to speaking, but you will learn in the process of Doing! </p><p>A good source of information is also the<a href="https://www.kibeha.dk/2021/03/mash-mentor-and-speaker-hub-is-here-for.html" target="_blank"> MASH program (link).</a> And there are the practicalities, things like: </p><p>Avoid boring slides (important, but less important than Your Mission)</p><p>How to work towards the conclusion (important, but less important than your intrinsic Motivation)</p><p>Use of Clip-art and moderate humour (important, not Essential to your Message)</p><p>How to determine your Tempo, Timing. Your first presentation will run-over, that is normal, and not a problem: Organizers will keep-time, or not. And getting late to other talks is (partly) the problem of the audience. Truly Motivated listeners may even remain behind, and harass you with Questions. The secret to not run over is.. a) do the presentation a few times, and b) remove irrelevant content (this can be hard - especially if you want to tell a "whole story")</p><p>Note: running-over is Totally Impolite because it creates problems for audience, for other speakers, and for organizers. If an experienced speaker goes over time, you can tell him off. But every beginner-speaker should be allowed to run-over (once, just once :-) ). If a "sales-pitch-speaker" runs over time: Ban Him (that is 99% of cases a him) and Shame his Product.</p><p><br /></p><p>Oh, and about that food+drink: After the event, tell the organizers how good or bad their catering was. Some will learn, some wont.</p><p>Now go out and Enjoy!</p><p><br /></p><p><br /></p><p><span style="font-size: x-small;">footnote: Another reason to Re-Shout this message is that, notably in the Ora-sphere, and in some of the dev-rel oriented conferences, the nr of speakers seems to decrease,` and I see a lot of "habitués" and company-sponsored professional Dev-Rel ppl (pre-sales in disguise) repeating their messages. Some are Great People, and I love to discuss with them, but some others are ... </span></p><p><span style="font-size: x-small;">foot-footnote: (deleted, too Rant-ty, NSFW)</span></p><p><br /></p><p><br /></p><p><br /></p><p><br /></p>PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com0tag:blogger.com,1999:blog-2170637149389736039.post-4851236544692283152019-10-10T00:31:00.001-07:002019-10-10T04:51:27.045-07:00 OGB Appreciation Day: Efficiency with Partitions.<div dir="ltr" style="text-align: left;" trbidi="on">
Partitioning is Very Useful - If ... you take the effort to Design for it properly.<br />
<br />
What I like best about Partitioning is the "life cycle feature". You can remove large amounts of (old) data with a single statement in the blink of an eye, and with very little Redo incurred.<br />
<br />
If you want, you can also Add or Move data around in the blink of an eye as well.<br />
<br />
Google for :<br />
Alter table Drop Partition<br />
Alter table Add Partition<br />
<br />
<br />
<b>Use Partitioning for Fast and Efficient Data Manipulation (notably Deletes)</b><br />
<br />
The main "successful" use I've seen from partitioning is this: (Re)Moving large amounts of data in the blink of an eye, without incurring Undo+Redo for the total amount of data, and without locking.<br />
<br />
Very little Redo (it is a DDL operation, not DML)<br />
No Locking.<br />
No Index Rebuilding (e.g. only local indexes).<br />
Fast!<br />
Efficient!<br />
<br />
<br />
Your Mission, should you choose to accept it:<b> </b><br />
<b>Verify this for yourself!</b><br />
<br />
Here is your homework.<br />
<div>
<br /></div>
If you are serious about trying / using this feature:<br />
- Create a partitioned table with at least 2 partitions.<br />
- Insert data into both partitions, at least 100K records (and commit)<br />
- generate statistics, verify the approx row-counts in each partition.<br />
- set Autotrace on<br />
- set timing on<br />
- Delete all the data in 1 partition with a "Delete from ... Where ..."<br />
- Note the time and the amount of Redo involved.<br />
- commit (just dont forget...)<br />
- Re-generate stats and verify the row-counts, your data is deleted ?<br />
<br />
Now for the Partition-operation...<br />
- Re-create the same table, same partitions, with same data (commit!)<br />
- generate + verify stats...<br />
- Set autotrace + timing again<br />
- Drop a partition: Alter table ... drop partition ;<br />
- note: the implicit commit... this is a DDL operation.<br />
- Note the time and the amount of redo.<br />
- Verify the data is gone.. (trust-no-1....)<br />
<br />
<br />
<b>QED.</b><br />
<br />
Now how cool is that?<br />
And so Simple...<br />
<br />
<br />
Go ahead, do it, and et us know the differences in Time and Redo in the comments...<br />
<br />
My (not quite conform) example script is <a href="https://raw.githubusercontent.com/pdvmoto/binsql/master/demo_part.sql" target="_blank">here</a><br />
<br />
And if You can Do this:<br />
Congratulations! You are on your way to Master the use of Partitioning.<br />
<br />
<br />
<br />
Now, there is a lot more to Partitioning, of course.<br />
<br />
My opinion in Short, rather simplified statements.<br />
- You need to "design" partitioning from the start. A "bolt on" to an existing data-model will 99/100 fail.<br />
- Use (only) automatic interval partitioning, prevent yourself from having to pre-create partitions regularly (you will forget...)<br />
- Every SQL to Ever Access that table needs a partition-key in the where-clause.<br />
- Avoid global indexes if possible.<br />
<div>
<br /></div>
NB: I know the "maintain global indexes" was created to prevent+fix some of my pet-problems-with-partitions, but I am still skeptical about the usage in live (OLTP) systems.<br />
<br />
NB2: Kuddos to Hermann (dot) Baer (at oracle dot com), a.ka. @sdjh2000 for constantly improving the capabilities of the partitioning option. Good Work on a Great Feature.<br />
<br />
<br />
<br />
<b>That's all for now folks.</b><br />
<br />
With a Large Kuddos to Tim Hall (@oraclebase) for the Yearly OGB-appreciation day<a href="https://oracle-base.com/blog/2019/09/30/ogb-appreciation-day-2019-thanksogb/" target="_blank"> (link)</a>. Makes me blog at least once per year...<br />
<br />
OGB-appreciation day was f.k.a. the OTN-Appreciation-Day,<br />
re-branded as the ODC-Appreciation day,<br />
re-branded as the OGB-Appreciation Day.<br />
Who knows what next year will bring - Oracle Cloud Dev-Ops (OCD) Appreciation day?<br />
<br />
<br />
And if you got this far, a word of warning:<br />
Do Not Ever Make Fun of Oracle Buzzwords or Hashtags.<br />
Humour and Oracle only work if it is Oracle-Approved humour.<br />
(mandatory buzzword compliant content...)<br />
Remember: We all work for Larry, Only some of us dont know it yet.<br />
<br />
CU at some event, at some webinar or next year on this blog-event.<br />
<br />
Hashtag : #ThanksOGB<br />
<br /></div>
PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com1tag:blogger.com,1999:blog-2170637149389736039.post-55932227143642912662018-10-10T23:35:00.001-07:002018-10-10T23:46:02.783-07:00ODC Appreciation Day..<div dir="ltr" style="text-align: left;" trbidi="on">
It is that time of year: Write about your favorite feature and say "Thank You Oracle"..<br />
<br />
This year, I'll pick <b>Oracle</b> <b>Golden Gate</b><br />
<br />
Short story: if you need to replicate data, GG is one of your easy choices.<br />
<br />
Slightly Longer story:<br />
<br />
I was lucky: customer choose GG to replicate data for one country to another in major move of IT systems and data (yes, to the cloud, to those large Remote Database Systems in AWSome land...).<br />
<br />
GG helped us run in sync for a number of weeks, and the final cutover was a breeze.<br />
GG also (sort of) kept open a replica back to our original datacenter, so management felt they could potentially chicken out and move back to the old provider.<br />
<br />
The whole process was easier than I expected,<br />
and a <b>Large Tip of the Hat to Golden Gate </b>is appropriate.<br />
<br />
<br />
(the issues, the problems, the quirks.. Later)<br />
<br />
The reason for this post Tim Hall<br />
<a href="https://oracle-base.com/blog/2018/10/11/odc-appreciation-day-effective-evangelism-staying-positive/">https://oracle-base.com/blog/2018/10/11/odc-appreciation-day-effective-evangelism-staying-positive/</a><br />
<br />
The link to Golden Gate (Hi Bobby!)<br />
<a href="https://www.oracle.com/middleware/data-integration/goldengate/">https://www.oracle.com/middleware/data-integration/goldengate/</a><br />
<br />
<br />
<br /></div>
PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com4tag:blogger.com,1999:blog-2170637149389736039.post-20230948982393988402018-01-05T07:59:00.000-08:002018-01-05T09:49:31.726-08:00What is SmartDB<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="p1">
What is #SmartDB ?</div>
<div class="p2">
<br /></div>
<div class="p1">
This question came up in the twitter discussion about #SmartDB, and all the advantages it brings <a href="https://twitter.com/PNosko/status/948909376278089728" target="_blank">(link to twitter).</a></div>
<div class="p1">
<br /></div>
<div class="p1">
Over the last year or so (and way before that, with the <a href="http://thehelsinkideclaration.blogspot.nl/" target="_blank">Helsinki-declaration</a> in 2008), Toon Koppelaars has given us the reasons and guidelines for #SmartDB, and it boils down to “do the work in the database” (correct?)</div>
<div class="p2">
So the shortest description, IMHO, would be :<span class="Apple-converted-space"> </span></div>
<div class="p2">
<br /></div>
<div class="p1">
<b>SmartDB:<span class="Apple-converted-space"> </span></b></div>
<div class="p1">
<b>Any IT system using a database should do as much as possible of its processing inside the Database, and as little as possible of its processing in other layers.</b></div>
<div class="p2">
<br /></div>
<div class="p1">
(Agreed?)</div>
<div class="p2">
<br /></div>
<div class="p1">
TL;DR ? Read no further.<span class="Apple-converted-space"> </span>;-)</div>
<div class="p2">
<br /></div>
<div class="p2">
<br /></div>
<div class="p1">
Background of this approach is that this would lead to least-complexity, least-components, least round-trips, least-overhead, and least-complicated troubleshooting (only one component to examine and fix… ).<span class="Apple-converted-space"> </span></div>
<div class="p2">
<br /></div>
<div class="p1">
Also, my too-short definition doesn’t include (yet) the need to apply sound database-practices.<span class="Apple-converted-space"> </span>Good IT systems start with good (system) design (based on requirements). It also<span class="Apple-converted-space"> </span>includes things like 3NF, ACID, notably resilience, and adequate security based on minimum-privs and minimum exposed surface. Then there are “scalability”, “upgrades” and “monitoring” to allow the system to remain in action over longer periods of time, and under various loads. Sustainability, if you like.</div>
<div class="p2">
<br /></div>
<div class="p1">
To me, all the above still makes sense. And I feel comfortable to design/build an IT system given those guidelines. </div>
<div class="p1">
Of course, some are not content with an extremely short definition, and others demand a more elaborate description or a how-to cookbook-guide. All of that, Toon and Bryn are trying to provide in various presentations, videos and white papers on the subject.</div>
<div class="p2">
<br /></div>
<div class="p1">
I’m going to add a a suggestion.</div>
<div class="p2">
<br /></div>
<div class="p1">
To better <b>define and describe SmartDB</b>, I suggest to follow these steps:</div>
<div class="p1">
<b>- Requirements,<span class="Apple-converted-space"> </span></b></div>
<div class="p1">
<b>- Reasoning,<span class="Apple-converted-space"> </span></b></div>
<div class="p1">
<b>- Recommendations.</b></div>
<div class="p1">
<b><span class="Apple-converted-space"></span></b></div>
<div class="p1">
Those three steps should lead us to a better description and thus to a better way to “evangelise” the SmartDB concept.<span class="Apple-converted-space"> </span></div>
<div>
<span class="Apple-converted-space"><br /></span></div>
<div class="p1">
<span class="Apple-converted-space">To Elaborate each step:</span><br />
<span class="Apple-converted-space"><br /></span></div>
<div class="p1">
The (list of) <b>Requirements </b>should state why SmartDB is needed, and which problems the concept tries to solve.</div>
<div class="p1">
<br />
By following logical <b>Reasoning</b>, with the knowledge and technology available, we can explain how each requirement is addressed in what is the most efficient way known to us.</div>
<div class="p1">
<br />
To finalize, we create a list of <b>Recommendations</b> (if not: Directives!), on how to implement SmartDB. The recommendations should, at first, not be connected to any particular database or programming language. Those details can be filled in later. Each "vendor" should do this for his own product, and hopefully stay within the concept of SmartDB.</div>
<div class="p2">
<br /></div>
<div class="p1">
The result of this exercise should be (yet another) white paper, 10 page max, and some presentation material that we could throw at Developers, Architects, Managers and even Dev-Ops and UX wizards to explain and convince them.</div>
<div class="p2">
<br /></div>
<div class="p1">
Some history: </div>
<div class="p1">
Long time ago, in a cubicle-space far far away, an OFA-standard was defined via a similar process in the early 90s<br />
I am that old. I recall how this Oracle Flexible Architecture was created and "explained" (OFA was the mother of all Oracle Standards - link needed).</div>
<div class="p1">
OFA was created roughly in those three steps: <b>R</b>equirements, <b>R</b>easoning and <b>R</b>ecommendations (Directives!) I’ve not checked the original paper for years but this is what I recall…<br />
<br /></div>
<div class="p1">
This proces of problem identification and reasoned solutions was clear and could be explained to most users (Sysadmins, DBA’s) at the time. The standard was widely adapted and cited in the Oracle-DBA world.<span class="Apple-converted-space"> </span></div>
<div class="p2">
<br /></div>
<div class="p2">
<br /></div>
<div class="p1">
So, to recapitulate, I think we should follow a similar path to define the SmartDB concept: Requirements,<span class="Apple-converted-space"> </span>Reasoning,<span class="Apple-converted-space"> </span>Recommendations. Those three steps should lead us to a better description and thus to a better way to explain, promote, and verify the SmartDB concept.<span class="Apple-converted-space"> </span></div>
<div class="p2">
<br /></div>
<div class="p1">
(I know, I know, Bryn is going to say that all of the above is covered 11.2 years ago with EBR, and we only need 4 schemas… but still, it wont hurt to re-assert some items…)</div>
<div class="p2">
<br /></div>
<div class="p2">
<br /></div>
<div class="p1">
Additional notes:</div>
<div class="p2">
<br /></div>
<div class="p1">
note1: I have barely mentioned Oracle (or PostgreSQL, or MySQL, sukkel-Srvr, or even sp-hna). The SmartDB concept should not be locked into a particular product.</div>
<div class="p1">
note2: I have not mentioned any theoretical or academic knowledge, but I would assume IT ppl to be familiar with handling requirements, IS-design, ACID, 0+12,<span class="Apple-converted-space"> </span>UX, some OOP, various methods of testing, etc…<span class="Apple-converted-space"> </span></div>
<div class="p1">
note3: I have not mentioned any procedural language, but SmartDB does imply the use of some stored-procedure dialect</div>
<style type="text/css">
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #000000}
p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #000000; min-height: 14.0px}
</style>
<br />
<div class="p1">
note4:… there is much more, but it needs to be discussed.<span class="Apple-converted-space"> </span></div>
</div>
PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com5tag:blogger.com,1999:blog-2170637149389736039.post-911201446804793222018-01-02T04:58:00.002-08:002018-01-02T05:26:58.550-08:00Quick notes on SmartDB (temporary post)<div dir="ltr" style="text-align: left;" trbidi="on">
Since there is a discussion going on, mostly on <a href="https://twitter.com/BrynLite/status/947888997220548610" target="_blank">Twitter (link)</a> about SmartDB, I'm going to put my own notes here. That way I have a public-place to "think out loud" about the concept of SmartDB.<br />
<br />
<b>Smart-DB is, </b>in my view:<br />
<b>A concept where an IT system is built In the DataBase.</b><br />
<b>Constructed so that most of the work (and processing, and maintenance) </b><br />
<b>is </b><b>done IN THE DATABASE.</b><br />
<br />
This makes for a single point where all (99%) of the logic can be found, and where all (99%) of the code can be found and maintained.<br />
<br />
Maximum logic/work/processing on the DB.<br />
Minimum processing in front-end-tier (browser?).<br />
<br />
<b>Eliminate other layers and components.</b><br />
<br />
Key point, in my view: Eliminate layers, eliminate components, and eliminate sources of problems.<br />
<div>
Also eliminates round-trips between components, a notable source of delay.</div>
<br />
note: SmartDB is more then just using PL/SQL or pl/pgsql, but those "stored procedure" constructs are an essential part of the SmartDB concept. Hence the focus often shifts to these tools.<br />
<br />
<b>Keep in mind the overall goal: </b><br />
<b>A Working and Sustainable IT system (to process data for some end-user-business purpose).</b><br />
<br />
Data and Data-model tends to survive the tools and even the "processes" of the system.<br />
<br />
SmartDB methodology, as i was taught years ago, would go more or less like this....<br />
<div class="p2">
<br /></div>
<div class="p1">
<span class="Apple-converted-space"> </span>- Ensure you have a notion of IT technology and Design. (e.g. design of process and data)<br />
- define the goal of your system: why will this system exist and who are the users, “consumers” ?<span class="Apple-converted-space"> </span></div>
<div class="p1">
<span class="Apple-converted-space"> </span>- define datamodel. what data needs to go<span class="Apple-converted-space"> </span>in+out of the system, and what needs to be stored.</div>
<div class="p1">
<span class="Apple-converted-space"> </span>- define processes, functionality, functions.</div>
<div class="p1">
<span class="Apple-converted-space"> </span>- refine data-model and table design. one possible Quality checks is 3NF. </div>
<div class="p1">
<span class="Apple-converted-space"> </span>- define the interaction between processes and data (CRUD matrix)</div>
<div class="p1">
<span class="Apple-converted-space"> </span>- list/define the interfaces : which external actors bring/consume data to/from the system</div>
<div class="p1">
<span class="Apple-converted-space"> </span>- define/determine the format of the interfaces (e.g. message-definitions)</div>
<div class="p1">
<span class="Apple-converted-space"> </span>- program the interfaces to receive and return their data and statuses (e.g. error-messages)</div>
<div class="p1">
<span class="Apple-converted-space"> </span>- check for completeness: determine the life-cycle of all entities/records.</div>
<div class="p1">
<span class="Apple-converted-space"><br /></span></div>
<div class="p1">
<span class="Apple-converted-space"><br /></span></div>
<div class="p1">
<span class="Apple-converted-space">List of notable advantages <a href="https://cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/f4a5b21d-66fa-4885-92bf-c4e81c06d916/File/6900012af83fa0f8eb8224513713fb55/why_use_plsql_whitepaper_10.pdf" target="_blank">(Toon + Bryn hava a much better list...link?):</a></span></div>
<div class="p1">
<span class="Apple-converted-space"> - logic and data in 1 place. (look no further)</span></div>
<div class="p1">
- back end can survive multiple versions of "front-end" or UX (if a front-end exists at all!)</div>
<div class="p1">
<span class="Apple-converted-space"> - troubleshooting in 1 place (look no further, no discusion, no hiding, no escape.... )</span></div>
<div class="p1">
<span class="Apple-converted-space"> - potential to minimize round-trips. Every "interaction" should be a message, preferably a single round trip.</span></div>
<div class="p1">
<span class="Apple-converted-space"> - Messages can be de-coupled (most, not all...), and queueing mechanisms can be used for resilience.</span></div>
<div class="p1">
<span class="Apple-converted-space"> - messages should be constructed to allow re-play (re-submit) without damage to integrity of the system or database.</span></div>
<div class="p1">
<span class="Apple-converted-space"> - ACID only required in the Database. </span></div>
<div class="p1">
<span class="Apple-converted-space"> - The database-replication mechanisms, and only the DB-replica!, provides DR capability.</span></div>
<div class="p1">
<span class="Apple-converted-space"><br /></span>
<span class="Apple-converted-space"><br /></span>
Use/sale of Tools or Products is not a goal<br />
Use/sale of Cloud is not a goal<br />
Use/sale of "big data" is not a goal.<br />
Use/sale of UX / Microservices / ClientServer / Cobol / R-sharp / Dockernetis / etc... is not a goal...<br />
<span class="Apple-converted-space"></span><br />
<div class="p1">
</div>
<br />
<div style="-webkit-text-stroke-width: 0px; color: black; font-family: Times; font-size: medium; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; margin: 0px; orphans: 2; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
<br /></div>
<div style="-webkit-text-stroke-width: 0px; color: black; font-family: Times; font-size: medium; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; margin: 0px; orphans: 2; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
Future work:</div>
<div style="-webkit-text-stroke-width: 0px; color: black; font-family: Times; font-size: medium; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; margin: 0px; orphans: 2; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
- include links to proven methods of data-design (Heli?) . Include data-design in text.</div>
<div style="-webkit-text-stroke-width: 0px; color: black; font-family: Times; font-size: medium; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; margin: 0px; orphans: 2; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
- include links to ACID and 12+1 rules for Databases, include in text.</div>
<div style="-webkit-text-stroke-width: 0px; color: black; font-family: Times; font-size: medium; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: 400; letter-spacing: normal; margin: 0px; orphans: 2; text-align: left; text-decoration-color: initial; text-decoration-style: initial; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
- include links to proven methods of Requirements and how-to Design... </div>
</div>
<div class="p1">
<span class="Apple-converted-space"> - Do we have to start "Teaching IT from Scratch" again ??? </span><br />
<span class="Apple-converted-space"><br /></span></div>
<div class="p1">
So far my notes.</div>
<div class="p1">
I also have life, you know..</div>
<div class="p1">
<span class="Apple-converted-space"><br /></span></div>
<style type="text/css">
p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #000000}
p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #000000; min-height: 14.0px}
</style></div>
PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com3tag:blogger.com,1999:blog-2170637149389736039.post-78842765874788717432017-10-09T22:08:00.000-07:002017-10-09T22:08:43.243-07:00ODC Appreciation Day: IOT, Index Organized Tables<div dir="ltr" style="text-align: left;" trbidi="on">
The ODC appreciation Day is a yearly tradition created by Tim Hall of Oracle-Base (link) to say "Thank You" to the Oracle Developer Community. This year he designated 10-Oct-2017, hence this post.<br />
<div>
<div>
<br /></div>
<div>
link to Tim: <a href="https://oracle-base.com/blog/2017/09/25/odc-appreciation-day-2017-thanksodc/">https://oracle-base.com/blog/2017/09/25/odc-appreciation-day-2017-thanksodc/</a><br />
<div>
<br /></div>
<div>
To join in on the ODC chorus, I'll present my favorite, very Simple, feature. </div>
<div>
<br />
<div>
<b>The INDEX ORGANIZED TABLE. </b></div>
</div>
<div>
<br /></div>
<div>
This feature is a favorite of mine, because 1) it is simple, you can explain it to almost anyone in under five minutes 2) there are a few additional benefits that kick in with the way Oracle has implemented this feature. </div>
<div>
<br /></div>
<div>
<b>Short Description:</b></div>
<div>
<div>
In an Index-Organized Table (IOT) is a table of which the of records are (physically) stored in the order of their defined primary-key index, even if they are inserted at very different points in time. In a conventional (heap) table, the dependants of 1 parent may get scattered all over the heap, depending on the time of insert, whereas in an IOT the children of a parent will always end up in the same block. (or adjacent blocks if more are needed).</div>
<div>
<br /></div>
<div>
<b>Use-Cases:</b></div>
<div>
In my experience, IOTs can be beneficial in 3 specific cases :</div>
<div>
1) parent-child tables where retrieval often includes the "fetch all children" case. </div>
<div>
2) link-tables that connect two sets in an n:m relation.</div>
<div>
3) lookup tables.</div>
<div>
<br /></div>
<div>
I will not say you need to take these cases as "must-do" or "best practices", because there are cases where IOTs will fit nicely, but there are also cases where they will backfire on you. Go Read.</div>
<div>
<br /></div>
<div>
<b>History and "other databases":</b></div>
<div>
IOT has been around since Oracle 8 or 8.1 (the nineties!), but somehow has only been used by nerdy-geeky DBA's in specific cases. Competing Databases have "discovered" and used this feature more than Oracle. Hence explaining this feature to converted colleagues is sometimes very easy.</div>
<div>
<br /></div>
<div>
<b>Further reading:</b></div>
<div>
I would encourage you to explore the Manual and other sources of information, especially if you have large "hierarchical" sets of data with parent-child relations such as history-keeping or sets of object-attribute tables.</div>
<div>
Richard -Mr Index- Foote has discussed IOTs in 2012:</div>
<div>
<a href="https://richardfoote.wordpress.com/2012/01/10/index-organized-tables-an-introduction-of-sorts-pyramid-song/">https://richardfoote.wordpress.com/2012/01/10/index-organized-tables-an-introduction-of-sorts-pyramid-song/</a></div>
<div>
And Martin Widlake in 2011:</div>
<div>
<a href="https://mwidlake.wordpress.com/2011/07/18/index-organized-tables-the-basics/">https://mwidlake.wordpress.com/2011/07/18/index-organized-tables-the-basics/</a></div>
<div>
And here is the link to my original blogpost about IOTs, mainly based on my work on a few project from around that time:</div>
</div>
<div>
<a href="http://simpleoracledba.blogspot.nl/2007/05/index-organized-tables-iots-forgotten.html">http://simpleoracledba.blogspot.nl/2007/05/index-organized-tables-iots-forgotten.html</a></div>
<div>
<br /></div>
<div>
<div>
<b>Voodoo Anecdote: </b></div>
<div>
Kamil and his team on the Voodoo product have recently found to their surprise(?), that direct-inserts do not work on IOTs. (link) Not surprising, if you realise that each record will have to be inserted in exactly the right place in the table: in between it's "siblings", to maintain the correct ordering of the records. Hence a Direct-Insert (inserting into a new block) would only be possible if the inserted data adhered to at least two conditions: 1) new records should have higher PK values than the highest existing record and 2) the newly inserted data would need to be presented in a (pre)ordered way to maintain the IOT property.</div>
<div>
Link to Kamil: <a href="http://blog.ora-600.pl/2017/10/02/direct-path-insert-and-iots/">http://blog.ora-600.pl/2017/10/02/direct-path-insert-and-iots/</a></div>
<div>
Careful study of the <b>R</b>eally <b>T</b>erribly <b>F</b>antastic <b>M</b>anual may reveal that a direct-insert on IOT is (was?) possible, but only when inserting pre-ordered data into an empy-table (link to docu, if I can still find it).</div>
</div>
<div>
<br /></div>
<div>
<b>IoT versus IOT</b></div>
<div>
Since the abbreviation IoT is now in use for Internet of Things, any mention of IOT (index organized table) seems to get lost in the noise. Hence this blog-post is also little reminder to the search engines to keep the legacy-item of IOT alive.</div>
</div>
</div>
<div>
<div>
<br /></div>
<div>
</div>
</div>
</div>
PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com1tag:blogger.com,1999:blog-2170637149389736039.post-15940110014378655642017-06-27T11:43:00.000-07:002017-06-27T11:43:01.524-07:00PG Day Russia, out of comfort zone.<div dir="ltr" style="text-align: left;" trbidi="on">
In one week from now, I will be visiting the <a href="https://pgday.ru/en/2017">pgday.ru</a> event in St Petersburg.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://pgday.ru/ru/2017" target="_blank"><img alt="There is a Russian and an English page... " border="0" data-original-height="1310" data-original-width="1396" height="375" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKytX7OQfOg2ZYQv6w6uusVRGS_eYVUgtbaFkZvnUXN8JTbg72jKu24XIBLgoz2DPWOHHDyoiYjTnG2dxeIYlsmoYVs32Apeecuo3_RGIYfeMY665oUhrfBwW0dh9YwnpLkq1EPascDmA/s400/Screen+Shot+2017-06-26+at+20.00.54.png" title="" width="400" /></a></div>
<br />
<br />
A big Thanks (Спасибо) goes to <a href="https://pgday.ru/en/2017/speakers/117" target="_blank">Timur Akhmadeev</a> for bringing the event to my attention.<br />
<br />
Curious as I am, I decided to try and send them some abstracts, and Low-and-Behold: I got selected to speak. <a href="https://pgday.ru/en/2017/speakers/133">Twice</a>. Wow, Honored. My head was swelling with pride...<br />
<br />
To some of my oracle Friends: I can recommend visiting non-Oracle events: You can stop bickering about the CBO or the opatch-inventory, and re-start living again. We can learn a lot, and we may even warn the PG community of some of our oracle-based mistakes.<br />
<br />
It is Interesting and Fun just exploring the fellow-speakers and topics.<br />
The PG community seems full of talent and experience. For example, they are also (re)discovering the benefits of "smart-DB" and "thick-DB" <a href="https://pgday.ru/en/2017/papers/214">(link to "continuous delivery of stored-procedures").</a><br />
<br />
So today I got my Passport back (visum approved), and I am all set to go. I really look forward to wandering around this event as a complete noob in a Foreign Environment.<br />
<br />
#Curious #любопытный<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQEZ97IN2W4CjK_9m-KeekY89e7xX8GXhucW2gRK7g7i_5UIc_ohLAEM_r7djoQcfCjUn3JR7MOFdneZ_z1FJmwujGt70tz5CBrGUIk-LV5m5o8W5fdUX-7AQs3UggaUCxsuBXXtmYx-Q/s1600/pgdays.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="213" data-original-width="460" height="148" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQEZ97IN2W4CjK_9m-KeekY89e7xX8GXhucW2gRK7g7i_5UIc_ohLAEM_r7djoQcfCjUn3JR7MOFdneZ_z1FJmwujGt70tz5CBrGUIk-LV5m5o8W5fdUX-7AQs3UggaUCxsuBXXtmYx-Q/s320/pgdays.png" width="320" /></a></div>
<br /></div>
PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com4tag:blogger.com,1999:blog-2170637149389736039.post-86152416110649323782017-03-28T07:36:00.000-07:002017-03-30T00:41:01.086-07:00OUG Meetings Spring 2017<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="p1">
Planning is in the air.<br />
<br />
This spring, if all continues to go well, I will visit six events in a nice, long spring "tour". And here is the Logo-and-Link-fest of the various events:<br />
<br />
<br />
<div style="text-align: center;">
15-16 May, the <a href="https://rigadevdays.lv/" target="_blank">Riga Dev Days (RDD)</a>:<br />
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://rigadevdays.lv/" imageanchor="1" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img alt="Riga Dev Days." border="0" height="216" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVFUTMc3Zdh7CJMVX1HVKrmForA_lwdmU5Zhpq0wY6FeWRUmC-H0xXofvabLRxbEoAFncj9spBkK86IE5cvte0FpeQYusmEKjOAH3Yrq87XsffZdjf4CDoLRgoHGBOYuz72NNf-PNmakk/s400/speaking_rigadevdays_4.jpeg" title="Riga Dev Days, have a look" width="400" /></a>
</div>
<div class="separator" style="clear: both; text-align: center;">
<span style="text-align: left;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="text-align: left;">24-26 May: Serbian Oracle User Group </span><a href="http://www.sroug.rs/" style="text-align: left;" target="_blank">conference in Zlatibor</a><span style="text-align: left;">:</span></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="text-align: left;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.sroug.rs/" imageanchor="1" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img alt="Serbian Oracle User Group - Zlatibor Conference." border="0" height="125" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiUXU67Y_Sa7qt_KSnUWttpyBTLGw8bXQIGAVwC0LzgFddgDQzkvzdPDqXgZNFhyR0GEkF2zwcMXN1a4Nrm19kQVHNAjVaWG0SZEQwZxK4ZUjocKQ-wR3dGMIrqsmyQsKaLGXEIqBXiVO3l/s400/SrOUG.png" title="Serbian Oracle User Group - Zlatibor Conference." width="400" /></a></div>
<br />
<br />
<br />
<div style="-webkit-text-stroke-width: 0px; color: black; font-family: Times; font-size: medium; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
</div>
<br />
<div style="-webkit-text-stroke-width: 0px; color: black; font-family: Times; font-size: medium; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;">
<div style="margin: 0px; text-align: center;">
30 May: <a href="http://www.rooug.ro/?lang=en" target="_blank">meetup of RoOUG</a>: </div>
<div style="margin: 0px; text-align: center;">
<br /></div>
<div style="margin: 0px; text-align: center;">
<br /></div>
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.rooug.ro/" imageanchor="1" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img border="0" height="110" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4blMm6tbDi8l853VoSR3TLzdqTrWKZokXrJ5Wqcdcjivs-5TIQZBQSsWG3h1FJQ-2ryB24n1g5f-8O0X9dUMGuvjNlh1Q3M2DXToqsCL-lKWhyBDZFNoZlxAd1GOTKUVuSY6VE8bWxLG1/s400/ROOUG-logo.png" title="Romanian Oracle Usergroup - meetup in Bucharest" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div>
<div style="text-align: center;">
02-04 June: <a href="http://website.bgoug.online/index.html" target="_blank">BgOUG spring conference</a> :</div>
<div style="text-align: center;">
<br /></div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://website.bgoug.online/index.html" imageanchor="1" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img border="0" height="118" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJ065oDACtu9lt8e9QhdNJSklal0YFGZjdcujVfCtfT5Le8N00W79dF_6s0nLZ4q_kgToU7fgt8Bod0PU1bl6O_DXLknkTjtKwiZv55rWvBfrHraLkxWpxdeRklMGPL-JFe2G7cQgN9hmj/s400/bgoug_logo.png" title="Bulgarian Oracle User Group - Spring Conference in Pravets" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
<div>
<div style="text-align: center;">
15-16 June: the <a href="https://www.ogh.nl/page.aspx?event=368">OGH tech experience</a>:</div>
</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://www.ogh.nl/page.aspx?event=368" imageanchor="1" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiD48moQkjNUYd-mkE1UXgdkPrcIpsj2ufez1wzr6-tJzLpDKKrE_p73YrCfXQbv_UdLmIeXMS2h1HN1vhjm8Rwxxviu7H3Gr2fC9AS_YhWvfozoW98kw5fORgNhsRVnRrAOKYs9G0Dp53w/s400/ogh_logo_2014.gif" title="OGH Tech Experience - Utrecht" width="400" /></a></div>
<br />
<div>
<div style="text-align: center;">
20 June: <a href="http://www.aoug.at/" target="_blank">AOUG, Austrian Oracle Anwender Conferenz</a> :</div>
</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.aoug.at/" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Austrian Anwender Conference." border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXPx9dtg2rf-wN8KLbSuku3LGLyRnIOHsqlb-R_SjeJr8i-zrlfhYljfSZ_bS26-GPZERt5AQhhAohIA23hwQ3OV3NCDMxIud5Hdck2kzgs7C5t_9nXAfdDMdRAZDqHgNAXvdURcV1w3tn/s400/AK2017-nur-20.jpg" title="Austrian Usergroup - AnwenderKonferenz" width="400" /></a></div>
<br />
<br />
<br />
I cannot wait to get on the motorcycle again. On a map, it looks like this:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_RF6A27jNRz11Xi70gd0_jVIlY_qeaRUCp9qjzgw-RQzD-kjXDB0Xa2E2-EtimN4tXasGyxlhq0gn7DuMdVXK2eesDL-eGGrmh5nxTI3GGCuzk8NKWIAXz_u5T48L4OnpzdkVk3S7YMs/s1600/Screen+Shot+2017-03-24+at+17.30.32.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_RF6A27jNRz11Xi70gd0_jVIlY_qeaRUCp9qjzgw-RQzD-kjXDB0Xa2E2-EtimN4tXasGyxlhq0gn7DuMdVXK2eesDL-eGGrmh5nxTI3GGCuzk8NKWIAXz_u5T48L4OnpzdkVk3S7YMs/s400/Screen+Shot+2017-03-24+at+17.30.32.png" width="385" /></a></div>
<span style="font-size: x-small;">Hmm, if I stretch it, I could make an interactive Google-map with all the events + dates on it. But I'm too lazy; I'd rather go visit those places by motorcycle... </span><br />
<br />
The map link for the complete trip-plan is here, if you want to zoom in on the details: <a href="https://goo.gl/maps/1YvXPzCowMJ2">https://goo.gl/maps/1YvXPzCowMJ2</a> .<br />
<br />
And if you live along that route and feel like grabbing a cup of Coffee while I check the Wifi Signal: Contact me!<br />
<div>
<br /></div>
Regrettably, I will have to skip two events due to time constraints: OUGF and ITOUG<br />
I have politely cancelled going to Helsinki this year. I’m really sorry to miss the <a href="http://www.ougf.fi/index.php/en/uutisia-mainmenu-3/231-harmony17" target="_blank">OUGF.fi Harmony event</a> because it is always good quality content and nice social activities. I’ll try to be there next year folks. Kiitosh.<br />
<div>
<br /></div>
<div>
And, although I am very curious, I have to politely skip the (first?) Italian<a href="http://www.itoug.it/" target="_blank"> ITOUG tech day on 06 Giugno</a>. I love to go to “new” places and start-up-usergroups, but this one would mean a race from Bulgaria to Milano. I hope to visit an Italian event soon though (it will mean going over the Alps and drinking Good Coffee, always a Pleasure!)</div>
<div>
<br /></div>
<div>
And by the way, I've done trips like this for 3 years in a row now. I know how to do this by now, and it is Great Fun. Check last year's tour on the <a href="http://myadventure.bike/Trips/137" target="_blank">interactive map-log at MyAdventure.bike</a>...</div>
<br />
<b>See you on an OUG somewhere ! </b><br />
<br />
<br /></div>
</div>
PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com2tag:blogger.com,1999:blog-2170637149389736039.post-47606185378705773362016-10-10T12:25:00.006-07:002016-10-10T12:54:24.758-07:00OTN Appreciation Day : Function Result Cache<div dir="ltr" style="text-align: left;" trbidi="on">
Feature: Function Result Cache.<br />
<br />
Why : because this feature is Simple.<br />
It is_almost_ no hassle to activate, and it had a Good Effect where we needed it. <br />
Simple.<br />
<br />
Why not: If you need this feature badly, you probably got something wrong in the design of the application. But hey: our app was an off-the-shelf COTS package, and the users took advantage of the software in ways that had never been envisaged 10 years ago...<br />
<br />
Why not : because it didnt work in our case, despite all the RTFM we did. But one of my colleagues was persistent... and we found a workaround. Now it works like a charm.<br />
<br />
Thx Peter Swier for persisting.<br />
<br />
Thx Tim Hall for re-vitalizing my blog-postings.<br />
<br />
More on FRC: Later.<br />
Or come see me somewhere...<br />
At <a href="http://sioug.si/index.php/srecanje-sioug-2016/vsebine-na-sioug-2016/dba/197-function-madness" target="_blank">SIOUG</a> in Portoroz,<br />
at <a href="http://www.hroug.hr/Novosti/Agenda-HrOUG-2016-je-LIVE" target="_blank">HROUG</a> in Rovinj,<br />
at <a href="https://www.doag.org/konferenz/konferenzplaner/konferenzplaner_details.php?id=512089&locS=1&vid=525808" target="_blank">DOAG</a> in <span style="background-color: white; color: #252525; font-family: sans-serif; font-size: 14px;">Nürnberg</span>,<br />
or at <a href="http://www.tech16.ukoug.org/" target="_blank">UKOUG</a> in Birmingham(UK).<br />
<br />
Or... provide me with a desk to work + some coffee on one of my <a href="http://myadventure.bike/Trips/137" target="_blank">motorcycle tours</a> ...<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br /></div>
PdVhttp://www.blogger.com/profile/11518325134965208858noreply@blogger.com7