User has no SELECT privilege on V$SESSION v_$sql_plan v_$sql

if you are getting this error from scott



SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'OUTLINE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN


then grant following permission to scott



SQL> GRANT SELECT ON v_$session TO scott;

Grant succeeded.

SQL> grant select on v_$sql_plan_statistics_all to scott;

Grant succeeded.

SQL> grant select on v_$sql_plan to scott;

Grant succeeded.

SQL> grant select on v_$sql to scott;

Grant succeeded.

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.