To Check Tablespace used and free space %:
set linesize 400
set pagesize 20
set heading on
select c.tablespace_name as "TABLESPACE",
ROUND((b.total_space_available/1048576),2) AS "SIZE MB",
ROUND((b.total_space_available/1048576)-(a.free_space/1048576),2) as "USED MB",
ROUND((a.free_space/1048576),2) as "FREE MB",
ROUND(100 - (((a.free_space/1048576)/(b.total_space_available/1048576))*100),1)||' %'
as "% USED",ROUND((((a.free_space/1048576)/(b.total_space_available/1048576))*100),1)||' %' as "% FREE"
from (select sum(bytes) as free_space,tablespace_name
from dba_free_space -- Free space in permanent tablespace
group by tablespace_name UNION ALL
select sum(bytes_free) as free_space,tablespace_name
from v$temp_space_header -- Free space in temporary tablespaces
group by tablespace_name) a, -- Calculate the free space that each tablespace has
(select sum(user_bytes) as total_space_available,tablespace_name
from dba_data_files -- Total space in permanent tablespaces
group by tablespace_name
UNION ALL select sum(user_bytes) as total_space_available,tablespace_name
from dba_temp_files -- Total space in temporary tablespaces
group by tablespace_name) b, -- Calculate the total size of each TS
dba_tablespaces c
where a.tablespace_name (+) = b.tablespace_name and -- show TS that are full so have no entries in dba_free_space
b.tablespace_name = c.tablespace_name
order by ROUND(100 - (((a.free_space/1048576)/(b.total_space_available/1048576))*100),1) DESC;
undo space utlization
select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents = 'UNDO'
group by b.tablespace_name) a,
(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name;
========================================================================
FRa size db
______
set lines 100
col name format a60
select name,
floor(space_limit / 1024 / 1024) "Size MB",
ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/
NAME Size MB Used MB
------------------------------------------------------------ ---------- ----------
+FLASH01 102400 102337
ALTER SYSTEM SET db_recovery_file_dest_size = 105g SCOPE = BOTH;
alter system set db_recovery_file_dest=
alter system set DB_RECOVERY_FILE_DEST='+FLASH01' SID='*';
ALTER SYSTEM SET db_recovery_file_dest_size = 53g; SCOPE = BOTH;
=================================
Select tablespace_name,file_id,file_name,ceil(bytes/1024/1024/1024) "Size in GB",autoextensible,status,ceil(maxbytes/1024/1024/1024) "MAx in GB" from dba_data_files where tablespace_name='SYSTEM';
to get schema size
set linesize 150
set pagesize 5000
col owner for a15
col segment_name for a30
col segment_type for a20
col TABLESPACE_NAME for a30
clear breaks
clear computes
compute sum of SIZE_IN_GB on report
break on report
select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;
===============================================================================
set linesize 150
column file_name format a50
column tablespace_name format a10
select file_name,bytes/1024/1024 ,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&tablespace_name';
alter tablespace obi_dat add datafile '/obiprd01/orafs025/oradata/obiprd01/obi_dat50.dbf' size 2048M;
alter tablespace obi_dat add datafile '/obiprd01/orafs025/oradata/obiprd01/obi_dat50.dbf' size 2048M;
set linesize 150
column file_name format a50
column tablespace_name format a10
select file_name,bytes/1024/1024 ,AUTOEXTENSIBLE from dba_temp_files where tablespace_name='&tablespace_name';
alter tablespace temp add tempfile 'F:\ORA10\DATABASE\QI3T\TEMP06.DBF' size 4096M;
Column tablespace_name heading 'Tablespace Name' format a10
COLUMN created_size HEADING 'Create (MB)' FORMAT 9999,999.99;
COLUMN max_free HEADING 'Max Free (MB)' FORMAT 9999,999.99;
COLUMN tot_free HEADING 'Tot Free (MB)' FORMAT 9999,999.99;
COLUMN pct_free HEADING 'Free %' FORMAT 999.999;
December 08, 2012ASM TABLESPACE Scripts
http://files.meetup.com/1729503/1729503/All_about_ASM.pdf
When a tablespace is getting filled up, action need to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
df -h (Linux,AIX)
df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Tablespace Utilization Script
----------------------------------
SET LINESIZE 300
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Script (In Detail)
---------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select file_name,bytes/1024/1024 Size_MB,maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB
from dba_temp_files where tablespace_name='&tablespace_name' order by 1,2;
ASM Disk Database Files Script
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile (ASM)
--------------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
----------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace (ASM)
-------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;
To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;
Schemas in a tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
SELECT SUBSTR(data_files.tablespace_name,1,20) "Tablespace Name",
tot_create_blk /1024/1024 created_size,
ROUND(max_free_blk / 1024/1024,2) max_free,
ROUND(tot_free_blk / 1024/1024,2) tot_free,
ROUND((tot_free_blk/tot_create_blk*100),3) pct_free
FROM ( SELECT tablespace_name,
SUM(bytes) tot_create_blk
FROM sys.dba_data_files
GROUP BY tablespace_name ) data_files,
( SELECT tablespace_name,
MAX(bytes) max_free_blk,
SUM(bytes) tot_free_blk
FROM sys.dba_free_space
GROUP BY tablespace_name ) free_space
WHERE data_files.tablespace_name = free_space.tablespace_name
and data_files.tablespace_name = '&tablespace'
ORDER BY data_files.tablespace_name;
============================================================================================================
1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.
1. Check the database details.
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;
2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
3. Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 50000 lines 32767
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;
Note:- If required, can get the DDL of a tablespace as below.
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
Datafile shrink
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/
select df.tablespace_name TABLESPACE_NAME,
round(df.bytes/(1024*1024),2) TOTAL_MB,
round(sum(fs.bytes)/(1024*1024),2) FREE_MB,
round(sum(fs.bytes)/(df.bytes) * 100,1) PCT_FREE
2 3 4 5 from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
6 group by tablespace_name ) df
7 where fs.tablespace_name = df.tablespace_name
8 group by df.tablespace_name, df.bytes
9 order by pct_free asc;
select df.tablespace_name TABLESPACE_NAME,
2 round(df.bytes/(1024*1024),2) TOTAL_MB,
3 round(sum(fs.bytes)/(1024*1024),2) FREE_MB,
4 round(sum(fs.bytes)/(df.bytes) * 100,1) PCT_FREE
5 from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
6 group by tablespace_name ) df
7 where fs.tablespace_name = df.tablespace_name
8 group by df.tablespace_name, df.bytes
9 order by pct_free asc;
Rman backup status:
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork)* 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';
/
Check restore status
=====================
@/usr/local/script/oracle/dba/show_longops.sql
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set pages 1000 lines 140
column units for a10
select sid,
serial#,
sofar,
totalwork,
TRUNC(sofar/elapsed_seconds) "Blocks/sec",
ROUND(time_remaining/60) "Mins to go",
start_time,
last_update_time,
ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"
from v$session_longops
where sofar != totalwork
and OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
/
oracle script to check the database growth
SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
set linesize 132 tab off trimspool on
set pagesize 105
set pause off
set echo off
set feedb on
column tablespace_name format a25
column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00
select a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name, sum(bytes) tot_used
from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and a.tablespace_name not like 'UNDO%'
order by 1
--order by 5
/
Reorganizing Steps:-
So first we have to check how much data got fragmented in that particular Table.
SQL> set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page
select a.owner,a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
hgwtr from dba_tables a,dba_segments b where a.table_name=b.segment_name
and a.owner=b.owner and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'and a.table_name like upper('&table_name')||'%'
order by 1,2;
How to find Top 10 Fragmented Tables in Oracle
SQL> select * from (select table_name,round((blocks * 8), 2) "size (kb)",round((num_rows * avg_row_len / 1024), 2) "actual_data (kb)", (round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2)) "wasted_space (kb)" from dba_tables where (round((blocks * 8), 2) > round((num_rows * avg_row_len / 1024), 2)) order by 4 desc)WHERE ROWNUM <= 10;
set linesize 400
set pagesize 20
set heading on
select c.tablespace_name as "TABLESPACE",
ROUND((b.total_space_available/1048576),2) AS "SIZE MB",
ROUND((b.total_space_available/1048576)-(a.free_space/1048576),2) as "USED MB",
ROUND((a.free_space/1048576),2) as "FREE MB",
ROUND(100 - (((a.free_space/1048576)/(b.total_space_available/1048576))*100),1)||' %'
as "% USED",ROUND((((a.free_space/1048576)/(b.total_space_available/1048576))*100),1)||' %' as "% FREE"
from (select sum(bytes) as free_space,tablespace_name
from dba_free_space -- Free space in permanent tablespace
group by tablespace_name UNION ALL
select sum(bytes_free) as free_space,tablespace_name
from v$temp_space_header -- Free space in temporary tablespaces
group by tablespace_name) a, -- Calculate the free space that each tablespace has
(select sum(user_bytes) as total_space_available,tablespace_name
from dba_data_files -- Total space in permanent tablespaces
group by tablespace_name
UNION ALL select sum(user_bytes) as total_space_available,tablespace_name
from dba_temp_files -- Total space in temporary tablespaces
group by tablespace_name) b, -- Calculate the total size of each TS
dba_tablespaces c
where a.tablespace_name (+) = b.tablespace_name and -- show TS that are full so have no entries in dba_free_space
b.tablespace_name = c.tablespace_name
order by ROUND(100 - (((a.free_space/1048576)/(b.total_space_available/1048576))*100),1) DESC;
undo space utlization
select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents = 'UNDO'
group by b.tablespace_name) a,
(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name;
========================================================================
FRa size db
______
set lines 100
col name format a60
select name,
floor(space_limit / 1024 / 1024) "Size MB",
ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/
NAME Size MB Used MB
------------------------------------------------------------ ---------- ----------
+FLASH01 102400 102337
ALTER SYSTEM SET db_recovery_file_dest_size = 105g SCOPE = BOTH;
alter system set db_recovery_file_dest=
alter system set DB_RECOVERY_FILE_DEST='+FLASH01' SID='*';
ALTER SYSTEM SET db_recovery_file_dest_size = 53g; SCOPE = BOTH;
=================================
Select tablespace_name,file_id,file_name,ceil(bytes/1024/1024/1024) "Size in GB",autoextensible,status,ceil(maxbytes/1024/1024/1024) "MAx in GB" from dba_data_files where tablespace_name='SYSTEM';
to get schema size
set linesize 150
set pagesize 5000
col owner for a15
col segment_name for a30
col segment_type for a20
col TABLESPACE_NAME for a30
clear breaks
clear computes
compute sum of SIZE_IN_GB on report
break on report
select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner;
===============================================================================
set linesize 150
column file_name format a50
column tablespace_name format a10
select file_name,bytes/1024/1024 ,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&tablespace_name';
alter tablespace obi_dat add datafile '/obiprd01/orafs025/oradata/obiprd01/obi_dat50.dbf' size 2048M;
alter tablespace obi_dat add datafile '/obiprd01/orafs025/oradata/obiprd01/obi_dat50.dbf' size 2048M;
set linesize 150
column file_name format a50
column tablespace_name format a10
select file_name,bytes/1024/1024 ,AUTOEXTENSIBLE from dba_temp_files where tablespace_name='&tablespace_name';
alter tablespace temp add tempfile 'F:\ORA10\DATABASE\QI3T\TEMP06.DBF' size 4096M;
Column tablespace_name heading 'Tablespace Name' format a10
COLUMN created_size HEADING 'Create (MB)' FORMAT 9999,999.99;
COLUMN max_free HEADING 'Max Free (MB)' FORMAT 9999,999.99;
COLUMN tot_free HEADING 'Tot Free (MB)' FORMAT 9999,999.99;
COLUMN pct_free HEADING 'Free %' FORMAT 999.999;
December 08, 2012ASM TABLESPACE Scripts
http://files.meetup.com/1729503/1729503/All_about_ASM.pdf
When a tablespace is getting filled up, action need to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
df -h (Linux,AIX)
df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Tablespace Utilization Script
----------------------------------
SET LINESIZE 300
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Script (In Detail)
---------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select file_name,bytes/1024/1024 Size_MB,maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB
from dba_temp_files where tablespace_name='&tablespace_name' order by 1,2;
ASM Disk Database Files Script
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile (ASM)
--------------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
----------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace (ASM)
-------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;
To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;
Schemas in a tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
SELECT SUBSTR(data_files.tablespace_name,1,20) "Tablespace Name",
tot_create_blk /1024/1024 created_size,
ROUND(max_free_blk / 1024/1024,2) max_free,
ROUND(tot_free_blk / 1024/1024,2) tot_free,
ROUND((tot_free_blk/tot_create_blk*100),3) pct_free
FROM ( SELECT tablespace_name,
SUM(bytes) tot_create_blk
FROM sys.dba_data_files
GROUP BY tablespace_name ) data_files,
( SELECT tablespace_name,
MAX(bytes) max_free_blk,
SUM(bytes) tot_free_blk
FROM sys.dba_free_space
GROUP BY tablespace_name ) free_space
WHERE data_files.tablespace_name = free_space.tablespace_name
and data_files.tablespace_name = '&tablespace'
ORDER BY data_files.tablespace_name;
============================================================================================================
1. Check the database details.
2. Check the tablespace Utilization.
3. Check the details of the datafiles for a particular TableSpace which needs attention.
4. Resize or Add the datafiles as per the standards of the existing datafiles on the database.
1. Check the database details.
$ sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE from v$database,v$instance;
2. Check the tablespace Utilization.
Tablespace Utilization Script including AUTOEXTEND (IN GB)
----------------------------------------------------------
$ sqlplus "/as sysdba"
set pages 50000 lines 32767
col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
col Current_size heading "Current|Size(GB)" form 99999999.99
col Used_size heading "Used|Size(GB)" form 99999999.99
col Available_size heading "Available|Size(GB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
select a.tablespace_name
,a.alloc_size/1024/1024/1024 Allocated_size
,a.cur_size/1024/1024/1024 Current_Size
,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size>80
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/
3. Check the details of the datafiles for a particular TableSpace which needs attention.
Datafiles of a particular TableSpace:
------------------------------------
set pages 50000 lines 32767
col tablespace_name for a30
col CREATION_TIME for a15
col file_name for a70
select dd.tablespace_name TABLESPACE_NAME,dd.file_name,dd.bytes/1024/1024 Size_MB,dd.autoextensible,dd.maxbytes/1024/1024 MAXSIZE_MB,df.CREATION_TIME
from dba_data_files dd, v$datafile df where df.name=dd.file_name and tablespace_name='&TABLESPACENAME' order by 1,2,6;
Note:- If required, can get the DDL of a tablespace as below.
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
Datafile shrink
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/
select df.tablespace_name TABLESPACE_NAME,
round(df.bytes/(1024*1024),2) TOTAL_MB,
round(sum(fs.bytes)/(1024*1024),2) FREE_MB,
round(sum(fs.bytes)/(df.bytes) * 100,1) PCT_FREE
2 3 4 5 from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
6 group by tablespace_name ) df
7 where fs.tablespace_name = df.tablespace_name
8 group by df.tablespace_name, df.bytes
9 order by pct_free asc;
select df.tablespace_name TABLESPACE_NAME,
2 round(df.bytes/(1024*1024),2) TOTAL_MB,
3 round(sum(fs.bytes)/(1024*1024),2) FREE_MB,
4 round(sum(fs.bytes)/(df.bytes) * 100,1) PCT_FREE
5 from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
6 group by tablespace_name ) df
7 where fs.tablespace_name = df.tablespace_name
8 group by df.tablespace_name, df.bytes
9 order by pct_free asc;
Rman backup status:
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork)* 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%';
/
Check restore status
=====================
@/usr/local/script/oracle/dba/show_longops.sql
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set pages 1000 lines 140
column units for a10
select sid,
serial#,
sofar,
totalwork,
TRUNC(sofar/elapsed_seconds) "Blocks/sec",
ROUND(time_remaining/60) "Mins to go",
start_time,
last_update_time,
ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE"
from v$session_longops
where sofar != totalwork
and OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
/
oracle script to check the database growth
SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
set linesize 132 tab off trimspool on
set pagesize 105
set pause off
set echo off
set feedb on
column tablespace_name format a25
column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00
select a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name, sum(bytes) tot_used
from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and a.tablespace_name not like 'UNDO%'
order by 1
--order by 5
/
Reorganizing Steps:-
So first we have to check how much data got fragmented in that particular Table.
SQL> set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks' just c
column hgwtr heading 'High|Water' just c
break on owner skip page
select a.owner,a.table_name,b.blocks alcblks,a.blocks usdblks,(b.blocks-a.empty_blocks-1)
hgwtr from dba_tables a,dba_segments b where a.table_name=b.segment_name
and a.owner=b.owner and a.owner not in('SYS','SYSTEM') and a.blocks <> (b.blocks-a.empty_blocks-1)
and a.owner like upper('&owner')||'%'and a.table_name like upper('&table_name')||'%'
order by 1,2;
How to find Top 10 Fragmented Tables in Oracle
SQL> select * from (select table_name,round((blocks * 8), 2) "size (kb)",round((num_rows * avg_row_len / 1024), 2) "actual_data (kb)", (round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2)) "wasted_space (kb)" from dba_tables where (round((blocks * 8), 2) > round((num_rows * avg_row_len / 1024), 2)) order by 4 desc)WHERE ROWNUM <= 10;
No comments:
Post a Comment