1


The size of a ROWID, stored in either external binary format (datatype 11)
or a CHAR buffer, has not changed in Oracle8, therefore, the size and
datatypes of host variables used to hold Oracle8 ROWIDs has not changed.
Applications that employ ROWIDs to fetch rows from the database will be
compatible with Oracle8 as long as the ROWIDs are obtained from the
server and are otherwise unmodified.

However, in order to address a larger number of rows commensurate with
larger databases, the encoding scheme for ROWIDs has changed. When
fetching Oracle7 ROWIDs into CHAR datatype buffers it was possible to
interpret the string of digits as 'BBBBBBBB.SSSS.FFFF' (Block.Slot.File).
This interpretation no longer works under Oracle8. Oracle8 ROWIDs must
be treated as opaque data items whose constituent elements cannot be
accessed directly. Any applications that attempt to interpret or construct
ROWID contents will fail.






	Total Files Listed:
	 7751 File(s)	 276,907,239 bytes
		775,323,648 bytes free






C:\WINNT\PROFILES\ABBEYM\DESKTOP>orapwd80
Usage: orapwd file=<<fname> password=<<password> entries=<<users>
	where
	file - name of password file (mand),
	password - password for SYS and INTERNAL (mand),
	entries - maximum number of distinct DBA and OPERs (opt),
	There are no spaces around the equal-to (=) character.
C:\WINNT\PROFILES\ABBEYM\DESKTOP> orapwd80 file=\orant\database\passwd.ora password=oracle entries=12






search error.log/win=10 error






grep -i ORA- error.log






/sys (500MB)
/data (1g)
/indexes (500MB)






t:\prod\appl_catscan\catp1.dbf
t:\prod\appl_nurse\nursep1.dbf
t:\prod\/appl_doctor\doctorp1.dbf
t:\training\appl_catscan\catt1.dbf
t:\training\appl_nurse\nurset1.dbf
t:\test\appl_doctor\doctort1.dbf






t:\oradbf\catprod1.dbf
t:\oradbf\nurseprod1.dbf
t:\oradbf\doctorprod1.dbf
t:\oradbf\cattrn1.dbf
t:\oradbf\nursetrn1.dbf
t:\oradbf\doctortst1.dbf






create database prod
<193>
datafile 't:\oradbf\systemprod1.dbf' size 50MB
<193>
maxdatafiles 255;






SVRMGR> create database PROD
	2> logfile group 1
	3>	('p:\oradbf\redo1a.dbf', 'q:\oradbf\redo1b.dbf') size 10MB,
	4> logfile group 2
	5>	('q:\oradbf\redo2a.dbf', 'r:\oradbf\redo2b.dbf') size 10MB,
	6> logfile group 3
	7>	('r:\oradbf\redo3a.dbf', 'p:\oradbf\redo3b.dbf') size 10MB
	8> datafile 't:\oradbf\systemprod1.dbf' size 50MB
	9> maxdatafiles 255;
Statement processed.
SVRMGR>






create tablespace rollback_segs
datafile 's:\oradbf\rollprod1.dbf' size 100MB
default storage (initial 500K next 500K pctincrease 0);






create tablespace users
datafile 'f:\oradbf\userprod1.dbf' size 50MB
default storage (initial 10K next 10K pctincrease 0);






/* this is the create tablespace script for the production database */
spool script_name.lis
set echo on
create tablespace temp datafile 'd:\oradbf\temp01.dbf' size 50m
default storage (initial 100k next 100k pctincrease 2);
/*	*/
create tablespace user_data datafile 'e:\oradbf\user01.dbf' size 100m
default storage (initial 10k next 10k pctincrease 0);
spool off






System parameters with non-default values:
processes= 50
shared_pool_size= 3000000
control_files= T:\oradbf\ctl1tst.ora, T:\oradbf\ctl2tst.ora
db_block_buffers= 200
db_block_size= 2048
compatible= 8.0.3.0.0
log_buffer= 8192
log_checkpoint_interval= 8000
	log_checkpoint_timeout	= 0
	db_files	= 1020
	db_file_multiblock_read_count= 8
	dml_locks	= 100
	sequence_cache_entries	= 10
	sequence_cache_hash_buckets= 10
	remote_login_passwordfile= SHARED
	sort_area_size	= 65536
	db_name	= tst
	background_dump_dest	= C:\ORANT\rdbms80\trace
	user_dump_dest	= C:\ORANT\rdbms80\trace
	max_dump_file_size	= 10240






C:\WINNT\PROFILES\ABBEYM\DESKTOP> svrmgr30
Oracle Server Manager Release 3.0.3.0.0 - Production
(c) Copyright 1997, Oracle Corporation.	All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
SVRMGR>






C:\WINNT\PROFILES\ABBEYM\DESKTOP> imp80
Import: Release 8.0.3.0.0 - Production on Thu Aug 14 8:18:1 1999
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
Username: system
Password:
Connected to: Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
C:\WINNT\PROFILES\ABBEYM\DESKTOP>
C:\WINNT\PROFILES\ABBEYM\DESKTOP> exp80
Export: Release 8.0.3.0.0 - Production on Thu Aug 14 8:19:8 1999
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
Username: system
Password:
Connected to: Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production






SQL*Plus: Release 8.0.3.0.0 - Production on Thu Aug 14 8:21:1 1999
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production






SQL*Plus: Release 8.0.3.0.0 - Production on Thu Aug 14 8:21:1 1999
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options






@?/rdbms80/admin/catproc






SQL> begin
	2	declare x number;
	3	begin
	4	x := 9;
	5	end;
	6	end;
	7	/
ERROR:
ORA-06553: PLS-213: package STANDARD not accessible
begin
*
ERROR at line 1:
ORA-06553: PLS-213: package STANDARD not accessible






/* ------------------------------------------------- */
/* This program assumes you are logged in as the	*/
/* owner of the tables with triggers associated with */
/* them. This is important, since DBA privileged	*/
/* users may experience problems compiling other	*/
/* user's code.	*/
/* ------------------------------------------------- */
set pages 0 feed off echo off trimsp on
spool trgcomp.sql
prompt spool trgcomp
prompt set echo on feed on
select 'alter trigger '||trigger_name||' compile;'
	from user_triggers;
prompt spool off
spool off






alter TRIGGER GEO_MAINT compile;
alter TRIGGER ED_LOCK compile;
alter TRIGGER EVENT_CREATE compile;
alter TRIGGER MAKE_SEGMENTS compile;






/* --------------------------------------------- */
/* Using the USER_SOURCE view, create a script	*/
/* to recompile all procedures, functions, and	*/
/* packages. Notice we exclude the TYPE of	*/
/* PACKAGE BODY since compiling a PACKAGE also	*/
/* compiles its BODY at the same time.	*/
/* --------------------------------------------- */
set pages 0 feed off echo off
spool ppfcomp.sql
prompt spool ppfcomp
prompt set echo on feed on
select distinct 'alter '||type||' '||name||' compile;'
	from user_source
 where type <<> 'PACKAGE BODY';
prompt spool off
spool off






alter PACKAGE DBMS_REFRESH compile;
alter PACKAGE DBMS_UTILITY compile;
alter PACKAGE DIANA compile;
alter PACKAGE DIUTIL compile;
alter PACKAGE PIDL compile;
alter PACKAGE STANDARD compile;






SQL> create public database link to_prod
	2	connect to dblink identified by dblink
	3	using 'prod';
Database link created.
SQL>






/* ------------------------------------------------- */
/* This program assumes you have been granted access */
/* to the DBA_SYNONYMS view owned by Oracle user SYS */
/* and that you have write access to the current	*/
/* directory.	*/
/* ------------------------------------------------- */
set echo off pages 0 feed off
spool remotes.sql
select 'select * from dual@'||db_link|| 
	from sys.dba_db_links
 where owner = 'PUBLIC';
spool off
set echo on 
spool remotes




2



Thread 1 cannot allocate new log, sequence 35
Checkpoint not complete
	Current log# 1 seq# 35 mem# 0: D:\ORANT\DATABASE\LOG2ORCL.ORA
Thread 1 advanced to log sequence 36
	Current log# 2 seq# 36 mem# 0: D:\ORANT\DATABASE\LOG1ORCL.ORA




Thread 1 cannot allocate new log, sequence 35
Checkpoint not complete
	Current log# 1 seq# 35 mem# 0: D:\ORANT\DATABASE\LOG2ORCL.ORA
Thread 1 advanced to log sequence 36
	Current log# 2 seq# 36 mem# 0: D:\ORANT\DATABASE\LOG1ORCL.ORA




Wed Mar 26 15:19:19 1999
Thread 1 cannot allocate new log, sequence 683
Checkpoint not complete




Starting up ORACLE RDBMS Version: 8.0.3.0.0.
System parameters with non-default values:
	processes	= 59
	timed_statistics	= TRUE
	shared_pool_size	= 10000000
	control_files	= D:\ORANT\DATABASE\ctl1orcl.ora
	db_block_size	= 2048
	log_buffer	= 8192
	log_checkpoint_interval	= 10000
	db_files	= 1024
	rollback_segments	= MONSTER, RB1, RB2, RB3, RB4, RB5, RB6, RB7
	sequence_cache_hash_buckets= 10
	remote_login_passwordfile= SHARED
	global_names	= TRUE
	distributed_lock_timeout = 300
	distributed_transactions = 5
	db_name	= oracle
	text_enable	= TRUE
	job_queue_processes	= 2
	job_queue_interval	= 10
	background_dump_dest	= %RDBMS80%\trace
	user_dump_dest	= %RDBMS80%\trace
	max_dump_file_size	= 10240



Oracle8 Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
SQLWKS> show sga
Total System Global Area	11726376 bytes
Fixed Size	46136 bytes
Variable Size	11053552 bytes
Database Buffers	102400 bytes
Redo Buffers	524288 bytes



SQL> select sum(pins) "Pins", sum(reloads) "Reloads",
	2	sum(reloads)/(sum(pins)+sum(reloads))*100
	3	"Percentage"
	4	from v$librarycache;
	Pins	Reloads Percentage
--------- --------- ----------
	77926	107	.13712147
SQL>



SQL> col "Percentage miss" format 990.00
SQL> col "Gets" format 999,999,990
SQL> col "Misses" format 999,999,990
SQL> select unique parameter "Cache entry",
	2	gets "Gets",	/*Read from memory*/
	3	getmisses "Misses",	/*read from disk*/
	4	getmisses/(gets+getmisses)*100 "Percentage miss"
	5	from v$rowcache
	6	where gets+getmisses <<> 0;
Cache entry	Gets	Misses Percentage miss
-------------------------------- ------------ ------------ ---------------
dc_constraints	516	259	33.42
dc_files	11	6	35.29
dc_free_extents	1,220	270	18.12
dc_object_ids	1,401	296	17.44
dc_objects	6,574	605	8.43
dc_profiles	20	1	4.76
dc_rollback_segments	2,144	19	0.88
dc_segments	2,126	224	9.53
dc_sequences	10	4	28.57
dc_synonyms	43	9	17.31
dc_tablespace_quotas	1,166	2	0.17
dc_tablespaces	1,740	5	0.29
dc_used_extents	368	246	40.07
dc_user_grants	2,079	20	0.95
dc_usernames	1,957	11	0.56
dc_users	3,273	23	0.70
SQL>



SQL> select (1 - (sum(getmisses)/
	2	(sum(gets)+sum(getmisses))))*100 "Hit rate"
	3	from v$rowcache;
 Hit rate
---------
92.496717
SQL>



SQL> select substr(name,1,20) "Latch",
	2	sum(gets) "WTW Gets",
	3	sum(misses) "WTW Misses",
	4	sum(immediate_gets)	"IMM Gets",
	5	sum(immediate_misses)	"IMM Misses"
	6	from v$latch where name like 'redo%'
	7	group by name;
Latch	WTW Gets WTW Misses	IMM Gets IMM Misses
-------------------- --------- ---------- --------- ----------
redo allocation	72248	15	0	0
redo copy	0	0	0	0
SQL>



LSNRCTL80 for 32-bit Windows: Version 8.0.3.0.0 - Production on 05-AUG-99
19:28:33
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=oracle.world))
STATUS of the LISTENER
------------------------
Alias	LISTENER
Version	TNSLSNR80 for 32-bit Windows: Version 8.0.3.0.0 -
Production
Start Date	04-AUG-99 11:57:36
Uptime	1 days 7 hr. 30 min. 57 sec
Trace Level	off
Security	ON
SNMP	OFF
Listener Parameter File	D:\ORANT\NET80\admin\listener.ora
Listener Log File	D:\ORANT\NET80\log\listener.log
Services Summary...
	ORCL	has 1 service handler(s)
	extproc	has 1 service handler(s)
The command completed successfully



SQL> select name,value
	2	from v$sysstat
	3	where name like '%sort%';
NAME	VALUE
---------------------------------------------------------------- ---------
sorts (memory)	4077
sorts (disk)	2
sorts (rows)	43789
SQL>



SQL> alter system enable restricted session;
System altered.
SQL>



SQL> alter system disable restricted session;
System altered.
SQL>



ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege



SQL> -- Give restricted access to FINANCE
SQL> grant restricted access to finance;
Grant succeeded.
SQL> -- Take it away
SQL> revoke restricted access from finance;
Revoke succeeded.
SQL>



SQL> create profile finusers limit
	cpu_per_session	20
	sessions_per_user	1
	idle_time	30;
Profile created.
SQL>



SQL> create profile humanres limit
	cpu_per_session	20
	idle_time	8;
Profile created.
SQL>



3

-- Create a role that has the right to look at the V$ and DBA views
create role vddba;
set echo off pagesize 0 feedback off linesize 200 trimspool on
spool vddba.sql
select 'grant select on '||view_name||' to vddba;' -- *****
	from user_views
 where view_name like 'V%'
	or view_name like 'DBA%';
spool off
set echo on feed on
spool vddba
start vddba
spool off





select 'grant select on '||view_name||' to public;'





select fname, lname
	from people
 where ssn = '123456789';



select blah_blah
		from index_only_table
	where primary_key_col_1 = 212299      -- equality
		and primary_key_col_2 = 99;
select blah_blah
		from index_only_table
	where primary_key_col_1 between 212299 and 231222      -- range search
		and primary_key_col_2 = 99;



SQL*Plus: Release 8.0.3.0.0 - Production on Wed Jul 30 8:19:23 1999
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
SQL> create table toc_jind
	2	(toc_id number,
	3	toc_locator varchar2(2),
	4	toc_component varchar2(10),
	5	toc_desc varchar2(600),
	6	constraint toc_jind_pk primary key (toc_id))
	7	organization index tablespace fm_iot
	8	pctthreshold 20
	9	overflow tablespace ov_fm_iot;
Table created.
SQL>





ORA-25182: feature not currently available for index-organized tables





select fname, lname, address
	from person, address
 where ssn = '123-45-0269'
	and person.ssn = address.ssn
	and address_type = 'HOME'





SQL> create tablespace nt_prd_data datafile
	2	'c:\orant\database\ntpd_1.dbf' size 200m,
	3	'd:\orant\database\ntpd_2.dbf' size 200m,
	4	'e:\orant\database\ntpd_3.dbf' size 200m,
	5	'h:\orant\database\ntpd_4.dbf' size 200m;
	6	default storage (initial 20m next 20m pctincrease 0);
Tablespace created.
SQL>





create table address (
	address_id	number,
	street_id	number,
	address_type_id	number,
<193>
<193>
	rural_ind	varchar2(1))
storage (initial 199m next 199m minextents 4 pctincrease 0)
tablespace nt_prd_data;





SQL> select extent_id,file_id
	2	from dba_extents
	3	where segment_name = 'ADDRESS';
 EXTENT_ID	FILE_ID
---------- ----------
	0	7
	1	5
	2	6
	3	8
4 rows selected.





create table address (
	address_id	number,
	street_id	number,
	address_type_id	number,
<193>
<193>
	rural_ind	varchar2(1))
storage (initial 199m next 199m pctincrease 0)
tablespace nt_prd_data;





SQL> select file_name,file_id 
	2	from dba_data_files
	3	order by 2;
FILE_NAME	FILE_ID
---------------------------------------- ----------
C:\ORANT\DATABASE\SYS1ORCL.ORA	1
C:\ORANT\DATABASE\USR1ORCL.ORA	2
D:\ORANT\DATABASE\RBS1ORCL.ORA	3
H:\ORANT\DATABASE\TMP1ORCL.ORA	4
C:\ORANT\DATABASE\NTPD_1.DBF	5
D:\ORANT\DATABASE\NTPD_2.DBF	6
E:\ORANT\DATABASE\NTPD_3.DBF	7
H:\ORANT\DATABASE\NTPD_4.DBF	8
8 rows selected.
SQL> select segment_name,file_id
	2	from dba_extents 
	3	where segment_name = 'ADDRESS';
SEGMENT_NAME	FILE_ID
---------------------------------------- ----------
ADDRESS	7





SQL> alter table address allocate extent
	2	(size 199m datafile 'C:\ORANT\DATABASE\NTPD_1.DBF');
Table altered.
SQL> alter table address allocate extent
	2	(size 199m datafile 'D:\ORANT\DATABASE\NTPD_2.DBF');
Table altered.
SQL> alter table address allocate extent
	2	(size 199m datafile 'H:\ORANT\DATABASE\NTPD_4.DBF');
Table altered.
SQL>





SQL> select extent_id,file_id
	2	from dba_extents
	3	where segment_name = 'ADDRESS';
 EXTENT_ID	FILE_ID
---------- ----------
	0	7
	1	5
	2	6
	3	8
4 rows selected.
SQL>





if a row's column values are updated
	if the amount of data in the row increases
	if there is enough space to accommodate the new amount in current block
	update the row in place
	else
	if there is another block allocated to the table with room
	migrate the row to that block
	else
	break the row into pieces creating a chained row
	end if
	end if
	end if
end if





analyze table address list chained rows;





create table chained_rows (
	owner_name	varchar2(30),
	table_name	varchar2(30),
	cluster_name	varchar2(30),
	partition_name	varchar2(30),
	head_rowid	rowid,
	analyze_timestamp	date);





TABLE_NAME	HEAD_ROWID
----------------------------- ---------------------
ADDRESS	AAAACsAABAAAATmAAD
ADDRESS	AAAACsAABAAAATmAAE





SQL> -- Make a copy of the chained rows in a holding table
SQL> create table add_chained as
	2	select *
	3	from address
	4	where rowid in
	5	(select head_rowid
	6	from chained_rows
	7	where table_name = 'ADDRESS');
Table created.
SQL> -- Delete chained rows from ADDRESS
SQL> delete address
	2	where rowid in
	3	(select head_rowid
	4	from chained_rows
	5	where table_name = 'ADDRESS');
2 rows deleted.
SQL> -- Move rows back in to ADDRESS
SQL> insert into address
	2	select * from add_chained;
2 rows created.
SQL> -- Drop the intermediate table
SQL> drop table add_chained;
Table dropped.
SQL>





rollback_segments = (rbs01, rbs02, rbs03, rbs04, rbs05, rbs06, rbs07, rbs08)





rollback_segments = (rbs01, rbs05, rbs02, rbs06...)





-- We recommend ordering as we do below to be able to inspect
-- the output looking for what are adjacent chunks of free space.
select file_id, block_id, blocks, bytes
	from dba_free_space
 where tablespace_name = 'SYSTEM'
 order by 1,2;





if BLOCK_ID + BLOCKS (from one row) = BLOCK_ID (from next row)
	if FILE_ID (from one row) = FILE_ID (from next row)
	the space is contiguous and can be coalesced
	end if
end if





SQL> alter tablespace system coalesce;
Tablespace altered.
SQL> select file_id, block_id, blocks, bytes
	2	from dba_free_space
	3	where tablespace_name = 'SYSTEM'
	4	order by 1,2;
FILE_ID	BLOCK_ID	BLOCKS	BYTES
------- ----------- ------------ ------------
	1	1801	250	512000
	1	5107	525	1075200
	2	5632	2655	5437440





SQL> create tablespace temp_tst datafile
	2	'f:\orant\database\temp1.dbf' size 100m,
	3	'f:\orant\database\temp1.dbf' size 100m,
	4	'f:\orant\database\temp1.dbf' size 100m
	5	temporary;
Tablespace created.
SQL>





ORA-02195: Attempt to create PERMANENT object in a TEMPORARY tablespace





SQL> desc v_$logfile
 Name	Null?	Type
 ------------------------------- -------- ----
 GROUP#	NUMBER
 STATUS	VARCHAR2(7)
 MEMBER	VARCHAR2(513)
SQL> desc v_$log_history
 Name	Null?	Type
 ------------------------------- -------- ----
 RECID	NUMBER
 STAMP	NUMBER
 THREAD#	NUMBER
 SEQUENCE#	NUMBER
 FIRST_CHANGE#	NUMBER
 FIRST_TIME	DATE
 NEXT_CHANGE#	NUMBER
SQL> desc v_$archived_log
 Name	Null?	Type
 ------------------------------- -------- ----
 RECID	NUMBER
 STAMP	NUMBER
 NAME	VARCHAR2(513)
 THREAD#	NUMBER
 SEQUENCE#	NUMBER
 RESETLOGS_CHANGE#	NUMBER
 RESETLOGS_TIME	DATE
 FIRST_CHANGE#	NUMBER
 FIRST_TIME	DATE
 NEXT_CHANGE#	NUMBER
 NEXT_TIME	DATE
 BLOCKS	NUMBER
 BLOCK_SIZE	NUMBER
 ARCHIVED	VARCHAR2(3)
 DELETED	VARCHAR2(3)
 COMPLETION_TIME	DATE





SQL> desc v_$filestat
 Name	Null?	Type
 ------------------------------- -------- ----
 FILE#	NUMBER
 PHYRDS	NUMBER
 PHYWRTS	NUMBER
 PHYBLKRD	NUMBER
 PHYBLKWRT	NUMBER
 READTIM	NUMBER
 WRITETIM	NUMBER





SQL> select name, phyrds, phywrts
	2	from v$datafile a, v$filestat b	-- Join will get the name
	-- of each datafile
	3	where a.file# = b.file#;
