Daylight savings time change

This can impact your systems several different ways

Operating System

UNIX, Linux, and Mac OS use the zoneinfo utility which allows a single time zone to have multiple daylight saving time rules to handle changes from year to year. The zoneinfo database is a collaborative compilation of information about the world's time zones. New editions of the database are published as changes warrant, with the latest edition being the 2006g edition (2006-05-08) which lists 387 primary time zones and contains the new time changes.  This can be obtained from your O/S vendor.

Oracle Database

This issue will affect Oracle Server - Enterprise Edition – Versions 8.1 to 10.2. Depending on the version of your database, the impact will differ. 

In short, a database patch and a JVM may need to be applied. Once applied, a script provided by Oracle called utltzuv2.sql will need to be run. This will tell you if any code in the db needs to be modified.

Oracle DDL Triggers

Auditing database changes: Oracle provides DDL triggers to audit all schema changes and can report the exact change, when it was made, and by which user.  There are several ways to audit within Oracle and the following auditing tools are provided:

• SQL audit command (for DML)

• Auditing with object triggers (DML auditing)

• Auditing with system-level triggers (DML and DDL)

• Auditing with LogMiner (DML and DDL)

• Fine-grained auditing (select auditing)

DDL triggers: Using the Data Definition Language (DDL) triggers, the Oracle DBA can automatically track all changes to the database, including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA.

DDL triggers execute every time a DDL statement is executed, and adds new entries to your new table, as shown below:

connect sys/manager

create or replace trigger
DDLTrigger
AFTER DDL ON DATABASE
BEGIN
insert into
perfstat.stats$ddl_log
(
user_name,
ddl_date,
ddl_type,
object_type,
owner,
object_name
)
VALUES
(
ora_login_user,
sysdate,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_owner,
ora_dict_obj_name
);

END;/
What is truly exciting is that we can combine the system-level triggers with STATSPACK extension tables, thereby building a mechanism that will give complete auditing information on user logon and logoff, DDL, and server errors.

Just as an Oracle trigger fires on a specific DM event, system-level triggers are fired at specific system events such as logon, logoff, database startup, DDL execution, and servererror triggers

DDL triggers -- Using Oracle DDL triggers, the Oracle administrator can automatically track all changes to the database including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA.  Laurent Schneider also has this alternative DDL triggers script for DDL auditing:
CREATE TABLE AUDIT_DDL (
  d date,
  OSUSER varchar2(255),
  CURRENT_USER varchar2(255),
  HOST varchar2(255),
  TERMINAL varchar2(255),
  owner varchar2(30),
  type varchar2(30),
  name varchar2(30),
  sysevent varchar2(30));
  
create or replace trigger audit_ddl_trg after ddl on schema
begin
  if (ora_sysevent='TRUNCATE')
  then
    null; -- I do not care about truncate
  else
    insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)
    values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','CURRENT_USER') ,
      sys_context('USERENV','HOST') ,
      sys_context('USERENV','TERMINAL') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent
    );
  end if;
end;
/
Now whenever a change is made, the details will be written to your table, and you can create audit reports for management. This report is critical for the Oracle DBA who must track changes to his or her production database. This report uses the DDL trigger and produces a complete audit log of all Oracle databases changes, as shown below:
DDL table -- Using the code snippet shown below, we create an Oracle table to capture all of the salient metrics required to do effective change management within an Oracle environment. Note this table contains the date that the DDL was made, the ID of the user who originated the DDL change, the type of the object, and the object's name. This information can be quite useful for tracking purposes.

DDL Triggers - DDL triggers execute every time a DDL statement is executed and adds new entries to the stats$ddl_log table.

DDL Reports - This report is critical for the Oracle DBA who must track changes to their production database. This report uses Oracle DDL triggers and produces a complete audit log of all Oracle databases changes.

Oracle CREATE TABLESPACE Tips

You create tablespaces with the CREATE TABLESPACE command. Before you create the tablespace you should decide:

1. How big you wish the tablespace to be.
2. Where you want to put the datafile or datafiles that will be associated with that tablespace.
3. What you want to call the tablespace and the datafiles.

We recommend that you include the following in the datafile name:

1. The name of the database
2. The name of the tablespace
3. A number that makes the datafile unique

So, for a tablespace called USERS assigned to a database called BOOKTST we would have a datafile called booktst_users_01.dbf. Here is an example of creating such a tablespace using the create tablespace command:

create tablespace
   users
datafile  
  ‘/ora01/oracle/oradata/booktst_users_01.dbf’
size
   50m;

Note that we used 50m to indicate that we wanted to create the tablespace 50 Megabytes in size. You can also use the K symbol for Kilobytes, and the G symbol for Gigabytes. If you want to try to use the P symbol for a 50 Petabyte tablespace, we want to talk to you!

By default, an Oracle tablespace will not grow if it runs out of space. If you use up all your space, you are just out of luck unless you use the autoextend keyword to indicate that the tablespace may grow, on it’s own, dynamically. Here is an example of creating a tablespace that is set to autoextend:

create tablespace
   users
datafile
   ‘/ora01/oracle/oradata/booktst_users_01.dbf’
size
   50m
autoextend on
next 10m
maxsize 100m;

In this example, the tablespace will auto extend in increments of 10 megabytes until it reaches a maximum size of 100 megabytes. We recommend you use auto extend on all tablespaces for any production database

Why are PL/SQL ref cursors important to PL/SQL performance?

The ref cursor is a "pointer" data types that allows you to quickly reference any cursor result (usually an internal PL/SQL table array) with data values kept in super-fast RAM.

I'm a big fan of Dr. Hall, one of the world's top PL/SQL programming guru's, and I'm referencing his wonderful book "PL/SQL Tuning" where he has many ref cursor examples and ref cursor tips.

Definition of a ref cursor

The name "ref cursor" can be confusing because a ref cursor variable is not a cursor, but a variable that points to a cursor.  In essence, a ref cursor allows you to store the result of a "bulk collect" from a table (or any PL/SQL cursor return set) into a PL/SQL table (RAM array) and then reference the array with the ref cursor as a pointer.  Here is a simple example of using a ref cursor.

Once you load a PL/SQL table (an in-RAM array), a ref cursor allows you to reference the array within your PL/SQL code without having to pass the array to a new procedure or function.  Instead of passing the "baggage" of the actual RAM array, you simple pass the ref cursor, which points to the array values.

How do I relocate the datafile and tablespace to another file system?

When you run out of space in a filesystem you have two choices:

- Increase the size of the filesystem

- Move the datafile to another filesystem on a separate mount point

If you want to move the tablespace you need to:

1 - Quiecse the tablespace (alter tablespace dba_temp_files offline)

2 - Copy the data file to the new location

3 - Rename the tablespace datafile "alter tablespace dba_temp_files
rename datafile /oracle/temp2 to /oraclenew/temp2"

4 - Bring the tablespace online (alter tablespace dba_tamp_files online;

5 - Nuke the old file from the fileysystem (rm temp2)