cmclean.sql

REM
REM FILENAME
REM cmclean.sql
REM DESCRIPTION
REM Clean out the concurrent manager tables
REM NOTES
REM Usage: sqlplus @cmclean
REM
REM
REM $Id: cmclean.sql,v 1.4 2001/04/07 15:55:07 pferguso Exp $
REM
REM
REM +======================================================================+


set verify off;
set head off;
set timing off
set pagesize 1000

column manager format a20 heading 'Manager short name'
column pid heading 'Process id'
column pscode format a12 heading 'Status code'
column ccode format a12 heading 'Control code'
column request heading 'Request ID'
column pcode format a6 heading 'Phase'
column scode format a6 heading 'Status'


WHENEVER SQLERROR EXIT ROLLBACK;

DOCUMENT

WARNING : Do not run this script without explicit instructions
from Oracle Support


*** Make sure that the managers are shut down ***
*** before running this script ***

*** If the concurrent managers are NOT shut down, ***
*** exit this script now !! ***

#

accept answer prompt 'If you wish to continue type the word ''dual'': '

set feed off
select null from &answer;
set feed on


REM Update process status codes to TERMINATED

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating invalid process status codes in FND_CONCURRENT_PROCESSES
set feedback off
set head on
break on manager

SELECT concurrent_queue_name manager,
concurrent_process_id pid,
process_status_code pscode
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE process_status_code not in ('K', 'S')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id;

set head off
set feedback on
UPDATE fnd_concurrent_processes
SET process_status_code = 'K'
WHERE process_status_code not in ('K', 'S');



REM Set all managers to 0 processes

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating running processes in FND_CONCURRENT_QUEUES
prompt -- Setting running_processes = 0 and max_processes = 0 for all managers

UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;




REM Reset control codes

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating invalid control_codes in FND_CONCURRENT_QUEUES
set feedback off
set head on
SELECT concurrent_queue_name manager,
control_code ccode
FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

set feedback on
set head off
UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

REM Also null out target_node for all managers
UPDATE fnd_concurrent_queues
SET target_node = null;


REM Set all 'Terminating' requests to Completed/Error
REM Also set Running requests to completed, since the managers are down

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating any Running or Terminating requests to Completed/Error
set feedback off
set head on
SELECT request_id request,
phase_code pcode,
status_code scode
FROM fnd_concurrent_requests
WHERE status_code = 'T' OR phase_code = 'R'
ORDER BY request_id;

set feedback on
set head off
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'E'
WHERE status_code ='T' OR phase_code = 'R';





REM Set all Runalone flags to 'N'
REM This has to be done differently for Release 10

prompt
prompt ------------------------------------------------------------------------

prompt -- Updating any Runalone flags to 'N'
prompt
set serveroutput on
set feedback off
declare
c pls_integer := dbms_sql.open_cursor;
upd_rows pls_integer;
vers varchar2(50);
tbl varchar2(50);
col varchar2(50);
statement varchar2(255);
begin

select substr(release_name, 1, 2)
into vers
from fnd_product_groups;

if vers >= 11 then
tbl := 'fnd_conflicts_domain';
col := 'runalone_flag';
else
tbl := 'fnd_concurrent_conflict_sets';
col := 'run_alone_flag';
end if;


statement := 'update ' || tbl || ' set ' || col || '=''N'' where ' || col || ' = ''Y''';
dbms_sql.parse(c, statement, dbms_sql.native);
upd_rows := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
dbms_output.put_line('Updated ' || upd_rows || ' rows of ' || col || ' in ' || tbl || ' to ''N''');
end;
/



prompt

prompt ------------------------------------------------------------------------

prompt Updates complete.
prompt Type commit now to commit these updates, or rollback to cancel.
prompt ------------------------------------------------------------------------

prompt

set feedback on

REM <= Last REM statment -----------------------------------------------------

Enable FRD in 11i

How to Enable FRD trace in 11i.

1. Make the user value for profile option 'ICX: Forms Launcher' the same as the site value
e.g. copy value of ICX: Forms Launcher for the site Test115
'http://testserver.oracle.com:8005/dev60cgi/f60cgi'
to be the value for the user.
2. Append the user value of ICX: Forms Launcher with the Forms parameters for FRD
e.g. update the value of ICX: Forms Launcher for the user to be
'http://testserver.oracle.com:8005/dev60cgi/f60cgi?record=all&log=/tmp/username_frd.log
3. Login to the Personal Home Page, navigate to the form and perform the steps that you wish to trace.
4. Locate the file in the directory set by variable $FORMS60_TRACE_PATH. Note: the default directory for
$FORMS60_TRACE_PATH is $ORACLE_HOME/forms60/log.

Autoconfig steps in ecsprod

BEFORE AUTOCONFIG

Internal and External Nodes

· Save context files
· cp $TNS_ADMIN/tnsnames.ora $TNS_ADMIN/tnsnames.ora.preauto
· cp $FND_SECURE/ecsprod.dbc $FND_SECURE/ecsprod.dbc.preauto
· Save jserv.properties and zone.properties
· Add xml entry for smtp host if it is not done already.
· Commentout cron jobs on rh132 and rh133

Internal Nodes

· cp $OA_HTML/bin/appsweb_$CONTEXT_NAME.cfg $OA_HTML/bin/appsweb_$CONTEXT_NAME.cfg.preauto
· cp $COMMON_TOP/admin/scripts/$CONTEXT_NAME/adfrmctl.sh $COMMON_TOP/admin/scripts/$CONTEXT_NAME/adfrmctl.sh.preauto
(this is needed because the DIRTMP environment variable is customized and cannot be set in the context file)


External Nodes

· cp $IAS_CONFIG_HOME/Apache/Apache/conf/url_fw.conf $IAS_CONFIG_HOME/Apache/Apache/conf/url_fw.conf.preauto


Database

· Rename workflow tables and create temporary workflow tables from the original workflow tables.



AFTER AUTOCONFIG

Internal and External Nodes

· Take out the entries created for the database services from the tnsnames.ora file – they already exist in the include file


Internal Nodes

· put back the Markview entries in the .cfg files - this step should be eliminated with Begin/End Customization or some other methodology - in all environments we should have .cfg.170 files that include the Markview entries and are not overwritten by autoconfig
· make sure "ICX: Forms Launcher" has "?config=170systems" appended to the end for Site Level and all the internal server level- this step will be eliminated as well - John has the solution
· create new _reporting.dbc DBC file from $FND_SECURE similar to DBC file used for discoverer (check with Matt) - this should use the _reporting service
· send _reporting.dbc to the Discoverer admins (Jason Dimmer) + Discoverer middle tier admins (Krishnan)
· fix CITRIX ADI by putting in the new server ID form the DBC file - once this is done inform Skip and he will propagate the change to all users
· make sure the workflow configurations are were not lost – recreate them if they were
· make sure that in the file $COMMON_TOP/admin/scripts/$CONTEXT_NAME/adfrmctl.sh the environment variable DIRTMP is set to the pre-autoconfig value


External Nodes

· jdbc entry in the .dbc file should use the external service

APPS_JDBC_URL=jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=usmliu112-vip.arrow.com)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=usmliu111-vip.arrow.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ecsprod_external)))
· Copy jserv.properties from backup for double-click fix.
· Replace this section from the $IAS_CONFIG_HOME/Apache/Apache/conf/url_fw.conf

#======================================================================
#Include URLs for product OIR (iReceivables)
#======================================================================

#RewriteRule ^/OA_HTML/AnonymousLogin\.jsp$ - [L]
#RewriteRule ^/OA_HTML/SelfRegistration\.jsp$ - [L]

WITH:

#======================================================================
#Include URLs for product OIR (iReceivables)
#======================================================================

RewriteRule ^/OA_HTML/AnonymousLogin\.jsp$ - [L]
RewriteRule ^/OA_HTML/SelfRegistration\.jsp$ - [L]
RewriteRule ^/OA_CGI/FNDWRR\.exe$ - [L]

· Prepare new DBC file for reporting with the reporting service if needed – follow the above example and use ecsprod_reporting instead of ecsprod_external.
· Fix broken link between GFS and Ascent if we change the apps password.


Database

· Rename backup copy of workflow tables to actual names.

vas startup/shutdown

VASPROD Instance Start Procedure:

Database Node : USMLRS540
Application Node : USMLRS541

1) Login to USMLRS540

[oracle@usmlrs540 ~]$

2) Run Environment file

[oracle@usmlrs540 ~]$ . ./vasprod_usmlrs540.env
[oracle@usmlrs540 ~]$

3) Start VASPROD database and listener.

[oracle@usmlrs540 ~]$ sqlplus / as sysdba

SQL>startup;

[oracle@usmlrs540 ~]$lsnrctl start vasprod

4) Now Login to Application tier USMLRS541

Last login: Thu Sep 2 02:58:58 2010 from 10.18.92.42

###############################
VASPROD AppTier Env Sourced
###############################

[applmgr@usmlrs541 ~]$

5) Go to scripts directory ( In R12 all start/stop scripts reside in INST_TOP)

[applmgr@usmlrs541 ~]$ cd $INST_TOP/admin/scripts

[applmgr@usmlrs541 scripts]$ ls
adalnctl.sh adautocfg.sh adexecsql.pl adformsctl.sh adoacorectl.sh adopmnctl.sh adstpall.sh gsmstart.sh java.sh msc mwactlwrpr.sh
adapcctl.sh adcmctl.sh adforms-c4wsctl.sh adformsrvctl.sh adoafmctl.sh adpreclone.pl adstrtal.sh ieo jtffmctl.sh mwactl.sh sqlnet.log
[applmgr@usmlrs541 scripts]$

6) Run start script as follows.

[applmgr@usmlrs541 scripts]$ sh adstrtal.sh apps/

7) Check Login URL: http://usmlrs541.arrow.com:8000


VASPROD Instance Stop Procedure:

1) Login to Application tier USMLRS541

Last login: Thu Sep 2 02:58:58 2010 from 10.18.92.42

###############################
VASPROD AppTier Env Sourced
###############################

[applmgr@usmlrs541 ~]$

2) Go to scripts directory

[applmgr@usmlrs541 ~]$ cd $INST_TOP/admin/scripts

[applmgr@usmlrs541 scripts]$ ls
adalnctl.sh adautocfg.sh adexecsql.pl adformsctl.sh adoacorectl.sh adopmnctl.sh adstpall.sh gsmstart.sh java.sh msc mwactlwrpr.sh
adapcctl.sh adcmctl.sh adforms-c4wsctl.sh adformsrvctl.sh adoafmctl.sh adpreclone.pl adstrtal.sh ieo jtffmctl.sh mwactl.sh sqlnet.log
[applmgr@usmlrs541 scripts]$

3) Run stop script as follows.

[applmgr@usmlrs541 scripts]$ sh adstpall.sh apps/