NAME	PHYRDS	PHYWRTS
----------------------------------- ---------- ----------
C:\ORANT\DATABASE\SYS1ORCL.ORA	11706	573	1
C:\ORANT\DATABASE\USR1ORCL.ORA	9021	8109	2
H:\ORANT\DATABASE\RBS1ORCL.ORA	667	1199	3
G:\ORANT\DATABASE\TMP1ORCL.ORA	349	1288	4
I:\ORANT\DATABASE\FY9697.DBF	43889	0	5
O:\ORANT\DATABASE\FY9798.DBF	29888	21112	6





TS	FILE_NAME	PHYS_READS PHYS_WRITES
-------- ---------------------------- ----------- -----------
AP	/tunafsh9/prdxy/ap01.dbf	34673	523
APX	/tunafsh10/prdxy/apx01.dbf	6808	501
AR	/tunafsh9/prdxy/ar01.dbf	3	0
CONV	/tunafsh2/prdxy/conv01.dbf	1236	369
FA	/tunafsh11/prdxy/fa01.dbf	18916	14
FAX	/tunafsh12/prdxy/fax01.dbf	24804	33
FND	/tunafsh13/prdxy/fnd01.dbf	16815	731
FNDX	/tunafsh14/prdxy/fndx01.dbf	5640	1921
GL	/tunafsh7/prdxy/gl01.dbf	66061	239
GL	/tunafsh7/prdxy/gl02.dbf	3743	45
GLX	/tunafsh8/prdxy/glx01.dbf	16873	713
HR	/tunafsh13/prdxy/hr01.dbf	13895	99
HR	/tunafsh13/prdxy/hr02.dbf	1824	23
HRX	/tunafsh14/prdxy/hrx01.dbf	1839	188
HRX	/tunafsh14/prdxy/hrx02.dbf	258	21
PO	/tunafsh9/prdxy/po01.dbf	12089	583
POX	/tunafsh10/prdxy/pox01.dbf	3081	1081
RBS	/tunafsh5/prdxy/rbs01.dbf	177	2822
RBS1	/tunafsh6/prdxy/rbs101.dbf	34	616
SYSTEM	/tunafsh1/prdxy/system01.dbf	8502	1024
SYSTEM	/tunafsh1/prdxy/system02.dbf	6948	340
TEMP	/tunafsh11/prdxy/temp03.dbf	17	1064
TEMP	/tunafsh12/prdxy/temp02.dbf	20	3081
TEMP	/tunafsh14/prdxy/temp01.dbf	10	228





SQL> desc dba_audit_object
 Name	Null?	Type
 ------------------------------- -------- ----
 OS_USERNAME	VARCHAR2(255)
 USERNAME	VARCHAR2(30)
 USERHOST	VARCHAR2(128)
 TERMINAL	VARCHAR2(255)
 TIMESTAMP	NOT NULL DATE
 OWNER	VARCHAR2(30)
 OBJ_NAME	VARCHAR2(128)
 ACTION_NAME	VARCHAR2(27)
 NEW_OWNER	VARCHAR2(30)
 NEW_NAME	VARCHAR2(128)
 SES_ACTIONS	VARCHAR2(19)
 COMMENT_TEXT	VARCHAR2(4000)
 SESSIONID	NOT NULL NUMBER
 ENTRYID	NOT NULL NUMBER
 STATEMENTID	NOT NULL NUMBER
 RETURNCODE	NOT NULL NUMBER
 PRIV_USED	VARCHAR2(40)
 OBJECT_LABEL	RAW MLSLABEL
 SESSION_LABEL	RAW MLSLABEL





SQL> set pages 0 ver off feed off echo off term off
SQL> spool sch_audit.sql
SQL> select 'audit select on blairr.'||table_name||' by access;'
	2	from sys.dba_tables
	3	where owner = 'BLAIRR';
SQL> spool off
SQL> start sch_audit





SQL> create table audit_summary (
	2	owner	varchar2(30),
	3	obj_name	varchar2(30),
	4	cal_day	date
	5	c_access	number);
Table created.
SQL>





SQL> -- Only copy rows from yesterday <193> any earlier data will have been
SQL> -- deleted yesterday by SQL statement after the insert.
SQL> insert into audit_summary
	2	select owner, obj_name, trunc(timestamp), count(*)
	3	from sys.dba_audit_object
	4	where trunc(timestamp) << trunc(sysdate-1)
	5	group by owner, obj_name, trunc(timestamp);
1210 rows created.
SQL> -- Delete yesterday's data.
SQL> delete sys.aud$
	2	where trunc(timestamp) << trunc(sysdate);





SQL> select owner, obj_name, cal_day, c_access
	2	from audit_summary
	3	order by cal_day;
OWNER	OBJ_NAME	CAL_DAY	C_ACCESS
---------------------- ---------------- ----------- ---------
BLAIRR	ADDRESS	12-DEC-1999	212
BLAIRR	ADDRESS	13-DEC-1999	309
BLAIRR	ADDRESS	14-DEC-1999	322
BLAIRR	ADDRESS	15-DEC-1999	221
<193>
<193>
BLAIRR	LOCATION	12-DEC-1999	3232
BLAIRR	LOCATION	13-DEC-1999	3209
BLAIRR	LOCATION	14-DEC-1999	3210
BLAIRR	LOCATION	15-DEC-1999	4221





SQL> select owner, obj_name, count(*)
	2	from audit_summary
	3	group by owner, obj_name
	4	order by 3 desc;
OWNER	OBJ_NAME	COUNT(*)
---------------------- ------------------------ --------
BLAIRR	LOCATION	388990
BLAIRR	PERSON	230990
<193>
<193>
BLAIRR	ADDRESS	22981





SQL> set pages 0 ver off feed off echo off term off
SQL> spool sch_noaudit.sql
SQL> select 'noaudit select on blairr.'||table_name||';'
	2	from sys.dba_tables
	3	where owner = 'BLAIRR';
SQL> spool off
SQL> start sch_noaudit





SQL> select type, description, type_size
	2	from v$type_size
	3	where type in ('UB1', 'UB2', 'UB4', 'SB2','KCBH',
	4	'KTBIT','KTBBH','KDBH','KDBT');
 TYPE	DESCRIPTION	TYPE_SIZE
 -------- -------------------------------- ----------
 UB1	UNSIGNED BYTE 1	1
 UB2	UNSIGNED BYTE 2	2
 UB4	UNSIGNED BYTE 4	4
 SB2	SIGNED BYTE 2	2
 KCBH	BLOCK COMMON HEADER	20
 KTBIT	TRANSACTION VARIABLE HEADER	24
 KTBBH	TRANSACTION FIXED HEADER	48
 KDBH	DATA HEADER	14
 KDBT	TABLE DIRECTORY ENTRY	4
9 rows selected.
SQL> select value
	2	from v$parameter
	3	where name = 'db_block_size';
VALUE
-------------------------------------------------------
2048
SQL>





Name	Null?	Type
------------------------------- -------- ----
LOC_ID	NUMBER(6)
LOCATION_NAME	VARCHAR2(30)
PROVINCE_ID	VARCHAR2(2)
LOC_TYPE	VARCHAR2(2)


db_block_size - kcbh - ub4 - ktbbh - (initrans-1) * ktbit - kdbh
2048 - 20 - 4 - 48 - (2-1) * 24 - 14
2048 - 20 - 4 - 48 - 24 - 14 = 1938 bytes


ceil (SAD * (1 - pctfree/100)) - kdbt
ceil (1938 * (1-20/100)) - 4 [we use a pctfree of 20 for this sample]
ceil (1938 * .8) - 4 = 1551 - 4 = 1547 bytes


colum size including byte length =
		colum size + (1, if colum size < 250, else 3)
-- For all the colums in LOC (assuming the average LOCATION_NAME is
-- 11 characters)
LOC_ID		6 + 1 = 7
LOCATION_NAME		30 + 1 = 31
PROVICE_ID		2 + 1 = 3
LOC_TYPE		2 + 1 = 3	For a total of 44 bytes (TCS)


3 * ub1 + TCS = 3 * 1 + 44 = 47 bytes


max (ub1 * 3 + ub4 + sb2, ROWSZ) + sb2
max (1 * 3 + 4 + 2 , 47) + 2 = 49
max (9, 47) + 2 = 49 bytes


floor (ADS / SPROW) = floor (1547/49) = 31 rows per data block




SQL> -- Ceil (390000 [rows] / 31 [rows per data block]) = 12581 blocks
SQL> -- 12581 blocks @ 2048 bytes per block = 12581 * 2048 = 25765888 bytes
SQL> -- We allow 20% extra space for record creation bringing the initial
SQL> -- allocation up to 30 megabytes.
SQL> create table location (
	2	loc_id	number(6),
	3	location_name	varchar2(30),
	4	province_id	varchar2(2),
	5	loc_type	varchar2(2))
	6	storage (initial 30m next 4m pctincrease 0);
Table created.
SQL>





SQL> select segment_name, segment_type, tablespace_name, extent_id,
	2	bytes
	3	from user_extents
	4	where segment_name = 'ADDRESS';
SEGMENT_NAME <_><_><_><P3M><P255D>SEGMENT_TYPE	<P3M> <P255D>TABLESPACE_NAME	EXTENT_ID		BYTES
--------------	-----------	------------------------------
ADDRESS	TABLE	ADDRESS_DATA	0	31457280
ADDRESS	TABLE	ADDRESS_DATA	1	31457280
ADDRESS	TABLE	ADDRESS_DATA	2	31477380
ADDRESS	TABLE	ADDRESS_DATA	3	31457280
4 records selected.
SQL>





OWNER	NOT NULL VARCHAR2(30)
TABLE_NAME	NOT NULL VARCHAR2(30)
...
NUM_ROWS	NUMBER
BLOCKS	NUMBER
EMPTY_BLOCKS	NUMBER
...
AVG_ROW_LEN	NUMBER
...





SQL> analyze table add_owner.address estimate statistics sample 20 percent;
Table analyzed.
SQL> select empty_blocks
	2	from sys.dba_tables
	3	where owner = 'ADD_OWNER'
	4	and table_name = 'ADDRESS';
	EMPTY_BLOCKS
-----------------
	75000
SQL>





more_rows = empty_blocks * db_block_size * (100 - pctfree) / 100 / avg_row_len
<P6M><P255D>= 75000 * 2048 * 80 / 100 / 41 =	2997073





space_in_use (SIU) = num_rows * avg_row_len
	=	11988293 * 41 = 491520013 bytes
space_allocated (SA) = blocks * db_block_size
	= 300000 * 2048 =	614400000 bytes
more_rows = (SA - SIU) * (100 - pctfree) / 100 / avg_row_len
	= (614400000 - 491520013) * 80 / 100 / 41 =	2397658





INDEX_STATS
-----------
HEIGHT	height of the b-tree
BLOCKS	blocks allocated to the segment
NAME	name of the index
PARTITION_NAME	name of the index partition, if partitioned
LF_ROWS	number of leaf rows (values in the index)
LF_BLKS	number of leaf blocks in the b-tree
LF_ROWS_LEN	sum of the lengths of all the leaf rows
LF_BLK_LEN	useable space in a leaf block
BR_ROWS	number of branch rows
BR_BLKS	number of branch blocks in the b-tree
BR_ROWS_LEN	sum of the lengths of all the branch blocks in the
	b-tree
BR_BLK_LEN	useable space in a branch block
DEL_LF_ROWS	number of deleted leaf rows in the index
DEL_LF_ROWS_LEN	total length of all deleted rows in the index
DISTINCT_KEYS	number of distinct keys in the index
MOST_REPEATED_KEY	how many times the most repeated key is repeated
BTREE_SPACE	total space currently allocated in the b-tree
USED_SPACE	total space that is currently being used in the
	b-tree
PCT_USED	percent of space allocated in the b-tree that is
	being used
ROWS_PER_KEY	average number of rows per distinct key
BLKS_GETS_PER_ACCESS <P12M><P255D>Expected number of consistent mode block gets per
	row. This assumes that a row chosen at random from
	the table is being searched for using the index
INDEX_HISTOGRAM
---------------
REPEAT_COUNT	<P8M> <P255D>number of times that a key is repeated
KEYS_WITH_REPEAT_COUNT number of keys that are repeated REPEAT_COUNT





SQL> select pct_used
	2	from index_stats;
	PCT_USED
------------
	92
SQL>





alter index sample_index_name rebuild;





rem * Index extract statement program
rem * MASI Inc.	August 1999
rem 
rem	By specifying rows=n, export extracts no data, just the SQL statements
rem	required to recreate all aspects of the table's characteristics.
exp80 userid=add_owner/abc123 tables=address rows=n file=addr_nr
rem	With the export complete, we now read the dump file and put the index
rem	creation statements in an SQL file to be run in SQL*Plus.
imp80 userid=add_owner/abc123 file=addr_nr fully indexfile=addr_ind.sql





