Creating 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 Reading

OEM : 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 Reading

Oracle 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 Reading

ORAENV_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 Reading

Basic 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 Reading

Session 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 Reading

Index 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

Index Fast Full Scan

Index index Today I want to query our table again. We alter the table so column N is not null. alter table index_demo modify n not null Table altered. desc index_demo Name Null? Type —————— ——– ————— N NOT NULL NUMBER D NUMBER M NUMBER S VARCHAR2(4000) What I would like to see is all […]

Continue Reading

INDEX FULL SCAN (MIN/MAX)

Index index Another way an index can be used to access a piece of data: Index Full Scan (Min/Max) If I’m only interested in the minimal or the maximum value of a column, and there is an index on that column, Oracle can use the Index Full Scan (Min/Max). This access-method starts at (ofcourse) the […]

Continue Reading