Move indexes from one Tablespace to another Tablespace

Method 1:


Select ' ALTER INDEX ' || owner || '.'||INDEX_NAME ||' rebuild tablespace [
 new_tablespace_name];' from DBA_indexes
 where index_name in ( select segment_name from
 dba_segments where tablespace_name='USERS')

Method 2:

declare

type vt is table of varchar2(64);
l_in vt;
begin
-- fetching all names to avoid ORA-01555
select index_name 
bulk collect into l_in
from dba_indexes where tablespace_name='<current index tablespace>';

for i in 1..l_in.count loop
execute immediate 'alter index '||l_in(i)||' rebuild tablespace <new index tablespace>';
end loop;
end;
/

"EXP-00091 Exporting questionable statistics" Error

The "EXP-00091 Exporting questionable statistics" error happens when you export table data with its related optimizer statistics, and Oracle cannot verify the currency of these statistics.  When CBO statistics are created/updated with dbms_stats, the time is noted, but it’s always a good idea not to export statistics since they should be recalculated after import.
You can remove the EXP-00091 error by exporting with "statistics="none" or by setting the client character set

(e.g. export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1).
The Oracle docs note this on the EXP-00091 error:

EXP-00091 Exporting questionable statistics

Cause: Export was able to export statistics, but the statistics may not be usable  The statistics are questionable because one or more of the following happened during export: 

- A row error occurred, client character set or NCHARSET does not match with the server, a query clause was specified on export,

- Only certain partitions or sub partitions were exported, or a fatal error occurred while processing a table.

Action: To export non-questionable statistics, change the client character set or NCHARSET to match the server, export with no query clause, or export complete tables. 

If desired, import parameters can be supplied so that only non-questionable statistics will be imported, and all questionable statistics will be recalculated.

STEPS TO GENERATE AWR REPORT

Privileges:

STEP-1
Please connect to sys and give these privileges to Scott.
GRANT SELECT ON SYS.V_$DATABASE TO SCOTT;
GRANT SELECT ON SYS.V_$INSTANCE TO SCOTT;
GRANT SELECT ON SYS.DBA_HIST_DATABASE_INSTANCE TO SCOTT;
GRANT SELECT ON SYS.DBA_HIST_SNAPSHOT TO SCOTT;
GRANT ADVISOR TO SCOTT;
GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO SCOTT;

STEP-2 :

SET LINES 400
SET PAGES 400
COL BEGIN_INTERVAL_TIME FOR A40
COL END_INTERVAL_TIME FOR A40
Spool d:\awr.txt
SELECT DBID,INSTANCE_NUMBER,SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME FROM DBA_HIST_SNAPSHOT ORDER BY 5 DESC;
Spool off;
Note: Use above query to get low snap, high snap details. DBID and Instance number

STEP 3:
Query to Generate AWR report:

SELECT OUTPUT FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML (DBID,INSTANCE_NUMBER, LOW_SNAP_ID,HIGH_SNAP_ID,8));
Note : Please put all the details which you have got from step :2

Generate the AWR report on HTML format using below query:
Ex:
SET HEADING OFF;
SET FEEDBACK OFF;
SET LINESIZE 1500;
PROMPT GETTING HTML FORMAT REPORT:
SPOOL d:\AWR_REPORT_HTML.HTML;

SELECT OUTPUT FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML (1318580148,1,798,800,8));
SPOOL OFF;

METHOD 2:

Also we can run awrrpt.sql which is located in ORACLE_HOME\RDBMS\ADMIN location.

Datapump jobs

Identifying datapump jobs

TTITLE 'Currently Active DataPump Operations'
COL owner_name FORMAT A06 HEADING 'Owner'
COL job_name FORMAT A20 HEADING 'JobName'
COL operation FORMAT A12 HEADING 'Operation'
COL job_mode FORMAT A12 HEADING 'JobMode'
COL state FORMAT A12 HEADING 'State'
COL degree FORMAT 9999 HEADING 'Degr|(Num|Wrkers)'
COL attached_sessions FORMAT 9999 HEADING 'Sess'
SELECT
owner_name
,job_name
,operation
,job_mode
,state
,degree
,attached_sessions
FROM dba_datapump_jobs;

Killing or stopping a running datapump job


The difference between Kill and Stop is simple to explain. When killing a job, you won’t be able to resume or start it again. Also logs and dumpfiles will be removed!
When exporting (or importing), press Ctrl-c to show the datapump prompt and type KILL_JOB or STOP_JOB[=IMMEDIATE]. You will be prompted to confirm if you are sure…
Adding ‘=IMMEDIATE‘ to STOP_JOB will not finish currently running ‘sub-job’ and must be redone when starting it again.

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
[Ctrl-c]
Export> KILL_JOB
..or..
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes


Resuming a stopped job

Now we can ATTACH to the job using it as a parameter to the expdp or impdp command, and a lot of gibberish is shown:
> expdp system ATTACH=EXP_FULL


(Re)start the job with START_JOB, use ‘=SKIP_CURRENT‘ if you want to skip the current job. To show progress again, type CONTINUE_CLIENT (Job will be restarted if idle).

Export> START_JOB[=SKIP_CURRENT]
Export> CONTINUE_CLIENT

GENERATE SCRIPT TO CLEAN THE OBJECTS IN A SCHEMA.

