| | Display free, used and total space per tablespace in a database
Query to get tablespace information from Oracle database - total, used and free space, extents etc.
select a.tablespace_name, round(c.tbytes/1024/1024, 2) as TMB,
round(b.ubytes/1024/1024, 2) as UMB,
round((c.tbytes - b.ubytes)/1024/1024, 2) as FMB,
a.initial_extent, a.next_extent, a.max_extents, a.status, a.extent_management
from
(select tablespace_name, initial_extent, next_extent, max_extents, status, extent_management
from dba_tablespaces) ... | |  
More...
10/21/05 | | | | | | | | |
|
| | Explain plan for an SQL query
1. Create the plan table
SQLPLUS> @${ORACLE_HOME}/rdbms/admin/utlxplan.sql
2. Delete old plan from the table and run explain plan.
Replace 'select * from v$database' with the actuall sql statement you want explain plan for.
set lin 180
set pages 0
set feedback on
set echo off
delete from plan_table
where STATEMENT_ID = 'p1'
/
commit
/
EXPLAIN PLAN SET STATEMENT_ID = 'p1' FOR ... | | More...
10/13/05 | | | | | | | | |
|
| | Delete a Service on Windows
Normally uninstalling the application would also remove the service, but sometimes you have to do it manually. This procedure may be applied to Windows NT 4, Windows 2000 and Windows XP.
1. Stop the service you want to remove
2. Run regedit or regedt32
3. Find the registry entry:
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services
4. Find the service name ... | | 
More...
10/05/05 | | | | | | | | |
|
| | Find out which object has corrupted block
--
-- Find out the file id and block id, usually from alert.log
-- or from error message when running sql statement
--
-- Replace F with the file id and B with block id
--
SELECT SEGMENT_NAME, SEGMENT_TYPE, RELATIVE_FNO
FROM DBA_EXTENTS
WHERE FILE_ID = F
AND B BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
/ | |
More...
10/04/05 | | | | | | | | |
|
| | Generate a script to unbreak / break Oracle jobs
--
-- Generate a script to unbreak jobs for a schema
-- Replace USERNAME with schema owner and PASSWORD with schema owner's password
--
-- To break the jobs replace:
-- spool unbreak_jobs.sql -> spool break_jobs.sql
-- broken=>FALSE -> broken=>TRUE
-- where broken = 'Y' -> where broken = 'N'
--
set echo off verify off feedback off ... | |
More...
10/03/05 | | | | | | | | |
|
| | Generate a script to run complete refresh for all tables in schema
-- Replace USERNAME with schema owner who's snapshots are to be refreshed
set echo off verify off feedback off head off pagesize 0
spool comp_refresh.sql
select 'set head on feedback on verify on echo on'
from dual
/
select 'spool comp_refresh.log'
from dual
/
select 'exec DBMS_SNAPSHOT.REFRESH (''' || 'USERNAME.' || name || '''' || ',' || '''' ... | | More...
10/03/05 | | | | | | | | |
|
| | Generate an analyze script for all tables in schema
-- Replace USERNAME with the schema owner who's tables are to be analyzed.
set head off pagesize echo off verify off feedback off linesize 200
spool analyze.sql
select 'set head on echo on verify on feedback on'
from dual
/
select 'spool analyze.log'
from dual
/
select 'exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>' || '''' || owner ||
'''' || ', ' || ... | | More...
10/03/05 | | | | | | | | |
|
|
|
|
<< 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | Page 19 | 20 | 21 >>
|