...
...
REM	MAXTRANS 255 LOGGING STORAGE(INITIAL 317440 NEXT 102400 MINEXTENTS 1
REM	MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
REM	BUFFER_POOL DEFAULT) TABLESPACE "ADDRESS_DATA" ;
CONNECT ADDRESS;
CREATE INDEX "ADD_OWNER"."ADDRESS_1" ON "ADDRESS" ("LOC_ID" ) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE (INITIAL 71680 NEXT 36864 MINEXTENTS 1 MAXEXTENTS
121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ADDRESS_INDEX" LOGGING ;





SQL> create table test_dlf (
	2	test_dlf_id	number(5),
	3	tdlf_desce	varchar2(20),
	4	tdlf_descf	varchar2(20));
Table created.
SQL> alter table test_dlf add constraint test_dlf_pk primary
	2	key (test_dlf_id) using index;
Table altered.





SQL> analyze index test_dlf_pk validate structure;
Index analyzed.
SQL> select del_lf_rows from index_stats;
DEL_LF_ROWS
-----------
	0





SQL> analyze index test_dlf_pk validate structure;
Index analyzed.
SQL> select del_lf_rows from index_stats;
DEL_LF_ROWS
-----------
	981





SQL> alter index test_dlf_pk rebuild;
Index altered.





alter table used_to_be_humungous deallocate unused;





alter table used_to_be_humungous deallocate unused keep XXXX;





SQL> alter table address pctfree 50;
Table altered.





alter table address pctfree 20;





if amount of data in block falls below pctused<F102>
	place data block on free list
else
	data block is full and can receive no more rows
end if





pctused = floor ((available_space - length_1_row) / data_block_size * 100)
	= floor((1501 - 41 ) / 2048 * 100)
	= floor(1460 / 2048 * 100) = 71 percent





SQL> select count(*)
	2	from user_extents
	3	where segment_name = 'OBJ$';
	COUNT(*)
----------
	14
SQL> select sum(bytes)
	2	from user_extents
	3	where segment_name = 'OBJ$';
SUM(BYTES)
----------
	7839744
SQL> select count(*)
	2	from obj$;
	COUNT(*)
----------
	63275
SQL>



4


create table doc_master (
	fy_code	varchar2(5),
	doc_num	number(7),
	doc_type	varchar2(2),
	budget_amt	number(10,2),
	precomm_amt	number(10,2),
	comm_amt	number(10,2),
	exp_amt	number(10,2))
tablespace doc_master;



create table doc_master (
	fy_code	varchar2(5),
	doc_num	number(7),
	doc_type	varchar2(2),
	budget_amt	number(10,2),
	precomm_amt	number(10,2),
	comm_amt	number(10,2),
	exp_amt	number(10,2))
partition by range (fy_code)
	(partition fy_9697 values less than ('9798')
	tablespace ts_9697,
	partition fy_9798 values less than ('9899')
	tablespace ts_9798);



p160_01
create table document_9697 (
	fy_code	varchar2(5),
	doc_num	number(7),
	doc_type	varchar2(2),
	budget_amt	number(10,2),
	precomm_amt	number(10,2),
	comm_amt	number(10,2),
	exp_amt	number(10,2));
create table document_9798 (
	fy_code	varchar2(5),
	doc_num	number(7),
	doc_type	varchar2(2),
	budget_amt	number(10,2),
	precomm_amt	number(10,2),
	comm_amt	number(10,2),
	exp_amt	number(10,2));




p160_02
insert into document_9697
	select * from document_master
		where fy_code = '9697';
insert into document_9798
	select * from document_master
		where fy_code = '9798';



p161_01
create or replace view doc_master as
select * from document_9697
umion all
select * from document_9798
union all
select * from ... ... ...;



p161_02
SQL> -- A quety where partition elimination could occur on the
SQL> -- FINANCE_HIST partition view partitioned by FY
SQL> select account, sum(amount), sum(item), ...
	2		from finance_hist
	3	where fy = '98/99'
	4	group by account;
ACCOUNT	AMOUNT	ITEM_CT
-------------------  -------------  ------------
ATYH232	19090.67	8879
...
...
1290 rows selected.
SQL>




SQL> create table sales (
	2	sales_id	number,
	3	cust_id	number(6),
	4	other_col1	varchar2(20),
	5	other_col2	varchar2(20),
	6	other_col3	varchar2(20),
	7	other_col4	varchar2(20),
	8	other_col5	varchar2(20),
	9	od_flag	varchar2(1))
	 10	storage (initial 240m next 240m pctincrease 0)
	 11	partition by range (cust_id)
	 12	(partition part1 values less than (299999) tablespace sales_p1,
	 13	partition part2 values less than (699999) tablespace sales_p2,
	 14	partition part3 values less than (899999) tablespace sales_p3,
	 15	partition part4 values less than (999999) tablespace sales_p4);
Table created.
SQL> select *
	2	from user_extents
	3	where segment_name = 'SALES';
SEGMENT_NA PARTITION_ SEGMENT_TYPE	TABLESPACE EXTENT_ID	BYTES	BLOCKS
---------- ---------- ----------------- ---------- --------- --------- ---------
SALES	PART2	TABLE PARTITION	SALES_P2	0 251658240	122880
SALES	PART3	TABLE PARTITION	SALES_P3	0 251658240	122880
SALES	PART4	TABLE PARTITION	SALES_P4	0 251658240	122880
SALES	PART1	TABLE PARTITION	SALES_P1	0 251658240	122880



SQL> create tablespace sales_p1 datafile 'd:\orant\database\sales_p1.dbf'
	2	size 60m reuse autoextend on next 60m maxsize 500m
	3	default storage (initial 60m next 60m pctincrease 0);
Tablespace created.
SQL> create tablespace sales_p2 datafile 'f:\orant\database\sales_p2.dbf'
	2	size 60m reuse autoextend on next 60m maxsize 500m
	3	default storage (initial 60m next 60m pctincrease 0);
Tablespace created.
SQL> create tablespace sales_p3 datafile 'h:\orant\database\sales_p3.dbf'
	2	size 60m reuse autoextend on next 60m maxsize 500m
	3	default storage (initial 60m next 60m pctincrease 0);
Tablespace created.
SQL> create tablespace sales_p4 datafile 'o:\orant\database\sales_p4.dbf'
	2	size 60m reuse autoextend on next 60m maxsize 500m
	3	default storage (initial 60m next 60m pctincrease 0);
Tablespace created.



P168_01

ERROR at line 1:
ORA-14400: inserted partition key is beyond highest legal partition key



P168_02

ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change




partition_found = FALSE
partition_num = 1
starting with first column in first partition key
loop until partition_found = TRUE
	if partition key (partition_num) = (maxvalue,maxvalue) then
	partition_found = TRUE
	elsif cv1 > column 1 in partition (partition_num) key then
	go on to next partition
	elsif cv1 = column 1 in partition (partition_num) key then
	if cv2 >= column 2 in partition (partition_num) key then
	go on to next partition
	else
	place row in partition (partition_num)
	partition_found = TRUE
	end if
	end if
	add 1 to partition_num
end loop



SQL> create tablespace cust_p1
	2	datafile 'e:\orant\database\cust_p1.dbf' size 60m
	3	default storage (initial 40m next 40m pctincrease 0);
Tablespace created.
SQL> create tablespace cust_p2
	2	datafile 'h:\orant\database\cust_p2.dbf' size 60m
	3	default storage (initial 40m next 40m pctincrease 0);
Tablespace created.
SQL> create tablespace cust_p3
	2	datafile 'j:\orant\database\cust_p3.dbf' size 60m
	3	default storage (initial 40m next 40m pctincrease 0);
Tablespace created.



P174_01

ERROR at line 1:
ORA-14083: cannot drop the only partition of a partitioned table



P174_02

SQL> alter table purchase split partition cust_p2 at (555555,1997,44)
	2	into (partition cust_pla,
	3	partition cust_plb);
Table altered.
SQL>




SQL> -- The following assumes that the cust_ip1, cust_ip2, and cust_ip3
SQL> -- tablespaces have been created and the user has quota in place.
SQL> -- Remember that the table is partitioned using a multicolumn
SQL> -- partition key, whereas the index partitioning is global and set
SQL> -- up differently.
SQL> create index purchase_1 on purchase (cust_num) global
	2	partition by range (cust_num)
	3	(partition values less than (555555)
	4	tablespace cust_ip1,
	5	partition values less than (555557)
	6	tablespace cust_ip2,
	7	partition values less than (maxvalue)
	8	tablespace cust_ip3);
Index created.
SQL>



SQL> select * from user_part_key_columns;
NAME	COLUMN_NAME	COLUMN_POSITION
------------------------------ ------------------------------ ---------------
CUSTOMER	FY_CODE	1
CUSTOMER	CUST_NUM	2
CUSTOMER	REGION	3
CUSTOMER_1	FY_CODE	1
CUSTOMER_1	CUST_NUM	2
CUSTOMER_1	REGION	3
6 records selected.
SQL>



SQL> select partition_name,high_value
	2	from user_tab_partitions
	3	order by 1;
PARTITION_NAME	HIGH_VALUE
-------------------------- ---------------------------------
P1	1997, 1200, 'B'
P2	1997, 9000, 'F'
P3	1997, 99999, 'Z'
P4	1998, 10000, 'Z'
P5	1999, 29999, 'A'
P6	1999, 99999, 'A'
SQL> select partition_name,high_value 
	2	from user_ind_partitions 
	3	order by 1;
PARTITION_NAME	HIGH_VALUE
-------------------------- ---------------------------------
P1	1997, 1200, 'B'
P2	1997, 9000, 'F'
P3	1997, 99999, 'Z'
P4	1998, 10000, 'Z'
P5	1999, 29999, 'A'
P6	1999, 99999, 'A'



SQL> analyze table purchase partition (cust_p1) compute statistics;
Table analyzed.
SQL>



SQL> create index purchase_cnum on purchase (cust_num) local;
Index created.
SQL>



SQL> analyze index purchase_cnum compute statistics;
Index analyzed.
SQL>



SQL> analyze index purcase_cnum partition (p4) compute statistics;
Index analyzed.
SQL>



SQL> desc sys.user_tab_partitions
 Name	Null?	Type
 ------------------------------- -------- ----
 TABLE_NAME	NOT NULL VARCHAR2(30)
 PARTITION_NAME	VARCHAR2(30)
 HIGH_VALUE	LONG
 HIGH_VALUE_LENGTH	NOT NULL NUMBER
 PARTITION_POSITION	NOT NULL NUMBER
 TABLESPACE_NAME	NOT NULL VARCHAR2(30)
 PCT_FREE	NOT NULL NUMBER
 PCT_USED	NOT NULL NUMBER
 INI_TRANS	NOT NULL NUMBER
 MAX_TRANS	NOT NULL NUMBER
 INITIAL_EXTENT	NUMBER
 NEXT_EXTENT	NUMBER
 MIN_EXTENT	NOT NULL NUMBER
 MAX_EXTENT	NOT NULL NUMBER
 PCT_INCREASE	NOT NULL NUMBER
 FREELISTS	NUMBER
 FREELIST_GROUPS	NUMBER
 LOGGING	VARCHAR2(3)
 NUM_ROWS	NUMBER
 BLOCKS	NUMBER
 EMPTY_BLOCKS	NUMBER
 AVG_SPACE	NUMBER
 CHAIN_CNT	NUMBER
 AVG_ROW_LEN	NUMBER
 SAMPLE_SIZE	NUMBER
 LAST_ANALYZED	DATE
 BUFFER_POOL	VARCHAR2(7)
SQL> desc sys.user_part_col_statistics
 Name	Null?	Type
 ------------------------------- -------- ----
 TABLE_NAME	NOT NULL VARCHAR2(30)
 PARTITION_NAME	VARCHAR2(30)
 COLUMN_NAME	VARCHAR2(30)
 NUM_DISTINCT	NUMBER
 LOW_VALUE	RAW(32)
 HIGH_VALUE	RAW(32)
 DENSITY	NUMBER
 NUM_NULLS	NUMBER
 NUM_BUCKETS	NUMBER
 SAMPLE_SIZE	NUMBER
 LAST_ANALYZED	DATE



C:\WINNT\PROFILES\ABBEYM\DESKTOP> exp80 userid=part/part tables=purchase:cust_p1
Export: Release 8.0.3.0.0 - Production on Mon Aug 30 10:11:46 1999
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
Connected to: Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table	PURCHASE
. . exporting partition	CUST_P1	23990 rows exported
Export terminated successfully without warnings.



C:\WINNT\PROFILES\ABBEYM\DESKTOP>imp80 userid=part/part tables=purchase:cust_p1
ignore=y
Import: Release 8.0.3.0.0 - Production on Mon Aug 11 10:55:54 1997
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
Connected to: Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
Export file created by EXPORT:V08.00.03 via conventional path
. importing PART's objects into PART
. . importing partition	"PURCHASE":"CUST_P1"	23990 rows imported
Import terminated successfully without warnings.
C:\WINNT\PROFILES\ABBEYM\DESKTOP>



load data
infile 'c:\purchase.dat'
into table purchase partition (cust_p1)
(po_num position (1:5) char, 
 ...
 ... )



ORA-14400: inserted partition key is beyond highest legal partition key



SQL> -- All this is already in place supporting partition views.
SQL> -- Create the tables to be part of the partition view.
SQL> create table purchase_lt555 (
	2	po_num	number,
	3	cust_num	number) tablespace purchase_low;
Table created.
SQL> create table purchase_gte555 (
	2	po_num	number,
	3	cust_num	number) tablespace purchase_high;
Table created.
SQL> -- Create rows in these 2 tables. Those whose PO_NUM < 555 are
SQL> -- in PURCHASE_LT555, the others in PURCHASE_GTE555.
SQL> insert into purchase_lt555 values (323,15);
1 row created.
SQL> insert into purchase_lt555 values (324,25);
1 row created.
SQL> insert into purchase_lt555 values (325,35);
1 row created.
SQL> insert into purchase_lt555 values (221,45);
1 row created.
SQL> insert into purchase_lt555 values (245,55);
1 row created.
SQL> insert into purchase_gte555 values (623,65);
1 row created.
SQL> insert into purchase_gte555 values (724,75);
1 row created.
SQL> insert into purchase_gte555 values (825,85);
1 row created.
SQL> insert into purchase_gte555 values (821,95);
1 row created.
SQL> insert into purchase_gte555 values (945,35);
1 row created.
SQL> -- Create the partition view.
SQL> create or replace view purchase_pv as
	2	select * from purchase_lt555
	3	union all
	4	select * from purchase_gte555;
View created.
SQL> -- Define the partitioned table.
SQL> drop table purchase;
Table dropped.
SQL> create table purchase (
	2	po_num	number,
	3	cust_num	number)
	4	partition by range (po_num)
	5	(partition pur_1 values less than (555)
	6	tablespace purchase_low,
	7	partition pur_2 values less than (maxvalue)
	8	tablespace purchase_high);
Table created.
SQL> -- Let's do the exchange.
SQL> alter table purchase exchange partition pur_1
	2	with table purchase_lt555;
Table altered.
SQL> alter table purchase exchange partition pur_2
	2	with table purchase_gte555;
Table altered.
SQL> -- Let's look at the outcome.
SQL> select *
	2	from purchase partition (pur_1);

	PO_NUM	CUST_NUM
---------- ----------
	323	15
	324	25
	325	35
	221	45
	245	55
SQL> select *
	2	from purchase partition (pur_2);



	PO_NUM	CUST_NUM
@
---------- ----------
	623	65
	724	75
	825	85
	821	95
	945	35
SQL>



SQL> -- This becomes a global index since GLOBAL is the default.
SQL> create index purchase_1 on purchase (po_num);
SQL> alter table purchase drop partition pur_2;
Table altered.
SQL> select * from purchase where po_num < 444;
select * from purchase where po_num < 444
	*
ERROR at line 1:
ORA-01502: index 'PART.PURCHASE_1' or partition of such index is in unusable
	state



SQL> create or replace view po_97 as
	2	select *
	3	from purchase partition (pur_97);
View created.
SQL> begin
	2	declare dummy number;
	3	begin
	4	select count(*) into dummy from po_97;
	5	end;
	6	end;
	7	/
PL/SQL procedure successfully completed.



5


PARALLEL_MIN_SERVERS = the likely number of simultaneous parallel operations



STATISTIC	VALUE
------------------------------ ----------
Servers Busy	0
Servers Idle	0



PARALLEL_MAX_SERVERS = 2 * # of CPUs * # of concurrent users
	= 2 * 4 * 30 = 240



OPTIMIZER_PERCENT_PARALLEL = 100/number of concurrent users



SHARED_POOL_SIZE =
	current value + ((3 * message buffer size ) * (CPUs + 2) *
PARALLEL_MAX_SERVERS)
	= 20000000 + ((3 * 2048) * (8 + 2) * 16)
	20000000 + 6144 * 10 * 16 = 20000000 + 983040 = 20983040



ALWAYS_ANTI_JOIN = hash



select *
	from individual
 where lastname like 'KERZNER%'
	and office_id is not null
	and office_id not in
	(select /*+ hash_aj */ office
	from national_office
	where office_id is not null
	and role = 'SENATORS');



ROW_LOCKING = ALWAYS



COMPATIBLE = 8.0.0



select /*+ parallel (product,4) */ date_range, product, unit_price
	from product;



/*+ parallel_index (tablename, DOP, parallel server instance split) */
select /*+ parallel_index (product,4) */ date_range, product, unit_price
	from product;



/*+ parallel_index (indexname, DOP, parallel server instance split) */
select /*+ parallel_index (date_range_index,4) */ date_range,
	product, unit_price
	from product;



/*+ noparallel (tablename) */
@
select /*+ noparallel */ date_range, product, unit_price
	from product;



/*+ append*/



select firstname,lastname,birthdate,sex
	from individual
 order by lastname;



SQL> create table individual (
 2	firstname	varchar2(20) not null,
 3	lastname	varchar2(30) not null,
 4	birthdate	date	not null,
 5	gender	varchar2(1)	not null,
 6	initial	varchar2(1),
 7	favorite_beatle	varchar2(6))
 8	parallel (degree 4);
Table created.
SQL>



-- This statement will be parallelized looking for a pool of 4 parallel
-- query server processes.
select col1, col2, col3 /*+ parallel (tab1,4) */ ...
-- This will not be since the word is misspeledd.
select col1, col2, col3 /*+ paralell (tab1,4) */ ...
-- As will this one, since there is an extra space between the leading
-- asterisk and the + sign, thereby violating a stipulation for writing
-- hints
select col1, col2, col3 /* + parallel (tab1,4) */ ...



SQL> create table contributions_1997 /*+ parallel (contributions_1997,10) */
	2	as
	3	select indivual_id,sum(income_tax_withholding),
	4	sum(pension_withholding) /*+ parallel (weekly_details,5) */
	5	from weekly_details 
	6	where fiscal_year = 1997
	7	group by individual;
Table created.
SQL>



 P212_01

SQL> insert into children_detail values
	2	('Baila','Abramson','29-SEP-1989','F','Radar');
SQL>




 P212_02

SQL> insert into children_detail
	2	select firstname, lastname, birthdate, sex
	3		from individual
	4	where type = 'KID';
1289 records created.
SQL>




SQL> insert into children_detail /*+ parallel (children_detail, 4) */
	2	select firstname, lastname, birthdate,
	3	gender, sum(annual_income) /*+ parallel (individual, 6) */
	4	from individual
	5	where type = 'KID'
	6	group by firstname, lastname, birthdate, gender;
239 rows created.
SQL>



SQL> create index idx_1997_sales_product
	2	on 1997_sales (product_id)
	3	nosort
	4	nologging
	5	parallel (degree 5);
Index created.
SQL>



SQL> alter index idx_1997_sales_product rebuild
 2	nologging parallel (degree 5);
Index altered.
SQL>



sqlldr80 userid=jillian/brentford control=tab1.ctl data=01jan.dat parallel=true
sqlldr80 userid=jillian/brentford control=tab1.ctl data=02jan.dat parallel=true
sqlldr80 userid=jillian/brentford control=tab1.ctl data=03jan.dat parallel=true



sqlload userid=baila/radar control=tab1.ctl data=01jan.dat parallel=true direct=true
sqlload userid=baila/radar control=tab1.ctl data=02jan.dat parallel=true direct=true
sqlload userid=baila/radar control=tab2.ctl data=addr.dat parallel=true direct=true



sqlload userid=pakman/sue control=tab1.ctl data=01jan.dat parallel=true direct=true
sqlload userid=pakman/sue control=tab1.ctl data=02jan.dat parallel=true direct=true
sqlload userid=pakman/sue control=tab1.ctl data=03jan.dat parallel=true direct=true



load data
infile 'susan_decor_sales_0197.dat'
insert into table sales
options (file='/app/data/sales_97.dat')
...



SQL> select * from v$pq_sesstat;
STATISTIC	LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
DML Parallelized	2	4
Queries Parallelized	0	0
DFO Trees	2	2
Server Thread	3	0
Allocation Height	3	0
Allocation Width	0	0
Local Msgs Sent	21	50
Distr Msgs Sent	0	0
Local Msgs Recv'd	21	50
Distr Msgs Recv'd	0	0
11 rows selected.
SQL>



SQL> select * from v$pq_sysstat;
STATISTIC	VALUE
------------------------------	---------
Servers Busy	5
Servers Idle	3
Servers Highwater	16
Server Sessions	8
Servers Started	5
Servers Shutdown	0
Servers Cleaned Up	0
Queries Initiated	0
DML Initiated	20
DFO Trees	2
Local Msgs Sent	60
Distr Msgs Sent	0
Local Msgs Recv'd	60
Distr Msgs Recv'd	0
15 rows selected.
SQL>



6


SQLWKS> show parameters



cardinality = number of possible unique column values / number of rows in table 
	= 2 / 12909 * 100 = .0155



cardinality =	50 / 12909 * 100 = .39



SQL> select * from v$sgastat where name = 'free memory';
POOL	NAME	BYTES	
----------- -------------------------- ----------
shared pool free memory	1419576
1 row selected.
SQL>



SQL> select a.sid, b.name,a.value from v$sesstat a, v$statname b
	2 where a.statistic# in (15,16)
	3 and a.statistic# = b.statistic#
	4 order by 1,3;
SID	NAME	VALUE
---------- ------------------------ ----------
	1 session uga memory	11864
	1 session uga memory max	11864
	2 session uga memory	13232
	2 session uga memory max	13232
	3 session uga memory	11584
	3 session uga memory max	11584
	4 session uga memory	11584
	4 session uga memory max	11584
	5 session uga memory	38488
	5 session uga memory max	38488
	6 session uga memory	32428
	6 session uga memory max	32428
	7 session uga memory	17636
	7 session uga memory max	17636
	8 session uga memory	20496
	8 session uga memory max	20496
	9 session uga memory	24580
	9 session uga memory max	24580
	11 session uga memory	24636
	11 session uga memory max	24636
20 rows selected.
SQL>



if requested allocation > shared_pool_reserved_size
	search unreserved free list
	if space found in unreserved area
	allocate space for request in unreserved area
	else
	return ORA- error
	end if
else 
	if requested allocation can be satisfied in unreserved free list
	allocate space for request in unreserved area
	else
	if requested allocation < shared_pool_reserved_min_alloc
	return ORA- error
	else
	if requested allocation can be satisfied in reserved free list
	allocate space for request in reserved area
	else
	return ORA- error
	end if
	end if
	end if
end if 



LOG_CHECKPOINT_INTERVAL =	size of redo log in bytes /
	bytes in os block /
	desired checkpoints between log switches
	=	5242880 / 512 / 4 = 2560



LOG_CHECKPOINT_INTERVAL =	CEIL (redo log size in bytes / bytes in os block) + 1)
		=	CEIL (5242880 / 512 + 1) = 10241



redo bytes written = 10241 * 512 = 5243392



log_archive_dest = f:\prod\arclogs\arc_
log_archive_format = %s.arc
log_archive_duplex_dest = t:\prod\arclogs\duplex\arc_



Tue Aug 26 19:58:09 1999
Beginning database checkpoint by background
Tue Aug 26 19:58:10 1999
Thread 1 advanced to log sequence 3423
	Current log# 1 seq# 3423 mem# 0: /oradata2/rep01/redo01a.log
	Current log# 1 seq# 3423 mem# 1: /oradata3/rep01/redo01b.log
	Current log# 1 seq# 3423 mem# 2: /oradata4/rep01/redo01c.log
Tue Aug 26 19:58:23 1999
Completed database checkpoint by background
Tue Aug 26 22:12:19 1999
Beginning database checkpoint by background



select decode(state,0,'Free',
	1, 'Read and Modified',
	2, 'Read and Not Modified',
	3, 'Currently Being Read', 'Other'), Count(*)
	from x$bh
 group by decode(state, 0,'Free',
	1, 'Read and Modified', 
	2, 'Read and Not Modified', 
	3, 'Currently Being Read', 'Other');



ROLLBACK_SEGMENTS = (RS1,RS2,RBS_HUGE)



 P249_01
select name, account, balance
		from orcl
	where name = 'MONEY GRABBERS'
		and account = '5002300';



 P250_01

update orcl
set balance = balance-2000
	where name = 'MONEY GRABBERS'
		and account = '5002300';



commit_work;



SESSIONS = (1.1 * PROCESSES) + 5



UTL_FILE_DIR = F:\UTL_DIR\PRD1
UTL_FILE_DIR = D:\UTL_DIR\PRD2
UTL_FILE_DIR = K:\UTL_DIR\PRD3




SQL> @%RDBMS80%\admin\utlbstat



SQLWKS> @%RDBMS80%\admin\utlestat



LIBRARY	GETS	GETHITRATI PINS	PINHITRATI RELOADS	INVALIDATI
------------ ---------- ---------- ---------- ---------- ---------- ----------
BODY	3	1	3	1	0	0
CLUSTER	20	1	26	1	0	0
INDEX	0	1	0	1	0	0
OBJECT	0	1	0	1	0	0
PIPE	0	1	0	1	0	0
SQL AREA	90472	.999	184926	.998	198	356
TABLE/PROCED	18467	.985	20342	.963	190	0
TRIGGER	1	1	1	1	0	0



Statistic	Total	Per Transact Per Logon	Per Second
--------------------------- ------------ ------------ ------------ ------------
CPU used by this session	2088150694	19334728.65	119452.59	23857.22
CPU used when call started	6060	56.11	.35	.07
DBWR buffers scanned	16852	156.04	.96	.19
DBWR checkpoint buffers wri	2906	26.91	.17	.03
DBWR checkpoint write reque	737	6.82	.04	.01



LOGICAL READS = CONSISTENT GETS + DB BLOCK GETS
HIT RATIO = (LOGICAL READS - PHYSICAL READS)/LOGICAL READS



LOGICAL READS	= CONSISTENT GETS + DB BLOCK GETS
(157,694)	= ( 42,533 + 115,161 )
HIT RATIO	= (LOGICAL READS - PHYSICAL READS)/LOGICAL READS
(95.45)	= (157,694 - 7,171)/157,694



