Partition Table

Create partitioned table.

CREATE TABLE big_table2 (
id            NUMBER(10),
created_date  DATE,
lookup_id     NUMBER(10),
data          VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2007 VALUES LESS THAN (MAXVALUE));

– Add new keys, FKs and triggers.
ALTER TABLE big_table2 ADD (
CONSTRAINT big_table_pk2 PRIMARY KEY (id)
);

CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;

CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;

ALTER TABLE big_table2 ADD (
CONSTRAINT bita_look_fk2
FOREIGN KEY (lookup_id)
REFERENCES lookup(id)
);With this destination table in place we can start the conversion.

EXCHANGE PARTITION
We now switch the segments associated with the source table and the partition in the destination table using the EXCHANGE PARTITION syntax.

ALTER TABLE big_table2
EXCHANGE PARTITION big_table_2007
WITH TABLE big_table
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;The exchange operation should not be affected by the size of the segments involved.

Once this is complete we can drop the old table and rename the new table and all it’s constraints.

DROP TABLE big_table;
RENAME big_table2 TO big_table;

ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;
ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;
ALTER INDEX big_table_pk2 RENAME TO big_table_pk;
ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;
ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;SPLIT PARTITION
Next, we split the single large partition into smaller partitions as required.

ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE(‘31-DEC-2005 23:59:59′, ‘DD-MON-YYYY HH24:MI:SS’))
INTO (PARTITION big_table_2005,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;

ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE(‘31-DEC-2006 23:59:59′, ‘DD-MON-YYYY HH24:MI:SS’))
INTO (PARTITION big_table_2006,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;

EXEC DBMS_STATS.gather_table_stats(USER, ‘BIG_TABLE’, cascade => TRUE);The following queries show that the partitioning was successful.

SELECT partitioned
FROM   user_tables
WHERE  table_name = ‘BIG_TABLE’;

PAR
—
YES

1 row selected.

SELECT partition_name, num_rows
FROM   user_tab_partitions
WHERE  table_name = ‘BIG_TABLE’;

PARTITION_NAME                   NUM_ROWS
—————————— ———-
BIG_TABLE_2005                     335326
BIG_TABLE_2006                     332730
BIG_TABLE_2007                     334340

3 rows selected.

Package Debuging

Debugging BP – Debugging Using Pre-Processor Directives

In Oracle 10g Release 2, Oracle introduced a new feature called conditional compilation. Using conditional compilation makes debugging statements a breeze. For instance, here is an example of the debugging statements placed in the source code:

CREATE OR REPLACE PACKAGE mypkg
IS
   PROCEDURE wrapper;

   gv_debug   CONSTANT BOOLEAN := FALSE;
END;
/

CREATE OR REPLACE PACKAGE BODY mypkg
AS
   v_empid   NUMBER (3) := 100;

   PROCEDURE myproc
   IS
      v_empid   NUMBER (3);
   BEGIN
      v_empid := 10;

      IF (gv_debug)
      THEN
         DBMS_OUTPUT.put_line ('Inside myproc(). v_empid = ' || v_empid);
      END IF;
   END;

   PROCEDURE wrapper
   IS
   BEGIN
      myproc;

      IF (gv_debug)
      THEN
         DBMS_OUTPUT.put_line ('Outside myproc(). v_empid = ' || v_empid);
      END IF;
   END;
END;

When the code is run in production, the debugging statements do not get executed because the condition “if (gv_debug)” evaluates to false. When needed, you can turn on the debugging by changing the value of gv_debug to TRUE. This works, but the variable is always evaluated at the runtime, affecting performance. A better approach would be to use conditional compilation, as shown below:

CREATE OR REPLACE PACKAGE mypkg
IS
   PROCEDURE wrapper;
END;
/

CREATE OR REPLACE PACKAGE BODY mypkg
AS
   g_empid   NUMBER (3) := 100;

   PROCEDURE myproc
   IS
      l_empid   NUMBER (3);
   BEGIN
      l_empid := 10;
      $if $$debug $then
      DBMS_OUTPUT.put_line ('Inside myproc(). l_empid = ' || l_empid);
      $end
   END;

   PROCEDURE wrapper
   IS
   BEGIN
      myproc;
      $if $$debug $then
      DBMS_OUTPUT.put_line ('Outside myproc(). g_empid = ' || g_empid);
      $end
   END;
END;

After the package is created, you can alter the debug level by simply issuing:

alter package mypkg compile plsql_ccflags = 'debug:true'

This turns on the debugging statements. So, why it is better that the variables approach? In the variable approach, here is a portion of the code where the condition is evaluated:

  BEGIN
      v_empid := 10;

      IF (gv_debug)
      THEN
         DBMS_OUTPUT.put_line ('Inside myproc(). v_empid = ' || v_empid);
      END IF;
   END;

In the post-processing example, if the value of the variable debug is false, the condition “$if $$debug” evaluates to false and hence the actual code that gets executed in the database is:

  BEGIN
      v_empid := 10;

   END;

Package Best Practice

Package Best Practice – Hide Package DataBy placing data inside the package body, it is protected from direct access by any programs outside of the package.

Recommendation:

Never put your variables and other data structures in the package specification.

• Always put them in the body.
• Then build programs to change values in the data structures and retrieve the current values.
• Make these “get and set” programs available in the package specification.

The benefits include:

• Tighter control over data structures.
• Flexibility to change implementation of data structure.

Hiding Package Data Examples

Package specification with an “exposed” variable:

PACKAGE P_and_L
IS
   last_stmt_dt DATE;
END P_and_L;

Package body which hides the variable and also asserts a rule about last statement date:

PACKAGE BODY P_and_L
IS
   last_stmt_dt DATE;               i NOW IN BODY

   FUNCTION last_date RETURN DATE IS
   BEGIN
      RETURN last_stmt_dt;
   END;

   PROCEDURE set_last_date (date_in IN DATE)
   IS
   BEGIN
      last_stmt_dt := 	          Business Rule!
         LEAST (date_in, SYSDATE);
   END;
END P_and_L;

This following version fully exposes the variable and allows for violation of the business rule: The last statement sets the date into the future.

IF P_and_L.last_stmt_dt <
   ADD_MONTHS (SYSDATE, -3)
THEN
   P_and_L.last_stmt_dt := SYSDATE + 12;
END IF;

This next version of same code hides the variable completely and protects against violations of the rules.

IF P_and_L.last_date <
   ADD_MONTHS (SYSDATE, -3)
THEN
   P_and_L.set_last_date (SYSDATE + 12);
END IF;

It’s recommended that you set standards for elements and their names in your “get and set” programs. For example, if the data structure is “maximum length,” then create the following elements:

• A public procedure named “set_max_length”
• A public function named “max_length”
• A private variable named “v_max_length”.

This way, you can use the most natural names (max_length) for the public elements. If the variable has a fixed number of distinct values, such as a Boolean, then you might take a different approach and create multiple procedures and functions to “set and get.”

PACKAGE PLVtrc
IS
   PROCEDURE turn_on;
   PROCEDURE turn_off;

   FUNCTION tracing RETURN BOOLEAN;
   FUNCTION not_tracing RETURN BOOLEAN;
END PLVtrc;

Table Joins Versus EXISTS

SQL Tuning – Table Joins versus EXISTS

Consider Table Joins in Place of EXISTS

In general, consider joining tables rather than specifying subqueries when the percentage of successful rows returned from the driving table (i.e., the number of rows that need to be validated against the subquery) is high. For example, if we are selecting records from the EMP table and are required to filter those records that have a department category of “A”, then a table join will be more efficient.

Consider the following example:

SELECT emp_name
FROM   emp E
WHERE  EXISTS ( SELECT 'X'
                   FROM   dept
                   WHERE  dept_no  = E.dept_no
                   AND    dept_cat = 'A');
   Execution Plan
   ---------------------------------------------------
   SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
      FILTER
        TABLE ACCESS (FULL) OF 'EMP'
        TABLE ACCESS (BY ROWID) OF 'DEPT'
          INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)

