How to find object/schema size

Find SCHEMA SIZE or USER SIZE

SELECT tablespace_name,
Sum(bytes)/1024/1024 AS total_size_mb
FROM dba_segments
WHERE owner = Upper('&User_Name')
GROUP BY owner, rollup(tablespace_name)

Find OBJECT Size

select segment_name, sum(bytes/(1024*1024)) size_mb from user_segments
where segment_type = 'TABLE' and segment_name='&SEGMENT_NAME'
group by segment_name

Simple Unix Commands AWK, SED etc....

Find files greater than 1M

$> find . -depth -xdev -size +1000000c -print

Delete files older than 3 days

$> find . -mtime +3 -print -exec rm * {} ;

To find out what is running on a port

$> lsof -i tcp grep port number

Change contents of file from lower case to upper case

$> cat x.lst tr [a-z] [A-Z] > x.sql

AWK COMMANDS

To print the first two fields in opposite order, enter:

$> awk '{ print $2, $1 }' chapter1

To display all lines between the words start and stop, including "start" and "stop", enter:

$> awk '/start/,/stop/' chapter1

To run an awk command program, sum2.awk, that processes the file, abc1, enter:

$> awk -f sum2.awk abc1

contents of file ‘abc1’
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
------- Start of Program sum2.awk ------
{
sum += $2
}
END {
print "Sum: ", sum;
print "Average:", sum/NR;
}
------- End of Program sum2.awk ------
Explanation: The first action adds the value of the second field of each line to the variable sum. All variables are initialized to the numeric value of 0 (zero) when first referenced. The pattern END before the second action causes those actions to be performed after all of the input file has been read. The NR special variable, which is used to calculate the average, is a special variable specifying the number of records that have been read.

****************************************************************************************
Repeat everyline in the file and prefix it with some word

Example :

Source : a.sh

abc
def
ghi

Output : b.sh

echo abc
tnsping abc
echo def
tnsping def
echo ghi
tnsping ghi

Solution

for line in `cat a.sh`
do
echo echo "$line"
echo tnsping $line
done >> b.sh

*********************************************************************************

Bring the 1st line at the end of 2nd line and then 3rd line at the end of 4th line and so on…..

Example :

Input

ADMINDB1
SYSTEM/manager@
ADSERVD1
SYSTEM/manager@

Output

SYSTEM/manager@ADMINDB1
SYSTEM/manager@ADSERVD1

Solution

awk 'NR % 2 == 0' testfile > file1
awk 'NR % 2 ' testfile > file2
paste file1 file2 tr -d 't'

******************************************************

DBMS_JOB

Here's few examples of how to submit the jobs.

The following would create a job to refresh a MV everyday at Midnight starting from tomorrow

SQL> variable jno number;
SQL> exec dbms_job.submit(:jno,'DBMS_MVIEW.REFRESH(''MV_TESTING'',''C'');',trunc(sysdate)+1,'sysdate+1');

Here's another example

declare
jno number;
begin
dbms_job.submit(job => jno,
what => 'my_proc(abc);'
next_date => sysdate+1,
interval => 'sysdate+1');
end;
/

next_date = > next date/time you want to run the job to run. An argument of DATE datatype must be passed in.

Example : to_date('09/30/2007 11:24 AM','MM/DD/YYYY HH:MI AM');
OR
sysdate+1 Same time tomorrow
trunc(sysdate)+1 Midnight tomorrow
trunc(sysdate)+19/24 7 pm (1900 Hrs) today

interval => at which frequency the job should be run . The argument passed is in varchar2.
Example
'sysdate+1' 24 hours after the job's current run time
'trunc(sysdate)+1' Midnight after the day the job is run
'trunc(sysdate)+19/24' 7 PM on the day the job is run

Data Guard Tips

Shutting Down a Physical Standby Database

To shut down a physical standby database, use the SQL*Plus SHUTDOWN command. If the database is performing managed recovery, you must cancel managed recovery operations before issuing the SHUTDOWN command.

If the primary database is up and running, defer the archive log destination on the primary database and perform a log switch operation (to make the defer operation take effect) before shutting down the standby database. Otherwise, log transport services will not be able to transmit redo data to this standby site.

The following steps show you how to shut down a standby database:

