Accessing partition table

Accessing partition table-
Select * from aa partion(pa1);
Drop partion
-Alter table AA
drop partion pa1;

SQL Language Extension-
Oracle * provide new built-in datatype, object datatypes, nested tables, and a no of other features that require new DDL extension.
VARRAY
REF
LOBS
Create table AA(a N (10)
B date,
C varchar2 (10));

Create type aa1 as varray (5) of number (5);

The UTLBSTAT and UTLESTAT script to get general overview of database 's performance over a certain period of time.
UTLBSTAT creates table and views containing cumulative database performance summary information at the time when the script runs .All the objects create by UTLBSTAT contain word login.
Utlbstat.sql

UTLESTAT creates table and views containing cumulative database performance summary information at the time when the script runs .All the objects create by UTLESTAT contain word end.
UTLESTAT spools the results of these SQL statements to a file called REPORT.TXT
Utlestat.sql

Determine the shared Pool Performance.

The shared pool is one of the memory structures in SGA .It is comprised of the data dictionary and the library cache. Check v$sgastat
The data dictionary cache buffers data dictionary objects that contain data about tables, indexes, users and all other objects.
The Library Cache/SQL Cache buffers previously executed queries, so that they need not be reloaded and reparsed if user calls them again.

Otherwise if the information is not in the buffer then oracle must get it from disk.

The V$LIBRAY CACHE View stores performance data for library cache and V$ROWCACHE view stores performance data for the data dictionary cache.

Sometime we may have to increase the value of initialization parameter SHARED_POOL_SIZE.
To improve the performance .

Redo Log --

Oracle 8 stores all changes to the database, even uncommitted changes, in the redo log files.
LGWR writes .

Alter database archievelog

Edit the parameter initialization file.
Log_archieve_start =true -turn it on
Log_archieve_dest=c:/oracle/ora81/archieve -location
log_archieve_format="ARCH%S.LOG" - name format for archieve file .
%S for log sequence number .

By querying the V$SESSION view , we can determine who is logged on ,as well as information such as the time of logon .

Kill a session - ALTER system kill session '&sid,&serial'
Select Sid,serial#,status from V$session where username='name';

Unbalanced Index ?
if we do have lot on index on a table and we are doing I/U/D frequently then there is a problem of disk contention . To check this problem sees the BLEVEL value in DBA_INDEXES and if it is 1,2,3,4 then it?s ok else rebuild the index .

Alter index satish.a_satish rebuild unrecoverable ;

Comments on table and columns