4) Login to USMLRS540

[oracle@usmlrs540 ~]$

5) Run Environment file

[oracle@usmlrs540 ~]$ . ./vasprod_usmlrs540.env
[oracle@usmlrs540 ~]$

6) Stop VASPROD database and listener

[oracle@usmlrs540 ~]$lsnrctl stop vasprod

[oracle@usmlrs540 ~]$sqlplus / as sysdba

SQL> shutdown immediate;

partition to non-partition table

Main Conversion

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('QP','QP_LIST_HEADERS_B',2);


PL/SQL procedure successfully completed.

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool a.log

SELECT DBMS_METADATA.GET_DDL('TABLE','QP_PRICING_ATTRIBUTES','QP') FROM DUAL;

make the changes.
· Change the name to add “_nopart”. This will be the interim table.
· Remove all the “not null” constraints
· remove the not required the partition clause

once the _nopart is created.

SQL> EXEC dbms_redefinition.start_redef_table('QP','QP_LIST_HEADERS_B','QP_LIST_HEADERS_B_NOPART',NULL,2);

PL/SQL procedure successfully completed.

SQL> VARIABLE NUM_ERRORS NUMBER;
SQL> exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('QP','QP_LIST_HEADERS_B','QP_LIST_HEADERS_B_NOPART',1,TRUE,TRUE,TRUE,TRUE,:NUM_ERRORS,FALSE);

PL/SQL procedure successfully completed.

SQL> PRINT NUM_ERRORS

NUM_ERRORS
----------
1

SQL> SHOW ERROR
No errors.

SQL>
SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('QP','QP_LIST_HEADERS_B','QP_LIST_HEADERS_B_NOPART');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('QP','QP_LIST_HEADERS_B','QP_LIST_HEADERS_B_NOPART');

PL/SQL procedure successfully completed.




Checks

SQL> Select partition_name, high_value from DBA_tab_partitions where table_name='QP_LIST_HEADERS_B';

no rows selected


SQL> DESC QP.QP_LIST_HEADERS_B;
Name Null? Type
----------------------------------------- -------- ----------------------------
LIST_HEADER_ID NUMBER
CREATION_DATE DATE
CREATED_BY NUMBER
LAST_UPDATE_DATE DATE
LAST_UPDATED_BY NUMBER
LAST_UPDATE_LOGIN NUMBER
PROGRAM_APPLICATION_ID NUMBER
PROGRAM_ID NUMBER
PROGRAM_UPDATE_DATE DATE
REQUEST_ID NUMBER
LIST_TYPE_CODE VARCHAR2(30)
START_DATE_ACTIVE DATE
END_DATE_ACTIVE DATE
AUTOMATIC_FLAG VARCHAR2(1)
CURRENCY_CODE VARCHAR2(30)
ROUNDING_FACTOR NUMBER
SHIP_METHOD_CODE VARCHAR2(30)
FREIGHT_TERMS_CODE VARCHAR2(30)
TERMS_ID NUMBER
COMMENTS VARCHAR2(2000)
DISCOUNT_LINES_FLAG VARCHAR2(1)
GSA_INDICATOR VARCHAR2(1)
PRORATE_FLAG VARCHAR2(30)
SOURCE_SYSTEM_CODE VARCHAR2(30)
ASK_FOR_FLAG VARCHAR2(1)
ACTIVE_FLAG VARCHAR2(1)
PARENT_LIST_HEADER_ID NUMBER
START_DATE_ACTIVE_FIRST DATE
END_DATE_ACTIVE_FIRST DATE
ACTIVE_DATE_FIRST_TYPE VARCHAR2(30)
START_DATE_ACTIVE_SECOND DATE
END_DATE_ACTIVE_SECOND DATE
ACTIVE_DATE_SECOND_TYPE VARCHAR2(30)
CONTEXT VARCHAR2(30)
ATTRIBUTE1 VARCHAR2(240)
ATTRIBUTE2 VARCHAR2(240)
ATTRIBUTE3 VARCHAR2(240)
ATTRIBUTE4 VARCHAR2(240)
ATTRIBUTE5 VARCHAR2(240)
ATTRIBUTE6 VARCHAR2(240)
ATTRIBUTE7 VARCHAR2(240)
ATTRIBUTE8 VARCHAR2(240)
ATTRIBUTE9 VARCHAR2(240)
ATTRIBUTE10 VARCHAR2(240)
ATTRIBUTE11 VARCHAR2(240)
ATTRIBUTE12 VARCHAR2(240)
ATTRIBUTE13 VARCHAR2(240)
ATTRIBUTE14 VARCHAR2(240)
ATTRIBUTE15 VARCHAR2(240)
LIMIT_EXISTS_FLAG VARCHAR2(1)
MOBILE_DOWNLOAD VARCHAR2(1)
CURRENCY_HEADER_ID NUMBER
PTE_CODE VARCHAR2(30)
LIST_SOURCE_CODE VARCHAR2(30)
ORIG_SYSTEM_HEADER_REF VARCHAR2(50)
ORIG_ORG_ID NUMBER
GLOBAL_FLAG VARCHAR2(1)
SHAREABLE_FLAG VARCHAR2(1)
SOLD_TO_ORG_ID NUMBER
LOCKED_FROM_LIST_HEADER_ID NUMBER

SQL> DESC DBA_INDEXES;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
INDEX_TYPE VARCHAR2(27)
TABLE_OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLE_TYPE VARCHAR2(11)
UNIQUENESS VARCHAR2(9)
COMPRESSION VARCHAR2(8)
PREFIX_LENGTH NUMBER
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)
NUM_ROWS NUMBER
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
DEGREE VARCHAR2(40)
INSTANCES VARCHAR2(40)
PARTITIONED VARCHAR2(3)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
BUFFER_POOL VARCHAR2(7)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
PCT_DIRECT_ACCESS NUMBER
ITYP_OWNER VARCHAR2(30)
ITYP_NAME VARCHAR2(30)
PARAMETERS VARCHAR2(1000)
GLOBAL_STATS VARCHAR2(3)
DOMIDX_STATUS VARCHAR2(12)
DOMIDX_OPSTATUS VARCHAR2(6)
FUNCIDX_STATUS VARCHAR2(8)
JOIN_INDEX VARCHAR2(3)
IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)
DROPPED VARCHAR2(3)

SQL> select privilege, grantee from dba_tab_privs where table_name='QP_LIST_HEADERS_B';

PRIVILEGE GRANTEE
---------------------------------------- ------------------------------
ALTER APPS
DELETE APPS
INDEX APPS
INSERT APPS
SELECT XXBI_OWNER
SELECT OECA_ORDER_ENTRY
SELECT OEX0_ORDER_ENTRY
SELECT ARW_READ_ONLY
SELECT OEUS_ORDER_ENTRY
SELECT NOETIX_SYS
SELECT OEON1_ORDER_ENTRY
SELECT APPS
UPDATE APPS
REFERENCES APPS
ON COMMIT REFRESH APPS
QUERY REWRITE APPS
DEBUG APPS
FLASHBACK APPS

18 rows selected.

SQL> SELECT * FROM DBA_INDEXES WHERE TABLE_NAME='QP_LIST_HEADERS_B';

OWNER INDEX_NAME
------------------------------ ------------------------------
INDEX_TYPE TABLE_OWNER
--------------------------- ------------------------------
TABLE_NAME TABLE_TYPE UNIQUENES COMPRESS PREFIX_LENGTH
------------------------------ ----------- --------- -------- -------------
TABLESPACE_NAME INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT
------------------------------ ---------- ---------- -------------- -----------
MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS
----------- ----------- ------------ ------------- -------------- ----------
FREELIST_GROUPS PCT_FREE LOG BLEVEL LEAF_BLOCKS DISTINCT_KEYS
--------------- ---------- --- ---------- ----------- -------------
AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS
----------------------- ----------------------- ----------------- --------
NUM_ROWS SAMPLE_SIZE LAST_ANAL DEGREE
---------- ----------- --------- ----------------------------------------
INSTANCES PAR T G S BUFFER_ USE DURATION
---------------------------------------- --- - - - ------- --- ---------------
PCT_DIRECT_ACCESS ITYP_OWNER ITYP_NAME
----------------- ------------------------------ ------------------------------
PARAMETERS
--------------------------------------------------------------------------------
GLO DOMIDX_STATU DOMIDX FUNCIDX_ JOI IOT DRO
--- ------------ ------ -------- --- --- ---
QP QP_LIST_HEADERS_B_N9
NORMAL QP
QP_LIST_HEADERS_B TABLE NONUNIQUE DISABLED
APPS_TS_TX_IDX 11 255 131072 131072
1 2147483645 0
10 YES 2 2014 331590
1 1 77931 VALID
331590 331590 28-JAN-11 1
1 NO N N N DEFAULT NO


NO NO NO NO

QP QP_LIST_HEADERS_B_N10
NORMAL QP
QP_LIST_HEADERS_B TABLE NONUNIQUE DISABLED
APPS_TS_TX_IDX 11 255 131072 131072
1 2147483645 0
10 YES 1 2 358
1 1 286 VALID
358 358 28-JAN-11 1
1 NO N N N DEFAULT NO


NO NO NO NO

QP QP_LIST_HEADERS_B_N6
NORMAL QP
QP_LIST_HEADERS_B TABLE NONUNIQUE DISABLED
APPS_TS_TX_IDX 11 255 131072 131072
1 2147483645 0
10 YES 2 715 2
357 4738 9477 VALID
331590 331590 28-JAN-11 1
1 NO N N N DEFAULT NO


NO NO NO NO

QP QP_LIST_HEADERS_B_N7
NORMAL QP
QP_LIST_HEADERS_B TABLE NONUNIQUE DISABLED
APPS_TS_TX_IDX 11 255 131072 131072
1 2147483645 0
10 YES 2 1045 331590
1 1 78800 VALID
331590 331590 28-JAN-11 1
1 NO N N N DEFAULT NO


NO NO NO NO

QP QP_LIST_HEADERS_B_N1
NORMAL QP
QP_LIST_HEADERS_B TABLE NONUNIQUE DISABLED
APPS_TS_TX_IDX 11 255 131072 131072
1 2147483645 0
10 YES 2 1426 331590
1 1 77931 VALID
331590 331590 28-JAN-11 1
1 NO N N N DEFAULT NO


NO NO NO NO

QP QP_LIST_HEADERS_B_N2
NORMAL QP
QP_LIST_HEADERS_B TABLE NONUNIQUE DISABLED
APPS_TS_TX_IDX 11 255 131072 131072
1 2147483645 0
10 YES 2 1046 331590
1 1 78413 VALID
331590 331590 28-JAN-11 1
1 NO N N N DEFAULT NO


NO NO NO NO

