SELECT TABLESPACE_NAME "Tablespace", (TOTAL_BLOCKS * 8192) "Total Size", (USED_BLOCKS * 8192 ) "Used" ,(FREE_BLOCKS * 8192) "Free" , (MAX_USED_BLOCKS * 8192 ) "Max Used" FROM V$SORT_SEGMENT;
To find temporary tablespace usage query
"SELECT s.sid ""SID"",s.username ""User"",s.program ""Program"", u.tablespace ""Tablespace"",
u.contents ""Contents"", u.extents ""Extents"", u.blocks*8/1024 ""Used Space in MB"", q.sql_text ""SQL TEXT"",
a.object ""Object"", k.bytes/1024/1024 ""Temp File Size""
FROM v$session s, v$sort_usage u, v$access a, dba_temp_files k, v$sql q
WHERE s.saddr=u.session_addr
and s.sql_address=q.address
and s.sid=a.sid
and u.tablespace=k.tablespace_name;
To find free space,alloated space in TEMP tablespace
select TABLESPACE_NAME, BYTES_USED/1024/1024,BYTES_FREE/1024/1024 from v$temp_space_header;
TABLESPACE_NAME BYTES_USED/1024/1024
BYTES_FREE/1024/1024
------------------------------ --------------------
--------------------
TEMP 1100
900
select TABLESPACE_NAME,BYTES/1024/1024 from dba_temp_files where TABLESPACE_NAME ='%TEMP%';
select TABLESPACE_NAME,BYTES/1024/1024 from dba_temp_files;
TABLESPACE_NAME BYTES/1024/1024
------------------------------ ---------------
TEMP 1100
find if it is autoextensible:
select substr(file_name,1,35),AUTOEXTENSIBLE,bytes/1024/1024 from dba_temp_files;
SUBSTR(FILE_NAME,1,35) AUT
BYTES/1024/1024
--- ---------------
/ge2vis/oradata/data01/temp01.dbf NO
1100
add tempfile or resize:
ALTER TABLESPACE TEMP ADD TEMPFILE '/dbtemp/PROD/temp01.dbf'
SIZE 2000M REUSE AUTOEXTEND ON NEXT 8192 MAXSIZE 2000M;
or
alter database tempfile '/ge2vis/oradata/data01/temp01.dbf' resize 2000m ;
How can one see who is using a temporary segment?
For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.
All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment.
If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT
From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these examples:
select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks
from sys.v_$session s, sys.v_$sort_usage u
where s.saddr = u.session_addr
/
select s.osuser, s.process, s.username, s.serial#,
sum(u.blocks)*vp.value/1024 sort_size
from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vp
where s.saddr = u.session_addr
and vp.name = 'db_block_size'
and s.osuser like '&1'
group by s.osuser, s.process, s.username, s.serial#, vp.value
/
How would you check if the tablespace is in backup mode ?- Check in v$backup
SQL> SELECT d.tablespace_name, b.status FROM dba_data_files d, v$backup b
WHERE d.file_id = b.FILE# and b.STATUS = ‘ACTIVE’ ;
Creating dictionary mangaed temporary tablespsce
create tablespace temp datafile '/d14/oracle/proddata/temp01.dbf' size 2048M temporary default storage (initial 5m next 5m pctincrease 0);
To find ablespace free size
set pagesize 0 linesize 200 heading off feedback off
column total_bytes format 999999999999
column free_bytes format 999999999999
select d.tablespace_name,
d.num_files num_files,
f.bytes free_bytes,
-- d.file_bytes file_bytes,
d.maxbytes bytes ,
round((d.file_bytes/d.maxbytes)*100,0) percent_used
from (select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) f,
(select tablespace_name, count(distinct(file_id)) num_files,
sum(bytes) file_bytes,
sum(decode(autoextensible,'YES',maxbytes,bytes)) maxbytes
from dba_data_files group by tablespace_name) d
where d.tablespace_name = f.tablespace_name
and d.tablespace_name not like 'DW%'
and d.tablespace_name not like 'RBS%'
and d.tablespace_name not like '%UNDO%'
and d.tablespace_name not like '%TEMP%'
order by d.tablespace_name;
quit;
To find free space , available space and % of used space in a tablespace use
SELECT tba, free_spc, avail_spc, free_spc/avail_spc "free %"
FROM
( SELECT tablespace_name TBA,
SUM ( bytes ) free_spc
FROM dba_free_space
GROUP BY tablespace_name ),
( SELECT tablespace_name TBB,
SUM ( bytes ) avail_spc
FROM dba_data_files
GROUP BY tablespace_name )
where TBA='APPS_TS_INTERFACE'
SELECT tablespace_name ,SUM ( bytes ) free_spc
FROM dba_free_space
where tablespace_name ='APPS_TS_INTERFACE'
group by tablespace_name ;
select a.tablespace_name, b.tot_space, a.free_space from ( select tablespace_name, sum (bytes) free_space from dba_free_space group by tablespace_name) a, ( select tablespace_name, sum( bytes) tot_space from dba_data_files group by tablespace_name )b where a.tablespace_name = b.tablespace_name and ( a.free_space / b.tot_space ) * 100 < 10;
select tba,free_spc,avail_spc, free_spc/avail_spc "free%" FROM
(select tablespace_name TBA, SUM(bytes) free_spc FROM dba_free_space GROUP BY tablespace_name),
(select tablespace_name TBB, SUM(bytes) avail_spc FROM dba_data_files GROUP By tablespace_name)
WHERE TBB=TBA(+) AND free_spc/avail_spc <.2 ;
To check which all tablespaces are less than 15 % free etc
select d1.tablespaces_name , sum(d2.bytes)/sum(d1.bytes) “free”
from dba_data_files d1, dba_free_space d2
where d1.tablespace_name=d2.tablespace_name
group by d1.tablespace_name having ( sum(d2.bytes)/sum(d1.bytes) ) < 0.15;
Table move to new tablespace command
rebuild is new tablespace created before and added 10 GB space in that
alter TABLE owner.table_name move tablespace REBUILD storage (initial 500m next 500m minextents 5 maxextents unlimited pctincrease 0);
alter index owner.name rebuild online tablespace REBUILD parallel 2;
alter table AP.AP_SELECTED_INVOICES_ALL move tablespace reorg;
"SQL> alter table AP.AP_SELECTED_INVOICES_ALL move tablespace reorg;
alter table AP.AP_SELECTED_INVOICES_ALL move tablespace reorg
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'REORG'
SQL> ALTER USER AP QUOTA unlimited on reorg;
User altered."
Set auto extend on for all data files in the database with a maxsize of 2GB
Login as Oracle user
You can run the following SELECT to generate auto extend script for all data files and spool it to a file.
$ sqlplus system/xxxx
SQL> set pagesize 0
SQL> set linesize 140
SQL> set feedback off
SQL> spool autoextfile.sql
SQL> select 'alter database datafile '||chr (39)||file_name||chr (39)||' autoextend on next 10M maxsize 2000m;' from dba_data_files;
SQL> exit;
Edit the file autoextfile.sql appropriately and then run it.
$ sqlplus system/ @autoextfile.sql
Database Corrupted Block Information
select * from v$database_block_corruption;
select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=&file_no and &block_number between block_id and block_id + blocks -1;
TO Know tablespaces which have less than 20% freee space
SQL> select tba, free_spc, avail_spc, free_spc/avail_spc "free%" from
2 (select tablespace_name TBA, sum(bytes) free_spc from dba_free_space group by tablespace_name),
3 (select tablespace_name TBB, sum(bytes) avail_spc from dba_data_files group by tablespace_name)
4 where TBB=TBA(+) and free_spc/avail_spc<.2;
command to rebuild the QP_QUALIFIERS_N1 and the QP_QUALIFIERS_N2 indexes and gather the stats on the QP_QUALIFIERS table by logging as APPS
alter session set db_file_multiblock_read_count = 32;
alter index QP.QP_QUALIFIERS_N2 rebuild;
alter index QP.QP_QUALIFIERS_N1 rebuild;
exec fnd_stats.gather_table_stats(OWNNAME=>'QP',TABNAME=>'QP_QUALIFIERS',percent => '100', granularity => 'ALL', partname => NULL,cascade =>true, degree => 4 );
alter session set db_file_multiblock_read_count = 8;
To find HWM , blocks is HWM
SELECT blocks, empty_blocks, num_rows FROM dba_tables WHERE table_name ='AP_SELECTED_INVOICE_CHECKS_ALL';
Command to Rebuild index and gather stats
spool ind.sql
select 'alter index '||index_name||' rebuild nologging parallel 8;' from user_indexes
where index_name not like 'SYS%' and index_name not like '%_FK_%' and index_name not like '%_PK';
spool off
few more eg:
alter index GL.GL_INTERFACE_CONTROL_N1 rebuild parallel 4 nologging;
alter index APPLSYS.FND_HELP_TARGETS_N1 rebuild online nologging parallel 8;
select 'alter index '||owner||'.'||index_name||' rebuild online parallel tablespace
exec fnd_stats.gather_table_stats(owner=>'GL',tabname=>'GL_INTERFACE',percent=>10);
command to move table, index, table partition, index partition of owner = MSC
o/p like
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_0 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_1023 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_DEMANDS move partition ALLOC_DEMANDS_2023 tablespace APPS_TS_TX_DATA;
alter TABLE MSC.MSC_ALLOC_SUPPLIES move partition ALLOC_SUPPLIES_0 tablespace APPS_TS_TX_DATA;
.
.
.
.
.
.
alter INDEX MSC.MSC_X_EXCEPTION_DETAILS_U1 rebuild online tablespace APPS_TS_TX_IDX;
alter INDEX MSC.MSC_ZONE_REGIONS_N1 rebuild online tablespace APPS_TS_TX_IDX;
alter INDEX MSC.MSC_ZONE_REGIONS_N2 rebuild online tablespace APPS_TS_TX_IDX;
Set pages 0
Set trimspool on
Set lines 300
Spool moveobj.sql
select 'alter ' || decode( segment_type, 'TABLE', 'TABLE', 'INDEX','INDEX','TABLE PARTITION','TABLE','INDEX PARTITION','INDEX')
|| ' ' || owner||'.'|| segment_name || decode(segment_type, 'TABLE',' move ','INDEX',' rebuild online ','TABLE PARTITION',' move partition ','INDEX PARTITION',' rebuild partition ') || decode (segment_type, 'TABLE PARTITION',partition_name,'INDEX PARTITION',partition_name||' online ') || ' tablespace '||tablespace_name ||';' from dba_segments
where segment_type in ( 'TABLE', 'INDEX', 'TABLE PARTITION','INDEX PARTITION') and owner = 'MSC' and segment_name not like '%MSC_ERRORS%' order by segment_type desc, segment_name ASC
/
How to create index on a table: As Apps Database user in PROD, run the following sql statements:
SQL> drop index po.PO_HEADERS_N5 ;Make sure the ""index dropped "" message comes through.
SQL> CREATE INDEX PO.PO_HEADERS_N5 ON PO.PO_HEADERS_ALL
(WF_ITEM_TYPE, WF_ITEM_KEY)
LOGGING
TABLESPACE PO_IDX
PCTFREE 0
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 48K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 4
FREELIST GROUPS 4
BUFFER_POOL DEFAULT
)
NOPARALLEL;
Make sure the ""index created"" message comes through.
To take backup of table:
create table XYZ as select * from orig_name ;
Find if all datafiles are autoextensible
select distinct AUTOEXTENSIBLE from dba_data_files ;
o/p is ‘NO’ or ‘YES’
script to create all tablespaces with diffrent mount locations
set pages 0
set feedback off
set lines 200
spool tbscreate.sql
select 'create tablespace '||tablespace_name||' datafile ''/topusi/oradata/data01/'||lower(tablespace_name)||'01.dbf'' size '|| bytes *1.0 || ' autoextend on maxsize 1999m;'
from dba_data_files
order by tablespace_name
/
select 'create tablespace '||tablespace_name||' tempfile ''/topusi/oradata/data01/'||lower(tablespace_name)||'01.dbf'' size '|| bytes *1.0 || ' autoextend on maxsize 1999m;'
from dba_temp_files
order by tablespace_name
/
spool off
To create a tablespace: eg XDO
create tablespace xdod datafile '/d01/oradata/data03/xdod01.dbf' size 20m autoextend on maxsize 2000m;
To autoextend all datafiles
Ensure all datafile are autoextensible: Spool & Run this script & fix it for specific datafiles of size more than 4000M: -
select 'alter database datafile '||''''||file_name||''''||' autoextend on next 50M maxsize 4000M;' from dba_data_files;
You can run the following SELECT to generate auto extend script for all data files and spool it to a file
Edit the file autoextfile.sql appropriately and then run it. $sqlplus system/ @autoextfile.sql
$ sqlplus system/
SQL> set pagesize 0
SQL> set linesize 140
SQL> set feedback off
SQL> spool autoextfile.sql
SQL> select 'alter database datafile '||chr(39)||file_name||chr(39)||' autoextend on next 10M maxsize 2000m;' from dba_data_files;
SQL> exit;
Before resizing tablespace check :to make sure that there is space or not left for the tablespace and then resize or add datafile
select tablespace_name, max(bytes/1024/1024) from dba_free_space
where tablespace_name ='APPLSYSD' group by tablespace_name;
usn corresponds to segment_id in dba_rollback_segs;
select usn , optsize,extents,hwmsize from v$rollstat where usn=2;
select SEGMENT_NAME,OWNER,INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,MAX_EXTENTS from dba_rollback_segs where SEGMENT_ID=2;
To list all tables
select * from tab where tname like “%…%’;
To check constraint name in a table
select constraint_name from dba_constraints;
Adding a datafile
alter tablespace CNX add datafile '/data/oravis/visdata/cnx_03.dbf' size 1200M AUTOEXTEND ON NEXT 20M MAXSIZE 2000M;
alter tablespace GLD add datafile '/data/oravis/visdata/gld02.dbf' size 1000M; (if dbf > 1.5 GB)
alter database datafile '/data/oravis/visdata/xkbx02.dbf' resize 1500M;(if dbf < 1.5 GB)
TO FIND THE DATAFILE MOUNT POINT LOCATION ADN THE TOTAL SIZE OF DATABASE
select substr(file_name,6,9),sum(bytes)/1024 from dba_data_files group by substr(file_name,6,9);
change (6,9) accordingly
select substr(file_name,1,35),AUTOEXTENSIBLE,sum(bytes)/1024/1024 from dba_data_files where TABLESPACE_NAME='APPS_TS_INTERFACE';
SUBSTR(FILE_NAME,1,35)
--------------------------------------------------------------------------------
AUT BYTES/1024/1024
--- ---------------
/d01/oradata/data03/a_int01.dbf
YES 1800
To create a datafiel for a schema which use 16k databalock
Set the parameter db_16k_cache_size=250MB in the init.ora parameter
Create a tablespace with 16k data block size by using the following command
Create tablespace schemaname datafile ‘/d03/visdata/data01.dbf’ size 500M autoextend on maxsize 8000M blocksize 16k;
No comments:
Post a Comment