Askdbaonline

Downloaded advanced compression advisor from

http://www.oracle.com/technology/products/database/compression/compression-advisor.html

1. Setup

Create User
create user comp_test identified by comp_test;
grant connect,resource to comp_test;
grant create public synonym to comp_test;
grant create any table to comp_test;

Create Table
conn comp_test/comp_test
CREATE TABLE market
(marketman_id NUMBER(5),
marketman_name VARCHAR2(30),
market_state VARCHAR2(20),
market_amount NUMBER(10),
market_date DATE);

3. Use Advanced Compression Advisor to estimate compression ratio
SQL> @dbmscomp.sql
SQL> @prvtcomp.plb

SQL> set serveroutput on
SQL> exec DBMS_COMP_ADVISOR.getratio(‘COMP_TEST’,'MARKET’,'DIRECT_LOAD’,10);
Sampling table: COMP_TEST.MARKET
Sampling percentage: 10%
Compression Type: DIRECT_LOAD
Compression ratio: 3.01

PL/SQL procedure successfully completed.

4. Compress the table and compare actual compression ratio to estimation

Table Size before compression
select sum(bytes)/1024/1024 MB from dba_segments where segment_name=’MARKET’;

MB
—————–
136.3125

Compress the table
alter table market move compress;

Table Size after compression
select sum(bytes)/1024/1024 MB from dba_segments where segment_name=’MARKET’;

MB
——————–
45.3125

Actual Compression Ratio
Compression Ratio
= Size Before Compression(136.3125)/Size After Compression(45.3125)
= 3.008

1. Redirect output from Java Stored Procedure to SQL*Plus

set serveroutput on
call dbms_java.set_output(1000);

2. Create a Java Stored Procedure to retrieve the java.version property

create or replace and compile java source named “jdkversion” as
public class jdkversion
{
public static void printVersion()
{
String ver;
ver = System.getProperty(“java.version”);
System.out.println(“JDK version ” + ver);
}
}
/

3. Create a PL/SQL call spec so the JSP will be callable from SQL*Plus

create or replace procedure JDK_Version is language java
name ‘jdkversion.printVersion()’;
/

4. Call the procedure to display the JDK version and display it

execute JDK_Version;

On 10gR2 recyclebin is a initialization parameter and bydefault its ON.

We can disable recyclebin by using the following commands:

SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF;

There are various ways to PURGE objects:

PURGE TABLE emp;
PURGE INDEX emp_indx;
PURGE recyclebin; (Purge all objects in Recyclebin)
PURGE dba_recyclebin; (Purge all objects / only SYSDBA can)
PURGE TABLESPACE users; (Purge all objects of the tablespace)
PURGE TABLESPACE users USER bh; (Purge all objects of the tablspace belonging to BH)