To improve performance, specify:

SELECT emp_name
FROM   dept D,
       emp  E
WHERE  E.dept_no  = D.dept_no
AND    D.dept_cat = 'A';

Execution Plan
---------------------------------------------------
SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
  NESTED LOOPS
     TABLE ACCESS (FULL) OF 'EMP'
     TABLE ACCESS (BY ROWID) OF 'DEPT'
       INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)

Consider EXISTS in Place of Table Joins

Consider breaking some table joins out to separate subqueries when the percentage of successful rows returned from the driving table (i.e., the number of rows that need to be validated against the subquery) is small. When two tables are joined, all rows need to be matched from the driving table to the second table. If a large number of rows can be filtered from the driving table before having to perform the validation against the second table, the number of total physical reads can be dramatically reduced.

Consider the following example:

SELECT . . .                         SELECT . . .
FROM   dept D,                       FROM   dept D,
       emp  E                               emp E
WHERE  E.dept_no  = D.dept_no        WHERE  E.dept_no = D.dept_no
AND    E.emp_type = 'MANAGER'        AND    ( E.emp_type = 'MANAGER'
AND    D.dept_cat = 'A';             OR     D.dept_cat = 'A'     );

To improve performance, specify:

SELECT . . .
FROM   emp E
WHERE  EXISTS     ( SELECT 'X'
                       FROM   dept
                       WHERE  dept_no  = E.dept_no
                       AND    dept_cat = 'A' )