Event Name	Count	Total Time	Avg Time
-------------------------------- ------------- ------------- -------------
rdbms ipc message	17486	17485887	999.99
SQL*Net message from client	3595	8825822	2455.03
db file sequential read	3898	6211	1.59
db file scattered read	203	2390	11.77
log file switch completion	76	2061	27.12
log file switch (checkpoint inco	12	298	24.83
log file sync	178	89	.5
write complete waits	93	51	.55
free buffer waits	31	36	1.16
buffer busy waits	11	25	2.27
control file sequential read	11	19	1.73
latch free	7	13	1.86
SQL*Net break/reset to client	116	12	.1
SQL*Net message to client	3595	5	0
file identify	1	2	2
file open	22	1	.05



BUFFER BUSY WAITS RATIO	= BUFFER BUSY WAITS/LOGICAL READS
LOGICAL READS	= CONSISTENT GETS + DB BLOCK GETS



LOGICAL READS	= CONSISTENT GETS + DB BLOCK GETS
	(157,694) =	( 42,533 + 115,161 )
 BUFFER BUSY WAITS RATIO	= BUFFER BUSY WAITS/LOGICAL READS
	(.00007)	= (11/157,694)



CLASS	COUNT	TIME
------------------ ---------------- ----------------
data block	11	25



NON-INDEX LOOKUPS RATIO = Table Scans (Long Tables) /
	Table Scans (Long Tables) +
	Table Scans (Short Tables)



Table Scans (Long Table)	=	43
Table Scans (Short Table)	=	542
Non-Index Lookups Ratio	.07	=	43/(43+542)



Average Write Queue Length
---------------------------
 .2864897466827503015681544



	PHYS	PHYS
	PHYS	PHYS	BLKS READ	PHYS WRITE
TABLE_SPACE	FILE_NAME	READS READ TIME WRITES	WRT TIME MEGABYTES
	
-------------- ------------------ ----- ---- ---- ------ ---- ---- ----
ROLLBACK_DATA	D:\...\RBS1ORCL.ORA	918	918 1407	2428 2428 2243	10
ROLLBACK_DATA	D:\...\RBS2ORCL.ORA	558	558	897	1362 1362 1178	52
SYSTEM	D:\...\SYS1ORCL.ORA 1847 3244 3231	427	427	418	42
TEMPORARY_DATA D:\...\TMP1ORCL.ORA	75	75	0	75	75	0	2
USER_DATA	D:\...\USR1ORCL.ORA	420	834 1663	791	791	617	3
USER_DATA	D:\...\USR2ORCL.ORA 1039 1573 2558	1255 1255 1011	524



LATCH_NAME	GETS	MISSES	HIT_RATIO	SLEEPS	SLEEPS/MISS
------------------ ----------- ----------- ----------- ----------- -----------
Active checkpoint	30950	1	1	1	1
Checkpoint queue l	60056	3	1	3	1
Token Manager	2422	0	1	0	0
cache buffer handl	2776	0	1	0	0 
cache buffers chai	552924	14	1	14	1
cache buffers lru	14103	0	1	0	0
dml lock allocatio	1711	0	1	0	0
enqueue hash chain	14580	0	1	0	0
enqueues	62116	0	1	0	0
ktm global data	291	0	1	0	0
latch wait list	2	0	1	0	0
library cache	1049780	2	1	2	1
library cache load	1132	0	1	0	0
list of block allo	821	0	1	0	0
messages	223018	0	1	0	0
modify parameter v	17481	0	1	0	0
multiblock read ob	924	0	1	0	0
ncodef allocation	1391	0	1	0	0
process allocation	3	0	1	0	0
redo allocation	161272	11	1	11	1
row cache objects	180542	1	1	1	1
sequence cache	2310	0	1	0	0
session allocation	71658	0	1	0	0
session idle bit	125277	0	1	0	0
session switching	1391	0	1	0	0
shared pool	225438	0	1	0	0
sort extent pool	291	0	1	0	0
transaction alloca	3259	0	1	0	0
undo global data	8371	0	1	0	0
user lock	8	0	1	0	0



LATCH_NAME	NOWAIT_GETS	NOWAIT_MISSES	NOWAIT_HIT_RATIO
------------------ ---------------- ---------------- ----------------
Token Manager	5	0	1
cache buffers chai	664672	24	25
cache buffers lru	17010	1	2
latch wait list	2	0	1
library cache	5	0	1
process allocation	3	0	1
row cache objects	5	0	1
vecio buf des	5	0	1



UNDO	TRANS	TRANS	UNDO	SEGMENT
SEGMENT	TBL_GETS	TBL_WAITS BYTES_WRITTEN	SIZE_BYTES	XACTS	SRINKS	WRAPS
-------- --------- --------- ------------- ------------ ------- ------- ------
	0	294	0	0	407552	0	0	0
	2	403	0	36094	2199552	0	0	1
	3	421	0	49996	2199552	-1	0	1
	4	1528	0	1694524	1738752	0	0	34
	5	1480	0	2119492	2250752	1	0	43
	6	413	0	37542	2301952	0	0	1
	7	413	0	48072	509952	0	0	1
	8	409	0	40512	151552	0	0	1
	18	423	0	52300	110592	0	0	6



ROLLBACK WAIT RATIO	=	TRANS_TBL_WAITS/TRANS_TBL_GETS * 100



NAME	VALUE
--------------------------------------- --------------------------------------
background_dump_dest	%RDBMS80%\trace
control_files	D:\ORANT\DATABASE\ctl1orcl.ora
db_block_size	2048
db_files	1024
db_name	oracle
distributed_lock_timeout	300
distributed_transactions	5	
global_names	TRUE
job_queue_interval	10
job_queue_processes	2
log_buffer	8192
log_checkpoint_interval	10000
max_dump_file_size	10240
processes	59
remote_login_passwordfile	SHARED
rollback_segments	MONSTER, RB1, RB2, RB3, RB4, RB5, RB6,
sequence_cache_hash_buckets	10
shared_pool_size	10000000
text_enable	TRUE
timed_statistics	TRUE
user_dump_dest	%RDBMS80%\trace



NAME	GET_REQS	GET_MISS	SCAN_REQ	SCAN_MIS	MOD_REQS	COUNT	CUR_USAG
--------------- -------- -------- -------- -------- -------- -------- --------
dc_tablespaces	22920	0	0	0	0	6	5
dc_free_extents	3279	26	28	0	80	68	24
dc_segments	794	1	0	0	28	614	592
dc_rollback_seg	11098	0	0	0	0	24	20
dc_used_extents	127	28	0	0	28	505	504
dc_users	554	0	0	0	0	29	21
dc_user_grants	224	1	0	0	0	70	20
dc_objects	18938	138	0	0	312	1436	1435
dc_synonyms	216	103	0	0	103	141	134
dc_usernames	619	0	0	0	0	21	16
dc_object_ids	523	103	0	0	103	847	843
dc_sequences	41	0	0	0	39	48	45
dc_profiles	2	0	0	0	0	10	3



START_TIME	END_TIME
------------------ ------------------
18-aug-99 17:56:02 19-aug-99 18:14:49



update my_table
set col1 = 'NEW VALUE';



SQLWKS> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES	MEMBERS ARC STATUS	FIRST_CHAN FIRST_TIM
------ ------- --------- ------ ------- --- --------- ---------- ---------
	1	1	373 204800	1 NO	CURRENT	46695 19-AUG-99
	2	1	372 204800	1 NO	INACTIVE	46670 19-AUG-99
2 rows selected.
@
SQLWKS> select * from v$logfile;
GROUP# STATUS	MEMBER
------ ------- ------------------------------
	1	D:\ORANT\DATABASE\LOG2ORCL.ORA
	2	D:\ORANT\DATABASE\LOG1ORCL.ORA
2 rows selected.
SQLWKS>



 P280_01
select class, sum(count) 'Total Waits'
		from sys.v$waitstat
	where class in ('undo header', undo block', 'data block')
	group by class;




7


/home/oracle --> (prod)
masii> env | grep NLS_LANG



/home/oracle> # If defined, the output will be
/home/oracle --> (prod)
masii> env | grep NLS_LANG
NLS_LANG=american_america.we8dec
/home/oracle> # If not defined, the output will be a system prompt since
/home/oracle> # NLS_LANG has no current value
/home/oracle --> (prod)
masii> env | grep NLS_LANG
masii>



SQL> -- The offending character is in position 10 in the TEXT_F column
SQL> select ascii(substr(text_f,10,1))
	2	from off_master
	3	where off_id = 213;
ASCII(SUBSTR(TEXT_F,10,1))
--------------------------
	233
SQL>



if the ASCII sequence on the server is 233(*)then
	if the client displays * then
	all is well
	elsif the client displays i or e then
	problem is on the client
	end if
else
	problem is on the server
end if



SQL> select file_name, tablespace_name
	2	from sys.dba_data_files
	3	order by 1;
FILE_NAME	TABLESPACE_NAME
-------------------------------------------------
F:\ORANT\DATABASE\CUSTOM_1.ORA	CUSTOM
F:\ORANT\DATABASE\CUSTOM_2.ORA	CUSTOM
F:\ORANT\DATABASE\FINANCE_1.ORA	FINANCE
H:\ORANT\DATABASE\FINANCE_2.ORA	FINANCE
G:\ORANT\DATABASE\RBS1ORCL.ORA	ROLLBACK
G:\ORANT\DATABASE\SYS1ORCL.ORA	SYSTEM
G:\ORANT\DATABASE\TEMP1ORCL.ORA	TEMPORARY_DATA



ORA-01135: file 2 accessed for DML/query is offline
ORA-01110: data file 2: 'C:\ORANT\DATABASE\USR1ORCL.ORA'



ORA-01659: unable to allocate MINEXTENTS beyond 17 in tablespace IND1_TBSP



SQL> create index naddy_1 on naddy (col1, col2;
	*
ERROR at line: 1
ORA-00907: missing right parenthesis
SQL>



sqlplus @objects STEVE Before



sqlplus @objects STEVE After



/* -------------------------------------------------------- */
/*	objects.sql	*/
/*	*/
/*	Display partial contents of 4 USER_ series of views	*/
/*	for comparison between before and after rebuild.	*/
/*	*/
/*	Oracle8 Tuning	Corey & Abbey & Dechichio & Abramson	*/
/* -------------------------------------------------------- */
def sowner="&1"
def timer="&2"
set echo off feed off pages 0 ver off lines 1000 trimsp on
-- Spool file ends up with name of second parameter (i.e.,
-- Before.lst or After.1st.
spool &2
prompt ===== &timer =====
prompt
select table_name
	from sys.dba_tables
 where owner = upper('&&sowner');
select view_name
	from sys.dba_views
 where owner = upper('&&sowner');
select table_name,index_name
	from sys.dba_indexes
 where owner = upper('&&sowner');
select index_name,column_position,column_name
	from sys.dba_ind_columns
 where index_owner = upper('&&sowner')
 order by index_name,column_position;
prompt
spool off
exit



1c1
< After
---
> Before
1d0
< AP_INVOICES_1
5c4
< AP_GENERAL_2
---
>
9a9,11
> GL_GOTCHAS_1
> GL_GOTCHAS_2
> GL_BALANCES_N1



SVRMGR> create database newtest
	2>	controlfile reuse
	3>	datafile 'f:\orant\database\sys1orcl.ora' autoextend on,
	4>	'g:\orant\database\sys2orcl.ora' autoextend on
	5>	next 10m maxsize unlimited
	6>	logfile group 1 ('diskb:log1.log', 'diskc:log1.log') size 50k,
	7>	group 2 ('diskb:log2.log', 'diskc:log2.log') size 50k
	8>	maxlogfiles 5
	9>	maxloghistory 100
	10>	maxdatafiles 10
	11>	maxinstances 2
	12>	exclusive
	13>	character set we8dec
	14>	national character set ja16sjisfixed
	15>	noarchivelog
	16>	maxlogmembers 4;
Statement processed.
SVRMGR>



db_files = 60



db_files = 32



SQL> alter tablespace prd_idx 
	2	add datafile 'f:\orant\database\prdind_2.dbf' size 10M;
alter tablespace prd_idx
	*
ERROR at line 1:
ORA-01118: cannot add any more database files: limit of 40 exceeded



db_files = 40



db_files = 60



C:\WINNT\PROFILES\ABBEYM\DESKTOP> svrmgr30
Oracle Server Manager Release 3.0.3.0.0 - Production
(c) Copyright 1997, Oracle Corporation.	All Rights Reserved.
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
SVRMGR> connect internal
Password:
Connected.
SVRMGR> rem The following command will
SVRMGR> rem	create the SYSTEM tablespace
SVRMGR> rem	create the SYSTEM rollback segment
SVRMGR> rem	create the control files for the database
SVRMGR> rem	create the redo log files for the database
SVRMGR> rem	create the users SYS and SYSTEM
SVRMGR> rem	specify the character set that stores data in the database
SVRMGR> startup nomount
ORACLE instance started.
Total System Global Area	12071016 bytes
Fixed Size	46136 bytes
Variable Size	11090992 bytes
Database Buffers	409600 bytes
Redo Buffers	524288 bytes
SVRMGR>	create database devel
	2> datafile 'd:\orant\database\sysdev1.dbs'	size 20M
	3> logfile group 1 ('c:\orant\redo\dev_log1a.log', 
	4>	'd:\orant\redo\dev_log1b.log') size 2M,
	5>	group 2 ('d:\orant\redo\dev_log2a.log', 
	6>	'c:\orant\redo\dev_log2b.log') size 2M
	7>	maxdatafiles 40
	8>	character set we8iso8859p1;
Statement processed.
SVRMGR>



SQL> create table my_table (seq	number,
	2	my_key	varchar2(10),
	3	desc	varchar2(40))
	4	freelist 12;
Table created.



SQL> select class, count 
	2	from v$waitstat 
	3	where class = 'free list';
CLASS	COUNT
----------------------- -----
free list	59
SQL> select name,value 
	2	from v$sysstat 
	3	where name in ('db_block_gets','consistent gets');
CLASS	COUNT
----------------------- -----------
db block gets	12850
consistent gets	10119
SQL>



freelist wait events = (free list count) / (db block gets +
	consistent gets) * 100
	= 59 / (12850 + 10119) * 100
	= 59 / 22969 = 0.26



SQL> create table my_table (seq	number,
	2	my_key	varchar2(10),
	3	desc	varchar2(40))
	4	storage (initial 100K next 100K pctincrease 0);
Table created.
SQL>



SQL> alter table my_table storage (pctincrease 0);
Table altered.
SQL>



next extent size = previous extent size * pctincrease / 100



alter table my_table allocate extent size 1M;



SQL> validate index pk_period_dtl;
Index analyzed.
SQL> select lf_rows, lf_rows_len, del_lf_rows, del_lf_rows_len
	2	from index_stats where name = 'PK_PERIOD_DTL';

 LF_ROWS LF_ROWS_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN
-------- ----------- ----------- --------------- 
	2589	92340		97	 3432
SQL>



SQL>create index my_index on my_table (seq, my_key)
	2	storage (initrans 1 maxtrans 4);
Table created.
SQL>



ORA-00257: archiver is stuck. CONNECT INTERNAL only, until freed
Cause: The ARCH process received an error while trying to archive a redo log file.
If the problem is not resolved soon, the database will stop executing transactions. The most likely cause of this message 
is that the destination device is out of space to store the 
redo log file.
Action: Check the archiver trace file for a detailed description of the problem. Also, verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is set up properly for archiving.



 P327_01

SQL> delete finance_9596;
12908 rows deleted.
SQL>



 P327_02

SQL> rollback;
Rollback complete.
SQL> select count(*)
	2	from finance_9596;
		COUNT(*)
-------------
	12908
SQL>



SQL> truncate table finance_9596;
Table truncated.
SQL> rollback;
Rollback complete.
SQL> select count(*)
	2	from finance_9596;
	COUNT(*)
------------
	0



8


select pin,pos_id,last_name,first_name from people where pin = :blk1.pin;
select pin,pos_id,last_name,first_name from people where pin = :blk1.pin;
select pos_id,sal_cap from sal_limit where over_time = :blk1_ot_ind;
select pos_id,sal_cap from sal_limit where over_time = :blk1_ov_ind;



select b.address,b.sql_text,sorts,users_executing
		from sys.v_$sqlarea a,sys.v_$sqltext b
 	where a.hash_value = b.hash_value
order by b.address,piece,sorts,users_executing;



ADDRESS	SQL_TEXT	SORTS USERS_EXECUTING
-------- ---------------------------------------- --------- ---------------
0291E218 select b.address,b.sql_text,sorts,users_	4	1
	executing	from sys.v_$
0291E218 sqlarea a,sys.v_$sqltext b	where a.hash	4	1
	_value = b.hash_value	o
0291E218 rder by b.address,piece,sorts,users_exec	4	1
	uting
02ABABE0 begin dbms_output.disable; end;	0	0
02ABC1F8 alter session set nls_language= 'AMERICA	0	0
	N' nls_territory= 'AMERI
02ABC1F8 CA' nls_currency= '$' nls_iso_currency=	0	0
	'AMERICA' nls_numeric_ch
02ABC1F8 aracters= '.,' nls_calendar= 'GREGORIAN'	0	0
	nls_date_format= 'DD-MO
02ABC1F8 N-YY' nls_date_language= 'AMERICAN'	nls	0	0
	_sort= 'BINARY'



SQL> select namespace, gets, gethits, gethitratio, pins,
	2	pinhits, pinhitratio
	3	from v$librarycache;
NAMESPACE	GETS	GETHITS GETHITRATIO	PINS	PINHITS PINHITRATIO
--------------- --------- --------- ----------- --------- --------- -----------
SQL AREA	125908	125755	.99878483	254244	253884	.99858404
TABLE/PROCEDURE	25439	25307	.99481112	25896	25648	.99042323
BODY	11	9	.81818182	11	9	.81818182
TRIGGER	0	0	1	0	0	1
INDEX	28	0	0	28	0	0
CLUSTER	163	158	.96932515	228	223	.97807018
OBJECT	0	0	1	0	0	1
PIPE	0	0	1	0	0	1
8 rows selected.
SQL>



SQL> select sum(pins) Hits, sum(reloads) Misses,
	2	sum(pins)/(sum(pins)+sum(reloads)) HitRatio
	3	from v$librarycache;
	Hits	Misses	HitRatio
--------- --------- ---------
	5576287	3013 .99945997
SQL>



SQL> desc dbms_shared_pool
PROCEDURE ABORTED_REQUEST_THRESHOLD
 Argument Name	Type	In/Out Default?
 ------------------------------ ----------------------- ------ --------
 THRESHOLD_SIZE	NUMBER	IN	DEFAULT
PROCEDURE KEEP
 Argument Name	Type	In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME	VARCHAR2	IN	DEFAULT
 FLAG	CHAR	IN	DEFAULT
PROCEDURE SIZES
 Argument Name	Type	In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MINSIZE	NUMBER	IN	DEFAULT
PROCEDURE UNKEEP
 Argument Name	Type	In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME	VARCHAR2	IN	DEFAULT
 FLAG	CHAR	IN	DEFAULT



SQL> execute dbms_shared_pool.sizes (20)
SIZE(K) KEPT	NAME
------- ------ -----------------------------------------------------------
165	SYS.STANDARD	(PACKAGE)
73	SELECT TO_CHAR(SHARABLE_MEM / 1000 ,'999999') SZ,DECODE(KEPT_VE
ERSIONS,0,'	',RPAD('YES(' || TO_CHAR(KEPT_VERSIONS)	|
| ')' ,6)) KEEPED,RAWTOHEX(ADDRESS) || ','	|| TO_CHAR(HASH
_VALUE)	NAME,SUBSTR(SQL_TEXT,1,354) EXTRA,1 ISCURSOR	FRO
M V$SQLAREA	WHERE SHARABLE_MEM > :b1 * 1000	UNION SELECT
TO_CHAR(SHARABLE_MEM / 1000 ,'999999') SZ,DECODE(KEPT,'
(02998050,473041910)	(CURSOR)
26	select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,
, i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.le
afcnt,i.distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.anal
yzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.inst
ances,1),i.rowcnt,i.pctthres$,i.indmethod#,i.trunccnt,nvl(c
.unicols,0),nvl(c.deferrable#+c.valid#,0) from ind$ i, (
(02B50F70,2577101735)	(CURSOR)
24	BEGIN sys.dbms_ijob.remove(:job); END;
(02B28704,88643110)	(CURSOR)
24	SYS.STANDARD	(PACKAGE BODY)
20	SYS.V$SQLAREA	(VIEW)
20	SYS.X$KGLOB	(TABLE)
PL/SQL procedure successfully completed.
SQL>



SQL> execute dbms_shared_pool.keep ('GEO_MAINT','P');
PL/SQL procedure successfully completed.
SQL> execute dbms_shared_pool.sizes (1600)
SIZE(K) KEPT	NAME
------- ------ ---------------------------------------------------
1650	YES	GM.GEO_MAINT	(PACKAGE)
SQL>



SQL> execute dbms_shared_pool.keep ('02998050,473041910','C')
PL/SQL procedure successfully completed.
SQL>



SQL> execute dbms_shared_pool.keep ('02998050,473041914','C')
begin dbms_shared_pool.keep ('02998050,473041914','C'); end;
*
ERROR at line 1:
ORA-06570: shared pool object does not exist, cannot be pinned
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 23
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 41
ORA-06512: at line 1
SQL>



SQL>	execute dbms_shared_pool.unkeep ('02998050,473041910','C')
PL/SQL procedure successfully completed.
SQL>



SQL> create table plant_detail	(
	2	plant_id	number(2),
	3	city_id	number(2) check (city_id < 90),
	4	location	varchar2(20));
Table created.
SQL>



SQL> conn mallia/shauna
Connected.
SQL> desc budget_a
ERROR:
ORA-04043: object budget_a does not exist
SQL> -- Obviously there is no public or private synonym on BUDGET_A, that's why
SQL> -- the error was raised. Let's try again with the account qualifier.
SQL> desc finance.budget_a
ERROR:
ORA-04043: object budget_a does not exist
SQL> -- So why does the following statement work, considering there is a
SQL> -- trigger that fires when the EXP_COMM column in the BUDGET table is
SQL> -- updated??
SQL> update budget
	2	set exp_comm = 1.5*exp_comm
	3	where line_id = 890
	4	and comp_ind is null;
11 rows updated. 
SQL> rollback;
Rollback complete.
SQL>



analyze table work_city {method} statistics {size of sample}



SQL> analyze index work_city_1 compute statistics;
Index analyzed.
SQL> analyze table work_city compute statistics;
Table analyzed.
SQL>



/* --------------------------------------------------------- */
/*	statgath.sql	*/
/*	Analyze all tables and indexes in the database	*/
/* --------------------------------------------------------- */
set pagesize 0 feedback off trimspool on linesize 999 echo off
spool ana_db.sql
prompt
prompt spool ana_db
prompt
prompt set echo on feedback on
select 'analyze index '||owner||'.'||index_name||
	' compute statistics;'
	from sys.dba_indexes
 where owner not in ('SYS','SYSTEM');
select 'analyze table '||owner||'.'||table_name||
	' estimate statistics sample 20 percent;'
	from sys.dba_tables
 where owner not in ('SYS','SYSTEM');
prompt 
prompt spool off
spool off



PROCEDURE ANALYZE_OBJECT
 Argument Name	Type	In/Out Default?
------------------------------- ----------------------- ------ --------
 TYPE	VARCHAR2	IN	DEFAULT
 SCHEMA	VARCHAR2	IN	DEFAULT
 NAME	VARCHAR2	IN	DEFAULT
 METHOD	VARCHAR2	IN	DEFAULT
 ESTIMATE_ROWS	NUMBER	IN	DEFAULT
 ESTIMATE_PERCENT	NUMBER	IN	DEFAULT
 METHOD_OPT	VARCHAR2	IN	DEFAULT



PROCEDURE ANALYZE_SCHEMA
 Argument Name	Type	In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SCHEMA	VARCHAR2	IN/OUT DEFAULT
 METHOD	VARCHAR2	IN/OUT DEFAULT
 ESTIMATE_ROWS	NUMBER	IN/OUT DEFAULT
 ESTIMATE_PERCENT	NUMBER	IN/OUT DEFAULT
 METHOD_OPT	VARCHAR2	IN/OUT DEFAULT



SQL> -- Sample 20 percent for table in current schema
SQL> execute dbms_ddl.analyze_object ('TABLE',null,'PHONE','ESTIMATE',
	null,20);
PL/SQL procedure successfully completed.
SQL> -- Compute for index in current schema
SQL> execute dbms_ddl.analyze_object ('INDEX',null,'PHONE_1','COMPUTE');
PL/SQL procedure successfully completed.
SQL> -- Compute for index belonging to MASON
SQL> execute dbms_ddl.analyze_object ('INDEX','MASON','PHONE_1',
	'COMPUTE');
PL/SQL procedure successfully completed.
SQL> -- Compute for all objects in current schema - note the word USER, a
SQL> -- system variable containing the username of the session.
SQL> execute dbms_utility.analyze_schema (user,'COMPUTE');
PL/SQL procedure successfully completed.
SQL> -- Estimate sampling 20 percent for schema MORDECAI.
SQL> execute dbms_utility.analyze_schema ('MORDECAI','ESTIMATE',null,20);
PL/SQL procedure successfully completed.
SQL> 



SQL> desc dba_tables
 Name	Null?	Type
 ------------------------------- -------- ----
 OWNER	NOT NULL VARCHAR2(30)
 TABLE_NAME	NOT NULL VARCHAR2(30)
 TABLESPACE_NAME	VARCHAR2(30)
 CLUSTER_NAME	VARCHAR2(30)
 IOT_NAME	VARCHAR2(30)
 PCT_FREE	NUMBER
 PCT_USED	NUMBER
 INI_TRANS	NUMBER
 MAX_TRANS	NUMBER
 INITIAL_EXTENT	NUMBER
 NEXT_EXTENT	NUMBER
 MIN_EXTENTS	NUMBER
 MAX_EXTENTS	NUMBER
 PCT_INCREASE	NUMBER
 FREELISTS	NUMBER
 FREELIST_GROUPS	NUMBER
 LOGGING	VARCHAR2(3)
 BACKED_UP	VARCHAR2(1)
 NUM_ROWS	NUMBER
 BLOCKS	NUMBER
 EMPTY_BLOCKS	NUMBER
 AVG_SPACE	NUMBER
 CHAIN_CNT	NUMBER
 AVG_ROW_LEN	NUMBER
 AVG_SPACE_FREELIST_BLOCKS	NUMBER
 NUM_FREELIST_BLOCKS	NUMBER
 DEGREE	VARCHAR2(10)
 INSTANCES	VARCHAR2(10)
 CACHE	VARCHAR2(5)
 TABLE_LOCK	VARCHAR2(8)
 SAMPLE_SIZE	NUMBER
 LAST_ANALYZED	DATE
 PARTITIONED	VARCHAR2(3)
 IOT_TYPE	VARCHAR2(12)
 TEMPORARY	VARCHAR2(1)
 NESTED	VARCHAR2(3)
 BUFFER_POOL	VARCHAR2(7)
@
SQL> desc sys.dba_indexes
 Name	Null?	Type
 ------------------------------- -------- ----
 OWNER	NOT NULL VARCHAR2(30)
 INDEX_NAME	NOT NULL VARCHAR2(30)
 INDEX_TYPE	VARCHAR2(12)
 TABLE_OWNER	NOT NULL VARCHAR2(30)
 TABLE_NAME	NOT NULL VARCHAR2(30)
 TABLE_TYPE	VARCHAR2(11)
 UNIQUENESS	VARCHAR2(9)
 TABLESPACE_NAME	VARCHAR2(30)
 INI_TRANS	NUMBER
 MAX_TRANS	NUMBER
 INITIAL_EXTENT	NUMBER
 NEXT_EXTENT	NUMBER
 MIN_EXTENTS	NUMBER
 MAX_EXTENTS	NUMBER
 PCT_INCREASE	NUMBER
 PCT_THRESHOLD	NUMBER
 INCLUDE_COLUMN	NUMBER
 FREELISTS	NUMBER
 FREELIST_GROUPS	NUMBER
 PCT_FREE	NUMBER
 LOGGING	VARCHAR2(3)
 BLEVEL	NUMBER
 LEAF_BLOCKS	NUMBER
 DISTINCT_KEYS	NUMBER
 AVG_LEAF_BLOCKS_PER_KEY	NUMBER
 AVG_DATA_BLOCKS_PER_KEY	NUMBER
 CLUSTERING_FACTOR	NUMBER
 STATUS	VARCHAR2(8)
@
SQL> desc dba_part_col_statistics
 Name	Null?	Type
 ------------------------------- -------- ----
 OWNER 	NOT NULL VARCHAR2(30)
 TABLE_NAME	NOT NULL VARCHAR2(30)
 PARTITION_NAME	VARCHAR2(30)
 COLUMN_NAME	VARCHAR2(30)
 NUM_DISTINCT	NUMBER
 LOW_VALUE	RAW(32)
 HIGH_VALUE	RAW(32)
 DENSITY	NUMBER
 NUM_NULLS	NUMBER
 NUM_BUCKETS	NUMBER
 SAMPLE_SIZE	NUMBER
 LAST_ANALYZED	DATE
@
SQL> desc dba_tab_col_statistics
 Name	Null?	Type
 ------------------------------- -------- ----
 TABLE_NAME	NOT NULL VARCHAR2(30)
 COLUMN_NAME	NOT NULL VARCHAR2(30)
 NUM_DISTINCT	NUMBER
 LOW_VALUE	RAW(32)
 HIGH_VALUE	RAW(32)
 DENSITY		NUMBER
 NUM_NULLS 	NUMBER
 NUM_BUCKETS	NUMBER
 LAST_ANALYZED	DATE
 SAMPLE_SIZE 	NUMBER
SQL>




/*+ first_rows */



--+ first_rows



select /*	full (plant_detail) */	-- hint ignored (missing + sign)
...
select --+ parallel (plant_detail, 4)
	from plant_detail pd	-- hint ignored since it does not use alias
...
select /*+ parallel (city, YES) */ -- hint ignored since YES is in a number's place



select	/*+ RULE	*/ ...
select	/* there is no hint in this statement */ ...
select	/*+ ALL_ROWS	*/ ...



alter session set optimizer_goal= {mode_of_desire};



alter session set optimizer_goal = ALL_ROWS;
select /*+ RULE	: uses rule-based approach */ ...
select /* there is no hint in this statement */ ...
select /*+ FIRST_ROWS */ ...
alter session set optimizer_goal = RULE;
select /* uses rule-based approach for remainder of statements
	without hints */ ...



explain plan set statement_id ...



delete plan_table where statement_id = 'ZZ';
explain plan set statement_id = 'ZZ' for
select substr(a.flex_value,1,4) responsibility,
	a.description	edesc,
	c.description	fdesc
	from fnd_flex_values a,
	fnd_flex_value_sets b,
	fnd_flex_values c
 where b.flex_value_set_name = 'RESP'
	and a.flex_value_set_id = b.flex_value_set_id
	and c.flex_value_set_id = b.flex_value_set_id
	and a.flex_value = c.flex_value;



set echo off term off feed off ver off
spool xpl
select decode(id,0,'',
	lpad(' ',2*(level-1))||level||'.'||position)||' '||
	operation||' '||options||' '||object_name||' '||
	object_type||' '||
	decode(id,0,'Cost = '||position) Query_plan
	from plan_table
connect by prior id = parent_id
	and statement_id = upper('&1')
 start with id = 0 and statement_id = upper('&1');
spool off
set term on
prompt
prompt Output from EXPLAIN PLAN is in file called "xpl.lst" . . .
prompt



QUERY_PLAN
------------------------------------------------------------------
 SELECT STATEMENT	Cost = 1416
	2.1 NESTED LOOPS
	3.1 NESTED LOOPS
	4.1 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUE_SETS
	5.1 INDEX RANGE SCAN FND_FLEX_VALUES_SETS_1 NON-UNIQUE
	4.2 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES
	5.1 INDEX RANGE SCAN FND_FLEX_VALUES_2 NON-UNIQUE
	3.2 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES
	4.1 INDEX RANGE SCAN FND_FLEX_VALUES_1 NON-UNIQUE



set pagesize 0 feedback off verify off linesize 900 autotrace on
	trimspool on



SQL*Plus: Release 8.0.3.0.0 - Production on Wed Sep 3 18:28:44 1999
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
Connected to:
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
SQL> set autotrace on
Unable to verify PLAN_TABLE format or existence
Error enabling EXPLAIN report
Cannot find the Session Identifier.	Check PLUSTRACE role is enabled
Error enabling STATISTICS report
SQL>



SQL> drop role plustrace;
drop role plustrace
	*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$session to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>



SQL> grant plustrace to public;
Grant succeeded.
SQL>



AUTOT[RACE] {OFF|ON|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]



SQL> select perno, name, status_desc
	2	from person, status_codes
	3	where person.status_cd = status_codes.status_cd;
14 rows selected.
Execution Plan
----------------------------------------------------------
	0	SELECT STATEMENT Optimizer=CHOOSE
	1	0	NESTED LOOPS
	2	1	TABLE ACCESS (FULL) OF 'PERSON'
	3	1	TABLE ACCESS (BY INDEX ROWID) OF 'STATUS_CODES'
	4	3	INDEX (UNIQUE SCAN) OF 'PK_SC' (UNIQUE)
Statistics
----------------------------------------------------------
	34	recursive calls
	3	db block gets
	24	consistent gets
	2	physical reads
	0	redo size
	1121	bytes sent via SQL*Net to client
	695	bytes received via SQL*Net from client
	4	SQL*Net roundtrips to/from client
	1	sorts (memory)
	0	sorts (disk)
	14	rows processed
SQL>



QUERY_PLAN using the hint /*+ ORDERED */
-----------------------------------------------------------------
 SELECT STATEMENT	Cost = 4679
	2.1 NESTED LOOPS
	3.1 MERGE JOIN
	4.1 SORT JOIN
	5.1 TABLE ACCESS FULL FND_FLEX_VALUES
	4.2 SORT JOIN
	5.1 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUE_SETS
	6.1 INDEX RANGE SCAN FND_FLEX_VALUES_SETS_1 NON-UNIQUE
	3.2 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES
	4.1 INDEX RANGE SCAN FND_FLEX_VALUES_1 NON-UNIQUE

QUERY_PLAN using the hint /*+ FIRST_ROWS */
-----------------------------------------------------------------
 SELECT STATEMENT	Cost = 1416
	2.1 NESTED LOOPS
	3.1 NESTED LOOPS
	4.1 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUE_SETS
	5.1 INDEX RANGE SCAN FND_FLEX_VALUES_SETS_1 NON-UNIQUE
	4.2 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES
	5.1 INDEX RANGE SCAN FND_FLEX_VALUES_2 NON-UNIQUE
	3.2 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES
	4.1 INDEX RANGE SCAN FND_FLEX_VALUES_1 NON-UNIQUE



alter session set sql_trace = true;



alter session set sql_trace = false;



timed_statistics = true



sql_trace = true



TKPROF: Release 8.0.3.0.0 - Production on Tue Sep 42 19:35:58 1999
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
Trace file: ora00204.trc
Sort options: default
************************************************************************
count	= number of times OCI procedure was executed
cpu	= cpu time in seconds executing 
elapsed	= elapsed time in seconds executing
disk	= number of physical reads of buffers from disk
query	= number of buffers gotten for consistent read
current	= number of buffers gotten in current mode (usually for update)
rows	= number of rows processed by the fetch or execute call
***********************************************************************
select /*+ choose */
	substr(a.flex_value,1,4) responsibility,
	a.description	edesc,
	c.description	fdesc
	from fnd_flex_values a,
	fnd_flex_value_sets b,
	fnd_flex_values c
 where b.flex_value_set_name = 'RESP'
	and a.flex_value_set_id = b.flex_value_set_id
	and c.flex_value_set_id = b.flex_value_set_id
	and a.flex_value = c.flex_value
call	count	cpu	elapsed	disk	query	current	rows
------- ------	-------- ---------- ---------- ---------- ----------	----------
Parse	1	0.00	0.00	0	0	0	0
Execute	1	0.00	0.00	0	0	0	0
Fetch	69	0.00	0.00	208	7307	0	1012
------- ------	-------- ---------- ---------- ---------- ----------	----------
total	71	0.00	0.00	208	7307	0	1012
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 29	(FND)
Rows	Execution Plan
-------	---------------------------------------------------
	0	SELECT STATEMENT	GOAL: HINT: CHOOSE
	0	NESTED LOOPS
	0	NESTED LOOPS
	0	TABLE ACCESS	GOAL: ANALYZED (BY INDEX ROWID) OF 
	'FND_FLEX_VALUE_SETS'
	0	INDEX	GOAL: ANALYZED (RANGE SCAN) OF 
	'FND_FLEX_VALUES_SETS_1' (NON-UNIQUE)
	0	TABLE ACCESS	GOAL: ANALYZED (BY INDEX ROWID) OF 
	'FND_FLEX_VALUES'
	0	INDEX	GOAL: ANALYZED (RANGE SCAN) OF 'FND_FLEX_VALUES_2' 
	(NON-UNIQUE)
	0	TABLE ACCESS	GOAL: ANALYZED (BY INDEX ROWID) OF 
	'FND_FLEX_VALUES'
	0	INDEX	GOAL: ANALYZED (RANGE SCAN) OF 'FND_FLEX_VALUES_1' 
	(NON-UNIQUE)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call	count	cpu	elapsed	disk	query	current	rows
------- ------	-------- ---------- ---------- ---------- ----------	----------
Parse	12	0.00	0.00	0	0	0	0
Execute	13	0.00	0.00	2	2	17	11
Fetch	78	0.00	0.00	208	7327	33	1049
------- ------	-------- ---------- ---------- ---------- ----------	----------

total	103	0.00	0.00	210	7329	50	1060
@
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call	count	cpu	elapsed	disk	query	current	rows
------- ------	-------- ---------- ---------- ---------- ----------	----------
Parse	2	0.00	0.00	0	0	0	0
Execute	12	0.00	0.00	2	1	11	9
Fetch	3	0.00	0.00	6	15	0	3
------- ------	-------- ---------- ---------- ---------- ----------	----------
total	17	0.00	0.00	8	16	11	12
Misses in library cache during parse: 0
	14	user	SQL statements in session.
	1	internal SQL statements in session.
	15	SQL statements in session.
	5	statements EXPLAINed in this session.
********************************************************************************
Trace file: ora00204.trc
Trace file compatibility: 7.03.02
Sort options: default
	1	session in tracefile.
	14	user	SQL statements in trace file.
	1	internal SQL statements in trace file.
	15	SQL statements in trace file.
	12	unique SQL statements in trace file.
	5	SQL statements EXPLAINed using schema:
	FND.prof$plan_table
	Default table was used.
	Table was created.
	Table was dropped.
	270	lines in trace file.



tkprof80 [name_of_trace_file] output=[output_file_name] explain=[userid_password] sort=[sort_options]



prscnt	number of times parse was called
prscpu	cpu time parsing
prsela	elapsed time parsing
prsdsk	number of disk reads during parse
prsqry	number of buffers for consistent read during parse
prscu	number of buffers for current read during parse
prsmis	number of misses in library cache during parse
execnt	number of execute was called
execpu	cpu time spent executing
exeela	elapsed time executing
exedsk	number of disk reads during execute
exeqry	number of buffers for consistent read during execute
execu	number of buffers for current read during execute
exerow	number of rows processed during execute
exemis	number of library cache misses during execute
fchcnt	number of times fetch was called
fchcpu	cpu time spent fetching
fchela	elapsed time fetching
fchdsk	number of disk reads during fetch
fchqry	number of buffers for consistent read during fetch
fchcu	number of buffers for current read during fetch
fchrow	number of rows fetched



SQL> select sid, serial#
	2	from v$session
	3	where osuser = 'oradba';
SID	SERIAL#
------------- ----------------
	11	17
SQL>



SQL> execute dbms_system.set_sql_trace_in_session(11, 17, true);
PL/SQL procedure successfully completed.
SQL> 



dbms_session.set_sql_trace (true);



select substr(location,1,10) from plant_detail;



SQL> select distinct_keys, avg_leaf_blocks_per_key,
	2	avg_data_blocks_per_key, clustering_factor
	3	from user_indexes
	4	where table_name = 'GT_PERIODIC_DTL';
DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
------------- ----------------------- ----------------------- -----------------
	2492	1	1	2282
SQL>
SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
	2	from user_tables
	3	where table_name = 'GT_PERIODIC_DTL';
 NUM_ROWS	BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
--------- --------- ------------ --------- --------- -----------
	2492	59	0	364	0	36 
SQL>



select colc, substr(cold,1,30) from taba where cola >= 'ABC';
select colc, substr(cold,1,30) from taba where colb >= 'ABC';



SQL> create table address (
	2	addr_id number primary key,
	3	city varchar2(20));
Table created.
SQL> select * 
	2	from user_indexes 
	3	where table_name = 'ADDRESS';
SYS_C00846	NORMAL	FND
ADDRESS	TABLE	UNIQUE
FND	2	255	10240	10240
	1	121	50	1
	1	10 YES
	VALID
	1
1	NO	N Y DEFAULT
SQL> select * 
	2	from user_ind_columns 
	3	where table_name = 'ADDRESS';
SYS_C00846	ADDRESS
ADDR_ID
	1	22
SQL>



SQL> @utloidxs OCOMP ORG_COMP_TYPE
TABLE_NAME	COLUMN_NAME	STAT_NAME	STAT_VALUE
--------------- --------------- ------------------------------ -------------
OCOMP	ORG_COMP_TYPE	Rows - Null	0.00
OCOMP	ORG_COMP_TYPE	Rows - Total	205.00
OCOMP	ORG_COMP_TYPE	Rows per key - avg	51.25
OCOMP	ORG_COMP_TYPE	Rows per key - dev	77.94
OCOMP	ORG_COMP_TYPE	Rows per key - max	166.00
OCOMP	ORG_COMP_TYPE	Rows per key - min	1.00
OCOMP	ORG_COMP_TYPE	Total Distinct Keys	4.00
OCOMP	ORG_COMP_TYPE	db_gets_per_key_hit	70.07
OCOMP	ORG_COMP_TYPE	db_gets_per_key_miss20	140.14
9 rows selected.
SQL> @utldidxs OCOMP ORG_CCOMP_TYPE
TABLE_NAME	<_>COLUMN_NAME	BADNESS	KEYS_COUNT	ROW_PERCENT	KEY_PERCENT
--------------- -------------- ------------ ---------- ------------ ------------
OCOMP	ORG_COMP_TYPE	166	1	80.98	25.00
OCOMP	ORG_COMP_TYPE	34	1	16.59	25.00
OCOMP	ORG_COMP_TYPE	4	1	1.95	25.00
OCOMP	ORG_COMP_TYPE	1	1	0.49	25.00
4 rows selected.
SQL>



SQL> @utloidxs STATUS_HISTORY PIN
TABLE_NAME	COLUMN_NAME	STAT_NAME	STAT_VALUE
--------------- --------------- ------------------------------ -------------
STATUS_HISTORY	PIN	Rows - Null	0.00
STATUS_HISTORY	PIN	Rows - Total	2,288.00
STATUS_HISTORY	PIN	Rows per key - avg	1.87
STATUS_HISTORY	PIN	Rows per key - dev	1.21
STATUS_HISTORY	PIN	Rows per key - max	9.00
STATUS_HISTORY	PIN	Rows per key - min	1.00
STATUS_HISTORY	PIN	Total Distinct Keys	1,222.00
STATUS_HISTORY	PIN	db_gets_per_key_hit	1.59
STATUS_HISTORY	PIN	db_gets_per_key_miss	2.65
9 rows selected.
SQL> @utldidxs STATUS_HISTORY PIN
TABLE_NAME	COLUMN_NAME	BADNESS KEYS_COUNT ROW_PERCENT KEY_PERCENT
--------------- --------------- ---------- ---------- ----------- ----------
STATUS_HISTORY	PIN	9	2	0.79	0.16
STATUS_HISTORY	PIN	8	1	0.35 	  0.08
STATUS_HISTORY	PIN	7	9	2.75	0.74
STATUS_HISTORY	PIN	6	10	2.62	0.82
STATUS_HISTORY	PIN	5	30	6.56	2.46
STATUS_HISTORY	PIN	4	68	11.89	5.57
STATUS_HISTORY	PIN	3	125	16.39	10.23
STATUS_HISTORY	PIN	2	365	31.91	29.87
STATUS_HISTORY	PIN	1	612	26.75 	50.08
9 rows selected.



SQL> select * from v$lock;
ADDR	KADDR	SID TY	ID1	ID2 LMODE	REQUEST	CTIME	BLOCK
-------- -------- ---- -- ---- ---- ----- -------- ------ ------
02202D90 02202DA0	2 MR	5	0	4	0	12481	0
02202CDC 02202CEC	2 MR	3	0	4	0	20599	0
02202CA0 02202CB0	2 MR	2	0	4	0	20599	0
02202C64 02202C74	2 MR	1	0	4	0	20599	0
02202BEC 02202BFC	2 MR	4	0	4	0	20599	0
02202BB0 02202BC0	3 RT	1	0	6	0	20606	0
6 rows selected.
SQL>



9


SQL> desc sys.v_$filestat
 Name	Null?	Type
 ------------------------------- -------- ----
 FILE#	NUMBER
 PHYRDS	NUMBER
 PHYWRTS	NUMBER
 PHYBLKRD	NUMBER
 PHYBLKWRT	NUMBER
 READTIM	NUMBER
 WRITETIM	NUMBER
SQL> desc sys.v_$dbfile
 Name	Null?	Type
 ------------------------------- -------- ----
 FILE#	NUMBER
 NAME	VARCHAR2(513)
SQL>



SQL> select *
	2	from v$filestat;
	FILE#	PHYRDS	PHYWRTS	PHYBLKRD PHYBLKWRT	READTIM	WRITETIM
--------- --------- --------- --------- --------- --------- ---------
	1	2799	1443	2020	443	0	0
	2	1742	12787	1742	12787	0	0
	3	324	1624	324	1624	0	0
	4	0	0	0	0	0	0
	5	32	3228	162	3228	0	0
	6	483	1	881	1	0	0
	7	2	0	2	0	0	0
	8	355393	2367	469489	2367	0	0
8 rows selected.
SQL>



Name	Null?	Type
------------------------------- -------- ----
STATISTIC#	NUMBER
NAME	VARCHAR2(64)
CLASS	NUMBER
VALUE	NUMBER



Name	Null?	Type
------------------------------- -------- ----
NAMESPACE	VARCHAR2(15)
GETS	NUMBER
GETHITS	NUMBER
GETHITRATIO	NUMBER
PINS	NUMBER
PINHITS	NUMBER
PINHITRATIO	NUMBER
RELOADS	NUMBER
INVALIDATIONS	NUMBER
DLM_LOCK_REQUESTS	NUMBER
DLM_PIN_REQUESTS	NUMBER
DLM_PIN_RELEASES	NUMBER
DLM_INVALIDATION_REQUESTS	NUMBER
DLM_INVALIDATIONS	NUMBER



Name	Null?	Type
------------------------------- -------- ----
STATISTIC#	NUMBER
NAME	VARCHAR2(64)
CLASS	NUMBER
VALUE	NUMBER



Name	Null?	Type
------------------------------- -------- ----
USN	NUMBER
EXTENTS	NUMBER
RSSIZE	NUMBER
WRITES	NUMBER
XACTS	NUMBER
GETS	NUMBER
WAITS	NUMBER
OPTSIZE	NUMBER
HWMSIZE	NUMBER
SHRINKS	NUMBER
WRAPS	NUMBER
EXTENDS	NUMBER
AVESHRINK	NUMBER
AVEACTIVE	NUMBER
STATUS	VARCHAR2(15)
CUREXT	NUMBER
CURBLK	NUMBER



Name	Null?	Type
------------------------------- -------- ----
SESSIONS_MAX	NUMBER
SESSIONS_WARNING	NUMBER
SESSIONS_CURRENT	NUMBER
SESSIONS_HIGHWATER	NUMBER
USERS_MAX	NUMBER



Name	Null?	Type
------------------------------- -------- ----
SADDR	RAW(4)
SID	NUMBER
SERIAL#	NUMBER
AUDSID	NUMBER
PADDR	RAW(4)
USER#	NUMBER
USERNAME	VARCHAR2(30)
COMMAND	NUMBER
OWNERID	NUMBER
TADDR	VARCHAR2(8)
LOCKWAIT	VARCHAR2(8)
STATUS	VARCHAR2(8)
SERVER	VARCHAR2(9)
SCHEMA#	NUMBER
SCHEMANAME	VARCHAR2(30)
OSUSER	VARCHAR2(15)
PROCESS	VARCHAR2(9)
MACHINE	VARCHAR2(64)
TERMINAL	VARCHAR2(16)
PROGRAM	VARCHAR2(64)
TYPE	VARCHAR2(10)



Name	Null?	Type
------------------------------- -------- ----
SID	NUMBER
SEQ#	NUMBER
EVENT	VARCHAR2(64)
P1TEXT	VARCHAR2(64)
P1	NUMBER
P1RAW	RAW(4)
P2TEXT	VARCHAR2(64)
P2	NUMBER
P2RAW	RAW(4)
P3TEXT	VARCHAR2(64)
P3	NUMBER
P3RAW	RAW(4)
WAIT_TIME	NUMBER
SECONDS_IN_WAIT	NUMBER
STATE	VARCHAR2(19)



select substr(file_name,1,10) "Device",
	sum(phyrds) "Reads",
	sum(phywrts) "Writes"
	from sys.v_$filestat a, sys.dba_data_files b
 where a.file# = b.file_id
 group by substr(file_name,1,10)
 order by 1;


select distinct substr(file_name,1,10) "Device",
	tablespace_name "Tablespace" 
	from sys.dba_data_files order by 1;
	



10


user_cman = true



set echo off pages 0 feed off
var a number
var b number
begin
	:a := dbms_utility.get_time;
end;
/
. . .
. . .
. . .
-- The code goes in here for the activity to be timed.
. . .
. . .
. . .
begin
	:b := dbms_utility.get_time;
end;
/
print a
print b



elapsed time = :b - :a
	= (4317107 - 4317050) / 100 = .57 seconds



PROCEDURE USE_ROLLBACK_SEGMENT
 Argument Name	Type	In/Out	Default?
 ------------- ---- ------ -------- 
 RB_NAME	VARCHAR2	IN



SQL> execute dbms_transaction.use_rollback_segment ('MONSTER');



SQL> update finance
	2	set amt_owing = null
	3	where fin_id = 88771;
1 row updated.
SQL> set transaction use rollback segment fin_rb;
set transaction use rollback segment fin_rb
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
SQL> update finance 
	2	set amt_owing = null
	3	where fin_id = 88771;
1 row updated.



PROCEDURE SET_NLS
 Argument Name	Type	In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PARAM	VARCHAR2	IN/OUT
 VALUE	VARCHAR2	IN/OUT
PROCEDURE SET_ROLE
 Argument Name	Type	In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ROLE_CMD	VARCHAR2	IN/OUT
PROCEDURE SET_SQL_TRACE
 Argument Name	Type	In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_TRACE	BOOLEAN	IN/OUT



SQL> begin
	2	dbms_session.set_nls ('NLS_TERRITORY','FRANCE');
	3	end;
	4	/
PL/SQL procedure successfully completed.



SQL> select sysdate from dual;
SYSDATE
---------
06/09/97



SQL> begin
	2	dbms_session.set_nls ('NLS_TERRITORY','AMERICA');
	3	end;
	4	/
PL/SQL procedure successfully completed.



SQL> select sysdate from dual;
SYSDATE
---------
06-SEP-97



SQL> begin
	2	dbms_session.set_role ('DBTECH');
	3	end;
	4	/
PL/SQL procedure successfully completed.



begin dbms_session.set_role ('DBTEC'); end;
*
ERROR at line 1:
ORA-01919: role 'DBTEC' does not exist
ORA-06512: at "SYS.DBMS_SESSION", line 26
ORA-06512: at line 1



SQL> begin
	2	dbms_session.set_sql_trace (true);
	3	end;
	4	/
PL/SQL procedure successfully completed.
SQL> begin
	2	dbms_session.set_sql_trace (false);
	3	end;
	4	/
PL/SQL procedure successfully completed.



SQL> select 'dbverf80 '||file_name
	2	from sys.dba_data_files;
dbverf c:\orant\database\sys1orcl.ora
dbverf c:\orant\database\rbs1orcl.ora
dbverf c:\orant\database\usr1orcl.ora
dbverf c:\orant\database\tmp1orcl.ora
SQL>



C:\ORANT\DATABASE> dbverf80 sys1orcl.ora
DBVERIFY: Release 8.0.3.0.0 - Production on Lu Sep 8 9:0:16 1999
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
DBVERIFY - Verification starting : FILE = sys1orcl.ora
DBVERIFY - Verification complete
Total Pages Examined	: 30720
Total Pages Processed (Data) : 18315
Total Pages Failing	(Data) : 0
Total Pages Processed (Index): 6784
Total Pages Failing	(Index): 0
Total Pages Empty	: 0
Total Pages Marked Corrupt	: 0
Total Pages Influx	: 0
@
C:\ORANT\DATABASE> dbverf80 rbs1orcl.ora
DBVERIFY: Release 8.0.3.0.0 - Production on Lu Sep 8 9:0:46 1999
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
DBVERIFY - Verification starting : FILE = rbs1orcl.ora
DBVERIFY - Verification complete
Total Pages Examined	: 10240
Total Pages Processed (Data) : 0
Total Pages Failing	(Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing	(Index): 0
Total Pages Empty	: 0
Total Pages Marked Corrupt	: 0
Total Pages Influx	: 0
@
C:\ORANT\DATABASE> dbverf80 usr1orcl.ora
DBVERIFY: Release 8.0.3.0.0 - Production on Lu Sep 8 8:35:4 1997
(c) Copyright 1997 Oracle Corporation.	All rights reserved.
DBVERIFY - Verification starting : FILE = usr1orcl.ora
DBVERIFY - Verification complete
Total Pages Examined	: 1536
Total Pages Processed (Data) : 100
Total Pages Failing	(Data) : 0
Total Pages Processed (Index): 140
Total Pages Failing	(Index): 0
Total Pages Empty	: 0
Total Pages Marked Corrupt	: 0
Total Pages Influx	: 0
C:\ORANT\DATABASE>



SVRMGR> alter database create standby controlfile 'prd_stby.ctl';
Statement processed.
SVRMGR>



SVRMGR> alter system archive log current;
Statement processed.
SVRMGR>



SQL> select 'copy '||file_name||' d:\prodbkp\copy'
	2		from sys.dba_data_files
	3	union
	4	select 'copy '||member||' d:\prodbkp\copy'
	5		from v$logfile;
copy C:\ORANT\DATABASE\RBS1ORCL.ORA d:\prodbkp\copy	
copy C:\ORANT\DATABASE\USR1ORCL.ORA d:\prodbkp\copy	
copy C:\ORANT\DATABASE\TMP1ORCL.ORA d:\prodbkp\copy	
copy C:\ORANT\DATABASE\SYS1ORCL.ORA d:\prodbkp\copy
copy C:\ORANT\DATABASE\LOG1ORCL.ORA d:\prodbkp\copy
copy C:\ORANT\DATABASE\LOG2ORCL.ORA d:\prodbkp\copy
SQL>



SVRMGR> startup nomount
ORACLE instance started.
Total System Global Area	12071016 bytes
Fixed Size	46136 bytes
Variable Size	11090992 bytes
Database Buffers	409600 bytes
Redo Buffers	524288 bytes
SVRMGR> alter database mount standby database exclusive;
Statement processed.
SVRMGR> recover standby database;



db_file_name_convert = "H:\DATABASE\PRD", "F:\STANDBY\DBFILE"
log_file_name_convert = "J:\REDO\PRD" , "K:\STANDBY\REDO"



SVRMGR> alter database activate standby database;
Database altered.
SVRMGR>



f:\orasys\prod\redo\arc\arch



_%s.prd



f:\orasys\prod\redo\arc\arch_1287.prd



PROCEDURE ROWID_INFO
 Argument Name	Type	In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ROWID_IN	ROWID	IN/OUT
 ROWID_TYPE	NUMBER	IN/OUT
 OBJECT_NUMBER	NUMBER	IN/OUT
 RELATIVE_FNO	NUMBER	IN/OUT
 BLOCK_NUMBER	NUMBER	IN/OUT
 ROW_NUMBER	NUMBER	IN/OUT



set serveroutput on size 1000000
set echo on
begin
	declare
		cursor get_data is
			select rowid
				from finance
			where rownum < 41;
		rid	rowid;
		p2	number;
		p3	number;
		p4	number;
		p5	number;
		p6	number;
	begin
		open get_data;
		fetch get_data into rid;
		while get_data%found loop
			dbms_rowid.rowid_info (rid,p2,p3,p4,p5,p6);
			dbms_output.put_line ('ROWID is '||
	lpad(p5,4,0)||'.'||
	lpad(p6,4,0)||'.'||
	lpad(p4,4,0));
	fetch get_data into rid;
	end loop;
	end;
end;
/



ROWID is AAAAASAABAAAACoAAA or 0168.0000.0001
ROWID is AAAAASAABAAAACoAAB or 0168.0001.0001
ROWID is AAAAASAABAAAACoAAC or 0168.0002.0001
ROWID is AAAAASAABAAAACoAAD or 0168.0003.0001
ROWID is AAAAASAABAAAACoAAE or 0168.0004.0001
ROWID is AAAAASAABAAAACoAAF or 0168.0005.0001
ROWID is AAAAASAABAAAACoAAG or 0168.0006.0001
ROWID is AAAAASAABAAAACoAAH or 0168.0007.0001
ROWID is AAAAASAABAAAACoAAI or 0168.0008.0001
ROWID is AAAAASAABAAAACoAAJ or 0168.0009.0001
...
...
ROWID is AAAAASAABAAAACoAAc or 0168.0028.0001
ROWID is AAAAASAABAAAACoAAd or 0168.0029.0001
ROWID is AAAAASAABAAAACpAAA or 0169.0000.0001
ROWID is AAAAASAABAAAACpAAB or 0169.0001.0001
ROWID is AAAAASAABAAAACpAAC or 0169.0002.0001
ROWID is AAAAASAABAAAACpAAD or 0169.0003.0001
ROWID is AAAAASAABAAAACpAAE or 0169.0004.0001
ROWID is AAAAASAABAAAACpAAF or 0169.0005.0001
ROWID is AAAAASAABAAAACpAAG or 0169.0006.0001
ROWID is AAAAASAABAAAACpAAH or 0169.0007.0001
ROWID is AAAAASAABAAAACpAAI or 0169.0008.0001
ROWID is AAAAASAABAAAACpAAJ or 0169.0009.0001



SQL> create or replace function extoreg (rid in rowid) return varchar2 is
	2	p2 number;
	3	p3 number;
	4	p4 number;
	5	p5 number;
	6	p6 number;
	7	begin
	8	dbms_rowid.rowid_info (rid,p2,p3,p4,p5,p6);
	9	return lpad(p5,4,0)||'.'||lpad(p6,4,0)||'.'||lpad(p4,4,0);
 10	end;
 11	/
Function created.
SQL>



SQL> col ct format 999999999 head 'Row count
SQL> col ba format a20 head 'Block number'
SQL> select substr(extoreg(rowid),1,4) ba,
	2	count(*) ct
	3	from finance 
	4 where rownum < 211	-- Limit the length of output 
	5 group by substr(extoreg(rowid),1,4);
Block number	Row count
-------------------- ----------
0168	30
0169	30
0170	29
0171	27
0848	27
0849	27
0850	26
0851	14
8 rows selected.
SQL>



show parameters dump_dest



select tablespace_name,sum(bytes) from sys.dba_free_space
	group by tablespace_name;
select tablespace_name,max(bytes) from sys.dba_free_space
	group by tablespace_name;



TABLESPACE_NAME	SUM(BYTES)
------------------------------ ----------
BORIS_ABELFRANTRO	2678784
NANCY_BESDESMITH	18423808
FRANCIS_DEFWAYNO	10932224
NORMAN_NADROJIAN	40550400
ALL_INDEXES	4861952
AUDIT_APPS	1331200
COMMON	13897728
DESIDERATA	5734400
FINANCIAL	40550400
ALL_INDEXES	4505600



/* -------------------------------------------------------- */
/*	fspace.sql	*/
/*	*/
/*	Create rows in FSPACE for FREE_SPACE in the database.	*/
/*	*/
/*	Oracle8 Tuning	Corey & Abbey & Dechichio & Abramson	*/
/* -------------------------------------------------------- */
rem *	Create rows in FSPACE for today
insert into fspace
select a.tablespace_name,
	sum(a.bytes),	/*	Allocated from DBA_DATA_FILES	*/
	round(sum(b.bytes)),	/*	Free bytes from DBA_FREE_SPACE */
	'','',sysdate
	from sys.dba_data_files a,sys.dba_free_space b
 where a.tablespace_name = b.tablespace_name
 group by a.tablespace_name,'','',sysdate;
rem *	Yesterday's free space is in the rows from yesterday's
rem *	FREE_TODAY column.	The FREE_TODAY column values from
rem *	yesterday are moved into the FREE_YESTERDAY columns for
REM *	today's rows.
update fspace a
set free_yesterday =
	(select free_today
	from fspace b
	where a.tablespace_name = b.tablespace_name
	and to_char(b.system_date) = to_char(sysdate - 1))
where to_char(system_date) = to_char(sysdate);
rem *	The PERCENT_CHANGED is set to represent the following:
rem *	% change = free_today - free_yesterday / free_yesterday
rem *	expressed as a percentage.	The calculation has to use a
rem *	DECODE in case the amout of free space has not changed.
rem *	This avoids dividing by 0.
update fspace
	set percent_changed =round(decode(free_today-free_yesterday,
	0,0,	/* If no change, set PERCENT_CHANGED to zero	*/
	100*(free_today-free_yesterday)/
	(free_yesterday)),2)
 where to_char(system_date) = to_char(sysdate);
rem *	Print changed free space report for today.
col tablespace_name heading 'Tablespace'
col allocated heading 'Allocated' 999,999,990
col free_today heading 'Free today' form 999,999,990
col free_yesterday heading 'Yesterday form 999,999,990
col percent_changed heading 'Pct Ch' form 90.00
select tablespace_name, allocated, free_today, free_yesterday,
	percent_changed
	from fspace
 where to_char(system_date) = to_char(sysdate);



Tablespace	Allocated	Today	Yesterday	Pct Ch
------------------- ------------ ------------ ------------ -------
GRUNGE	209,715,200	92,160	92,160	0.00
AUDIT_APPS	73,400,320	2,678,784	2,678,784	0.00
PAFE	209,715,200	18,423,808	18,423,808	0.00
DESIDERATA	209,715,200	10,932,224	7,245,824	50.88
BAFF	838,860,800	83,886,080	83,886,080	0.00
FINANCIAL	104,857,600	40,550,400	40,796,160	-6.25
ALL_INDEXES	15,728,640	4,861,952	4,861,952	0.00
DESCAP	73,400,320	14,680,064	29,360,128	50.00



select username 
	from sys.dba_users 
 where temporary_tablespace = 'SYSTEM';



if last 2 digits of system_date >= 50
	if last 2 digits of date_entered < 50
	store century of system_date + 1
	else
	store century of system_date
	end if
elsif last 2 digits of date_entered < 50
	store century of system_date
	else
	store century of system_date - 1
	end if
end if



truncate table plant_detail;
truncate cluster plant_clust;



set transaction use rollback segment;



procedure get_items is
cursor my_cursor is
	select *
	from plant_detail
	where city_id = 12;
	temp_buffer plant_detail%rowtype;
begin
	open my_cursor;
	fetch my_cursor into temp_buffer;
	while my_cursor%found loop
	. . .
	. . .
	end loop;
	close my_cursor;
end;



procedure get_items is
the_cnt number;
begin
	select count(*)
	into the_cnt
	from plant_detail
	where city_id = 12;
end;



SQL> set serveroutput on size 1000000
SQL> set echo off trimsp on feed off pages 0
SQL> spool dbmsout
SQL> begin
	2	dbms_output.put_line ('Hello world.');
	3	end;
	4	/
PL/SQL procedure successfully completed.
SQL> spool off



/*
 ** FILE_TYPE - File handle
 */
 TYPE file_type IS RECORD (id BINARY_INTEGER);



f_handle utl_file.file_type;



utl_file_dir = c:\orant\utl_file
utl_file_dir = c:\utl_file\users
utl_file_dir = *



SQL> begin
	2	declare
	4	f_handle utl_file.file_type;
	5	begin
	6	f_handle := utl_file.fopen ('c:\','text','w');
	7	utl_file.put (f_handle,'Hey there, buddy, are you still there?');
	8	utl_file.fclose (f_handle);
	9	end;
 10	end;
 11	/
PL/SQL procedure successfully completed.
SQL>




/* ----------------------------------------------------------- */
/*	sens_sth_select	Mark Kerzner 1997	*/
/*	sensfan@sensfans.com	*/
/*	Illustrate the use of UTL_FILE against an Oracle8	*/
/*	database.	*/	
/*	*/
/*	Oracle8 Tuning	Corey & Abbey & Dechichio & Abramson	*/
/* ----------------------------------------------------------- */
create or replace procedure sens_sth_select (i_section_number in number,
	i_year in number) is
	l_sth_file_handle	utl_file.file_type;
	-- Naturally, these could be set after the BEGIN, but it is much 
	-- cuter to do it here. The value assigned to "l_sth_file_path" must
	-- be mentioned in the UTL_FILE_DIR entry in INIT.ORA unless the entry
	-- says UTL_FILE_DIR = * as explained elsewhere.
	l_sth_file_path	varchar2(20) default 'c:\orant\bin';
	l_sth_file_name	varchar2(20) default 'sth_list.dat';
	l_record_buffer	varchar2(163);
	cursor sth_cur is
	select accnt_num,lst_nme,frst_nme,cmpny_nme,addr_ln1, 
	addr_ln2, city, prvnc, pstl_cde, club_stts
	from sth
	where sth.sctn = i_section_number
	and sth.yr = i_year;
	begin
	begin
	-- Open the file for write.
	l_sth_file_handle := utl_file.fopen (l_sth_file_path, l_sth_file_name, 'w');
	for sth_rec in sth_cur loop
	if sth_rec.club_stts = 'GOLD' then
		-- Build the output line (fixed length fields).
	l_record_buffer := 
	lpad(sth_rec.accnt_num,10,0)||
	rpad(nvl(sth_rec.lst_nme,' '),20,' ')||
	rpad(nvl(sth_rec.frst_nme,' '),10,' ')||
	rpad(nvl(sth_rec.cmpny_nme,' '),40,' ')||
	rpad(sth_rec.addr_ln1,30,' ')||
	rpad(nvl(sth_rec.addr_ln2,' '),30,' ')||
	rpad(sth_rec.city,15,' ')||
	sth_rec.prvnc||
	rpad(nvl(sth_rec.pstl_cde,' '),6,' ');
	utl_file.put_line (l_sth_file_handle, l_record_buffer);
	end if;
	end loop;
	exception
	-- Trap and deal with exceptions. We highly recommend doing this
	-- yourself to avoid aborted procedures with very little information
	-- that's useful to the end user or the help desk.
	when utl_file.internal_error then
	-- LOG_ERR is another procedure that writes error text to
	-- an error file or a table from which errors will be dumped.
	log_err ('A',SQLCODE,'Internal Error with utl_file package');
	when utl_file.invalid_filehandle then
	log_err ('A',SQLCODE,'An Invalid filehandle was declared');
	when utl_file.invalid_mode then
	log_err ('A',SQLCODE,'Invalid mode error with utl_file package');
	when utl_file.invalid_operation then
	log_err ('A',SQLCODE,'Invalid operation error with package');
	when utl_file.invalid_path then
	log_err ('A',SQLCODE,'An invalid path was specified '||
	'for the output file');
	when utl_file.write_error THEN
	log_err ('A',SQLCODE,'An error occurred while attempting '||
	'to write to the output file');
	when others then
	log_err ('A',SQLCODE,NULL);
	end;
	utl_file.fclose (l_sth_file_handle);
end;
/



11


SQL> desc hot_data
 Name	Null?	Type
 ------------------------------- -------- ----
 CYCLE	NUMBER(1)
 TABLESPACE_NAME		VARCHAR2(30)
 FILE_NAME		VARCHAR2(100)
 BYTES		NUMBER
SQL> desc hot_write
 Name	Null?	Type
 ------------------------------- -------- ----
 CTR		NUMBER
 TEXT		VARCHAR2(100)



/* --------------------------------------------------------	*/
/*	hot_data.sql	*/
/*		*/
/*	Create rows in HOT_DATA with tablespace and data file	*/
/*	information.	*/
/*		*/
/*	Oracle8 Tuning	Corey & Abbey & Dechichio & Abramson	*/
/* --------------------------------------------------------	*/
set echo on term off pages 0 feed off
set serveroutput on size 1000000
begin
	declare
	high_end	number := 5368709120;
	accum	number;
	cycle	number := 1;
	file_row	sys.dba_data_files%rowtype;
	-- Get all the information about tablespaces and datafiles
	cursor get_data is
	select *
	from sys.dba_data_files
	order by tablespace_name,file_name;
	begin
	delete hot_data;
	commit;
	accum := 0;
	-- Loop through the information in DBA_DATA_FILES, inserting rows
	-- into HOT_DATA. When the ACCUM variable is larger than HIGH_END, the
	-- cycle is incremented and we carry on into the next cycle.
	for file_row in get_data loop
	accum := accum+file_row.bytes;
	if accum > high_end then
	cycle := cycle+1;
	accum := 0;
	end if;
	-- Put the cycle number, tablespace name, file name, and file size in
	-- HOT_DATA.
	insert into hot_data values
	(cycle,file_row.tablespace_name,
	file_row.file_name,file_row.bytes);
	end loop;
	end;
end;
/



CYCLE	TABLESPACE	FILE_NAME	BYTES
---------- ---------- ----------------------------------- ----------
	3	POX	/oradata10/masii/pox01.dbf	419430400
	3	QA	/oradata2/masii/qa01.dbf	10485760
	3	QAX	/oradata13/masii/qax01.dbf	10485760
	3	RBS	/oradata5/masii/rbs01.dbf	1677721600
	3	RBS1	/oradata6/masii/rbs101.dbf	1677721600
	3	RG	/oradata13/masii/rg01.dbf	10485760
	3	RG	/oradata13/masii/rg02.dbf	20971520
	3	RGX	/oradata14/masii/rgx01.dbf	10485760
	3	SYSTEM	/oradata1/masii/system01.dbf	524288000
	3	SYSTEM	/oradata1/masii/system02.dbf	262144000



/* --------------------------------------------------------	*/
/*	hot_write.sql	*/
/*		*/
/*	Create rows in HOT_WRITE using the information in	*/
/*	HOT_DATA. We use the HOT_WRITE table simply to format	*/
/*	and dump rows as the backup script is created.	*/
/*		*/
/*	Oracle8 Tuning	Corey & Abbey & Dechichio & Abramson	*/
/* --------------------------------------------------------	*/
@
truncate table hot_write;
begin
	declare
	t_bytes	number := 0;	-- Keeps track of tablespace bytes
	f_bytes	number := 0;	-- Keeps track of datafile bytes
	ctr	number := 0;
	ts_name	varchar2(30);
	f_name	varchar2(100);
	l_cycle	number := 1;
	cursor get_files is
	select file_name,bytes
	from hot_data
	where tablespace_name = ts_name;
	cursor get_ts is
	select distinct tablespace_name
	from hot_data
	where cycle = l_cycle;
	begin
	-- The next few lines place the UNIX RCS header information in the
	-- backup script. Each time we write to HOT_WRITE we increment a counter
	-- so we can get the rows back in order.
	ctr := ctr+1;
	insert into hot_write values (ctr,'#');
	ctr := ctr+1;
	insert into hot_write values (ctr,'# $Header$');
	ctr := ctr+1;
	insert into hot_write values (ctr,'#');
	ctr := ctr+1;
	insert into hot_write values (ctr,null);
	ctr := ctr+1;
	insert into hot_write values (ctr,'cd $HOME/backups');
	ctr := ctr+1;
	-- Write the line to place us in the correct directory.
	insert into hot_write values (ctr,'export ORACLE_HOME=/home/oracle');
	ctr := ctr+1;
	-- Set the ORACLE_SID properly.
	insert into hot_write values (ctr,'export ORACLE_SID=masii');
	ctr := ctr+1;
	insert into hot_write values (ctr,null);
	ctr := ctr+1;
	-- This line gets the last completed cycle number from the disk file 
	-- mentioned in numbered point 3 at the top of this "Hot Backup Cycles"
	-- section.
	into hot_write values (ctr,'cycle=`cat hot_cycle`');
	ctr := ctr+1;
	insert into hot_write values (ctr,null);
	for i in 1..3 loop
	ctr := ctr+1;
	-- Build the start of the IF statement.
	insert into hot_write values (ctr,'if [ "$cycle" = '||
	l_cycle||' ]; then');
	ctr := ctr+1;
	insert into hot_write values (ctr,null);
	ctr := ctr+1;
	insert into hot_write values (ctr,'	#');
	ctr := ctr+1;
	insert into hot_write values (ctr,'	# Cycle '||l_cycle);
	ctr := ctr+1;
	insert into hot_write values (ctr,'	#');
	open get_ts;
	fetch get_ts into ts_name;
	while get_ts%found loop
	-- Outer loop gets the name of all the tablespaces.
	ctr := ctr+1;
	insert into hot_write values (ctr,null);
	ctr := ctr+1;
	-- The next line will place the desired tablespace in backup mode.
	insert into hot_write
	values (ctr,'	echo "alter tablespace '||ts_name||
	' begin backup;" | $ORACLE_HOME/bin/sqlplus /');
	open get_files;
	fetch get_files into f_name,f_bytes;
	while get_files%found loop
	-- Inner loop gets all the file names and sizes for the tablespace
	-- selected in the outer loop.
	ctr := ctr+1;
	-- Create the line that copies the datafile to the disk backup
	-- location.
	insert into hot_write values (ctr,'	cp '||f_name||' /backups/hot');
	fetch get_files into f_name,f_bytes;
	t_bytes := t_bytes+f_bytes;
	end loop;
	ctr := ctr+1;
	-- The next line takes the desired tablespace out of backup mode.
	insert into hot_write
	values (ctr,'	echo "alter tablespace '||ts_name||
	' end backup;" | $ORACLE_HOME/bin/sqlplus /');
	close get_files;
	fetch get_ts into ts_name;
	end loop;
	ctr := ctr+1;
	insert into hot_write values (ctr,null);
	ctr := ctr+1;
	insert into hot_write values (ctr,'	#');
	ctr := ctr+1;
	-- This line simply writes out the size of the cycle just completed.
	insert into hot_write values (ctr,'	# Cycle size --- '||
	to_char(t_bytes,'99,999,999,990'));
	t_bytes := 0;
	ctr := ctr+1;
	insert into hot_write values (ctr,'	#');
	ctr := ctr+1;
	-- Write the end of if statement for UNIX.
	insert into hot_write values (ctr,'fi');
	ctr := ctr+1;
	insert into hot_write values (ctr,null);
	-- Having just completed a cycle, go on to the next.
	l_cycle := l_cycle+1;
	close get_ts;
	end loop;
	end;
end;
/
spool off
set echo off
spool hot_backup.sh
select text
	from hot_write
 order by ctr;
prompt if [ "$cycle" = 1 ]; then
select '	echo 2 > $HOME/backups/hot_cycle' from dual;
prompt elsif [ "$cycle" = 2 ]; then
select '	echo 3 > $HOME/backups/hot_cycle' from dual;
prompt else
select '	echo 1 > $HOME/backups/hot_cycle' from dual;
prompt fi
spool off



#
# $Header$
#
cd $HOME/backups
export ORACLE_HOME=/home/oracle
export ORACLE_SID=masii
cycle=`cat hot_cycle`
if [ "$cycle" = 1 ]; then
	#
	# Cycle 1
	#
	echo "alter tablespace AP begin backup;" | $ORACLE_HOME/bin/sqlplus /
	cp /oradata9/masii/ap01.dbf /backups/hot
	echo "alter tablespace AP end backup;" | $ORACLE_HOME/bin/sqlplus /
	echo "alter tablespace APX begin backup;" | $ORACLE_HOME/bin/sqlplus /
	cp /oradata10/masii/apx01.dbf /backups/hot
	echo "alter tablespace APX end backup;" | $ORACLE_HOME/bin/sqlplus /
...
...
	echo "alter tablespace GL begin backup;" | $ORACLE_HOME/bin/sqlplus /
	cp /oradata7/masii/gl01.dbf /backups/hot
	cp /oradata7/masii/gl02.dbf /backups/hot
	echo "alter tablespace GL end backup;" | $ORACLE_HOME/bin/sqlplus /
	#
	# Cycle size ---	4,206,886,912
	#
fi
@
if [ "$cycle" = 2 ]; then
	#
	# Cycle 2
	#
...
...



SQL> select owner "Owner", segment_name "Segment Name",
	2	segment_type "Type", tablespace_name "Tablespace",
	3	extents "#Ext", max_extents "Max"
	4	from sys.dba_segments
	5	where extents > 5
	6	and owner not in ('SYS','SYSTEM')
	7	order by owner,segment_name;
Owner	Segment Name	Type	Tablespace	#Ext	Max
-----------------	------------------	--------	-----------------	----	----
USER1	ACC_TABLE	TABLE	TBSP_TESTONE	7	99
USER1	TBL_SECONDS	TABLE	TBSP_TESTONE	7	99
USER1	ACTORS	TABLE	TBSP_TESTONE	16	120
USER1	XFERS	TABLE	TBSP_TESTONE	6	120
USER2	HISTORY_FILE	TABLE	TBSP_TESTTWO	8	120
USER2	HISTORY_INDEX	INDEX	TBSP_TESTTWO	9	120
USER2	TEMP2	INDEX	TBSP_TESTTWO	8	120
USER3	FORM	TABLE	TBSP_TEST3	13	120
USER3	MENU	TABLE	TBSP_TEST3	21	120
USER3	REPORT	TABLE	TBSP_TEST3	8	120
10 rows selected.
SQL>



SQL> select segment_name, extents
	2	from sys.dba_segments
	3	where table_name = 'MY_TABLE' and owner = 'USER1';
SEGMENT_NAME	EXTENTS
------------------ -------
MY_TABLE	101
SQL>



SEGMENT_NAME	EXTENTS
------------------ -------
MY_TABLE	1
SQL>



create or replace function num_ext (object_name in varchar2)
	return number is
	m_extid	number;
	begin
	--
	-- Each table's initial allocation is tracked in USER_EXTENTS as extent
	-- with EXTENT_ID = 0. Thus, we add 1 to the largest extent ID to get
	-- the number of extents in use. If a table had 4 extents, the EXTENT_IDs
	-- would be 0 1 2 and 3. This function also assumes that every object 
	-- name (regardless of object type) is unique within schema. Hence, if
	-- a schema has a table called STADIUM, it will not have a SEQUENCE with
	-- the same name.
	--
	select max(extent_id)+1
	into m_extid
	from user_extents
	where segment_name = upper(object_name);
	return m_extid;
	end;
/



SQL> /* --------------------------------------------------------	*/
SQL> /*	exinuse.sql	*/
SQL> /*		*/
SQL> /*	Report on tables and indexes whose extent allocation is	*/
SQL> /*	within the specified maximum number allowed for the	*/
SQL> /*	object.	*/
SQL> /*		*/
SQL> /*	Oracle8 Tuning@Corey & Abbey & Dechichio & Abramson	*/
SQL> /* --------------------------------------------------------	*/
SQL> set pages 20 echo off ver off trimsp on
SQL> col eiu	form 999 head 'In Use'
SQL> col table_name	form a30 head 'Table name'
SQL> col index_name	form a30 head 'Index name'
SQL> col max_extents form 999 head 'MExtents'
SQL> repheader 'Tables within 2 extents of max allowed ...' skip 2
SQL> select table_name,max_extents, num_ext(table_name) eiu
	2	from user_tables
	3	where max_extents - num_ext(table_name) < 3;
Table name	MExtents In Use
------------------------- -------------- ------
NEPTUNE	12	10
FIXEMUP	99	98
SQL> repheader 'Indexes within 2 extents of max allowed ...' skip 2
SQL> select index_name,max_extents, num_ext(index_name) eiu
	2	from user_indexes
	3	where max_extents - num_ext(index_name) < 3;
Index name	MExtents In Use
------------------------- -------------- ------
NEPTUNE_PK	48	46
SQL>



SQL> select *
	2	from sys.dba_free_space
	2	where tablespace_name = 'USERS7'
	3	order by block_id;
TABLESPACE_NAME	FILE_ID	BLOCK_ID	BYTES	BLOCKS
---------------- ---------- ---------- ---------- ----------
USERS7	16	817	49152	12
USERS7	16	829	356352	87
USERS7	16	5359	1024000	250
USERS7	16	5609	409600	100
USERS7	16	5709	565248	138
USERS7	16	8463	13897728	3393
@
6 rows selected.



TABLESPACE_NAME	FILE_ID	BLOCK_ID	BYTES	BLOCKS
---------------- ---------- ---------- ---------- ----------
USERS7	16	817	405504	99
USERS7	16	5359	1998848	488
USERS7	16	8463	13897728	3393
@
3 rows selected.



SQL> set echo off pages 0 trimsp off feed off
SQL> spool coalesce
SQL> select 'alter tablespace '||tablespace_name||' coalesce;'
	2	from sys.dba_tablespaces
	3	where tablespace_name not in ('TEMP','ROLLBACK');
alter tablespace SYSTEM coalesce;
alter tablespace REP_DATA1 coalesce;
alter tablespace REP_INDEX1 coalesce;
alter tablespace CENTRAL coalesce;
SQL> spool off
SQL> set echo on feed on
SQL> @coalesce.lst
SQL> alter tablespace SYSTEM coalesce;
Tablespace altered.
SQL> alter tablespace REP_DATA1 coalesce;
Tablespace altered.
SQL> alter tablespace REP_INDEX1 coalesce;
Tablespace altered.
SQL> alter tablespace CENTRAL coalesce;
Tablespace altered.
SQL>



SQL> select b.file_id "File #",
	2	b.tablespace_name "Tablespace name",
	3	b.bytes "# bytes",
	4	(b.bytes - sum(nvl(a.bytes,0))) "# used",
	5	sum(nvl(a.bytes,0)) "# free",
	6	(sum(nvl(a.bytes,0))/(b.bytes))*100 "%free"
	7	from sys.dba_free_space a, sys.dba_data_files b
	8	where a.file_id(+) = b.file_id
	9	group by b.tablespace_name, b.file_id, b.bytes
	10	order by b.tablespace_name;
File#	Tablespace Name	# Bytes	# Used	# Free	%Free
-----	-------------------------	-----------	-----------	-----------	-----
	1	SYSTEM	104857600	26503168	78354432	74.7
	3	TBSP_INDEX001	83886080	78610432	5275648	6.3
	9	TBSP_INDEX001	10485760	6907904	3577856	34.1
	5	TBSP_PROD001	3145728	2048	3143680	99.9
	6	TBSP_PROD002	5242880	3381248	1861632	35.5
	7	TBSP_PROD003	52428800	50563792	1835008	3.5
	8	TBSP_PROD004	5242880	2021376	3221504	61.4
	2	TBSP_ROLLBACK	157286400	47310848	109975552	69.9
	4	TBSP_USER_TEMP	31457280	2048	31455232	100.0
	10	TBSP_WORK	31457280	11300864	20156416	64.1



select username "Username", created "Created",
	substr(granted_role,1,15) "Roles",
	default_tablespace "Default TS",
	temporary_tablespace "Temporary TS"
	from sys.dba_users, sys.dba_role_privs
 where username = grantee (+)
 order by username;



	USER LIST
PAGE: 1	DATE: 09-SEP-99

Username	Created	Roles	Default TS	Temporary TS
--------------	--------	---------------	---------------	---------------
ADAMS	03/18/99	CONNECT	TBSP_WORKDEV	TBSP_TEMPORARY
FORMS30	03/18/99	CONNECT	TBSP_WORKDEV	TBSP_TEMPORARY
FORMS30	03/18/99	RESOURCE	TBSP_WORKDEV	TBSP_TEMPORARY
OPS$USER1	03/18/99	CONNECT	SYSTEM	SYSTEM	
OPS$USER1	03/18/98	DBA	SYSTEM	SYSTEM	
OPS$USER1	03/18/97	RESOURCE	SYSTEM	SYSTEM	
OPS$USER002	03/18/98	CONNECT	TBSP_WORKDEV	TBSP_TEMPORARY
OPS$APPLIC1	03/18/98	CONNECT	TBSP_DATA001	TBSP_TEMPORARY
OPS$APPLIC1	03/18/99	RESOURCE	TBSP_DATA001	TBSP_TEMPORARY
SYS	03/17/98	CONNECT	SYSTEM	SYSTEM	
SYS	03/17/98	DBA	SYSTEM	SYSTEM	
SYS	03/17/98	EXP_FULL_DATABA	SYSTEM	SYSTEM	
SYS	03/17/98	IMP_FULL_DATABA	SYSTEM	SYSTEM	
SYS	03/17/98	RESOURCE	SYSTEM	SYSTEM



set echo off feed off pages 0
spool veedollar_dba.sql
select 'grant select on '||view_name||' to public;'
	from sys.dba_views
 where view_name like 'V_$%'
	or view_name like 'DBA_%;
set echo on feed on
@veedollar_dba



ORA-04031: unable to allocate num bytes of shared memory num, num, num
@
Cause: More shared memory is needed than was allocated in the operating system
process. SGA private memory has been exhausted.
@
Action: Either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value set for the SHARED_POOL_SIZE initialization parameter.



SQL> /* --------------------------------------------------------	*/
SQL> /*	spsizing.sql	*/
SQL> /*		*/
SQL> /*	Report on shared pool sizing for entered number of	*/
SQL> /*	concurrent users using a user logged on as a sample of	*/
SQL> /*	the amount of memory that will be used per session.	*/
SQL> /*		*/
SQL> /*	Oracle8 Tuning@@Corey & Abbey & Dechichio & Abramson	*/
SQL> /* --------------------------------------------------------	*/
set echo off ver off feed off pages 0
@
rem * You are prompted for the Oracle ID of someone currently
rem * logged on. That person's memory consumption will be used
rem * as a sample amount. You are also asked for the # of
rem * concurrent users to base this calculation on.
@
accept username prompt 'User to use?? '
accept numusers prompt '# of users ?? '
@
rem * Get that user's session identifier by joining v$process
rem * and v$session matching the ADDR column from v$process
rem * against the PADDR column from v$session, and matching
rem * the USERNAME column from v$session against the username
rem * entered before
set term off
col a new_value snum
select sid a
	from v$process p, v$session s
 where p.addr = s.paddr
	and s.username = upper('&username');
@
rem * Now that we have the sample user's session
rem * ID, we can go to v$sesstat for the amount of memory
rem * that user is consuming.	We use STATISTIC# = 16 which is
rem * the MAX SESSION MEMORY per user maintained in
rem * v$sesstat for each user connected to the database.
@
col b new_value pumem
select value b
	from v$sesstat
 where statistic# = 16
	and sid = &snum;
@
rem * Get the amount of memory in the shared pool that is
rem * currently in use (i.e., the size of the SQL sitting in the
rem * shared pool).
col c new_value spl
select sum(sharable_mem) c
	from v$sqlarea;
rem * Using the following formula, make the optimal shared
rem * pool size calculation.
rem * optimal size = 1.3 * (per_user_memory * number _users +
	size_of_sql_in_pool)
col d new_value size1
col e new_value size2
select (&pumem*&numusers+&spl) d,
	(&pumem*&numusers+&spl)+3/10*(&pumem*&numusers+&spl) e
	from dual;
@
col pmem form 99,999,990
col nu	like pmem
col sss	like pmem
col tmu	like pmem
col s1	like pmem
col s2	like pmem
set term on
prompt
prompt
prompt
prompt ===================================================
select 'Per user memory requirement:	', &pumem pmem
	from dual;
select 'Number of users	:	', &numusers nu
	from dual;
prompt ===================================================
select 'Total memory for users	:	', &numusers*&pumem tmu
	from dual;
select 'Size of stuff in shared SQL:	', &spl sss
	from dual;
prompt ===================================================
select 'Base shared pool size	:	', &size1 s1
	from dual;
select 'Pool size with 30% free	:	', &size2 s2
	from dual;
prompt ===================================================



SQL> @pool
User to use?? ops$jonespg
# of users ?? 30
===================================================
Per user memory requirement:	198,116
Number of users	:	30
===================================================
Total memory for users	:	5,943,480
Size of stuff in shared SQL:	10,360,432
===================================================
Base shared pool size	:	16,303,912
Pool size with 30% free	:	21,195,086
===================================================



select decode(state,0,'FREE',
	1,'Read and Modified',
	2,'Read and Non-Modified',
	4,'Current Block Read','Other'),count(*)
	from x$bh
 group by decode(state,0,'FREE',
	1,'Read and Modified',
	2,'Read and Non-Modified',
	4,'Current Block Read','Other');
@
DECODE(STATE,0,'FREE'	COUNT(*)
--------------------- ---------
FREE	62
Other	20
Read and Modified	118



alter database backup controlfile to trace;



Dump file C:\ORANT\RDBMS80\trace\ORA00220.TRC
Tue Sep 09 14:26:03 1999
ORACLE V8.0.3.0.0 - Production vsnsta=0
vsnsql=c vsnxtr=3
Windows NT V4.0, OS V5.101, CPU type 586
Oracle8 Enterprise Edition Release 8.0.3.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.3.0.0 - Production
Windows NT V4.0, OS V5.101, CPU type 586
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 10
pid: dc
Tue Sep 09 14:26:03 1999
Tue Sep 09 14:26:03 1999
*** SESSION ID:(10.700) 1999.09.09.14.26.03.993
# The following commands will create a new control file and use it
# to open the database.
# Data used by the recovery manager will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current version of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORACLE" NORESETLOGS NOARCHIVELOG
	MAXLOGFILES 32
	MAXLOGMEMBERS 2
	MAXDATAFILES 32
	MAXINSTANCES 16
	MAXLOGHISTORY 1630
LOGFILE
	GROUP 1 'C:\ORANT\DATABASE\LOG2ORCL.ORA'	SIZE 200K,
	GROUP 2 'C:\ORANT\DATABASE\LOG1ORCL.ORA'	SIZE 200K
DATAFILE
	'C:\ORANT\DATABASE\SYS1ORCL.ORA',
	'C:\ORANT\DATABASE\USR1ORCL.ORA',
	'C:\ORANT\DATABASE\RBS1ORCL.ORA',
	'C:\ORANT\DATABASE\TMP1ORCL.ORA',
	'FND.DBF'
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;



create table as select



Name	Null?	Type
------------------ -------- ----
FY_CODE	NOT NULL	VARCHAR2(5)
JOB_NUM	NOT NULL	VARCHAR2(6)
PROJ_NUM	NOT NULL	NUMBER(2)
SDESC_E		VARCHAR2(30)
SDESC_F		VARCHAR2(30)
LDESC_E		VARCHAR2(60)
LDESC_F		VARCHAR2(60)
BUDGET_HOURS		NUMBER(7,2)
STATUS	VARCHAR2(2)



rem * Create a temp table with the new column name.
create table jobs_temp (fy_code, job_num, proj_num, sdesc_e, sdesc_f,
	ldesc_e, ldesc_f, bud_hours, status)
as select * from jobs;
rem * swap names of tables.
rename jobs to jobs_old;
rename jobs_temp to jobs;



select owner, table_name, column_name
	from sys.dba_tab_columns
 where column_name = 'BUDGET_HOURS';



drop user charroel;



set echo off pages 0 feed off trimsp on
spool nukeuser.sql
select 'drop '||object_type||' '||owner||'.'||object_name||';'
	from sys.dba_objects
 where object_type in ('VIEW','SEQUENCE','SYNONYM')
	and owner = 'CHARROEL';
select 'drop ' ||object_type||' '||owner||'.'||object_name|| 
	'cascade constraints;'
	from sys.dba_objects
 where object_type =	'TABLE'
	and owner = 'CHARROEL';
spool off



spool ts_quotas.sql
select 'alter user quota ' || max_bytes || ' on ' || tablespace_name || ';'
	from sys.dba_ts_quotas
 where nvl(max_bytes,0) > 0;
spool off



set pages 0 feed off echo off
spool yr_trans.parfile
prompt userid=system/manager
prompt file=yr_trans
prompt buffer=10240000
prompt indexes=y
prompt grants=y
prompt owner=(
select unique owner || ','
	from sys.dba_tables
 where tablespace_name = 'YR_TRANS'
	and	owner <>
	(select max(owner)
	from sys.dba_tables 
	where tablespace_name = 'YR_TRANS');
select max(owner) || ')'
	from sys.dba_tables 
where tablespace_name = 'YR_TRANS';
spool off



select 'create rollback segment '||segment_name||chr(10),
	'	tablespace '||tablespace_name||chr(10),
	'	storage (initial	'||initial_extent||chr(10),
	'	next	'||next_extent||chr(10),
	'	minextents	'||min_extents||chr(10),
	'	maxextents	'||max_extents||');'
	from sys.dba_rollback_segs a,v$rollstat b,v$rollname c
	where segment_name <> 'SYSTEM'
	and b.usn = c.usn
	and a.segment_name = c.name;



/* --------------------------------------------------------	*/
/*	mpkg.sql	*/
/*		*/
/*	Receive the package name and the owner on the command	*/
/*	on the command line.	*/
/*		*/
/*	Oracle8 Tuning@@Corey & Abbey & Dechichio & Abramson	*/
/* --------------------------------------------------------	*/
set space 0 ver off pages 0 feed off echo off trimsp on lines 999
def package_name = "&1"
def owner = "&2"
spool package.sql
prompt /*
prompt
prompt SQL to recreate &&owner.'s PACKAGE &&package_name
prompt
prompt */
prompt
prompt set echo on feed on
prompt spool package
prompt
select decode(line,1,'create or replace '||text,text)
	from sys.dba_source a
 where type = 'PACKAGE'
	and name = upper('&&package_name')
	and owner = upper ('&&owner')
 order by line;
prompt /
prompt
select decode(line,1,'create or replace '||text,text)
	from sys.dba_source
 where type = 'PACKAGE BODY'
	and name = upper('&&package_name')
	and owner = upper ('&&owner')
 order by line;
prompt /
prompt spool off
spool off



/* --------------------------------------------------------	*/
/*	mpfn.sql	*/
/*		*/
/*	Receive the object name and the owner on the command	*/
/*	on the command line.	*/
/*		*/
/*	Oracle8 Tuning@@Corey & Abbey & Dechichio & Abramson	*/
/* --------------------------------------------------------	*/
def prfn_name = "&1"
def owner = "&2"
spool prfn
prompt
prompt /*
prompt SQL to recreate &&owner.'s PROCEDURE or FUNCTION &&prfn_name
prompt
prompt */
prompt
prompt set echo on feed on
prompt spool prfn
prompt
select rtrim(decode(line, 1,'create or replace '||text, text)) col1
	from sys.dba_source
 where owner = upper('&&owner')
	and name = upper('&&prfn_name')
 order by line; 
prompt /
prompt spool off
spool off



/* --------------------------------------------------------	*/
/*	recomp.sql	*/
/*		*/
/*	Write an SQL*Plus script to recreate the stored	*/
/*	objects for an entire schema.	*/
/*		*/
/*	Oracle8 Tuning@@Corey & Abbey & Dechichio & Abramson	*/
/* --------------------------------------------------------	*/
set echo off feed off pages 0
spool recomp.do
select distinct 'alter '||type||' '||name||' compile;'
	from user_source
 where type <> 'PACKAGE BODY';
spool off
set echo on feed on
spool recomp
@recomp.do
spool off



alter package WEMBLEY compile;
alter function YORN compile;
alter procedure DISNEY compile;



t^A


 Name	Null?	Type
 ------------------------------- -------- ----
 OS_USERNAME		VARCHAR2(255)
 USERNAME		VARCHAR2(30)
 USERHOST		VARCHAR2(128)
 TERMINAL		VARCHAR2(255)
 TIMESTAMP	NOT NULL	DATE
 OWNER		VARCHAR2(30)
 OBJ_NAME		VARCHAR2(128)
 ACTION_NAME		VARCHAR2(27)
 NEW_OWNER		VARCHAR2(30)
 NEW_NAME		VARCHAR2(128)
 SES_ACTIONS		VARCHAR2(19)
 COMMENT_TEXT		VARCHAR2(4000)
 SESSIONID	NOT NULL	NUMBER
 ENTRYID	NOT NULL	NUMBER
 STATEMENTID	NOT NULL	NUMBER
 RETURNCODE	NOT NULL	NUMBER
 PRIV_USED		VARCHAR2(40)
 OBJECT_LABEL		RAW MLSLABEL
 SESSION_LABEL		RAW MLSLABEL



 Name	Null?	Type
 ------------------------------- -------- ----
 OWNER		VARCHAR2(30)
 SEGMENT_NAME		VARCHAR2(81)
 PARTITION_NAME		VARCHAR2(30)
 SEGMENT_TYPE		VARCHAR2(17)
 TABLESPACE_NAME		VARCHAR2(30)
 EXTENT_ID	NOT NULL	NUMBER
 FILE_ID	NOT NULL	NUMBER
 BLOCK_ID	NOT NULL	NUMBER
 BYTES		NUMBER
 BLOCKS	NOT NULL	NUMBER
 RELATIVE_FNO	NOT NULL	NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 TABLESPACE_NAME	NOT NULL	VARCHAR2(30)
 FILE_ID	NOT NULL	NUMBER
 BLOCK_ID	NOT NULL	NUMBER
 BYTES		NUMBER
 BLOCKS	NOT NULL	NUMBER
 RELATIVE_FNO	NOT NULL	NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 OWNER	NOT NULL	VARCHAR2(30)
 NAME	NOT NULL	VARCHAR2(30)
 TYPE		VARCHAR2(12)
 LINE	NOT NULL	NUMBER
 TEXT		VARCHAR2(4000)



 Name	Null?	Type
 ------------------------------- -------- ----
 OWNER	NOT NULL	VARCHAR2(30)
 SYNONYM_NAME	NOT NULL	VARCHAR2(30)
 TABLE_OWNER		VARCHAR2(30)
 TABLE_NAME	NOT NULL	VARCHAR2(30)
 DB_LINK		VARCHAR2(128)



 Name	Null?	Type
 ------------------------------- -------- ----
 OWNER	NOT NULL	VARCHAR2(30)
 TABLE_NAME	NOT NULL	VARCHAR2(30)
 TABLESPACE_NAME		VARCHAR2(30)
 CLUSTER_NAME		VARCHAR2(30)
 IOT_NAME		VARCHAR2(30)
 PCT_FREE		NUMBER
 PCT_USED		NUMBER
 INI_TRANS		NUMBER
 MAX_TRANS		NUMBER
 INITIAL_EXTENT		NUMBER
 NEXT_EXTENT		NUMBER
 MIN_EXTENTS		NUMBER
 MAX_EXTENTS		NUMBER
 PCT_INCREASE		NUMBER
 FREELISTS		NUMBER
 FREELIST_GROUPS		NUMBER
 LOGGING		VARCHAR2(3)
 BACKED_UP		VARCHAR2(1)
 NUM_ROWS		NUMBER
 BLOCKS		NUMBER
 EMPTY_BLOCKS		NUMBER
 AVG_SPACE		NUMBER
 CHAIN_CNT		NUMBER
 AVG_ROW_LEN		NUMBER
 AVG_SPACE_FREELIST_BLOCKS		NUMBER
 NUM_FREELIST_BLOCKS		NUMBER
 DEGREE		VARCHAR2(10)
 INSTANCES		VARCHAR2(10)
 CACHE		VARCHAR2(5)
 TABLE_LOCK		VARCHAR2(8)
 SAMPLE_SIZE		NUMBER
 LAST_ANALYZED		DATE
 PARTITIONED		VARCHAR2(3)
 IOT_TYPE		VARCHAR2(12)
 TEMPORARY		VARCHAR2(1)
 NESTED		VARCHAR2(3)
 BUFFER_POOL		VARCHAR2(7)



 Name	Null?	Type
 ------------------------------- -------- ----
 HEIGHT		NUMBER
 BLOCKS		NUMBER
 NAME		VARCHAR2(30)
 PARTITION_NAME		VARCHAR2(30)
 LF_ROWS		NUMBER
 LF_BLKS		NUMBER
 LF_ROWS_LEN		NUMBER
 LF_BLK_LEN		NUMBER
 BR_ROWS		NUMBER
 BR_BLKS		NUMBER
 BR_ROWS_LEN		NUMBER
 BR_BLK_LEN		NUMBER
 DEL_LF_ROWS		NUMBER
 DEL_LF_ROWS_LEN		NUMBER
 DISTINCT_KEYS		NUMBER
 MOST_REPEATED_KEY		NUMBER
 BTREE_SPACE		NUMBER
 USED_SPACE		NUMBER
 PCT_USED		NUMBER
 ROWS_PER_KEY		NUMBER
 BLKS_GETS_PER_ACCESS		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 REPEAT_COUNT		NUMBER
 KEYS_WITH_REPEAT_COUNT		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 OBJ#	NOT NULL	NUMBER
 DATAOBJ#		NUMBER
 OWNER#	NOT NULL	NUMBER
 NAME	NOT NULL	VARCHAR2(30)
 NAMESPACE	NOT NULL	NUMBER
 SUBNAME		VARCHAR2(30)
 TYPE#	NOT NULL	NUMBER
 CTIME	NOT NULL	DATE
 MTIME	NOT NULL	DATE
 STIME	NOT NULL	DATE
 STATUS	NOT NULL	NUMBER
 REMOTEOWNER		VARCHAR2(30)
 LINKNAME		VARCHAR2(128)
 FLAGS		NUMBER
 OID$		RAW(16)
 SPARE1		NUMBER
 SPARE2		NUMBER
 SPARE3		NUMBER
 SPARE4		VARCHAR2(1000)
 SPARE5		VARCHAR2(1000)
 SPARE6		DATE



 Name	Null?	Type
 ------------------------------- -------- ----
 SEGMENT_NAME		VARCHAR2(81)
 PARTITION_NAME		VARCHAR2(30)
 SEGMENT_TYPE		VARCHAR2(17)
 TABLESPACE_NAME		VARCHAR2(30)
 EXTENT_ID	NOT NULL	NUMBER
 BYTES		NUMBER
 BLOCKS	NOT NULL	NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 NAME		VARCHAR2(30)
 COLUMN_NAME		VARCHAR2(30)
 COLUMN_POSITION		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 NAME	NOT NULL	VARCHAR2(30)
 TYPE		VARCHAR2(12)
 LINE	NOT NULL	NUMBER
 TEXT		VARCHAR2(4000)







 Name	Null?	Type
 ------------------------------- -------- ----
 INDEX_NAME	NOT NULL	VARCHAR2(30)
 PARTITION_NAME		VARCHAR2(30)
 HIGH_VALUE		LONG
 HIGH_VALUE_LENGTH	NOT NULL	NUMBER
 PARTITION_POSITION	NOT NULL	NUMBER
 STATUS		VARCHAR2(8)
 TABLESPACE_NAME	NOT NULL	VARCHAR2(30)
 PCT_FREE	NOT NULL	NUMBER
 INI_TRANS	NOT NULL	NUMBER
 MAX_TRANS	NOT NULL	NUMBER
 INITIAL_EXTENT		NUMBER
 NEXT_EXTENT		NUMBER
 MIN_EXTENT	NOT NULL	NUMBER
 MAX_EXTENT	NOT NULL	NUMBER
 PCT_INCREASE	NOT NULL	NUMBER
 FREELISTS		NUMBER
 LOGGING		VARCHAR2(3)
 BLEVEL		NUMBER
 LEAF_BLOCKS		NUMBER
 DISTINCT_KEYS		NUMBER
 AVG_LEAF_BLOCKS_PER_KEY		NUMBER
 AVG_DATA_BLOCKS_PER_KEY		NUMBER
 CLUSTERING_FACTOR		NUMBER
 NUM_ROWS		NUMBER
 SAMPLE_SIZE		NUMBER
 LAST_ANALYZED		DATE
 BUFFER_POOL		VARCHAR2(7)



 Name	Null?	Type
 ------------------------------- -------- ----
 TABLE_NAME	NOT NULL	VARCHAR2(30)
 PARTITION_NAME		VARCHAR2(30)
 COLUMN_NAME		VARCHAR2(30)
 NUM_DISTINCT		NUMBER
 LOW_VALUE		RAW(32)
 HIGH_VALUE		RAW(32)
 DENSITY		NUMBER
 NUM_NULLS		NUMBER
 NUM_BUCKETS		NUMBER
 SAMPLE_SIZE		NUMBER
 LAST_ANALYZED		DATE



 Name	Null?	Type
 ------------------------------- -------- ----
 TABLE_NAME	NOT NULL	VARCHAR2(30)
 COLUMN_NAME	NOT NULL	VARCHAR2(30)
 NUM_DISTINCT		NUMBER
 LOW_VALUE		RAW(32)
 HIGH_VALUE		RAW(32)
 DENSITY		NUMBER
 NUM_NULLS		NUMBER
 NUM_BUCKETS		NUMBER
 LAST_ANALYZED		DATE
 SAMPLE_SIZE		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 TABLE_NAME	NOT NULL	VARCHAR2(30)
 PARTITION_NAME		VARCHAR2(30)
 HIGH_VALUE		LONG
 HIGH_VALUE_LENGTH	NOT NULL	NUMBER
 PARTITION_POSITION	NOT NULL	NUMBER
 TABLESPACE_NAME	NOT NULL	VARCHAR2(30)
 PCT_FREE	NOT NULL	NUMBER
 PCT_USED	NOT NULL	NUMBER
 INI_TRANS	NOT NULL	NUMBER
 MAX_TRANS	NOT NULL	NUMBER
 INITIAL_EXTENT		NUMBER
 NEXT_EXTENT		NUMBER
 MIN_EXTENT	NOT NULL	NUMBER
 MAX_EXTENT	NOT NULL	NUMBER
 PCT_INCREASE	NOT NULL	NUMBER
 FREELISTS		NUMBER
 FREELIST_GROUPS		NUMBER
 LOGGING		VARCHAR2(3)
 NUM_ROWS		NUMBER
 BLOCKS		NUMBER
 EMPTY_BLOCKS		NUMBER
 AVG_SPACE		NUMBER
 CHAIN_CNT		NUMBER
 AVG_ROW_LEN		NUMBER
 SAMPLE_SIZE		NUMBER
 LAST_ANALYZED		DATE
 BUFFER_POOL		VARCHAR2(7)



 Name	Null?	Type
 ------------------------------- -------- ----
 RECID		NUMBER
 STAMP		NUMBER
 NAME		VARCHAR2(513)
 THREAD#		NUMBER
 SEQUENCE#		NUMBER
 RESETLOGS_CHANGE#		NUMBER
 RESETLOGS_TIME		DATE
 FIRST_CHANGE#		NUMBER
 FIRST_TIME		DATE
 NEXT_CHANGE#		NUMBER
 NEXT_TIME		DATE
 BLOCKS		NUMBER
 BLOCK_SIZE		NUMBER
 ARCHIVED		VARCHAR2(3)
 DELETED		VARCHAR2(3)
 COMPLETION_TIME		DATE



 Name	Null?	Type
 ------------------------------- -------- ----
 FILE#		NUMBER
 CREATION_CHANGE#		NUMBER
 CREATION_TIME		DATE
 TS#		NUMBER
 RFILE#		NUMBER
 STATUS		VARCHAR2(7)
 ENABLED		VARCHAR2(10)
 CHECKPOINT_CHANGE#		NUMBER
 CHECKPOINT_TIME		DATE
 UNRECOVERABLE_CHANGE#		NUMBER
 UNRECOVERABLE_TIME		DATE
 LAST_CHANGE#		NUMBER
 LAST_TIME		DATE
 OFFLINE_CHANGE#		NUMBER
 ONLINE_CHANGE#		NUMBER
 ONLINE_TIME		DATE
 BYTES		NUMBER
 BLOCKS		NUMBER
 CREATE_BYTES		NUMBER
 BLOCK_SIZE		NUMBER
 NAME		VARCHAR2(513)



 Name	Null?	Type
 ------------------------------- -------- ----
 FILE#		NUMBER
 PHYRDS		NUMBER
 PHYWRTS		NUMBER
 PHYBLKRD		NUMBER
 PHYBLKWRT		NUMBER
 READTIM		NUMBER
 WRITETIM		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 ADDR		RAW(4)
 LATCH#		NUMBER
 LEVEL#		NUMBER
 NAME		VARCHAR2(64)
 GETS		NUMBER
 MISSES		NUMBER
 SLEEPS		NUMBER
 IMMEDIATE_GETS		NUMBER
 IMMEDIATE_MISSES		NUMBER
 WAITERS_WOKEN		NUMBER
 WAITS_HOLDING_LATCH		NUMBER
 SPIN_GETS		NUMBER
 SLEEP1		NUMBER
 SLEEP2		NUMBER
 SLEEP3		NUMBER
 SLEEP4		NUMBER
 SLEEP5		NUMBER
 SLEEP6		NUMBER
 SLEEP7		NUMBER
 SLEEP8		NUMBER
 SLEEP9		NUMBER
 SLEEP10		NUMBER
 SLEEP11		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 NAMESPACE		VARCHAR2(15)
 GETS		NUMBER
 GETHITS		NUMBER
 GETHITRATIO		NUMBER
 PINS		NUMBER
 PINHITS		NUMBER
 PINHITRATIO		NUMBER
 RELOADS		NUMBER
 INVALIDATIONS		NUMBER
 DLM_LOCK_REQUESTS		NUMBER
 DLM_PIN_REQUESTS		NUMBER
 DLM_PIN_RELEASES		NUMBER
 DLM_INVALIDATION_REQUESTS		NUMBER
 DLM_INVALIDATIONS		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 GROUP#		NUMBER
 THREAD#		NUMBER
 SEQUENCE#		NUMBER
 BYTES		NUMBER
 MEMBERS		NUMBER
 ARCHIVED		VARCHAR2(3)
 STATUS		VARCHAR2(16)
 FIRST_CHANGE#		NUMBER
 FIRST_TIME		DATE



 Name	Null?	Type
 ------------------------------- -------- ----
 RECID		NUMBER
 STAMP		NUMBER
 THREAD#		NUMBER
 SEQUENCE#		NUMBER
 FIRST_CHANGE#		NUMBER
 FIRST_TIME		DATE
 NEXT_CHANGE#		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 GROUP#		NUMBER
 STATUS		VARCHAR2(7)
 MEMBER		VARCHAR2(513)



 Name	Null?	Type
 ------------------------------- -------- ----
 STATISTIC		VARCHAR2(30)
 LAST_QUERY		NUMBER
 SESSION_TOTAL		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 STATISTIC		VARCHAR2(30)
 VALUE		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 DFO_NUMBER		NUMBER
 TQ_ID		NUMBER
 SERVER_TYPE		VARCHAR2(10)
 NUM_ROWS		NUMBER
 BYTES		NUMBER
 OPEN_TIME		NUMBER
 AVG_LATENCY		NUMBER
 WAITS		NUMBER
 TIMEOUTS		NUMBER
 PROCESS		VARCHAR2(10)
 INSTANCE		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 CACHE#		NUMBER
 TYPE		VARCHAR2(11)
 SUBORDINATE#		NUMBER
 PARAMETER		VARCHAR2(32)
 COUNT		NUMBER
 USAGE		NUMBER
 FIXED		NUMBER
 GETS		NUMBER
 GETMISSES		NUMBER
 SCANS		NUMBER
 SCANMISSES		NUMBER
 SCANCOMPLETES		NUMBER
 MODIFICATIONS		NUMBER
 FLUSHES		NUMBER
 DLM_REQUESTS		NUMBER
 DLM_CONFLICTS		NUMBER
 DLM_RELEASES		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 SID		NUMBER
 STATISTIC#		NUMBER
 VALUE		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 POOL		VARCHAR2(11)
 NAME		VARCHAR2(26)
 BYTES		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 SQL_TEXT		VARCHAR2(1000)
 SHARABLE_MEM		NUMBER
 PERSISTENT_MEM		NUMBER
 RUNTIME_MEM		NUMBER
 SORTS		NUMBER
 VERSION_COUNT		NUMBER
 LOADED_VERSIONS		NUMBER
 OPEN_VERSIONS		NUMBER
 USERS_OPENING		NUMBER
 EXECUTIONS		NUMBER
 USERS_EXECUTING		NUMBER
 LOADS		NUMBER
 FIRST_LOAD_TIME		VARCHAR2(19)
 INVALIDATIONS		NUMBER
 PARSE_CALLS		NUMBER
 DISK_READS		NUMBER
 BUFFER_GETS		NUMBER
 ROWS_PROCESSED		NUMBER
 COMMAND_TYPE		NUMBER
 OPTIMIZER_MODE		VARCHAR2(25)
 PARSING_USER_ID		NUMBER
 PARSING_SCHEMA_ID		NUMBER
 KEPT_VERSIONS		NUMBER
 ADDRESS		RAW(4)
 HASH_VALUE		NUMBER
 MODULE		VARCHAR2(64)
 MODULE_HASH		NUMBER
 ACTION		VARCHAR2(64)
 ACTION_HASH		NUMBER
 SERIALIZABLE_ABORTS		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 ADDRESS		RAW(4)
 HASH_VALUE		NUMBER
 COMMAND_TYPE		NUMBER
 PIECE		NUMBER
 SQL_TEXT		VARCHAR2(64)



 Name	Null?	Type
 ------------------------------- -------- ----
 STATISTIC#		NUMBER
 NAME		VARCHAR2(64)
 CLASS		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 STATISTIC#		NUMBER
 NAME		VARCHAR2(64)
 CLASS		NUMBER
 VALUE		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 COMPONENT		VARCHAR2(8)
 TYPE		VARCHAR2(8)
 DESCRIPTION		VARCHAR2(32)
 TYPE_SIZE		NUMBER



 Name	Null?	Type
 ------------------------------- -------- ----
 CLASS		VARCHAR2(18)
 COUNT		NUMBER
 TIME		NUMBER



t^C



autoextend on default storage (initial 100k next 100k pctincrease 1
	minextents 1 maxextents 300)




