Oracle Table fragmentation

declare

space_used number ;
space_allocated number ;
chain_pcent number;

begin
for i in (select table_name, owner from dba_tables
where owner='ANUJ'
and table_name not in (select table_name from sys.dba_external_tables
where owner='ANUJ'))

LOOP
DBMS_SPACE.OBJECT_SPACE_USAGE(i.owner,i.table_name,'TABLE','0',space_used,space_allocated,CHAIN_PCENT,'');
dbms_output.put_line(i.table_name|| ',' || round(space_used/1024)||',' || ROUND(space_allocated/1024)||',' || ROUND((1-(space_used/space_allocated))*100));

end loop;
END;

====

to remove fragmentation in table



SQL> create table ANUJ as select * from dba_tables;

Table created.

SQL>
SQL>
SQL>
SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
from user_tables where table_name='ANUJ'; 2

Ever Used Never Used Total rows
---------- ---------- ----------


SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
68 4 2003

SQL> delete from test where owner='SYS';

722 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
68 4 1281

SQL> select count(*) from test;

COUNT(*)
----------
1281

SQL> alter table test enable row movement;

Table altered.

SQL> alter table test shrink space compact;

Table altered.

SQL> analyze table test compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
68 4 1281


SQL> alter table ANUJ shrink space
2 /

Table altered.

SQL> analyze table ANUJ compute statistics;

Table analyzed.

SQL> select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows" from user_tables where table_name='ANUJ';

Ever Used Never Used Total rows
---------- ---------- ----------
40 8 1281



script to find fragmentation In table

col wastedspace format a10
col blocksize format a10
col avgsize format a10
select table_name,round((blocks*8),2)||' kb' "blocksize",
round((num_rows*avg_row_len/1024),2)||' kb' "avgsize",
round((blocks*8),2) - round((num_rows*avg_row_len/1024),2) ||' kb' "wastedspace"
from user_tables
-- where table_name = 'ANUJ';

oracle trace file gzip

# Gzips Oracle Trace Files that are older than 5 days


/usr/bin/find /opt/oracle/admin/cccdb/udump /opt/oracle/admin/cccdb/bdump -name '*.trc' -mtime +5 -exec /usr/bin/gzip -9 {} \;

Put a line No in taxt file

awk '{print NR $0}' anuj.txt >anujline.txt

ORA-12916: Cannot use default permanent tablespace with this release

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200

SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

------------------------------ ------------------------------ --------------------------------------------------

DEFAULT_TEMP_TABLESPACE TEMP1 ID of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE SYSTEM Default Permanent Tablespace ID





SQL> ALTER DATABASE DEFAULT TABLESPACE USERS ;

ALTER DATABASE DEFAULT TABLESPACE USERS

*

ERROR at line 1:

ORA-12916: Cannot use default permanent tablespace with this release




if " *.compatible='9.2.0.0.0' " this parameter set then you can't change .

Is My Linux 32 bit or 64 bit ?

Quick way to find out

anuj-amd-02:/home/anujs/Downloads/Goldengate # uname -m
x86_64


oracle@anuj:~> uname -m
i686

How to grant select on v$session (or v$ ) views

v$session is synonyms of v_$session .


SQL> col OWNER format a15

SQL> col OBJECT_NAME like OWNER

SQL> col OBJECT_TYPE like OWNER



SQL> r

1* select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V$SESSION'



OWNER OBJECT_NAME OBJECT_TYPE

--------------- --------------- ---------------

PUBLIC V$SESSION SYNONYM


SQL> grant select on v$session to prod1;

grant select on v$session to cccprod1
*
ERROR at line 1:

ORA-02030: can only select from fixed tables/views


you can't give grant to synonyms



SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_NAME='V_$SESSION';



OWNER OBJECT_NAME OBJECT_TYPE

--------------- --------------- ---------------

SYS V_$SESSION VIEW





SQL> grant select on v_$session to prod1;



Grant succeeded.

Oracle ORA_ROWSCN Pseudocolumn and AS OF Flashback Query

SQL> CREATE TABLE anuj (c1 NUMBER, c2 VARCHAR2(16), c3 DATE) ;


SQL> INSERT INTO anuj VALUES (1,'A',SYSDATE);



1 row created.



SQL> INSERT INTO anuj VALUES (1,'B',SYSDATE);



1 row created.



SQL> commit;



Commit complete.



SQL> SELECT ORA_ROWSCN, a.* FROM anuj a;



ORA_ROWSCN C1 C2

---------- ---------- ------------------------------------------------

C3

---------------

45111390 1 A

18-AUG-10



45111390 1 B

18-AUG-10