AND    E.emp_type = `MANAGER'

SELECT . . .
FROM   emp E
WHERE  E.emp_type = 'MANAGER'
OR     EXISTS     ( SELECT 'X'
                       FROM   dept
                       WHERE  dept_no  = E.dept_no
                       AND    dept_cat = 'A' )

Virtual Private Database

Virtual Private Databases

Virtual Private Database (VPD) is the aggregation of server-enforced fine-grained access control and secure application context in the Oracle database. VPD enables you to build applications that enforce your security policies at the row level. When a user directly or indirectly accesses a table, a view, or a synonym associated with a VPD security policy, the server dynamically modifies the user’s SQL statement. The modification is based on a WHERE clause returned by a function which implements the security policy. The database modifies the statement dynamically (transparently to the user) using any condition that can be expressed in, or returned by, a function.

The DBMS_RLS.ADD_POLICY has the new argument LONG_PREDICATE. Its default value is FALSE so that the policy function may return up to 4000 bytes of predicate length. Setting this value to TRUE allows the function to return up to 32 KB of predicate text string.

There is a new security policy on INDEX maintenance operations performed with the create index and alter index statements. This is important because users need full access to tables to create indexes. Consequently, a user who has privileges to maintain an index can see all the row data although the user does not have full table access under a regular query.

The execution of policy functions can consume a significant amount of system resources. If you can minimize the number of times that policy functions must execute, then you can optimize your database performance.

In previous releases, policies were dynamic by default. This means that the Oracle database executed the policy function for each DML statement. In addition to dynamic policies, Oracle Database 10g provides static and context-sensitive policies. These policy types provide a way to improve server performance because they do not always reexecute policy functions for each DML statement, and they can be shared across multiple database objects.

Oracle 10g includes improvements to Oracle’s Virtual Private Database (VPD), including the following features:

• Column-Level Privacy
• VPD Policies
• Support for Parallel Query

Column-Level Privacy

The benefit of VPD is that it provides for row-level security in your Oracle database. Oracle 10g offers a feature that allows you to indicate that a VPD policy should only be enforced if specific columns are accessed or referenced. One or more columns can be defined within a policy. If you don’t specify any columns, VPD will operate just as in Oracle9i.

As a result, you can now provide varying levels of security for database tables. For example, you may not need to secure queries against certain columns, such as the name of an employee, but you may require some level of access control for queries against the social security number, because of privacy issues. In this case, you would create a VPD policy that references the column containing the social security number. The policy would be effective for any query that includes the SSN column. This allows you to define privacy policies for certain types of data, such as personal data, while making other data available.

This feature is supported with the addition of the sec_relevant_cols parameter in the dbms_rls.add_policy PL/SQL package supplied by Oracle. The following example usea dbms_rls.add_policy to create a policy on a table called RET_SCHEMA.RETIREE:

BEGIN
Dbms_rls.add_policy(object_schema=>'ret_schema',
Object_name=>'retiree',
Policy_name=>'retiree_policy',
Function_schema=>'retiree',
Policy_function=>'f_retiree_01',
Statement_types=>'select',
Sec_relevant_cols=>'ssn, sal');
END;
/

The steps to implement a VPD policy using this new feature are basically the same as in Oracle9i, with the exception of the sec_relevant_cols parameter in dbms_rls.add_policy (which is optional). If you do not include the sec_relevant_cols parameter, then the policy will apply to all columns, just as it did prior to Oracle 10g.

In the following example, we are applying the policy implemented by the RUN function to the EMP table. You also set the policy to only apply the VPD predicate for SELECT statements. The three security-relevant columns in the EMP table are SAL, HIREDATE and COMM.

Connect as scott user on sqlplus.

create or replace function scott.run
(object_schema in varchar2, object_name in varchar2)
return varchar2 is d_predicate varchar2(2000);
BEGIN
d_predicate :=  '(ename = sys_context (''USERENV'', ''SESSION_USER''))';
RETURN d_predicate;
END run;
/

After creating the function to implement policy, add the policy in database. Te user should have enough permission to execute DBMS_RLS package.

BEGIN
dbms_rls.add_policy(object_schema => 'scott',
object_name                       => 'emp',
policy_name                       => 'scott_policy',
function_schema                   => 'scott',
policy_function                   => 'run',
statement_types                   => 'select',
sec_relevant_cols                 => 'sal,hiredate,comm');
END;

With reference to the above example, Oracle database will not enforce the VPD policy when you select only the LAST_NAME column from the EMP table because LAST_NAME column is not a security relevant column and Oracle will not apply security policy on this column. So employees cannot access sensitive information in the EMP Table. However, when you issue queries that access columns considered as security-relevant (sal, hiredate and comm), then VPD applies the fine-grained access control defined by the policy function.

Here is an example of the way that the column level VPD policy created earlier would be applied. In this first example, we are querying the EMP table. We reference the FIRSTNAME column which is not a secured column. As a result, the VPD policy we created will not apply to this query and all rows in the table will be available to the query:

SELECT COUNT(*) FROM EMP WHERE FIRSTNAME  LIKE  ‘%’;

In contrast, the following query uses a secured column (SAL) in the WHERE clause. As a result, the column level VPD policy will be applied to the statement and only the rows/columns that the user has access to will be included in the result set:

SELECT COUNT(*) FROM EMP WHERE SAL > 0 OR SAL < 0 or SAL=0;

In processing the second statement, Oracle will affix an additional predicate that will restrict the result set such that it conforms to the VPD policy that is defined on the EMP table (which in our case means that the Oracle user will only be able to see their own individual record, and no one else’s).

VPD Policies

The only type of database policy offered in earlier versions of Oracle was a dynamic one. Oracle 10g offers five VPD policy types to choose from, which are listed and described below.

Policy Type	Description
Static	With a static policy, VPD will always use the same predicate for access control. The static policy only applies to a single object.
Shared_static	A shared_static policy is a static policy that is shared by multiple database objects.
Context_sensitive	This is a nonstatic (or dynamic) policy that executes each time the session context changes, such as when the username changes and you want your policy to be different for each user.
Shared_context_sensitive	This policy is dynamic just like a context_sensitive policy, but it can be shared across multiple objects.
Dynamic	This policy is the default type of policy. The policy function is executed each time the command accesses the object and the columns relevant to the access policy.

Static policy predicates execute quickly because they are stored in the SGA. Static policies also have the same predicate that is applied to all SQL statements accessing the objects assigned to the policy. Dynamic policies are re-created via the policy function each time the defined columns are accessed, and thus dynamic policy execution can be slower than static policy execution.

Note that some policy types can be shared between different objects. This allows a single policy to scale better, and keeps business rules more consistent. To define the policy type, use the policy_type argument to the dbms_rls.add_policy procedure for the correct policy type, as shown in this example:

BEGIN
Dbms_rls.add_policy(object_schema=>'ret_schema',
Object_name=>'retiree',
Policy_name=>'retiree_policy',
Function_schema=>'retiree',
Policy_function=>'f_retiree_01',
Statement_types=>'select',
Sec_relevant_cols=>'ssn',
Policy_type=>DBMS_RLE.STATIC);
END;
/

DBA Monthly Checklist Procedure

This list of procedures should be performed on a monthly basis to check the status of your Oracle databases:

1. Look for Harmful Growth Rates

Review changes in segment growth when compared to previous reports to identify segments with a harmful growth rate.

2. Review Tuning Opportunities

Review common Oracle tuning points such as cache hit ratio, latch contention, and other points dealing with memory management. Compare with past reports to identify harmful trends or determine impact of recent tuning adjustments.

3. Look for I/O Contention

Review database file activity. Compare to past output to identify trends that could lead to possible contention.

4. Review Fragmentation

Investigate fragmentation (e.g. row chaining, etc.).

5. Project Performance into the Future

1. Compare reports on CPU, memory, network, and disk utilization from both Oracle and the operating system to identify trends that could lead to contention for any one of these resources in the near future.
2. Compare performance trends to Service Level Agreement to see when the system will go out of bounds

6. Perform Tuning and Maintenance

Make the adjustment necessary to avoid the contention for system resources. This may include scheduled down time or request for additional resources

Overview Backup Full DB using exp

Complete Database Recovery – Full Export & Incrementals

Recovery using full, cumulative and incremental exports follows these general steps:

1. Rebuild the database (empty datafiles except for SYSTEM.)
    
2. Import the latest full,cumulative or incremental export file using the INCTYPE=SYSTEM option of the IMPORT command.
    
3. Import the last full export using the INCTYPE=RESTORE option for the IMPORT command
    
4. Import the available cumulative exports taken since the last full export, in order, using the INCTYPE=RESTORE option for the IMPORT command.
    
5. Import the available incremental exports taken since the last cumulative export, in order, using the INCTYPE=RESTORE option for the IMPORT command.
    
6. Shutdown and take a full backup of the database system.

The combination of full, incremental and cumulative backups allow for full recovery to the date and time of the last full, incremental or cumulative export.

Incremental and cumulative exports will generally take less room than full exports and can be taken with the database active. This means that cumulative or incremental exports can be run more often, thus reducing data loss.

Like full exports, incremental and cumulative exports can only guarantee table level consistancy.

 

Full exports make a complete logical copy of the structure and data in a database. The command to perform a full export is:

$ exp user/password full=y grants=y constraints=y compress=y file=filename

If the export command is issued without specifying any arguments, you will be prompted for them.

We suggest using the above command line options so that the database is restored to as nearly the same condition (other than the extents will be compressed) as it was when the export was taken.

If you allow the arguments to default you will not get back all grants, and constraints.

You should perform full exports at least weekly if possible, unless you are using incremental and cumulative exports. An example schedule would be:

Daily :	Incremental export
Weekly:	Cumulative export, after which all prior incrementals can be deleted
Monthly:	FULL or COMPLETE export, after which previous incremental and cumulative exports can be deleted.

Backup using Tar Command

Following the example backup using tar command :

BACKUP_DATE=`date | sed -e “s/:/_/g” | sed -e “s/ /_/g”`

tar -cvf /logs/backup_WIRELESS2/backupWIRELESS2_$BACKUP_DATE.tar /apps/firsthop/ /logs/log

compress /logs/backup_WIRELESS2/*.tar

Find the Bigest Number

if [ $# -ne 3 ]
    then
 echo “$0: number1 number2 number3 are not given” >&2
        exit 1   
    fi
    n1=$1
    n2=$2
    n3=$3
    if [ $n1 -gt $n2 ] && [ $n1 -gt $n3 ]
    then
 echo “$n1 is Bigest number”
    elif [ $n2 -gt $n1 ] && [ $n2 -gt $n3 ]        
    then
 echo “$n2 is Bigest number”
    elif [ $n3 -gt $n1 ] && [ $n3 -gt $n2 ]        
    then
        echo “$n3 is Bigest number”
    elif [ $1 -eq $2 ] && [ $1 -eq $3 ] && [ $2 -eq $3 ]
    then
 echo “All the three numbers are equal”   
    else
        echo “I can not figure out which number is biger”   
    fi

Display memory allocation in RAC

Folowing scripts is used for geting the memory allocation :

SET LINESIZE 200

COLUMN username FORMAT A20
COLUMN module FORMAT A20

SELECT a.inst_id,
NVL(a.username,’(oracle)’) AS username,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM   gv$session a,
gv$sesstat b,
gv$statname c
WHERE  a.sid = b.sid
AND    a.inst_id = b.inst_id
AND    b.statistic# = c.statistic#
AND    b.inst_id = c.inst_id
AND    c.name = ’session pga memory’
AND    a.program IS NOT NULL
ORDER BY b.value DESC;