Find out if the standby database is performing managed recovery. If the MRP0 or MRP process exists, then the standby database is performing managed recovery.

You can find out which instance is the apply-instance from the ps -eaf | grep mrp command at the OS Level OR using SQL*Plus at the standby database

SQL> select b.host_name,b.INSTANCE_NAME,a.PROCESS,a.STATUS
from gv$managed_standby a, gv$instance b where a.inst_id=b.inst_id

Cancel managed recovery operations.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Shut down the standby database.
SQL> SHUTDOWN IMMEDIATE;

Generate Test Data

Came across this website for generating test data.

http://www.generatedata.com/#about

http://www.sqledit.com/dg/

http://www.igs-edv.de/tdg_e/

Where is your Query stuck

If you know the process ID you can find the SID, SERIAL#, SQL_ID and other details using the following query: (process id can be taken from the unix TOP command)

-- To find the SID, username and SQL_ID associated with a process id taken from unix
select a.sid,a.serial#,a.username,a.status,a.sql_id
from v$session a,v$process b
where b.spid='&PROCESS_ID'
and a.paddr=b.addr
/

You can take a look at the actual explain_plan by executing the following

select operation,options,object_name,object_alias,object_type
from v$sql_plan where sql_id=''

Select from v$session_longops to find out where your query is

select qcsid,sid,username,opname,target,sofar,totalwork,units,start_time,
time_remaining,elapsed_seconds,message
from v$session_longops
order by qcsid
/

Refreshing a Materialized View owned by other user

Suppost USER_A owns the MV and USER_B wants the privileges to execute it.

While trying to refresh a materialized view owned by other users you might get

SQL> EXECUTE DBMS_MVIEW.REFRESH('SALES_SUMMARY','F');
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 2

In order to refresh a materialized view owned by other user, you must have the following privileges in addition to privileges on objects owned by USER_A which are being used in the MV.

SQL> GRANT ALTER ANY MATERIALIZED VIEW TO &USER_B

The DBMS_MVIEW package can manually invoke either a fast refresh or a complete refresh. F means Fast Refresh and C means Complete Refresh:

EXECUTE DBMS_MVIEW.REFRESH('SALES_SUMMARY','F');

**********
The privileges required to create a materialized view log directly relate to the privileges necessary to create the underlying objects associated with a materialized view log.

If you own the master table, you can create an associated materialized view log if you have the CREATE TABLE privilege.

If you are creating a materialized view log for a table in another user's schema, you must have the CREATE ANY TABLE and COMMENT ANY TABLE system privileges, as well as either the SELECT object privilege on the master table or the SELECT ANY TABLE system privilege.

In either case, the owner of the materialized view log must have sufficient quota in the tablespace intended to hold the materialized view log or must have the UNLIMITED TABLESPACE system privilege.

How does one clone database with RMAN

How does one clone/duplicate a database with RMAN?

The first step to clone or duplicate a database with RMAN is to create a new INIT.ORA and password file (use the orapwd utility) on the machine you need to clone the database to. Review all parameters and make the required changed. For example, set the DB_NAME parameter to the new database's name.

Secondly, you need to change your environment variables, and do a STARTUP NOMOUNT from sqlplus. This database is referred to as the AUXILIARY in the script below.

Lastly, write a RMAN script like this to do the cloning, and call it with "rman cmdfile dupdb.rcv":

connect target sys/secure@origdb

connect catalog rman/rman@catdb

connect auxiliary /

run {

set newname for datafile 1 to '/ORADATA/u01/system01.dbf';

set newname for datafile 2 to '/ORADATA/u02/undotbs01.dbf';

set newname for datafile 3 to '/ORADATA/u03/users01.dbf';

set newname for datafile 4 to '/ORADATA/u03/indx01.dbf';

set newname for datafile 5 to '/ORADATA/u02/example01.dbf';

allocate auxiliary channel dupdb1 type disk;

set until sequence 2 thread 1;

duplicate target database to dupdb

logfile

GROUP 1 ('/ORADATA/u02/redo01.log') SIZE 200k REUSE,

GROUP 2 ('/ORADATA/u03/redo02.log') SIZE 200k REUSE;

}