QP QP_LIST_HEADERS_B_N3
NORMAL QP
QP_LIST_HEADERS_B TABLE NONUNIQUE DISABLED
APPS_TS_TX_IDX 11 255 131072 131072
1 2147483645 0
10 YES 2 715 5
143 1919 9597 VALID
331590 331590 28-JAN-11 1
1 NO N N N DEFAULT NO


NO NO NO NO

QP QP_LIST_HEADERS_B_N5
NORMAL QP
QP_LIST_HEADERS_B TABLE NONUNIQUE DISABLED
APPS_TS_TX_IDX 11 255 131072 131072
1 2147483645 0
10 YES 1 2 358
1 1 286 VALID
358 358 28-JAN-11 1
1 NO N N N DEFAULT NO


NO NO NO NO

QP QP_LIST_HEADERS_B_PK
NORMAL QP
QP_LIST_HEADERS_B TABLE UNIQUE DISABLED
APPS_TS_TX_IDX 11 255 131072 131072
1 2147483645 0
10 YES 1 715 331590
1 1 77931 VALID
331590 331590 28-JAN-11 1
1 NO N N N DEFAULT NO


NO NO NO NO


9 rows selected.

SQL> SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'QP_LIST_HEADERS_B';

OWNER CONSTRAINT_NAME C
------------------------------ ------------------------------ -
TABLE_NAME
------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS
------------------------------ ------------------------------ --------- --------
DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHAN
-------------- --------- ------------- -------------- --- ---- ---------
INDEX_OWNER INDEX_NAME INVALID
------------------------------ ------------------------------ -------
VIEW_RELATED
--------------
QP SYS_C0043783 C
QP_LIST_HEADERS_B
"LIST_HEADER_ID" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 28-JAN-11



QP SYS_C0043784 C
QP_LIST_HEADERS_B
"CREATION_DATE" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 28-JAN-11



QP SYS_C0043788 C
QP_LIST_HEADERS_B
"LIST_TYPE_CODE" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 28-JAN-11



QP SYS_C0043786 C
QP_LIST_HEADERS_B
"LAST_UPDATE_DATE" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 28-JAN-11



QP SYS_C0043787 C
QP_LIST_HEADERS_B
"LAST_UPDATED_BY" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 28-JAN-11



QP SYS_C0043785 C
QP_LIST_HEADERS_B
"CREATED_BY" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 28-JAN-11




6 rows selected.

SQL> C/QP_LIST_HEADERS_B/QP_LIST_HEADERS_B_NOPART
1* SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'QP_LIST_HEADERS_B_NOPART'
SQL> /

OWNER CONSTRAINT_NAME C
------------------------------ ------------------------------ -
TABLE_NAME
------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS
------------------------------ ------------------------------ --------- --------
DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHAN
-------------- --------- ------------- -------------- --- ---- ---------
INDEX_OWNER INDEX_NAME INVALID
------------------------------ ------------------------------ -------
VIEW_RELATED
--------------
QP TMP$$_SYS_C00437831 C
QP_LIST_HEADERS_B_NOPART
"LIST_HEADER_ID" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 19-JAN-11



QP TMP$$_SYS_C00437841 C
QP_LIST_HEADERS_B_NOPART
"CREATION_DATE" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 19-JAN-11



QP TMP$$_SYS_C00437881 C
QP_LIST_HEADERS_B_NOPART
"LIST_TYPE_CODE" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 19-JAN-11



QP TMP$$_SYS_C00437861 C
QP_LIST_HEADERS_B_NOPART
"LAST_UPDATE_DATE" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 19-JAN-11



QP TMP$$_SYS_C00437871 C
QP_LIST_HEADERS_B_NOPART
"LAST_UPDATED_BY" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 19-JAN-11



QP TMP$$_SYS_C00437851 C
QP_LIST_HEADERS_B_NOPART
"CREATED_BY" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE NOT VALIDATED USER NAME 19-JAN-11




6 rows selected.

SQL> C/NOPART/PART
1* SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'QP_LIST_HEADERS_B_PART'
SQL> /

OWNER CONSTRAINT_NAME C
------------------------------ ------------------------------ -
TABLE_NAME
------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS
------------------------------ ------------------------------ --------- --------
DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHAN
-------------- --------- ------------- -------------- --- ---- ---------
INDEX_OWNER INDEX_NAME INVALID
------------------------------ ------------------------------ -------
VIEW_RELATED
--------------
QP TMP$$_SYS_C00437830 C
QP_LIST_HEADERS_B_PART
"LIST_HEADER_ID" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 15-JUN-04



QP TMP$$_SYS_C00437840 C
QP_LIST_HEADERS_B_PART
"CREATION_DATE" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 15-JUN-04



QP TMP$$_SYS_C00437850 C
QP_LIST_HEADERS_B_PART
"CREATED_BY" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 15-JUN-04



QP TMP$$_SYS_C00437860 C
QP_LIST_HEADERS_B_PART
"LAST_UPDATE_DATE" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 15-JUN-04



QP TMP$$_SYS_C00437870 C
QP_LIST_HEADERS_B_PART
"LAST_UPDATED_BY" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 15-JUN-04



QP TMP$$_SYS_C00437880 C
QP_LIST_HEADERS_B_PART
"LIST_TYPE_CODE" IS NOT NULL
ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 15-JUN-04




6 rows selected.

database scripts

active.sql
===========
/* module.sql
show info about user sessions */
col sid format 99999
col username format a5 trunc
col osuser format a10 trunc
col logged_on format a13 head LOGON_TIME
col umachine format a10 trunc
col program format a20 trunc
col proginfo format a15 trunc
col LastCallET format 99,999
set verify off
accept trgtmod char default ALL prompt 'Restrict to which module : '
accept trgtmach char default ALL prompt 'Restrict to which originating server : '
accept trgtstatus char default Y prompt 'Limit to active sessions only : '
select s.sid,s.status,s.username,s.osuser,
replace(s.machine,'GEIPS-AMER\',NULL) umachine,
s.sql_hash_value,s.command,s.process, p.spid,
to_char(logon_time,'mm/dd hh:miAM') logged_on,
floor(last_call_et/60) "LastCallET",
s.module proginfo
from v$session s, v$process p
where s.username is not null
and s.type = 'USER'
and s.paddr = p.addr
and ((s.status = 'ACTIVE' and upper('&trgtstatus') = 'Y') or upper('&trgtstatus') != 'Y')
and (upper(s.module) like upper('%&trgtmod%') or upper('&trgtmod') = 'ALL')
and (upper(s.machine) like upper('%&trgtmach%') or upper('&trgtmach') = 'ALL')
/




archive logs per hour per day basis.sql
==========================================
/* archlogs.sql
find the number of archivelogs each hour of the day
*/
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1
/



ckobjstats.sql
==================
/* ckobjectstat.sql
check stats info on objects of interet
SDR-Oracle 7/5/02
*/
set verify off
col objtype format a8
col stats_status format a15
col owner format a15
col pctanal format 999 head PctAnalyzed
col objorder noprint
col table_name format a30
accept objname char default NONE prompt 'What is the table name : '
accept objowner char default ALL prompt 'Who is the table owner : '
select 'Table' objtype, owner, table_name , table_name objorder,
decode(last_analyzed,null,'No Stats',to_char(last_analyzed,'mm/dd/yy hh24:mi')) stats_status,
nvl(sample_size,0)/decode(nvl(num_rows,1),0,1,nvl(num_rows,1)) * 100 pctanal
from dba_tables
where table_name like upper('&objname%') and upper('&objname') != 'NONE'
and (owner = upper('&objowner') or upper('&objowner') = 'ALL')
union
select ' Index' objtype,owner, index_name, table_name objorder,
decode(last_analyzed,null,'No Stats',to_char(last_analyzed,'mm/dd/yy hh24:mi')) stats_status,
nvl(sample_size,0)/decode(nvl(num_rows,1),0,1,nvl(num_rows,1)) * 100 pctanal
from dba_indexes
where (owner = upper('&objowner') or upper('&objowner') = 'ALL')
and (table_name like upper('&objname%') and upper('&objname') != 'NONE')
union
select ' Hist' objtype, owner, column_name, table_name objorder,
'Buckets' status_status,
count(*) pctanal
from dba_tab_histograms
where (table_name like upper('&objname%') and upper('&objname') != 'NONE')
and (owner = upper('&objowner') or upper('&objowner') = 'ALL')
and endpoint_number > 1
group by owner, column_name, table_name
union
select ' PartInd' objtype, p.index_owner, i.index_name, i.table_name objorder,
decode(p.last_analyzed,null,'No Stats',to_char(p.last_analyzed,'mm/dd/yy hh24:mi')),
nvl(p.sample_size,0)/decode(nvl(p.num_rows,1),0,1,nvl(p.num_rows,1)) * 100 pctanal
from dba_ind_partitions p, dba_indexes i
where p.index_name = i.index_name
and p.index_owner = i.owner
and (i.table_name like upper('&objname%') and upper('&objname') != 'NONE')
and p.partition_position = 1
order by 4, 1 desc,3
/


cktempts.sql
=============
select tablespace_name,contents,extent_management,initial_extent,next_extent from dba_tablespaces where tablespace_name like 'TEMP%'
/
col file_name format a45 trunc
col mbavail format 99999.90
col mbused format 99999.90
col db_block_size format a15 trunc
select a.file_name,
a.bytes/1024/1024 mbavail,
((nvl(b.blocks_used,1)*c.db_block_size) + c.db_block_size)/1024/1024 mbused,
lpad(c.db_block_size,15)
from dba_temp_files a, v$temp_space_header b,
(select value db_block_size
from v$parameter
where name='db_block_size') c
where a.tablespace_name = 'TEMP'
and a.file_id = b.file_id(+)
order by a.file_name;



ckurbs.sql
============

/* ckuserrbs.sql
check rollback segments for users and active transactions
*/
set pause off
set pause 'Mash Enter...'
set feedback off
set verify off
col name format a12 heading RollbackSeg
col username format a12 heading DBUser
col sid format 99999 heading DBSID
col serial# format 99999 heading SRL#
col osuser format a8 heading OSUser
col program format a30 heading Program trunc
col spid format a6 heading BOSPID
col bpgm format a20 heading "BackgroundPgm" trunc
col module format a15 trunc
col status format a1 trunc
col machine format a15 trunc
col lastcallet format a10 trunc
col tablespace_name format a15 trunc
accept trgtrbs char default ALL prompt 'Limit to which rollback segment : '
accept trgttbs char default ALL prompt 'Limit to which Tablespace : '
accept trgtmod char default ALL prompt 'Limit to which module : '
accept trgtstatus char default N prompt 'Limit to active sessions only : '
prompt Checking rollback segment info...
select /*+ rule */
e.tablespace_name, c.name,
b.username ,
b.sid , b.status , b.osuser, b.module, b.machine,
floor(b.last_call_et/3600)||':'||
floor(mod(b.last_call_et,3600)/60)||':'||
mod(mod(b.last_call_et,3600),60) "LastCallET",
d.spid, b.command
from v$lock a, v$session b, v$rollname c, v$process d, dba_rollback_segs e
where a.type='TX'
and a.sid=b.sid
and d.addr = b.paddr
and c.usn=trunc(a.id1/1048596)*16+trunc((mod(a.id1,1048596))/65536)
and e.segment_name = c.name
and (c.name = upper('&trgtrbs') or upper('&trgtrbs') = 'ALL')
and (b.module = upper('&trgtmod') or upper('&trgtmod') = 'ALL')
and (b.status = 'ACTIVE' and upper('&trgtstatus') = 'Y' or upper('&trgtstatus') = 'N')
and (e.tablespace_name = upper('&trgttbs') or upper('&trgttbs') = 'ALL')
order by 1,2,5 desc;


Count the number of rows for ALL tables in current schema.txt
================================================================
rem -----------------------------------------------------------------------
rem Filename: countall.sql
rem Purpose: Count the number of rows for ALL tables in current schema
rem using PL/SQL
rem Date: 15-Apr-2000
rem Author: Eberhardt, Roberto (Bolton) (reberhar@husky.ca)
rem -----------------------------------------------------------------------

set serveroutput on size 1000000

DECLARE
t_c1_tname user_tables.table_name%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
t_limit integer := 0; -- Only show tables with more rows
cursor c1 is select table_name from user_tables order by table_name;
BEGIN
t_limit := 0;
open c1;
loop
fetch c1 into t_c1_tname;
exit when c1%NOTFOUND;
t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
if t_total_records > t_limit then
DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')||
to_char(t_total_records,'99999999')||' record(s)');

