Oracle, Web, Script, SQLserver, Tips & Trick

October 20, 2010

Script Restore database

Filed under: oracle — sikathabis @ 3:05 am
Tags: ,

————-

— RESTORE.BAT

————-

SQLPLUS /NOLOG @D:\RESTORE.SQL

REM IMP SYSTEM/SYSTEM@DBWHOUSE PARFILE=D:\DMP\

REM SQLPLUS /NOLOG @D:\ENABLE.SQL

EXIT;

————–

— RESTORE.SQL

————–

CONNECT DBADMIN/DBADMIN@DBWHOUSE

@D:\GEN_DISABLE_CONS.SQL

@D:\GEN_DISABLE_TRIGGER.SQL

@D:\GEN_TRUNCATE_TABLE.SQL

REM @D:\DISABLE_CONS.SQL

REM @D:\DISABLE_CONS.SQL

REM @D:\TRUNCATE_TABLE,SQL

EXIT;

————-

— ENABLE.SQL

————-

CONNECT DBADMIN/DBADMIN@DBWHOUSE

@D:\ENABLE_CONS.SQL

@D:\ENABLE_TRIGGER.SQL

EXIT;

——————-

— FARFILE

————-

FILE=”D:\DMP_DBWHOUSE31072010\dbadmin20100816BB.dmp”

LOG=”D:\DMP_DBWHOUSE31072010\imp_dbadmin20100816BB.log”

FROMUSER=DBADMIN

TOUSER=DBADMIN

DIRECT=Y

BUFFER=20000

TABLES=(GL_MASTER)

GRANTS=N

INDEXES=N

CONSTRAINTS=N

IGNORE=N

FEEDBACK=10000

ROW=N

——————-

— GEN_DISABLE_CONS.SQL

——————-

SET PAUSE OFF

SET PAGES 0

SET LINES 200

SET HEADING OFF

SET FEEDBACK 0

SET TERM OFF

HOST DEL D:\DISABLE_CONS.SQL

SPOOL D:\DISABLE_CONS.SQL

SELECT ‘ALTER TABLE ‘ ||TABLE_NAME || ‘ DISABLE CONSTRAINT ‘|| CONSTRAINT_NAME || ‘;’ CONS_DIS

FROM DBA_CONSTRAINTS

WHERE OWNER = ‘DBADMIN’

AND CONSTRAINT_TYPE IN (‘P’,’U’,’R’)

ORDER BY TABLE_NAME;

SPOOL OF;

EXIT;

——————-

— GEN_ENABLE_CONS.SQL

——————-

SET PAUSE OFF

SET PAGES 0

SET LINES 200

SET HEADING OFF

SET FEEDBACK 0

SET TERM OFF

HOST DEL D:\ENABLE_CONS.SQL

SPOOL D:\ENABLE_CONS.SQL

SELECT ‘ALTER TABLE ‘ ||TABLE_NAME || ‘ ENABLE CONSTRAINT ‘|| CONSTRAINT_NAME || ‘;’ CONS_DIS

FROM DBA_CONSTRAINTS

WHERE OWNER = ‘DBADMIN’

AND CONSTRAINT_TYPE IN (‘P’,’U’,’R’)

ORDER BY TABLE_NAME;

SPOOL OF;

EXIT;

————————–

— GEN_DISABLE_TRIGGER.SQL

————————–

SET PAUSE OFF

SET PAGES 0

SET LINES 200

SET HEADING OFF

SET FEEDBACK 0

SET TERM OFF

HOST DEL D:\DISABLE_TRIGGER.SQL

SPOOL D:\DISABLE_TRIGGER.SQL

SELECT ‘ALTER TRIGGER ‘ || OBJECT_NAME || ‘ DISABLE;’ ZZZ

FROM DBA_OBJECTS

WHERE OWNER = ‘DBADMIN’

AND   OBJECT_TYPE = ‘TRIGGER’

ORDER BY TABLE_NAME;

SPOOL OF;

EXIT;

————————–

— GEN_ENABLE_TRIGGER.SQL

————————–

SET PAUSE OFF

SET PAGES 0

SET LINES 200

SET HEADING OFF

SET FEEDBACK 0

SET TERM OFF

HOST DEL D:\ENABLE_TRIGGER.SQL

SPOOL D:\ENABLE_TRIGGER.SQL

SELECT ‘ALTER TRIGGER ‘ || OBJECT_NAME || ‘ ENABLE;’ ZZZ

FROM DBA_OBJECTS

WHERE OWNER = ‘DBADMIN’

AND   OBJECT_TYPE = ‘TRIGGER’

ORDER BY TABLE_NAME;

SPOOL OF;

EXIT;

————————–

— GEN_TRUNCATE_TABLE.SQL

————————–

SET PAUSE OFF

SET PAGES 0

SET LINES 200

SET HEADING OFF

SET FEEDBACK 0

SET TERM OFF

HOST DEL D:\TRUNCATE_TABLE.SQL

SPOOL D:\TRUNCATE_TABLE.SQL

SELECT ‘TRUNCATE TABLE ‘ ||OBJECT_NAME || ‘;’ ZZZ

FROM DBA_OBJECTS

WHERE OWNER = ‘DBADMIN’

AND   OBJECT_TYPE = ‘TABLE’

ORDER BY OBJECT_NAME;

SPOOL OF;

EXIT;

—– LAST STEP——

— COMPILE ALL OBJECT

———————

=================

SELECT DISTINCT OBJECT_TYPE

–OBJECT_NAME , OBJECT_TYPE

FROM DBA_OBJECTS

WHERE OWNER = ‘DBADMIN’

–AND OBJECT_TYPE = ‘TRIGGER’

–ORDER BY 2,1

OBJECT_TYPE

——————-

SEQUENCE

PROCEDURE

DATABASE LINK

PACKAGE BODY

LOB

PACKAGE

TRIGGER

MATERIALIZED VIEW

TABLE

INDEX

VIEW

FUNCTION

SYNONYM

TYPE

14 rows selected.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Teknologi Pendidikan

Just another WordPress.com weblog

KATAKAMI

Jurnalisme Yang Dapat Dipercaya

Tonk Kosonk Baonk Bunyinya

Penampungan Serapah Tanpa Sumpah

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Bodoh Yang Tak Kunjung Pandai

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server

neinframe

Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Keluarga Andrivito

-- Andrivito's Journal --

DRK™

This site is about Latest NEWS, Articles, Videos, Books, Course, Presentations, Tips & Tricks related to I.T Pro's

Gaurav Saxena

|| तत् त्वम् असि ||

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa

TuanSUFI

Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku

angkasa13

Ceritanya saya, Pikirannya saya, Hatinya saya

sikathabis

sikathabis semua informasi disini

%d bloggers like this: