gather stat

begin
DBMS_STATS.GATHER_DICTIONARY_STATS();
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();
dbms_stats.gather_schema_stats(OWNNAME=>'XXX',
estimate_percent=>100,
METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',
CASCADE=>true, DEGREE=>16);
end;

check indexes status

CREATE OR REPLACE PROCEDURE P$check_ind_status
IS
  v_html  clob   :=' ';
  v_n     NUMBER := 0;

BEGIN
   --1) let loop to get email html
   v_html := '<h4>INDEXES</h4>'
               ||'<table style="border:1px solid lightgrey">'
               ||'<tr style="background-color:#85C1E9; font-family:verdana; font-size:80%;">'
               ||'<th>No</th>'
               ||'<th>Index Name</th>'
               ||'<th>Status</th>'
             ||'</tr>';

   FOR i IN (
              select index_name,status from user_indexes
               where (status <> 'VALID' and status <> 'N/A')
               union
              select index_name||':'||partition_name,status from user_ind_partitions
               where status <> 'USABLE')
   LOOP
         v_n := v_n +1;
         v_html := v_html || '<tr style="font-family:verdana; font-size:80%;">'
                            ||'<TD>'||v_n ||'</TD>'
                            ||'<TD>'|| i.index_name ||'</TD>'
                            ||'<TD>'|| i.status ||'</TD>'
                          ||'</tr>';
   END LOOP;

   --4) require send?
   IF  v_n > 0
   THEN
      v_html := v_html || '</table>';
      UTL_MAIL.send(sender => 'oracle@ababank.com',
                    recipients => '',
                    subject => '[' || SYS_CONTEXT ('USERENV', 'DB_NAME') || '] Index unusable alert',
                    message => v_html,
                    mime_type => 'text/html;charset=us-ascii');
   END IF;
    dbms_output.put_line('html: ' || v_html);
END;

backup pro eod . par

[oracle@garuda-2-17 script]$ cat FCCHOST_FCCLIVE.par
DIRECTORY=DATA_PUMP_DIR3
DUMPFILE=FCCHOST_FCCLIVE_PREEOD_201812112310_%U.DMP
LOGFILE=FCCHOST_FCCLIVE_PREEOD_201812112310.log
SCHEMAS=FCCHOST
FILESIZE=64G
EXCLUDE=statistics
EXCLUDE=grant
CONTENT=ALL
PARALLEL=8
QUERY=FCCHOST.SVTM_CIF_SIG_DET:"WHERE 1=2 "
QUERY=FCCHOST.IFTB_BRN_RECON_HIST:"WHERE 1=2 "
QUERY=FCCHOST.MSTB_ARCHIVE_OUT:"WHERE 1=2 "

pre eod backup and copy to new server

[oracle@garuda-2-17 ~]$ cat /u01/app/script/FCCHOST_FCCLIVE_PREEOD_expdp.sh
#!/bin/bash
. /home/oracle/.bash_profile
export script_dir=/u01/app/script
export DATA_PUMP_DIR=/backup/data_pump_dir
export DEST_DIR=/datastore/FCCLIVE

cd /u01/app/script
datevar=`date +%Y%m%d%H%M`

sed 's/<DATE>/'$datevar'/g' <FCCHOST_FCCLIVE_template.par >FCCHOST_FCCLIVE.par

export ORACLE_SID=FCCLIVE

sqlplus /nolog <<EOF
conn /as sysdba
set feedback off
set head off
set line 200
set pagesize 100
col object_type format a18
col status format a15
select object_type,status,count(*)
from dba_objects
where owner = 'FCCHOST'
group by object_type,status
/
exit;
EOF

expdp \' \/as sysdba \' parfile=FCCHOST_FCCLIVE.par


# Sending mail #
sqlplus /nolog <<EOF
conn /as sysdba
      BEGIN
         UTL_MAIL.send(sender => 'oracle@ababank.com',
                    recipients => 'dba@ababank.com;FlexcubeSupport@ababank.com',
                    subject => '[' || SYS_CONTEXT ('USERENV', 'DB_NAME') || '] Backup PREEOD '|| $(echo $datevar)||' Completed',
                    message => '<PRE>"$(tail -40 ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_${datevar}.log)"</PRE>',
                    mime_type => 'text/html;charset=us-ascii');
     END;
/
--create restore point BEFORE_RUN_EOD guarantee flashback database;
exit;
EOF

# Copy to Backup 10.152.1.52 #
scp ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_${datevar}* oracle@10.152.1.52:${DEST_DIR}

# deleting part #
grep "FCCHOST_FCCLIVE" ${DATA_PUMP_DIR}/FCCHOST_FCCLIVE_PREEOD_"$datevar".log|awk -F'/' '{print $4}' > filelist.txt

for filename in $(cat filelist.txt); do
a=`ssh oracle@10.152.1.52 ${DEST_DIR}"/FCCLIVE_dump_exist_check.sh "$filename`
if [ "$a" = "File is existing" ]
then
  rm ${DATA_PUMP_DIR}/$filename
fi
done

touch $script_dir/running_preeod_expdp_done.flg
if [ -f $script_dir/running_preeod_expdp.flg ]
then
  rm $script_dir/running_preeod_expdp.flg
fi

exit 1

Database size

The size of the database is the space the files physically consume on disk. You can find this with: 

select sum(bytes)/1024/1024 size_in_mb from dba_data_files;

But not all this space is necessarily allocated. There could be sections of these files that are not used. 

You can find the total space that is used with: 

select sum(bytes)/1024/1024 size_in_mb from dba_segments;

You can break this down by user by running: 

select owner, sum(bytes)/1024/1024 Size_MB from dba_segments
group  by owner;

Check used and free space:

select

"Reserved_Space(GB)", "Reserved_Space(GB)" - "Free_Space(GB)" "Used_Space(MB)","Free_Space(GB)"

from(

select

(select sum(bytes/(1014*1024*1024)) from dba_data_files) "Reserved_Space(GB)",

(select sum(bytes/(1024*1024*1024)) from dba_free_space) "Free_Space(GB)"

from dual

);

Configuring Clients for Environments with Both Shared and Dedicated Servers

If a shared server is configured on the server side, and a client connection request arrives when no dispatchers are registered, then the request is processed by a dedicated server process. If you want a particular client always to use a dispatcher, then configure (SERVER=shared) in the CONNECT_DATA section of the connect descriptor. For example:

sales=
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     (SERVER=shared)))

If the (SERVER=shared) attribute is configured and a dispatcher is not available, then the client connection request is rejected, and a message is sent to the client.

If the database is configured for a shared server and a particular client requires a dedicated server, then you can configure the client to use a dedicated server in one of the following ways:

• You can configure a network service name with a connect descriptor that contains (SERVER=dedicated) in the CONNECT_DATA section. For example:
  sales=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
    (CONNECT_DATA=
       (SERVICE_NAME=sales.us.example.com)
       (SERVER=dedicated)))
• You can configure the client profile file, sqlnet.ora, with USE_DEDICATED_SERVER=on. This adds (SERVER=dedicated) to the CONNECT_DATA section of the connect descriptor the client uses.
  Note:
  If USE_DEDICATED_SERVER is set to ON, then existing (SERVER=value) entries in connect descriptors are overwritten with (SERVER=dedicated).

Select object tablespace

select * from dba_segments

where owner = 'FCCHOST'

and lower(segment_name) like '%fc_%'

Privilege

select text from USER_SOURCE

where name = 'REMOVE_EMP'

order by line; 

-Check privilege granted to users

SELECT * FROM DBA_SYS_PRIVS A

JOIN DBA_ROLE_PRIVS B

ON (A.GRANTEE=B.GRANTEE

and A.GRANTEE='SAK')

If you want more than just direct table grants (e.g., grants via roles, system privileges such as select any table, etc.), here are some additional queries:

System privileges for a user:

SELECT PRIVILEGE
  FROM sys.dba_sys_privs
 WHERE grantee = 'SAK'
UNION
SELECT PRIVILEGE
  FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
 WHERE rp.grantee = 'SAK'
 ORDER BY 1;

Direct grants to tables/views:

SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv
  FROM table_privileges
 WHERE grantee = <theUser>
 ORDER BY owner, table_name;

Indirect grants to tables/views:

SELECT DISTINCT owner, table_name, PRIVILEGE
  FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
 WHERE rp.grantee = <theUser>
 ORDER BY owner, table_name;

Pasted from <http://stackoverflow.com/questions/1298473/how-can-i-list-all-grants-a-user-received>

24down vote

Assuming you want to list grants on all objects a particular user has received: select * from all_tab_privs_recd where grantee = 'SAK'

Pasted from <http://stackoverflow.com/questions/1298473/how-can-i-list-all-grants-a-user-received>

select distinct 'GRANT '||privilege||' ON '||OWNER||'.'||TABLE_NAME||' TO '||RP.GRANTEE
from DBA_ROLE_PRIVS RP join ROLE_TAB_PRIVS RTP
on (RP.GRANTED_ROLE = RTP.role) 
where (OWNER in ('SAK') --Change User Name
   OR RP.GRANTEE in ('SAK')) --Change User Name
and RP.GRANTEE not in ('SYS', 'SYSTEM')
;

Kill rman

Kill rman

Friday, August 10, 2018

4:30 AM

SQL> select b.sid, b.serial#, a.spid, b.client_info
from v$process a, v$session b
where a.addr=b.paddr and client_info like 'rman%';

SQL> alter system kill session '592,12' immediate;