A question on forums.oracle.com asked: can we recover a database further in time then the time of backup of the last controlfile. Nice question…my gut feeling immediately says : YES we can. But…I do not have a proof of it, and one quick search on the internet did not supply a adequate answer. So…then I […]
Continue ReadingDistribution of values in a column
Distribution of data For calculating the right execution-plan Oracle often needs the distribution of values for a column. For an outlier value a index can easily be used. For a value that is present in half the rows a index might be less effective. For a similar exercise I needed the distribution of values myself. […]
Continue ReadingCreating an ACL
A very simple example of creating an ACL for a principal and a host: Creating the ACLS (qith the associated principal) itself. exec dbms_network_acl_admin.create_acl( acl=>’print_permissions.xml’, description=>’Gebruikt voor Print functionaliteit’, principal=>’APEX_230100′, is_grant=>TRUE, privilege=>’connect’); Associating a host to the ACL: exec dbms_network_acl_admin.assign_acl( acl=>’print_permissions.xml’, host=>’machine-name.domain.domain’, lower_port=>8010, upper_port=>8010); This is a starting point. You can go from here … […]
Continue ReadingOEM : No Data
This is reaaly memory for myself 🙂 We often encounter sql performance screens where OEM says “Unable to Retrieve Data” What we do : create a job (in OEM) that deploys Database Management PL/SQL packages In OEM main Menu Click Enterprise –> Job –> Library –> Select Job Type “Deploy Database Management PL/SQL Packages” –> […]
Continue ReadingOracle system statistics
Oracle System Statistics When I look at the system statistics I’m not really getting any wiser. There a re really a lot of statistics: SQL> select count(*) from v$sysstat; COUNT(*) ———- 2252 SQL> Above from a Oracle 21c instance. I do not want to see them all. I would like to see the change in […]
Continue ReadingORAENV_ASK
Just a reminder I keep forgetting what the environment variable is called: ORA_ASKENV ORA_ENVASK ORAASK_ENV ORAENV_ASK This is _the_ variable: ORAENV_ASK=NO ORAENV_ASK=YES Happy scripting
Continue ReadingBasic Rman Configuration
A basic rman configuration (backups to disk): RMAN> show all; RMAN configuration parameters for database with db_unique_name TINUS are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS; CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u04/backups/tinus/%F’; CONFIGURE DEVICE TYPE […]
Continue Reading$SQLPATH vs $ORACLE_PATH
Every time I install a new oracle machine it hits me: Why is login.sql not executed when I start a sqlplus session. So here is a quick reminder for myself: sqlplus is not looking in $SQLPATH for login.sql ….. it is looking in $ORACLE_PATH So set $ORACLE_PATH and you will be fine. This […]
Continue ReadingSession statistics
You can have a look at v$sesstat (joined with v$staname) to see the value of some statistics at this moment. What is a little more difficult to see is how statistics change over time. So I wrote a script for it. What it does: Gather all statistics, of one or all sessions, and put them […]
Continue ReadingIndex Full Scan
Index index I would like to see all values of N, but I would like to see them in order. So, Oracle can not use the Index Fast Full Scan, since that operator doesn’t read the index in order. We need the Index Full Scan. The whole index must be read, in the right order, […]
Continue Reading