Tablespace usage

SET LINES 150
SET pages 300
col "Tablespace" FOR a30
col "Total MB" FOR a15
col "Free MB" FOR a15
col "Used MB" FOR a15
col "% Used" FOR a15      
SELECT
  tablespace_name "Tablespace",
  d.STATUS "Status",
  TO_CHAR((a.bytes / 1048576),'99,999,990.900') "Total MB",
  TO_CHAR(((DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),'99,999,990.900') "Free MB",
  TO_CHAR(((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576),'99,999,990.900') "Used MB",
  TO_CHAR((((a.bytes - DECODE(f.bytes, NULL, 0, f.bytes)) / 1048576)*100) / (a.bytes / 1048576),'999,999.9') "% Used"
FROM
  (sys.dba_tablespaces d JOIN sys.sm$ts_avail a USING (tablespace_name))
  LEFT OUTER JOIN sys.sm$ts_free f USING (tablespace_name)
where tablespace_name='PSAPIDOCD'
ORDER BY 6
/

select file_name,bytes/(1024*1024*1024) from dba_data_files where tablespace_name='PSAPTF90' order by 1;

GRANT SELECT ON SCHEMA OBJECTS

GRANT SELECT ON SCHEMA OBJECTS TO USER THROUGH ROLE
set heading off;

select 'grant select on '||owner|| '.' ||object_name || to ROLE_NAME;' from dba_objects where owner='SCHEMA_NAME';

grant ROLE_NAME to USER_NAME;

Find a role
select * from dba_roles where role like '&role'
/
Show what roles are granted to a userselect grantee,granted_role,admin_option from dba_role_privs
where grantee like ('&username')
/
Show what table privileges are granted to a role
select owner || '.' || table_name "TABLE",column_name,privilege,grantable
from role_tab_privs where role like '&role'
/
Show what system privileges are granted to a role
select privilege,admin_option from role_sys_privs where role like '&role'
/

Archivelog deletion using RMAN

rman target /

list archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-3';

delete noprompt archivelog all backed up 1 times to DEVICE TYPE DISK completed before 'sysdate-3';

If you know that the logs are unavailable because you deleted them by using an operating system utility, then run the following command at the RMAN prompt to update RMAN metadata:
CROSSCHECK ARCHIVELOG ALL;

It is always better to use RMAN to delete logs than to use an operating system utility. The easiest method to remove unwanted logs is to specify the DELETE INPUT option when backing up archived logs.

For example, enter:
BACKUP DEVICE TYPE sbt ARCHIVELOG ALL DELETE ALL INPUT;

PACKAGE BACKUP in ORACLE

PACKAGE BACKUP
--------------
set heading off
set echo off
set flush off
set pagesize 50000
set linesize 32767
set long 99999999
spool PACKAGE_NAME.pks
select dbms_metadata.get_ddl('PACKAGE','','') from dual ;
spool off;

OR

set linesize 500
set pagesize 100000
set head off
set verify off
spool PACKAGE_NAME.pks
select text from dba_source where owner=''and name='';
spool off

OR

select text from dba_source where owner='' and name='PACKAGE_NAME' and type='PACKAGE' order by line;

PACKAGE BODY BACKUP
-------------------
select dbms_metadata.get_ddl('PACKAGE_BODY','','') from dual ;

OR

set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool PACKAGE_BODY_NAME.pkb
select text from dba_source where owner=''and name='';
spool off

OR

select text from dba_source where owner='' and name='PACKAGE_BODY_NAME' and type='PACKAGE BODY' order by line;

DROP PACKAGE
------------
set pagesize 0
set linesize 800
col object_name for a30
col object_type for a25
col owner for a25
spool package_drop.sql
select owner,object_name,object_type from dba_objects where object_name='';
drop package .;
spool Off;

GRANT USER ACCESS ON SCHEMA

user needs read only access on existing schema

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

select 'grant '||decode(object_type,'TABLE','select','VIEW','select','SEQUENCE','SELECT ','PROCEDURE','EXECUTE ','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||owner||'.'||object_name||' to target user' from dba_objects where OWNER='source user' and object_type not in ('INDEX','PACKAGE BODY','DATABASE LINK','LOB');

user needs full access on existing schema

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

select 'grant '||decode(object_type,'TABLE','select,insert,delete,update ','VIEW','select,insert,delete,update ','SEQUENCE','SELECT ','PROCEDURE','EXECUTE ','PACKAGE','EXECUTE ','TRIGGER','EXECUTE ','FUNCTION','EXECUTE ')|| ' on '||owner||'.'||object_name||' to targetuser' from dba_objects where OWNER='source user' and object_type not in 

('INDEX','PACKAGE BODY','DATABASE LINK','LOB');

Related:

-------

Running the SQL*Plus script below (substituting &Owner and &NewUser) will produce a listing of all the permissions to allow the New User to access all the objects owned by OWNER. Review the output of the script and then run it to Grant the new permissions to NewUser.

Set pagesize 0

define OWNER=

define NEWUSER=

Spool new_grants.txt

Select

decode(OBJECT_TYPE,

'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON '||'&OWNER'||'.',

'VIEW','GRANT SELECT ON '||'&OWNER'||'.',

'SEQUENCE','GRANT SELECT ON '||'&OWNER'||'.',

'PROCEDURE','GRANT EXECUTE ON '||'&OWNER'||'.',

'PACKAGE','GRANT EXECUTE ON '||'&OWNER'||'.',

'FUNCTION','GRANT EXECUTE ON '||'&OWNER'||'.' )||object_name||' TO &NewUser;'

From USER_OBJECTS where OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE', 'FUNCTION')

Order By OBJECT_TYPE;

Spool Off;

exit

Kill sesion using script in rac database

BEGIN
  FOR r IN (select sid,serial#,inst_id from gv$session where username = 'user')
  LOOP
    EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid || ',' || r.serial# ||',@' ||r.inst_id|| '''immediate';
  END LOOP;
END;
/