The above script will connect to the "target" (database that will be cloned), the recovery catalog (to get backup info), and the auxiliary database (new duplicate DB). Previous backups will be restored and the database recovered to the "set until time" specified in the script.

Notes: the "set newname" commands are only required if your datafile names will different from the target database.

The newly cloned DB will have its own unique DBID.

PL/SQL Procedure to update values

PL/SQL Procedure to update values in a table based on values taken from another table.

EXAMPLE :
SQL> desc change_log

Name Null? Type
----------------------------------------- -------- ----------------------------
LOG_ID NOT NULL NUMBER(11)
POLL_ID NOT NULL NUMBER(11)
QUESTION_ID NOT NULL NUMBER(11)
ANSWER_ID NOT NULL NUMBER(11)
COUNT NOT NULL NUMBER(11)
TIME NOT NULL DATE

SQL> desc answers

Name Null? Type
----------------------------------------- -------- ----------------------------
ANSWER_ID NOT NULL NUMBER(11)
QUESTION_ID NOT NULL NUMBER(11)
POLL_ID NOT NULL NUMBER(11)
ANSWER_TEXT NOT NULL VARCHAR2(512)
ANSWER_VOTES NOT NULL NUMBER(11)

## Update answers table based on certain conditons on the change_log table.

EXECUTE DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''DD-MON-YYYY HH24:MI:SS''');
SET SERVEROUTPUT ON;
execute dbms_output.enable(1000000000);

Declare
Begin
for rec in (
select sum(NVL(change_log.count,0)) as count_total , POLL_ID,QUESTION_ID,ANSWER_ID
from change_log
group by POLL_ID,QUESTION_ID,ANSWER_ID
)
LOOP
begin
update answers
set answers.ANSWER_VOTES= NVL(rec.count_total,0)
where answers.ANSWER_ID=rec.ANSWER_ID
and answers.QUESTION_ID=rec.QUESTION_ID
and answers.POLL_ID=rec.POLL_ID
and answers.answer_id=3;
exception when Others then
dbms_output.put_line ( rec.ANSWER_ID || ' - ' ||rec.ANSWER_ID||' - '||rec.POLL_ID);
end;
END LOOP;
End;
/

Load Test Data

Many times development teams asks DBAs to populate test data so that they can perform some performance test.

Below you will find some examples to load test data.

SQL> create table sales
(trans_date date, cust_id int, sales_amount number );

SQL> insert /*+ APPEND */ into sales
select trunc(sysdate,'year')+mod(rownum,366) TRANS_DATE,
mod(rownum,100) CUST_ID,
abs(dbms_random.random)/100 SALES_AMOUNT
from all_objects;
SQL> commit;

SQL> begin
for i in 1 .. 4
loop
insert /*+ APPEND */ into sales
select trans_date, cust_id, abs(dbms_random.random)/100
from sales;
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> commit;

## SELECT A RANDOM VALUE ##
select cust_id from (select cust_id from sales order by dbms_random.value) where rownum=1;

Some other commands using DBMS_RANDOM to generate and populate values

SELECT dbms_random.normal FROM dual;
SELECT ABS(dbms_random.normal) FROM dual;
SELECT (1+ABS(MOD(dbms_random.random,100000))) FROM dual; ==>> force output to +ve values only
SELECT dbms_random.value(2, 3) FROM dual; ==>> Get a value between 2 and 3
## Random String ##
dbms_random.string(opt IN CHAR, len IN NUMBER)
RETURN VARCHAR2;

select dbms_random.string('A', 12) from dual; ==>> random alphanumeric values

opt seed values:
'a','A' alpha characters only (mixed case)
'l','L' lower case alpha characters only
'p','P' any printable characters
'u','U' upper case alpha characters only
'x','X' any alpha-numeric characters (upper)

-- create test data == another example ==
CREATE TABLE test (
col1 VARCHAR2(20),
col2 VARCHAR2(20));

DECLARE
x VARCHAR2(20);
y VARCHAR2(20);
BEGIN
FOR i IN 1..100
LOOP
x := dbms_random.string('A', 20);
y := dbms_random.string('A', 20);

INSERT INTO test
(col1, col2)
VALUES
(x,y);
END LOOP;
COMMIT;
END;
/