View user roles in Oracle

To view available system privileges, query DBA_SYS_PRIV.

SQL> select * from DBA_SYS_PRIVS;

To view available roles, query DBA_ROLES.

SQL> select * from DBA_ROLES;

To view roles assignated to a user, query DBA_ROLE_PRIVS.

SQL> select * from DBA_ROLE_PRIVS where grantee = 'BEN';

View SQL text for a specific OS PID

To view SQL text for a specific operatig system PID, first you must obtain the sql_id of that process and then select sqlarea to get the sql text.

$ ps -ef | grep 22910

oracle 22910     1 14 09:16:59 ?       32:09 oracleDBNAME
(DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))

In SQLPLUS run the query:

SELECT s.saddr, s.sid, s.serial#, s.username,
 s.osuser, s.machine, s.program, s.logon_time, s.status, 
 p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid IN (22910);

Then run the query:


select sql_id, sql_fulltext, sql_text from v$sqlarea
where sql_id in (' PREV_SQL_ID');


To view OS PID for a specific SID

SELECT p.program, p.spid, s.saddr, s.sid, s.serial#, s.username,
 s.osuser, s.machine, s.program, s.logon_time, s.status 
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.sid IN (39, 24)

Add tablespace containers

•  Din Control Center sau cu SQL se adauga containerele la tablespace

• Se verifica starea si dimensiunea tablespaces-urilor

select substr(tablespace_name,1,20) Tablespace_name,

case t.TBSPACETYPE when 'S' then 'System Managed'

                   when 'D' then 'Database managed'

end  as  TBSManaged,

s.total_pages Total_pages,

s.used_pages Used_pages,

(s.total_pages-s.USABLE_PAGES ) Overhead_pgs,

s.NUM_CONTAINERS No_of_containers,

s.free_pages Free_pages,

case t.DATATYPE when 'A'  then ' ALL typ perm '

                when 'L'  then ' Large '

                when 'T'  then ' System Temp'

                when 'U'  then ' User Temp '

end  as TBStype,

case s.TABLESPACE_STATE when          0    then       ' Normal  '

                when          1      then       ' Quiesced: SHARE  '

                when          2      then       ' Quiesced: UPDATE  '

                when          4      then       ' Quiesced: EXCLUSIVE  '

                when          8      then       ' Load pending  '

                when         16      then       ' Delete pending  '

                when         32      then       ' Backup pending  '

                when         64      then       ' Roll forward in progress  '

                when        128      then       ' Roll forward pending  '

                when        256      then       ' Restore pending  '

                when        512      then       ' Disable pending  '

                when       1024      then       ' Reorg in progress  '

                when       2048      then       ' Backup in progress  '

                when       4096      then       ' Storage must be defined  '

                when       8192      then       ' Restore in progress  '

                when      16384      then       ' Offline and not accessible  '

                when      32768      then       ' Drop pending  '

                when   33554432         then    ' Storage may be defined  '

                when   67108864         then    ' Storage Definition is in (final) state  '

                when  134217728         then    ' Storage Definition was changed prior to rollforward  '

                when  268435456         then    ' DMS rebalancer is active  '

                when  536870912         then    ' TBS deletion in progress  '

                when 1073741824         then    ' TBS creation in progress '

end TABLESPACE_STATE

from table(snapshot_tbs_cfg(' ',-1)) as s, sysibm.systablespaces t

where s.tablespace_name = t.tbspace ;

• Se face un snapshot pentru tablespace-uri pentru a vedea progresul rebalansarii

Connect to host

Db2 connect toDB

Db2 get snapshot for tablespaces on DB > tablespaces.txt

…..

Tablespace name                            = TBS

  Tablespace ID                            = 7

  Tablespace Type                          = Database managed space

  Tablespace Content Type                  = Long data only

  Tablespace Page size (bytes)             = 16384

  Tablespace Extent size (pages)           = 16

  Automatic Prefetch size enabled          = No

  Tablespace Prefetch size (pages)         = 16

  Buffer pool ID currently in use          = 2

  Buffer pool ID next startup              = 2

  Using automatic storage                  = No

  Auto-resize enabled                      = No

  File system caching                      = Yes

  Tablespace State                         = 0x'10000000'

   Detailed explanation:

     DMS rebalancer is active

  Total number of pages                    = 27262976

  Number of usable pages                   = 27261312

  Number of used pages                     = 26212800

  Number of pending free pages             = 0

  Number of free pages                     = 0

  High water mark (pages)                  = 26212800

  Current tablespace size (bytes)          = 0

  Rebalancer Mode                          = Forward

      Start Time                           = 2011-10-18 10:49:29.000000

      Restart Time                         = 2011-10-18 10:49:29.000000

      Number of extents processed          = 738419

      Number of extents remaining          = 899881

      Last extent moved                    = 738419

      Current priority                     = 0

  Minimum Recovery Time                    =

  Number of quiescers                      = 0

  Number of containers                     = 104

…..

Rename datafile

1. Put tablespace offline


sqlplus /nolog
SQL> conn admin_user/passwd
SQL> alter tablespace TBS offline normal;
SQL> exit


2. Copy or move datafile to new location using OS command cp


cd /u02/oradata/olcl
mv TBS_reorg0.dbf TBS.dbf


3. Change tablespace definition, renaming datafile


sqlplus /nolog
SQL> conn admin_user/passwd
SQL> alter tablespace TBS rename datafile '/u02/oradata/olcl/TBS_reorg0.dbf' to '/u02/oradata/olcl/TBS.dbf';


4. Put tablespace online


SQL> alter tablespace TBS online;
SQL> exit

ORA-20446: The owner of the job is not registered

This error appears in Enterprise Manager 11g when running a job


Solution:


sqlplus /nolog
conn sysman/passwd
SQL> execute MGMT_USER.MAKE_EM_USER(‘USERID’);

ORA-00600: internal error code, arguments: [kwqbdrcp101], [], [], [], [], [], [], []

In case of error ORA-00600: internal error code, arguments: [kwqbdrcp101], [], [], [], [], [], [], [] in alert.log, and you are using Oracle Streams

Stop propagation

exec DBMS_PROPAGATION_ADM.STOP_PROPAGATION('"[nume_propagare]"');

Drop propagation process

exec DBMS_PROPAGATION_ADM.DROP_PROPAGATION('"[nume_propagare]"');

Empty queue table for capture process

DECLARE

options dbms_aqadm.aq$_purge_options_t;

BEGIN

options.delivery_mode:=DBMS_AQADM.BUFFERED;

DBMS_AQADM.PURGE_QUEUE_TABLE('"[administrator_streams]"."[nume_captura]_QT"',NULL,options);

END;

/

Oracle Streams Apply

To tell Apply Process from Oracle Streams to ignore old values for columns at update or delete time: 

begin

dbms_apply_adm.compare_old_values(

object_name => '[nume_schema].[nume_tabela]',

column_list => '*',

operation => '*',

compare => FALSE);

end;

/