set heading off;
set feedback off;
SELECT 'DROP '||object_type||' '|| object_name||  DECODE(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') FROM user_objects ORDER BY object_id DESC;

SELECT 'PURGE RECYCLEBIN;' FROM dual;
SELECT 'PROMPT After cleanup, number of objects remaining in schema' FROM dual;
SELECT 'SELECT COUNT(*) FROM user_objects;' FROM dual;

LOGIN SYSTEM or SYS :

SELECT 'DROP '||object_type||' '|| object_name||  DECODE(object_type,'TABLE',' CASCADE CONSTRAINTS;',';') FROM dba_objects where owner='SCOTT' ORDER BY object_id DESC;

NOTE : Here owner= 'SCOTT' , You can change schema name which you want to clean.

ALTERNATIVE QUOTE (q) Operator

Many SQL statements use character literals in expressions or conditions. If the literal itself contains a single quotation mark, you can use the quote (q) operator and choose your own quotation mark delimiter.
You can choose any convenient delimiter, single-byte or multibyte, or any of the following character pairs: [], {}, (), or <>.
EX:   select q'<Oracle's quote operator>' from dual;
         select q'#Oracle's quote operator#' from dual;
         select q'(Oracle's quote operator)' from dual;
         select dname ||q'{ it's assigned manager id :}'|| mgr from emp,dept where emp.deptno=dept.deptno

DBMS_RANDOM

This package is very useful to generate random number as well as  random date and text values.

NUMBER:


EXAMPLE: 


SELECT DBMS_RANDOM.VALUE FROM DUAL;


SELECT TRUNC(DBMS_RANDOM.VALUE(0, 100)) FROM DUAL;

TEXT:


EXAMPLE:

SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL;


Q : Generate 100 random strings of 15 characters by using DUAL table.

 SELECT DBMS_RANDOM.STRING('X',15) FROM DUAL CONNECT BY ROWNUM<101;


DATE:



SELECT TO_CHAR(TO_DATE('01/01/12','mm/dd/yy'),'J')
FROM DUAL;


NOTE :Take the low_value from the above query and use it in the below query to generate random dates.



SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2455928,2455928+364)),'J')
 FROM DUAL;

Email From Oracle PL/SQL (UTL_SMTP)

Simple Emails: (connect as sys)



CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.quit(l_mail_conn);
END;
/


The code below shows how the procedure is called.



BEGIN
  send_mail(p_to        => 'me@mycompany.com',
            p_from      => 'hr@mycompany.com',
            p_message   => 'This is a test message.',
            p_smtp_host => 'smtp.mycompany.com');
END;
/
-----------------------------------------------------------------------------------------------------
Multi-line messages can be written by expanding the UTL_SMTP.DATA command using the UTL_SMTP.WRITE_DATA command as follows. This is a better method to use as the total message size is no longer constrained by the 32K limit on a VARCHAR2 variable. In the following example the header information has been included in the message also.
---------------------------------------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_subject   IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);
 
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf || UTL_TCP.crlf);
 
  UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;
/
The code below shows how the procedure is called.



BEGIN
  send_mail(p_to        => 'me@mycompany.com',
            p_from      => 'hr@mycompany.com',
            p_subject   => 'Test Message',
            p_message   => 'This is a test message.',
            p_smtp_host => 'smtp.mycompany.com');
END;
/

Script to find locks in the database

select
 (select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
 a.sid || ', ' ||
 (select serial# from v$session where sid=a.sid) sid_serial,
 ' is blocking ',
 (select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
 b.sid || ', ' ||
 (select serial# from v$session where sid=b.sid) sid_serial
 from v$lock a, v$lock b
 where a.block = 1
   and b.request > 0
   and a.id1 = b.id1
   and a.id2 = b.id2;

Queries for compling all the invalid objects

-- This Script is used to create a 'run_invalid.sql' file that has the queries for compliing all the invalid objects

Set heading off;
set feedback off;
set echo off;
Set lines 999;

Spool d:\run_invalid.sql

select
   'ALTER ' || OBJECT_TYPE || ' ' ||
   OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from
   dba_objects
where
   status = 'INVALID'
and
   object_type in ('PACKAGE','FUNCTION','PROCEDURE')
;

spool off;

set heading on;
set feedback on;
set echo on;

--@run_invalid.

SECOND:

spool d:/alter.sql
select 'alter '||object_type||' '||owner||'."'||object_name||'" compile;'
from dba_objects
where status<>'VALID'
and object_type not in ('PACKAGE BODY','TYPE BODY','UNDEFINED','JAVA CLASS','SYNONYM')
union
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects
where status<>'VALID'
and object_type='PACKAGE BODY'
union
select 'alter type '||owner||'.'||object_name||' compile body;'
from dba_objects
where status<>'VALID'
and object_type='TYPE BODY'
union
select 'alter materialized view '||owner||'.'||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='UNDEFINED'
union
select 'alter java class '||owner||'."'||object_name||'" resolve;'
from dba_objects
where status<>'VALID'
and object_type='JAVA CLASS'
union
select 'alter synonym '||owner||'.'||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='SYNONYM'
and owner<>'PUBLIC'
union
select 'alter public synonym '||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='SYNONYM'
and owner='PUBLIC';
spool off;

dbms_metadata.get_ddl for entire schema's scripts

1.  SELECT DBMS_METADATA.GET_DDL('TABLE','EMP') FROM DUAL;
2. SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','EMP') FROM DUAL;
3. EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
4.  EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);

SGA ARCHITECTURE PART 4

SGA ARCHITECTURE PART 3

SGA ARCHITECTURE PART 2

SGA ARCHITECTURE PART1