REM =============================================================================== REM = Script Name : db_status.sql = REM = Usage : This script is to be used to give an overview of the current status = REM = of a given database. Things like suspect default and temporary = REM = tablespaces, tablespaces that have less than 20% free, objects that = REM…
set pagesize 300 set linesize 205 set head on set echo off spool tblwopk accept usrid prompt ‘Enter User Name : ‘ REM Find all tables that does not have primary key on them. select /*+ rule */ a.table_name from dba_tables a where a.owner = upper(‘&usrid’) and a.table_name not in ( select b.table_name from dba_constraints…
set pagesize 300 set linesize 205 set head on set echo off spool tblwopk accept usrid prompt ‘Enter User Name : ‘ accept table_name prompt ‘Enter Table Name : ‘ column PrimaryKey format a80 heading ‘Follwoing Are Primary Keys’ select substr(a.column_name, 1, 20) || ‘ Position : ‘ || to_char(a.position) PrimaryKey from dba_cons_columns a, dba_constraints…
set pagesize 300 set linesize 205 set head on set echo off spool fkwoind accept usrid prompt ‘Enter User Name : ‘ col “Cons Name” format a30 heading “Cons Name” word_wrapped col status format a10 heading status col “Cons Type” format a12 heading “Cons Type” col “Column Name” format a30 heading “Column Name” col “Cons…
accept usrnm prompt “Enter The User Name -> ” accept tblnm prompt “Enter The Table Name -> ” select index_name, substr(column_name,1,15) “Colm Name”, column_position “Position” from dba_ind_columns where table_name = upper(‘&tblnm’) and table_owner = upper(‘&usrid’) order by index_name, column_position;
select table_name, tablespace_name from dba_tables where owner = upper(‘&usrnm’) order by table_name /
REM Author : Paresh Mehta REM Created : June 4, 2004 REM Purpose : Change the look of sql prompt (for 9i). set echo off set numwidth 9 set linesize 100 set pagesize 24 Set head off set time on set timing on define _PVMUSRNM=”” column USERNAME new_value _PVMUSRNM; column name new_value _PVMDBSTR; select username…
select /*+ all_rows */ distinct owner, segment_name, segment_type from dba_extents where file_id = &filid and &blkid between block_id and block_id+blocks-1;
truncate table plan_table; EXPLAIN PLAN SET STATEMENT_ID = ‘pvm’ FOR select distinct owner, segment_name, segment_type from dba_extents where file_id = &filid and &blkid between block_id and block_id+blocks-1; — syntax for viewing information from plan_table SELECT LPAD(‘ ‘,2*(LEVEL-1))||operation||’ ‘||options ||’ ‘||object_name ||’ ‘|| DECODE(id, 0, ‘Cost = ‘||position) “Query Plan” FROM plan_table START WITH id =…
REM Author : Paresh Mehta REM Created : June 4, 2004 REM Purpose : Change the look of sql prompt (for 9i). set echo off set numwidth 9 set linesize 100 set pagesize 24 Set head off set time on set timing on define _PVMUSRNM=”” column USERNAME new_value _PVMUSRNM; column name new_value _PVMDBSTR; select username…
