Maclean’s Oracle Database Tech Blog Archives

Script: — NAME: RACDIAG.SQL — SYS OR INTERNAL USER, CATPARR.SQL ALREADY RUN, PARALLEL QUERY OPTION ON — ———————————————————————— — AUTHOR: — Michael Polaski – Oracle Support Services — Copyright 2002, Oracle Corporation — ———————————————————————— — PURPOSE: — This script is intended to provide a user friendly guide to troubleshoot — RAC hung sessions or slow…

Oracle9i provides extended date and time support across different time zones with the help of new datetime data types and functions. To understand the working of these data types and functions, it is necessary to be familiar with the concept of time zones. This topic group introduces you to the concepts of time such as…

This practice will demonstrate the  concept of extent, wrap and shrink in rollback segment utilization. You will: Use the create rollback segment and alter rollback segment syntax. Examine the V$ROLLSTAT view. Determine what would be required to force an extent, a wrap and a shrink. ASSUMPTIONS The directory and filenames referenced in the commands in…

Good network security is accomplished by utilizing port and protocol screening with routers, firewalls, and Intrusion Detection Systems.Port and protocol screening with routers, firewalls, and Intrusion Detection Systems create a bastion against network attacks. A device that routes and translates information between interconnected networks is called a firewall. Firewalls have a different function Routers, not…

This practice uses common UNIX and NT Oracle utilities to practice enabling AUDIT on a database.  You will: See the procedure to enable and disable Oracle AUDIT on a database. Understand the SQL commands used to audit a specific user schema object. Investigate how to configure Audit to extend auditing into modified or new schema…

This practice uses common UNIX and Windows NT Oracle utilities to review database user objects’ key roles and  permissions.  You will: 1.        See default user objects and their default passwords. 2.        Understand V$PWFILE_USER data dictionary view, and how to use this view to inspect SYSDBA or SYSOPER privileges assigned to user objects. 3.        Examine two…

This practice uses common UNIX and Windows NT utilities to visualize key TCP/IP protocols.  You will: See IP information using the IPCONFIG utility. Understand and overcome IP fault situations using the PING utility. Examine the FTP (File Transfer Protocol) application, and overcome fault situations commonly seen when using FTP. Create a TELNET session with a…

This script calculates the average row size for all tables in a schema. It generates a script (getAvgCol.sql) from USER_TABLES and then runs it. The following type of SELECT is generated for each table in USER_TABLES: SELECT round(avg(nvl(vsize(COL1),0)) + round(avg(nvl(vsize(COL2),0)) + … + round(avg(nvl(vsize(COLn),0)) Where n=# of cols. on the table Tables with LONG and…

Oracle8 has introduced a change with the data block cache header format. The basic idea is that incarnation and sequence numbers stored in the cache header have been replaced with an SCN number and sequence number. The size of the cache header has remained 20 bytes. The size of the block trailer is still 4…

Script: select ‘session_cached_cursors’ parameter, lpad(value, 5) value, decode(value, 0, ‘ n/a’, to_char(100 * used / value, ‘990’) || ‘%’) usage from ( select max(s.value) used from v$statname n, v$sesstat s where n.name = ‘session cursor cache count’ and s.statistic# = n.statistic# ), ( select value from v$parameter where name = ‘session_cached_cursors’ ) union all select…