end if;
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
close c1;
END;
/


create user like existing one.txt
====================================
rem -----------------------------------------------------------------------
rem Filename: cr8like.sql
rem Purpose: Script to create a new user (with privs) like an existing
rem database user. User data will not be copied.
rem Date: 02-Nov-1998
rem Author: Frank Naude, Oracle FAQ
rem Updated: Konstantin Krivosheyev - 7 Dec 2002
rem Updated: Frank Naude - 18 Dec 2003
rem -----------------------------------------------------------------------

set pages 0 feed off veri off lines 500

accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
-- accept psw prompt "Enter new user's password: "

-- Create user...
select 'create user &&newname identified by values '''||password||''''||
-- select 'create user &&newname identified by &psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');

-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');

-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');

-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');

-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');

-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
for c1 in (select * from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES'
) loop
if length(defroles) > 0 then
defroles := defroles||','||c1.granted_role;
else
defroles := defroles||c1.granted_role;
end if;
end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/


Display database uptime in days and hours.TXT
==============================================
select SYSDATE-logon_time "Days", (SYSDATE-logon_time)*24 "Hours"
from sys.v_$session
where sid=1 /* this is PMON */
/


dynamically zip a trace file.txt
================================

column trc_file new_value trc_file noprint
column zip_file new_value zip_file noprint

select p.value || '/ora_' || u.spid || '.trc' trc_file,
p.value || '/ora_' || u.spid || '.trc.gz' zip_file
from sys.v_$session s,
sys.v_$process u,
sys.v_$parameter p
where s.audsid = userenv('SESSIONID')
and u.addr = s.paddr
and p.name = 'user_dump_dest'
/

set define :
host mknod :trc_file p && nohup gzip < :trc_file > :zip_file &
set define &

alter session set max_dump_file_size = unlimited
/




Dynamically zip a process's trace file .TXT
===============================================
column trc_file new_value trc_file noprint
column zip_file new_value zip_file noprint

select p.value || '/ora_' || u.spid || '.trc' trc_file,
p.value || '/ora_' || u.spid || '.trc.gz' zip_file
from sys.v_$session s,
sys.v_$process u,
sys.v_$parameter p
where s.audsid = userenv('SESSIONID')
and u.addr = s.paddr
and p.name = 'user_dump_dest'
/

set define :
host mknod :trc_file p && nohup gzip < :trc_file > :zip_file &
set define &

alter session set max_dump_file_size = unlimited
/


files on mount point.sql
===========================
col file_name format a55
accept trgtmp char default u00 prompt 'What is the mount point : '
select t.tablespace_name,f.file_name
from dba_tablespaces t, dba_data_files f
where f.tablespace_name = t.tablespace_name
and f.file_name like '/&trgtmp/%'
order by 1,2
/



find last ET from sid.sql
===========================
/* sidtime.sql
check sid for last call et
*/
col osuser format a10 trunc
col LastCallET format a11
col sid format 9999
col username format a10 trunc
col uprogram format a25 trunc
set linesize 132
set verify off
accept trgtsid number default 0 prompt 'What is the SID : '
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "LastCallET",
s.username, s.osuser,
p.spid, s.module || ' - ' || s.program uprogram, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and s.sid = &trgtsid
and &trgtsid > 0;





find oracle sid for given os process.sql
=========================================
/* osproc.sql
find the Oracle session info for a given OS process id
*/
col machine format a15 trunc
col program format a25 trunc
col LastCallET format a11
col status format a1 trunc
col module format a12 trunc
set verify off
accept trgtproc char default 0 prompt 'What is the OS process id : '
select s.sid,s.status,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "LastCallET",
s.machine,s.module,s.process,s.sql_hash_value
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = '&trgtproc';


find_hogger.txt
=================
#!/bin/ksh
#
# Find Highest CPU used Oracle processes and get the Username
# and SID from oracle
# Only 3 character SIDNAME is displayed - Adjust the script according to your need.
#
# Biju Thomas - 4/22/1999
#
date
echo " Top 20 CPU Utilized Session from `hostname`"
echo " ============================================"
echo "O/S Oracle Session Session Serial UNIX Login Ora CPU Time"
echo "ID User ID Status ID No ID MMDD:HHMISS SID Used"
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
ps -ef|grep LOCAL|cut -c1-15,42-79|sort -rn +2 | head -20 | while read LINE
do
SIDNAME=`echo $LINE | awk '{ print $4 }' | cut -c7-14`
CPUTIME=`echo $LINE | awk '{ print $3 }'`
UNIXPID=`echo $LINE | awk '{ print $2 }'`
#echo $SIDNAME $CPUTIME $UNIXPID
export ORACLE_SID=$SIDNAME
SIDNAME=`echo $ORACLE_SID | cut -c4-6`
export ORACLE_HOME=`/dba_script/bin/find_ohome.sh ${ORACLE_SID}`
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib
export TMPDIR=/tmp
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / "
$SQLPLUS >> $wlogfile < set pages 0 lines 80 trims on echo off verify off pau off
column pu format a8 heading 'O/S|ID' justify left
column su format a11 heading 'Oracle|User ID' justify left
column stat format a8 heading 'Session|Status' justify left
column ssid format 999999 heading 'Session|ID' justify right
column sser format 999999 heading 'Serial|No' justify right
column spid format 999999 heading 'UNIX|ID' justify right
column ltime format a11 heading 'Login|Time' justify right
select p.username pu,
s.username su,
s.status stat,
s.sid ssid,
s.serial# sser,
lpad(p.spid,7) spid,
to_char(s.logon_time, 'MMDD:HH24MISS') ltime,
'$SIDNAME $CPUTIME'
from v\$process p,
v\$session s
where p.addr=s.paddr
and p.spid=$UNIXPID
union all
select a.username, 'Kill Me', 'NoOracle', a.pid, a.serial#,
lpad(a.spid,7) spid, 'KILL UNIXID', '$SIDNAME $CPUTIME'
from v\$process a
where a.spid = $UNIXPID
and not exists (select 1 from v\$session s
where a.addr=s.paddr);
EOF
done
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
date
#
# End of Script


get sql fm sid.sql
=====================
col sid for 999999
set long 100000000
select sid, sql_text from v$session a, v$sqltext b
where a.sql_address = b.address
and a.sid=&SID_NUMBERR
/


getplsqldef.sql
===============
set lines 200
set pages 0
accept objowner char default APPS prompt 'Who is the object owner : '
accept objname char prompt 'What is the object name : '
accept objtype char prompt 'What is the object type : '
accept spoolfile char default plsql.prev prompt 'What is the spool file name : '
set verify off
set feedback off
spool &spoolfile
select text from dba_source
where owner = upper('&objowner')
and name = upper('&objname')
and type = upper('&objtype')
order by line;
spool off


httpget.txt
=============
rem -----------------------------------------------------------------------
rem Filename: httpget.sql
rem Purpose: Access Internet Web pages from SQL or PL/SQL
rem Notes: From Oracle 8.0 one can retrieve web pages directly
rem from SQL or PL/SQL. Note you need to run utlhttp.sql as
rem SYS before this procedure will work.
rem Date: 27-Mar-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000

select utl_http.request('http://www.orafaq.net/') from dual;




inactive user sessions.sql
============================
/* inactive.sql
shows the inactive user sessions
where lastcallet > 90 minutes
and session is a user session
SDR-Oracle 6/21/02 - sourced from my top10inactive.sql
SDR-Oracle 7/9/02 - added app process to ouput
*/
set serveroutput on size 100000
set verify off
set feedback off
declare
v_sidctr number := 0;
v_spidctr number := 0;

v_inact_threshold number := 90;
v_uctr number:= 0;
v_trgtsid number := 0;
v_shortname varchar(30);
v_longname varchar(45);
v_prevspid varchar(9) := '0';
v_usertype varchar(8);
v_umachine varchar(10);
v_lastcallet number;
v_loggedon varchar(16);

-- cursor for all inactive users over the threshold
cursor aiu is
select 'TOAD' usertype,
s.sid,
floor(last_call_et/60) lastcallet,
p.spid,
s.module uprogram,
substr(replace(s.machine,'GEIPS-AMER\',null),1,10) umachine,
s.osuser,
s.username,
to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.process appproc
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and module is not null
and s.status = 'INACTIVE'
and floor(last_call_et/60) > v_inact_threshold
and (machine not like 'gps%' and upper(module) like 'T%O%A%D%')
union
select 'FORMS' usertype,
s.sid,
floor(last_call_et/60) lastcallet,
p.spid,
nvl(s.module,'UNKnown') uprogram,
s.machine umachine,
s.osuser,
s.username,
to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.process appproc
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and s.status = 'INACTIVE'
and floor(last_call_et/60) > v_inact_threshold
and machine in ('gpsd011','gpsd012')
and nvl(module,'UNKnown') not like '%JAVA%'
union
select 'SQLPLUS' usertype,
s.sid,
floor(last_call_et/60) lastcallet,
p.spid,
s.module uprogram,
substr(replace(s.machine,'GEIPS-AMER\',null),1,10) umachine,
s.osuser,
s.username,
to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.process appproc
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and module is not null
and s.status = 'INACTIVE'
and floor(last_call_et/60) > v_inact_threshold
and upper(module) like 'SQL%'
and ((s.machine = 'gpsd002a' and s.username != 'APPS') or (s.machine like 'GE%'))
order by 4,3 desc;

begin
-- do it
<>
for eu in aiu
loop
v_usertype := eu.usertype;
v_umachine := eu.umachine;
v_lastcallet := eu.lastcallet;
v_loggedon := eu.loggedon;

if v_prevspid != eu.spid then
dbms_output.put_line('OSProc:' || eu.spid || chr(9) || v_usertype || ' user from ' || v_umachine ||
' (' || eu.appproc || ') logged on at ' || v_loggedon);
v_prevspid := eu.spid;
v_spidctr := v_spidctr + 1;

begin
-- determine the apps forms user
if eu.usertype = 'FORMS' then
v_trgtsid := eu.sid;
select count(*),
u.user_name, u.description
into v_uctr,
v_shortname, v_longname
from applsys.fnd_logins l,
applsys.fnd_user u,
v$process p,
v$session s
where s.sid = v_trgtsid
and s.paddr = p.addr
and p.pid = l.pid
and l.end_time is null
and l.spid = s.process
and l.start_time is not null
and l.user_id = u.user_id
and l.start_time = (select max(l2.start_time)
from applsys.fnd_logins l2
where l2.pid = l.pid)
group by u.user_name, u.description;

else v_shortname := eu.username;

end if; -- end of if a forms user

exception
when others then
v_shortname := 'UNKnown';
v_longname := null;
end;

end if; -- end of if a new spid

-- provide the individual SID info
dbms_output.put_line(chr(9) || 'SID:' || eu.sid || ', User ' || v_shortname || ' - ' || eu.uprogram ||
' ' || v_lastcallet || ' minutes inactive');
v_sidctr := v_sidctr + 1;

end loop each_sid;

dbms_output.put_line(v_spidctr || ' total OSProcs to kill');
dbms_output.put_line(v_sidctr || ' total SIDs affected');
end;
/



initora.sql
=============
/* init73params.sql
get all the parameters for v7.3
*/
set feedback off
set pause off
set pagesize 66
set linesize 132
col ksppinm format a30 heading Parameter trunc
col ksppdesc format a60 heading Description trunc
col ksppstvl format a30 heading SessionValue trunc
spool initoraparams.lst
select a.ksppinm,
a.ksppdesc,
b.ksppstvl
from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
where a.indx = b.indx
and a.indx = c.indx
order by 1;
spool off





killprocesses.sh
==================
select 'kill -9 ' || p.spid from v$session s, v$process p where s.paddr=p.addr and s.status='SNIPED'


List control file structures with usage limits.TXT
===================================================
rem -----------------------------------------------------------------------
rem Filename: ctlimits.sql
rem Purpose: List control file structures with usage limits
rem Date: 21-Sep-2000
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pages 50000
col PCT_USED format 990.09

-- Controlfile creation parameters:
-- Type DATAFILE is for MAXDATAFILES
-- Type REDO LOG is for MAXLOGFILES
-- Type LOG HISTORY is for MAXLOGHISTORY
-- Type REDO THREAD is for MAXINSTANCES
-- No entry for MAXLOGMEMBERS (?)

select type, records_used, records_total,
records_used/records_total*100 "PCT_USED"
from sys.v_$controlfile_record_section
/


Log all database errors to a table.TXT
========================================
rem -----------------------------------------------------------------------
rem Filename: db-error.sql
rem Purpose: Log all database errors to a table
rem Oracle8i or above/ DBA or CREATE ANY TRIGGER privs/ and
rem GRANT SELECT ON SYS.V_$SESSION required
rem Date: 21-Mar-2000
rem Author: Nico Booyse (booysen@saps.org.za)
rem -----------------------------------------------------------------------

drop trigger log_errors_trig;
drop table log_errors_tab;

create table log_errors_tab (
error varchar2(30),
timestamp date,
username varchar2(30),
osuser varchar2(30),
machine varchar2(64),
process varchar2(8),
program varchar2(48));

create or replace trigger log_errors_trig
after servererror on database
declare
var_user varchar2(30);
var_osuser varchar2(30);
var_machine varchar2(64);
var_process varchar2(8);
var_program varchar2(48);
begin
select username, osuser, machine, process, program
into var_user, var_osuser, var_machine, var_process, var_program
from sys.v_$session
where audsid = userenv('sessionid');

insert into log_errors_tab
values(dbms_standard.server_error(1),sysdate,var_user,
var_osuser,var_machine,var_process,var_program);
end;
/



new sessions which have recently started.sql
============================================
/* newses.sql
show the new user sessions based on logon time < 30 secs ago
*/
col LastCallET format a10
col sid format 9999
col sessprog format a25 trunc head PROGRAM
col username format a10 trunc
col osuser format a10 trunc
col lcet_hrs noprint
col lcet_min noprint
col lcet_sec noprint
set linesize 132
set verify off
col machine format a15 trunc
select
to_number(floor(last_call_et/3600)) lcet_hrs,
to_number(floor(mod(last_call_et,3600)/60)) lcet_min,
to_number(mod(mod(last_call_et,3600),60)) lcet_sec,
to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) "LastCallET",
s.machine, s.osuser,
p.spid, nvl(s.module,p.program) sessprog,
s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and type = 'USER'
and logon_time > (sysdate - 1/2880)
and nvl(s.username,'AQADMIN') != 'AQADMIN'
order by 1,2,3;


obects which are kept.sql
===========================
col type format a15 trunc
select kept,type,count(*),sum(loads),sum(sharable_mem),sum(executions)
from v$db_object_cache
where type in ('SEQUENCE','PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TRIGGER')
group by kept,type
/



objects which are not kept.sql
=============================
col owner format a15
col name format a30
col exec2load format 99,999,999
set lines 132
set pause off
set feedback off
select owner, name, type, loads, executions,
executions/loads exec2load,
sharable_mem
from v$db_object_cache
where type in ('SEQUENCE','PROCEDURE','PACKAGE','PACKAGE BODY','FUNCTION','TRIGGER')
and kept = 'NO'
and loads > 1
order by loads, owner, type;





objs_in_cache.sql
===================
/* objs_in_cache.sql
see the objects in the buffer cache
optionally limit by owner, object name
*/
set verify off
set lines 132
col filename format a35 trunc
col ownername format a10 head owner
col objname format a30 head object_name
col blksincache format 999,999 head footprint
accept trgtown char default ALL prompt 'Limit to owner : '
accept trgtobj char default ALL prompt 'Limit to object name : '
select u.name ownername,
o.name objname,
count(bc.block#) blksincache,
bc.status,
f.name filename
from sys.obj$ o, v$bh bc, v$datafile f, sys.user$ u
where o.obj# = bc.objd
and f.file# = bc.file#
and u.user# = o.owner#
and (u.name = upper('&trgtown') or upper('&trgtown') = 'ALL')
and (o.name = upper('&trgtobj') or upper('&trgtobj') = 'ALL')
group by u.name,o.name,bc.status,f.name
order by 3;


partition table info.sql
=========================
col column_name format a30 trunc
col owner format a15 trunc
col column_position format 999 head Order
set verify off
accept tabname char default NONE prompt 'What is the partitioned table name : '
prompt Getting partitioning info for &tabname....
select t.owner,t.table_name,t.partitioning_type,t.SUBPARTITIONING_TYPE ,
t.PARTITION_COUNT, k.column_name,k.column_position
from dba_part_tables t, dba_part_key_columns k
where t.table_name = upper('&tabname')
and upper('&tabname') != 'NONE'
and k.name = t.table_name
order by 1,2,7;

prompt Getting partioning info on indexes for &tabname....
select i.owner,i.index_name,i.partitioning_type,i.locality,i.SUBPARTITIONING_TYPE ,
i.PARTITION_COUNT, k.column_name,k.column_position
from dba_part_indexes i, dba_part_key_columns k
where i.table_name = upper('&tabname')
and upper('&tabname') != 'NONE'
and k.name = i.index_name
order by 1,2,7;



redo.sql
==========
col member format a40 trunc
col logsize format 9999 head MBSize
select l.group#,l.status,l.bytes/1024/1024 logsize,f.member
from v$log l, v$logfile f
where f.group# = l.group#
order by 1,3
/



redolog status.sql
=====================
col member format a40 trunc
select to_char(l.first_time,'mm/dd hh24:mi'),l.group#, l.sequence#, l.bytes,l.status,l.archived,f.member
from v$log l, v$logfile f
where l.group# = f.group#
order by sequence#
/



redostats.sql
================
/* redostats.sql
see the critical stats for redo and archive logs
SDR-Oracle
Note: this script is the v8-compatible version
*/
set pages 0
set lines 132
set pause off
set feedback off
set verify off
col dsnbr noprint

/* is the archiver falling behind, count should be 1 or 0 */
select decode(count(*),0,'Archiver is current',
1,'Archiver is archiving ' || count(*) || ' log...',
'Archiver has fallen behind by ' || count(*) || ' logs!')
from v$log
where status != 'CURRENT'
and archived = 'NO';

/* what is the current log COL::::<# members> */
select 'Current Online Log:' || chr(9) ||
substr(f.member,1,(instr(f.member,'/',1,2) -1))
|| '/.../' ||
substr(f.member,(instr(f.member,'/',-1,1)+1))
|| chr(9) || 'Sequence #' ||
l.sequence#
from v$logfile f, v$log l
where l.group# = f.group#
and l.status = 'CURRENT'
and l.archived = 'NO'
and rownum = 1;

/* what are the previous online logs */
select lc_1.sequence# dsnbr,
'Previous Online Logs:' || chr(9) ||
substr(f.member,1,(instr(f.member,'/',1,2) -1))
|| '/.../' ||
substr(f.member,(instr(f.member,'/',-1,1)+1))
|| chr(9) || 'Sequence #' ||
lc_1.sequence#
from v$log lc, v$log lc_1, v$logfile f
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_1.sequence# = lc.sequence# -1
and f.group# = lc_1.group#
union
select lc_2.sequence# dsnbr,
' ' || chr(9) ||
substr(f.member,1,(instr(f.member,'/',1,2) -1))
|| '/.../' ||
substr(f.member,(instr(f.member,'/',-1,1)+1))
|| chr(9) || 'Sequence #' ||
lc_2.sequence#
from v$log lc, v$log lc_2, v$logfile f
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_2.sequence# = lc.sequence# -2
and f.group# = lc_2.group#
union
select lc_3.sequence# dsnbr,
' ' || chr(9) ||
substr(f.member,1,(instr(f.member,'/',1,2) -1))
|| '/.../' ||
substr(f.member,(instr(f.member,'/',-1,1)+1))
|| chr(9) || 'Sequence #' ||
lc_3.sequence#
from v$log lc, v$log lc_3, v$logfile f
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_3.sequence# = lc.sequence# -3
and f.group# = lc_3.group#
order by 1 desc;

/* what is the latest archived log LAL:: */
select 'Latest Archived Log:' || chr(9) ||
substr(h.name,(instr(h.name,'/',-1,1)+1))
|| chr(9) || 'Sequence #' ||
max(h.sequence#)
from v$archived_log h
where h.sequence# = (select max(l.sequence#)
from v$log l
where l.archived = 'YES'
and l.status = 'INACTIVE')
group by 'Latest Archived Log:' || chr(9) ||
substr(h.name,(instr(h.name,'/',-1,1)+1));

/* how many minutes since the last log switch MSL: */
select 'Elapsed Minutes Since Last Log Switch:' || chr(9) ||
trunc((sysdate - first_time) * 24 * 60)
from v$log
where status = 'CURRENT'
and sequence# = (select max(sequence#) + 1
from v$log_history);

/* what are the last 3 individual switch intervals L3I:<1>:<2>:<3> */
select 'Prior 3 Actual Switch Intervals:' || chr(9) || '[' ||
round((lc.first_time - lc_1.first_time) * 24 * 60,1)
|| ']' || chr(9) || '[' ||
round((lc_1.first_time - lc_2.first_time) * 24 * 60,1)
|| ']' || chr(9) || '[' ||
round((lc_2.first_time - lc_3.first_time) * 24 * 60,1)
|| ']'
from v$log lc, v$log_history lc_1, v$log_history lc_2, v$log_history lc_3
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_1.sequence# = lc.sequence# -1
and lc_2.sequence# = lc.sequence# -2
and lc_3.sequence# = lc.sequence# -3;

/* what is the avg interval for last 3 log switches L3S: */
select 'Prior 3 Average Switch Interval:' || chr(9) ||
round( (
round((lc.first_time - lc_1.first_time) * 24 * 60,2) +
round((lc_1.first_time - lc_2.first_time) * 24 * 60,2) +
round((lc_2.first_time - lc_3.first_time) * 24 * 60,2) ) / 3, 1)
from v$log lc, v$log_history lc_1, v$log_history lc_2, v$log_history lc_3
where lc.sequence# = (select max(l.sequence#) from v$log l)
and lc_1.sequence# = lc.sequence# -1
and lc_2.sequence# = lc.sequence# -2
and lc_3.sequence# = lc.sequence# -3;

set pages 24





script to recreate the database minus data .TXT
====================================================
#!/bin/sh
set -x
export ORACLE_SID=$1
sqlplus -s internal <Rem Filename : Generate_createdb.sh
Rem Purpose : To reverse engineer the createdb.sql from a running database
Rem Syntax : Generate_createdb.sh
Rem produces an output called createdb_$ORACLE_SID.sql
Rem
Rem Notes : Developed and tested on Oracle 7.3.3 for Solaris
Rem (some cursory tests on Oracle 8.1.7 for Solaris and Linux done
Rem by A I Rehberg, but this script is not certified in any way).
Rem Runs ONLY catproc.sql, catalog.sql, catdbsyn.sql and pubbld.sql
Rem Non UNIX databases may need small modifications to the paths
Rem of called scripts i.e. catalog.sql, catproc.sql ...
Rem You will have to review the resulting script and apply some
Rem corrections/additions/changes to it before it will actually
Rem do what you expect from it - but it at least gives you a good
Rem basis to start with!
Rem
Rem Not done : - resource consumer groups are not catered for
Rem - account locks not catered for
Rem - "Create Role" statements are not created
Rem
Rem History : 20-01-2000 E Augustine Created
Rem 14-02-2000 E Augustine
Rem - Database is created in NOARCHIVELOG and then altered
Rem to ARCHIVELOG if originally configured in ARCHIVELOG.
Rem 09-04-2002 A I Rehberg Additions on Oracle 8i
Rem - AutoExtending datafiles recognized now
Rem - TableSpaces for SYS and SYSTEM read from DB
Rem (instead of being hardcoded)
Rem - User Accounts now also extracted (passwords are set to the
Rem user name since they can't be read from DB)
Rem - Grants are extracted now for users as well as roles
Rem - Profiles are catered for
Rem - Dropped the hardcoded creation of user OPS$ORACLE
Rem 12-04-2002 D Kapfer Shell Scripting & more
Rem - Embedded the generator in a bash script to support
Rem Environment variables
Rem - created work-around for "MaxExtents Unlimited" problem
Rem (Oracle just stores this value as a high integer)
Rem - location of executed scripts now based on $ORACLE_HOME
Rem - added execution of catdbsyn.sql and pubbld.sql
Rem 14-04-2002 A I Rehberg Small fixes
Rem - catproc.sql was not executed (added it now)
Rem - some statements had wrong syntax (fixed)
Rem 23-04-2002 A I Rehberg Small additions
Rem - added "Extent Management", "[No]Logging" and "Minimum Extent"
Rem clauses to "Create TableSpace" statements
Rem
Set TERMOUT OFF
Set SERVEROUTPUT On Size 1000000
Set LINESIZE 300
Set TRIMSPOOL On
Set FEEDBACK OFF
Set Echo Off
Spool createdb_${ORACLE_SID}.sql
Declare
-- Non SYSTEM Tablespaces are created explicitly.
-- The Min(FILE_ID) is used to select the order in which Tablespaces
-- were created.
Cursor C_Tablespaces Is
Select TABLESPACE_NAME, Min(FILE_ID) From DBA_DATA_FILES
Where TABLESPACE_NAME Not Like 'SYSTEM%'
Group By TABLESPACE_NAME
Order By 2;
-- All files for a tablespace.
Cursor C_Datafiles ( P_TS Varchar2 ) Is
Select * From DBA_DATA_FILES
Where TABLESPACE_NAME = P_TS
Order By FILE_ID;
-- Profile information
Cursor C_Profiles Is
Select Distinct PROFILE
From DBA_PROFILES
Where PROFILE <> 'DEFAULT';
Cursor C_Profile_Info (PROF In Varchar2) Is
Select PROF,RESOURCE_NAME,LIMIT
From DBA_PROFILES
Where PROFILE=PROF;

-- User information
Cursor C_User_Info Is
Select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE,ACCOUNT_STATUS
From DBA_USERS
Where lower(USERNAME) Not In ('sys','system','outln','dbsnmp')
Order By USER_ID;
-- Administrative User information
Cursor C_Admin_Info Is
Select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE
From DBA_USERS
Where lower(USERNAME) In ('sys','system')
Order By USER_ID;
-- Tablespace Quota information
Cursor C_Quota_Info Is
Select * From DBA_TS_QUOTAS
Order By USERNAME;
-- Role information
Cursor C_Role_Info Is
Select * From DBA_ROLE_PRIVS
Where lower(GRANTEE) Not In ('sys','system','outln','dbsnmp')
Order By GRANTEE;
-- System Privileges
Cursor C_SysPriv_Info Is
Select * from DBA_SYS_PRIVS
Where lower(GRANTEE) Not In ('sys','system','outln','dbsnmp','connect','dba','exp_full_database','imp_full_database','recovery_catalog_owner','resource','snmpagent','aq_administrator_role','aq_user_role','hs_admin_role','plustrace','tkprofer','ck_oracle_repos_owner')
Order By GRANTEE;
-- Object Privileges (only those on sys/systems objects; all others
-- are provided by exp/imp the schemes)
Cursor C_TabPriv_Info Is
Select * From DBA_TAB_PRIVS
Where lower(GRANTOR) In ('public','sys','system')
And GRANTEE Not In (Select ROLE From DBA_ROLES)
And lower(GRANTEE) Not In ('sys','system')
Order By GRANTEE,TABLE_NAME;

-- Tablespace information
Cursor C_Tablespace_Info ( P_TS Varchar2 ) Is
Select * From DBA_TABLESPACES
Where TABLESPACE_NAME = P_TS;
-- SYSTEM rollback segment is created implicitly hence not selected.
Cursor C_Rollback_Segs ( P_TS Varchar2 ) Is
Select * From DBA_ROLLBACK_SEGS
Where TABLESPACE_NAME = P_TS
And SEGMENT_NAME <> 'SYSTEM'
Order By SEGMENT_ID;
Cursor C_Rollback_Online Is
Select SEGMENT_NAME
From dba_rollback_segs
Where SEGMENT_NAME <> 'SYSTEM'
Order By SEGMENT_ID;
Cursor C_Logfile ( L_GROUP_NUM In Varchar2 ) Is
Select A.Group#, A.Bytes, A.Members, B.Member
From V\$LOG A, V\$LOGFILE B
Where A.Group# = B.Group#
And A.Group# = L_GROUP_NUM
Order By A.Group#;
FirstTime Boolean;
L_LINE Varchar2(2000); -- A line to output
L_SID Varchar2(20); -- Oracle Database Name
L_LOGMODE Varchar2(50); -- Archivelog or not
L_CHRSET Varchar2(50); -- NLS character set
L_GROUPS Number(9); -- Nos of Log Groups
L_LOGSIZE Number(9); -- Logfile size
L_MEMBERS Number(9); -- Nos Members per Group
L6 Varchar2(9); -- 6 spaces
L4 Varchar2(9); -- 4 spaces
L2 Varchar2(9); -- 2 spaces
L_MAXEXTENTS Varchar2(20); -- DKapfer required for 'Unlimited'
Begin
L2 := ' ';
L4 := L2||L2;
L6 := L2||L4;

L_LINE := 'Connect Internal'||Chr(10)||
'Set TERMOUT On ECHO On'||Chr(10)||
'Spool createdb_${ORACLE_SID}.log'||Chr(10)||
'Startup nomount'||Chr(10);

dbms_output.put_line(L_LINE);
Select NAME, LOG_MODE
Into L_SID, L_LOGMODE
From V\$DATABASE;
Select VALUE Into L_CHRSET
From V\$NLS_PARAMETERS
Where Upper(PARAMETER) = 'NLS_CHARACTERSET';
Select Max(GROUP#), Max(MEMBERS), Max(BYTES)/1024
Into L_GROUPS, L_MEMBERS, L_LOGSIZE
From V\$LOG;
L_LINE := 'Create Database "'|| L_SID ||'"'|| Chr(10)||
L4||'Maxlogfiles '||To_Char(L_GROUPS*L_MEMBERS*4)||Chr(10)||
L4||'Maxlogmembers '||To_Char(L_MEMBERS*2)||Chr(10)||
L4||'Maxloghistory 160'||Chr(10)||
L4||'Maxdatafiles 255'||Chr(10)||
L4||'Maxinstances 1'||Chr(10)||
L4||'NOARCHIVELOG'||Chr(10)||
L4||'Character Set "'||L_CHRSET||'"'||Chr(10)||
L4||'Datafile';
dbms_output.put_line(L_LINE);
--
-- Get the datafiles for the SYSTEM tablespace
--
FirstTime := TRUE;
For Rec_Datafiles In C_Datafiles ( 'SYSTEM' ) Loop
If FirstTime Then
FirstTime := FALSE;
L_LINE := ' ';
Else
L_LINE := L_LINE||' ,'||Chr(10)||' ';
End If;
L_LINE := L_LINE||
''''||Rec_Datafiles.FILE_NAME||''''||' Size '||
To_Char(Rec_Datafiles.BYTES/1024)||' K';
If Rec_Datafiles.AUTOEXTENSIBLE = 'YES' Then
L_LINE := L_LINE||' Autoextend On';
End If;
End Loop;
L_LINE := L_LINE || Chr(10) || L4 || 'Logfile ';
dbms_output.put_line(L_LINE);
--
-- Create the LOGFILE bits ...
--
FirstTime := TRUE; -- For groups
For L_INDEX In 1.. L_GROUPS Loop
If FirstTime Then
FirstTime := FALSE; -- For groups
L_LINE := ' Group '|| To_Char(L_INDEX) || ' (';
Else
L_LINE := ' ,Group '|| To_Char(L_INDEX) || ' (';
End If;
FirstTime := TRUE; -- For members
For Rec_Logfile In C_Logfile ( L_INDEX ) Loop
If FirstTime Then
FirstTime := FALSE; -- For members
Else
L_LINE := L_LINE ||Chr(10)||L6||L2||' ,';
End If;
L_LINE := L_LINE||''''||Rec_Logfile.MEMBER||'''';
End Loop;
L_LINE := L_LINE || Chr(10)||L6||L2||
' ) Size '||To_Char(L_LOGSIZE)||' K ';
dbms_output.put_line(L_LINE);
End Loop;
--
-- The bootstrapping stuff ...
--
L_LINE := '/'||Chr(10)||Chr(10)||
'Set TERMOUT Off ECHO Off'||Chr(10)||
'@${ORACLE_HOME}/rdbms/admin/catalog.sql'||Chr(10)||
'@${ORACLE_HOME}/rdbms/admin/catproc.sql'||Chr(10)||
'Set TERMOUT On ECHO On'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
--
-- The Rollback segments in the SYSTEM tablespace
--
For Rec_RBS In C_Rollback_Segs ('SYSTEM') Loop
L_LINE := 'Create Rollback Segment '||Rec_RBS.SEGMENT_NAME||
Chr(10)||' Tablespace '||
Rec_RBS.TABLESPACE_NAME||
Chr(10)||' '||'Storage (';

if Rec_RBS.MAX_EXTENTS > 2000000000 or Rec_RBS.MAX_EXTENTS is null then /* DKapfer */
L_MAXEXTENTS := 'Unlimited';
else
L_MAXEXTENTS := To_Char(Rec_RBS.MAX_EXTENTS);
end if;

L_LINE := L_LINE||Chr(10)||' Initial '||
To_Char(Rec_RBS.INITIAL_EXTENT/1024)||' K'||
Chr(10)||' Next '||
To_Char(Rec_RBS.NEXT_EXTENT/1024)||' K'||
Chr(10)||' Minextents '||
To_Char(Rec_RBS.MIN_EXTENTS)||
Chr(10)||' Maxextents '||
L_MAXEXTENTS || /* DKapfer */
-- Rec_RBS.MAX_EXTENTS|| /* DKapfer */
Chr(10)||' Optimal '||
To_Char(Rec_RBS.MIN_EXTENTS *
Rec_RBS.NEXT_EXTENT/1024)||' K'||Chr(10)
||' )'||Chr(10)||'/'||Chr(10)
;
dbms_output.put_line(L_LINE);
End Loop;

--
-- Create all other tablespaces ...
--
For Rec_Tablespaces In C_Tablespaces Loop
L_LINE := 'Create Tablespace '||Rec_Tablespaces.TABLESPACE_NAME;
dbms_output.put_line(L_LINE);
FirstTime := TRUE;
For Rec_Datafiles In
C_Datafiles ( Rec_Tablespaces.TABLESPACE_NAME ) Loop
If FirstTime Then
FirstTime := FALSE;
L_LINE := ' Datafile ';
Else
L_LINE := ' ,';
End If;
L_LINE := L_LINE||''''||Rec_Datafiles.FILE_NAME||''''||
' Size '||
To_Char(Rec_Datafiles.BYTES/1024)||' K';
If Rec_Datafiles.AUTOEXTENSIBLE = 'YES' Then
L_LINE := L_LINE||' Autoextend On';
End If;
dbms_output.put_line(L_LINE);
End Loop;

For Rec_TS_Info In
C_Tablespace_Info( Rec_Tablespaces.TABLESPACE_NAME ) Loop
if Rec_TS_Info.MAX_EXTENTS > 2000000000 or Rec_TS_Info.MAX_EXTENTS is null then /* DKapfer */
L_MAXEXTENTS := 'Unlimited';
else
L_MAXEXTENTS := To_Char(Rec_TS_Info.MAX_EXTENTS);
end if;

L_LINE :=' Default Storage ( '||
Chr(10)||' Initial '||
To_Char(Rec_TS_Info.INITIAL_EXTENT/1024)||' K'||
Chr(10)||' Next '||
To_Char(Rec_TS_Info.NEXT_EXTENT/1024)||' K'||
Chr(10)||' Minextents '||
To_Char(Rec_TS_Info.MIN_EXTENTS)||
Chr(10)||' Maxextents '||
L_MAXEXTENTS || /* DKapfer */
-- Rec_TS_Info.MAX_EXTENTS|| /*DKapfer*/
Chr(10)||' Pctincrease '||
To_Char(Rec_TS_Info.PCT_INCREASE)||
Chr(10)||
' )'
;
L_LINE := L_LINE||Chr(10)||' '||
'Extent Management '||Rec_TS_Info.EXTENT_MANAGEMENT||chr(10);
L_LINE := L_LINE||' '||Rec_TS_Info.LOGGING||Chr(10);
L_LINE := L_LINE||' Minimum Extent '||Rec_TS_Info.MIN_EXTLEN;
L_LINE := L_LINE||Chr(10)||' '||
Rec_TS_Info.CONTENTS||Chr(10)||'/'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
End Loop;

--
-- Create all Rollback segments in the tablespace being created ...
--
For Rec_RBS In C_Rollback_Segs (Rec_Tablespaces.TABLESPACE_NAME) Loop
L_LINE := 'Create Rollback Segment '||Rec_RBS.SEGMENT_NAME||
Chr(10)||' Tablespace '||
Rec_RBS.TABLESPACE_NAME||
Chr(10)||' '||'Storage (';
if Rec_RBS.MAX_EXTENTS > 2000000000 or Rec_RBS.MAX_EXTENTS is null then /* DKapfer */
L_MAXEXTENTS := 'Unlimited';
else
L_MAXEXTENTS := To_Char(Rec_RBS.MAX_EXTENTS);
end if;

L_LINE := L_LINE||Chr(10)||' Initial '||
To_Char(Rec_RBS.INITIAL_EXTENT/1024)||' K'||
Chr(10)||' Next '||
To_Char(Rec_RBS.NEXT_EXTENT/1024)||' K'||
Chr(10)||' Minextents '||
To_Char(Rec_RBS.MIN_EXTENTS)||
Chr(10)||' Maxextents '||
L_MAXEXTENTS || /* DKapfer */
-- Rec_RBS.MAX_EXTENTS|| /* DKapfer */
Chr(10)||' Optimal '||
To_Char(Rec_RBS.MIN_EXTENTS *
Rec_RBS.NEXT_EXTENT/1024)||' K'||Chr(10)
||' )'||Chr(10)||'/'||Chr(10)
;
dbms_output.put_line(L_LINE);
End Loop;
End Loop;
--
-- Alter all Rollback segments Online ...
--
For Rec_RBSO In C_Rollback_Online Loop
L_LINE := 'Alter Rollback Segment '||Rec_RBSO.SEGMENT_NAME||' Online;';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := Chr(10);
dbms_output.put_line(L_LINE);

--
-- Get the profiles information
--

For Rec_Prof In C_Profiles Loop
L_LINE := 'Create Profile '||Rec_Prof.PROFILE
||' Limit SESSIONS_PER_USER Default;';
dbms_output.put_line(L_LINE);
For Rec_ProfData In C_Profile_Info (Rec_Prof.PROFILE) Loop
L_LINE :=
'Alter Profile '||Rec_ProfData.PROF||' LIMIT '
||Rec_ProfData.RESOURCE_NAME||' '
||Rec_ProfData.LIMIT||';';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := Chr(10);
dbms_output.put_line(L_LINE);
End Loop;

--
-- Get the data for the Admins
--
FirstTime := TRUE;
For Rec_AdminData In C_Admin_Info Loop
If FirstTime Then
FirstTime := FALSE;
L_LINE := '';
Else
L_LINE := L_LINE||Chr(10);
End If;
L_LINE := L_LINE||
'Alter User '||Rec_AdminData.USERNAME||Chr(10)||L4
||'Default Tablespace '||Rec_AdminData.DEFAULT_TABLESPACE||Chr(10)||L4
||'Temporary Tablespace '||Rec_AdminData.TEMPORARY_TABLESPACE||';'
||Chr(10)||'/'||Chr(10);
End Loop;

L_LINE := L_LINE||Chr(10);
dbms_output.put_line(L_LINE);
--
-- Get the data for the other users
--
For Rec_UserData In C_User_Info Loop
L_LINE :=
'Create User '||Rec_UserData.USERNAME
||' Identified By '||Rec_UserData.USERNAME||Chr(10)||L4
||'Default Tablespace '||Rec_UserData.DEFAULT_TABLESPACE||Chr(10)||L4
||'Temporary Tablespace '||Rec_UserData.TEMPORARY_TABLESPACE||Chr(10)||L4
||'Profile '||Rec_UserData.PROFILE
||';'||Chr(10)||'/'||Chr(10);
dbms_output.put_line(L_LINE);
End Loop;

For Rec_Quota In C_Quota_Info Loop
L_LINE := 'Alter User '||Rec_Quota.USERNAME||' Quota ';
If Rec_Quota.MAX_BYTES = -1 Then
L_LINE := L_LINE||'Unlimited On ';
Else
L_LINE := L_LINE||Rec_Quota.MAX_BYTES||' On ';
End If;
L_LINE := L_LINE||Rec_Quota.TABLESPACE_NAME||';';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := '/'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);

For Rec_Role In C_Role_Info Loop
L_LINE := 'GRANT '||Rec_Role.GRANTED_ROLE
||' To '||Rec_Role.GRANTEE;
If Rec_Role.ADMIN_OPTION = 'YES' Then
L_LINE := L_LINE||' With Admin Option';
End If;
L_LINE := L_LINE||';';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := '/'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
For Rec_SysPriv In C_SysPriv_Info Loop
L_LINE := 'GRANT '||Rec_SysPriv.Privilege
||' To '||Rec_SysPriv.GRANTEE;
If Rec_SysPriv.ADMIN_OPTION = 'YES' Then
L_LINE := L_LINE||' With Admin Option';
End If;
L_LINE := L_LINE||';';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := '/'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
For Rec_TabPriv In C_TabPriv_Info Loop
L_LINE := 'GRANT '||Rec_TabPriv.PRIVILEGE
||' On '||Rec_TabPriv.GRANTOR||'.'||Rec_TabPriv.TABLE_NAME
||' To '||Rec_TabPriv.GRANTEE;
If Rec_TabPriv.GRANTABLE = 'YES' Then
L_LINE := L_LINE||' With Grant Option';
End If;
L_LINE := L_LINE||';';
dbms_output.put_line(L_LINE);
End Loop;
L_LINE := '/'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
L_LINE := 'Connect System/Manager'||Chr(10)||
'Set TERMOUT Off ECHO Off'||Chr(10)||
'@${ORACLE_HOME}/rdbms/admin/catdbsyn'||Chr(10)|| /*DKapfer*/
'@${ORACLE_HOME}/sqlplus/admin/pupbld'||Chr(10)|| /*DKapfer*/
'Set TERMOUT On ECHO On'||Chr(10);
dbms_output.put_line(L_LINE);
L_LINE := 'Connect Internal'||Chr(10)||
'Shutdown'||Chr(10)||
'Startup'||Chr(10)||Chr(10);
dbms_output.put_line(L_LINE);
If L_LOGMODE = 'ARCHIVELOG' Then
L_LINE := 'Shutdown immediate'||Chr(10)||
'Startup Mount'||Chr(10)||
'Alter Database ARCHIVELOG;'||Chr(10)||
'Alter Database Open;'||Chr(10);
dbms_output.put_line(L_LINE);
End If;
L_LINE := 'Spool Off'||Chr(10)||'Exit'||Chr(10);
dbms_output.put_line(L_LINE);
End;
/
Spool Off
Exit
EOF
echo $ORACLE_SID
exit 0


Shows current statements for active sessions.TXT
==================================================
rem *********************************************************************
rem * Copyright © Oracle-Consultant.co.uk 2001, all rights reserved *
rem * *
rem * Name : cursta.sql *
rem * Synopsis : Shows current statements for active sessions *
rem * Source : http://www.oracle-consultant.co.uk *
rem * *
rem * Oracle-Consultant.co.uk are not responsible for any liability *
rem * that may arise from the use of this code. Support can be obtained *
rem * by emailing script_support@oracle-consultant.co.uk *
rem * Note: This script is best viewed in a fixed-width font. *
rem *********************************************************************

column pu format a8 heading 'O/S|Login|ID'
column su format a8 heading 'Oracle|User ID'
column stat format a8 heading 'Session|Status'
column ssid format 999999 heading 'Oracle|Session|ID'
column sser format 999999 heading 'Oracle|Serial|No'
column spid format 99999999 heading 'O/S|Process|ID'
column txt format a2000 heading 'Current Statement' wrapped

set linesize 1000
set pagesize 32000
set trimspool on

select p.username pu
, s.username su
, s.status stat
, s.sid ssid
, s.serial# sser
, substr(p.spid,1,8) spid
, substr(sa.sql_text,1,2000) txt
from v$process p
, v$session s
, v$sqlarea sa
where p.addr = s.paddr
and s.username is not null
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
and s.status = 'ACTIVE'
order by 1,2,7
/

column pu clear
column su clear
column stat clear
column ssid clear
column sser clear
column spid clear
column txt clear


Shows status of DBMS jobs .TXT
===============================
rem *********************************************************************
rem * Copyright © Oracle-Consultant.co.uk 2001, all rights reserved *
rem * *
rem * Name : jobs.sql *
rem * Synopsis : Shows status of DBMS jobs *
rem * Source : http://www.oracle-consultant.co.uk *
rem * *
rem * Oracle-Consultant.co.uk are not responsible for any liability *
rem * that may arise from the use of this code. Support can be obtained *
rem * by emailing script_support@oracle-consultant.co.uk *
rem * Note: This script is best viewed in a fixed-width font. *
rem *********************************************************************

column this_date format a15
column next_date format a15
column last_date format a15
column what format a60
column failures format 999
column job format 999
column hidden_last_date noprint

set linesize 1000
set pagesize 32000

break on hidden_last_date skip 1

select job
, to_char(last_date, 'DD-MON-YY HH24:MI') last_date
, trunc(last_date) hidden_last_date
, to_char(this_date, 'DD-MON-YY HH24:MI') this_date
, to_char(next_date, 'DD-MON-YY HH24:MI') next_date
, broken
, failures
, what
from sys.dba_jobs
order by 2
/

column this_date clear
column next_date clear
column last_date clear
column what clear
column failures clear
column job clear
column hidden_last_date clear


space occupied by each user .TXT
==================================
select owner, sum(blocks) Totalblocks, sum(bytes)TotalBytes from DBA_SEGMENTS group by owner



tablespace usage.sql
======================

column file_name format a45
column tablespace_name format a10

set verify off
accept tbs prompt 'What is the Tablespace Name: '


SELECT dts.tablespace_name,
NVL(ddf.bytes / 1024 / 1024, 0) avail,
NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,
NVL(dfs.bytes / 1024 / 1024, 0) free,
TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00')
"Used %" ,
TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes
* 100, 0), '990.00') free_pct,
decode(sign(
(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 /
1024, 0)),-1,0,(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 / 1024, 0)) "Required MB"
FROM
sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) dfs
WHERE
dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+)
and dts.tablespace_name = '&tbs';

select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name = '&tbs';


Tabular display of redo-log archiving history (logs hour).TXT
================================================================
rem -----------------------------------------------------------------------
rem Filename: archdist.sql
rem Purpose: Tabular display of redo-log archiving history (logs/hour)
rem - Can only run from sqlplus
rem Author: Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

set pagesize 50000
set veri off
set colsep ""

set termout off
def time="time" -- Oracle7
col time new_value time
select 'to_char(first_time,''DD/MM/YY HH24:MI:SS'')' time
from dual
where &&_O_RELEASE like '8%' -- Oracle8
/
set termout on

select substr(&&time, 1, 5) day,
to_char(sum(decode(substr(&&time,10,2),'00',1,0)),'99') "00",
to_char(sum(decode(substr(&&time,10,2),'01',1,0)),'99') "01",
to_char(sum(decode(substr(&&time,10,2),'02',1,0)),'99') "02",
to_char(sum(decode(substr(&&time,10,2),'03',1,0)),'99') "03",
to_char(sum(decode(substr(&&time,10,2),'04',1,0)),'99') "04",
to_char(sum(decode(substr(&&time,10,2),'05',1,0)),'99') "05",
to_char(sum(decode(substr(&&time,10,2),'06',1,0)),'99') "06",
to_char(sum(decode(substr(&&time,10,2),'07',1,0)),'99') "07",
to_char(sum(decode(substr(&&time,10,2),'08',1,0)),'99') "08",
to_char(sum(decode(substr(&&time,10,2),'09',1,0)),'99') "09",
to_char(sum(decode(substr(&&time,10,2),'10',1,0)),'99') "10",
to_char(sum(decode(substr(&&time,10,2),'11',1,0)),'99') "11",
to_char(sum(decode(substr(&&time,10,2),'12',1,0)),'99') "12",
to_char(sum(decode(substr(&&time,10,2),'13',1,0)),'99') "13",
to_char(sum(decode(substr(&&time,10,2),'14',1,0)),'99') "14",
to_char(sum(decode(substr(&&time,10,2),'15',1,0)),'99') "15",
to_char(sum(decode(substr(&&time,10,2),'16',1,0)),'99') "16",
to_char(sum(decode(substr(&&time,10,2),'17',1,0)),'99') "17",
to_char(sum(decode(substr(&&time,10,2),'18',1,0)),'99') "18",
to_char(sum(decode(substr(&&time,10,2),'19',1,0)),'99') "19",
to_char(sum(decode(substr(&&time,10,2),'20',1,0)),'99') "20",
to_char(sum(decode(substr(&&time,10,2),'21',1,0)),'99') "21",
to_char(sum(decode(substr(&&time,10,2),'22',1,0)),'99') "22",
to_char(sum(decode(substr(&&time,10,2),'23',1,0)),'99') "23"
from sys.v_$log_history
group by substr(&&time,1,5)
/

set colsep " "



to find when was the last reset logs done.txt
================================================
select STATUS,ERROR,RECOVER,FUZZY,CREATION_TIME,RESETLOGS_TIME from v$datafile_header where rownum<10;

to find when was the resetlogs for a particular datafile

select STATUS,ERROR,RECOVER,FUZZY,CREATION_TIME,RESETLOGS_TIME from v$datafile_header where file#=397;


utlfile.txt
=============
CREATE DIRECTORY test_dir AS 'c:\';
-- CREATE DIRECTORY test_dir AS '/tmp';

DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('test_dir', 'test_file.txt', 'W');
UTL_FILE.PUTF(fileHandler, 'Look ma, I''m writing to a file!!!\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file.');
END;
/




whereami.sql
==============
/* whereami.sql
provides info on db instance, server etc for your session
*/
set pause off
set pages 24
set lines 132
col SERVER format a10 trunc
col program format a30 trunc
col username format a10 trunc
col sid format 9999
col serial# format 99999 head SRL#
col value format a6 head THREAD
col spid head OSProc
col thread# head THREAD format 999
col open_mode head DBSTAT
col startedon format a13
select p.machine SERVER, d.name DBNAME, i.thread#, i.status ISTAT,
to_char(i.startup_time,'mm/dd hh:miAM') startedon,
u.username, u.sid, u.serial#, u.program, o.spid
from v$database d, v$instance i, v$session p, v$session u, v$process o
where p.sid = 1
and u.sid = (select sid from v$mystat where rownum = 1)
and o.addr = u.paddr;
clear columns




worked performed by sesison.sql
==============================
/* sesswork.sql
show the work performed by a session
*/
accept trgtsid number prompt 'What is the SID : '
select n.name,s.value from v$sesstat s, v$statname n
where sid = &trgtsid
and s.statistic# = n.statistic#
and s.value > 0
and n.name not in ('session connect time','process last non-idle time')
order by 2;

prompt Specific Buffer Cache Hit ratio values.....
select n.name,s.value from v$sesstat s, v$statname n
where sid = &trgtsid
and s.statistic# = n.statistic#
and n.name in ('consistent gets','db block gets','physical reads');

prompt Specific Sorting values.....
select n.name,s.value from v$sesstat s, v$statname n
where sid = &trgtsid
and s.statistic# = n.statistic#
and n.name like '%sort%';