Full details of using DBMS_REPAIR for this can be found in the
documentation but in summary the steps are:
- Use DBMS_REPAIR.ADMIN_TABLES to create the admin tables
- Use DBMS_REPAIR.CHECK_OBJECT to find problem blocks
- Get any good data out of problem blocks before corrupting them.
- Use DBMS_REPAIR.FIX_CORRUPT_BLOCKS to mark the found problem
blocks as corrupt so that they will then signal ORA-1578
- If required use DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to skip corrupt
blocks on the table.
Tuesday, March 25, 2008
Hot Backup Script
TIP: Click help for a detailed explanation of this page.
Bookmark Go to End
Subject: Scripts To Perform Dynamic Hot/Online Backups
Doc ID: Note:152111.1 Type: BULLETIN
Last Revision Date: 20-MAY-2007 Status: PUBLISHED
PURPOSE
-------
For this article basis database knowledge is necessary.
This article will explain how you can create online backups and it contains 2
scripts which can be used to create you hotbackup's.
In this article the terms hotbackup and online backup will be used.
The topics explained are:
1) check archive log mode
2) change archive log mode
3) script hotbackup.bat + explanation
4) script hotbackup.sql + explanation
5) Scheduling
How to create online backups.
-----------------------------
--- 1 CHECK ARCHIVE LOG MODE ---
To create online backups you database has to be in archivelog mode.
To check if this is the case execute the next query in sqlplus connecting as
system
select log_mode from v$database;
If the the result is NOARCHIVELOG this must be changed, see topic 2. If the
result is ARCHIVELOG go to topic 3.
--- 2 CHANGE ARCHIVE LOG MODE ---
To change you settings from noarchivelog mode to archive log mode read Note 69739.1
This note explains in detail how to change from noarchive log to archivelog and visa versa.
--- 3 SCRIPT HOTBACKUP.BAT ---
The script will start with setting 4 parameters. These parameters must be changed to the values
you would like to use.
parameter SCRIPTS => location of the hotbackup.bat and hotbackup.sql
parameter ORACLE_SID => SID name of your database
parameter TEMP_DIR => location of a temporary directory (example c:\temp)
parameter DEST_DIR => location where the copys of the datafiles, controlfile will be created
After this the script will check for old logfiles, datafiles and will create the destination
directory if it doesn't exists.
The next part of deleting or renaming old datafiles can be changed. In this script the renaming of old
datafiles isn't used. But if you want to save a hotbackup of the day before remove the REM and added the
rem the next part.
Now the hotbackup.sql script is called and the parameters are also passed. For more info about this
script see topic 4.
When the hotbackup.sql is executed, the log files (2 in total) will be merged together.
When done the program will exit.
Rem ****************************************************************************************
Rem
Rem Script to create hot backup of database
Rem
Rem 09-07-2001 B. de Cock Buning
Rem created script for hotbackup.
Rem 11-07-2001 B. de Cock Buning
Rem changed script for dynamic locations (add parameters)
Rem
Rem
Set SCRIPTS=d:\oracle\admin
Set ORACLE_SID=prod
Set TEMP_DIR=c:\temp
Set DEST_DIR=E:\oracle\backup
Rem
Rem Delete old hotbackup.log file
Rem
if exist %TEMP_DIR%\hotbackup.log del %TEMP_DIR%\hotbackup.log /q
if exist %DEST_DIR%\%ORACLE_SID%\Log\backup.log del %DEST_DIR%\%ORACLE_SID%\Log\backup.log
Rem
Rem Create backup location
Rem
if not exist %DEST_DIR%\%ORACLE_SID% md %DEST_DIR%\%ORACLE_SID% >> %TEMP_DIR%\hotbackup.log
Rem
Rem Go to the log directory
Rem
if not exist %DEST_DIR%\%ORACLE_SID%\Log md %DEST_DIR%\%ORACLE_SID%\Log >> %TEMP_DIR%\hotbackup.log
cd %DEST_DIR%\%ORACLE_SID%\Log
cd
Rem
Rem Delete old log files
Rem
if exist *.log del *.log /q >> %TEMP_DIR%\hotbackup.log
if exist *.lst del *.lst /q >> %TEMP_DIR%\hotbackup.log
Rem
Rem Rename the old backup files
Rem Create a copy of the current backup files
Rem
Rem if exist %DEST_DIR%\%ORACLE_SID%\*.ctl ren %DEST_DIR%\%ORACLE_SID%\*.ctl *.ctl.old >> %TEMP_DIR%\hotbackup.log
Rem if exist %DEST_DIR%\%ORACLE_SID%\*.log ren %DEST_DIR%\%ORACLE_SID%\*.log *.log.old >> %TEMP_DIR%\hotbackup.log
Rem if exist %DEST_DIR%\%ORACLE_SID%\*.dbf ren %DEST_DIR%\%ORACLE_SID%\*.dbf *.dbf.old >> %TEMP_DIR%\hotbackup.log
Rem
Rem Delete the old backup files
Rem Saves no copy of current backup
Rem
if exist %DEST_DIR%\%ORACLE_SID%\*.ctl del %DEST_DIR%\%ORACLE_SID%\*.ctl /q >> %TEMP_DIR%\hotbackup.log
if exist %DEST_DIR%\%ORACLE_SID%\*.log del %DEST_DIR%\%ORACLE_SID%\*.log /q >> %TEMP_DIR%\hotbackup.log
if exist %DEST_DIR%\%ORACLE_SID%\*.dbf del %DEST_DIR%\%ORACLE_SID%\*.dbf /q >> %TEMP_DIR%\hotbackup.log
Rem
Rem Start the hot backup script
Rem
D:\Oracle\Oracle817\Bin\sqlplus "sys/oracle@%ORACLE_SID% as sysdba" @%SCRIPTS%\hotbackup.sql %ORACLE_SID% %TEMP_DIR% %DEST_DIR%
type %TEMP_DIR%\hotbackup.log >> %DEST_DIR%\%ORACLE_SID%\Log\backup.log
exit
Rem ******************************************************************************************************************************
Rem end of script
--- 4 SCRIPT HOTBACKUP.SQL ---
This script will create a spool file wich will be save in your temporary location.
The spool file will be used to finally execute the statement for the backup.
The spool file is filled with the result of the queries.
NOTE: when you have more then 2 controlfiles or the names are different then mentioned
in the script (remark 3) change the names to the names you use or add a controlfile.
This because when recovery is needed you will not have any problem with the naming used
and the names define in your parameter file of the parameter control_files.
remark HOTBACKUP.sql
remark
remark Make a hot backup of the database.
remark
remark Parameters: 1 - SID for the database
remark 2 - Temp for temporary directory
remark 3 - Destination for locatie of the backup
remark
remark
remark 1- for all tablespaces which are not ACTIVE
remark make tablespaces active
remark 2- for all loggroups
remark logswitch, so all changes/ inserts in redologfiles are archiveerd
remark 3- for every controlfile
remark make backup of the controlfile
remark 4- backup of the controlfile to trace directory
remark 5- for every tablespace
remark set tablespace in backup mode
remark copy datafile to destination directory
remark set tablespace in active mode
remark 6- show status of tablespaces
remark
remark 09-07-2001 B. de Cock Buning
remark Create script for dynamic hotbackup
remark
set pagesize 0
set feedback off
set linesize 132
set trimspool on
set verify off
define dbsid=&1
define temp =&2
define destination=&3
spool &temp\backup_prod.sql
select 'Rem Start tijd:' || to_char( sysdate, 'dd-mm-yyyy hh24:mi' ) from dual;
prompt Spool &destination\&dbsid\log\backup.log;
remark 1
select 'alter tablespace '||t.name||' end backup;'
from sys.file$ f, v$backup v, sys.ts$ t
where v.file# = f.file#
and f.ts# = t.ts#
and v.status = 'ACTIVE';
remark 2
select 'alter system switch logfile;'
from sys.v_$log;
remark 3
prompt alter database backup controlfile to '&destination\&dbsid\control01.ctl';;
prompt alter database backup controlfile to '&destination\&dbsid\control02.ctl';;
remark 4
prompt alter database backup controlfile to trace;;
remark 5
select 'alter tablespace '||tablespace_name||' begin backup;' || chr(10) ||
'host copy '||file_name||' &destination\&dbsid\ >> &temp\hotbackup.log' || chr(10) ||
'alter tablespace '||tablespace_name||' end backup;' || chr(10)
from dba_data_files;
remark 6
prompt select file#,status, to_char(time,'DD-MM-YYYY HH24:Mi') TIME from v$backup;;
prompt Spool off;;
prompt exit;;
spool off
set feedback on
set heading on
set pagesize 40
set linesize 80
--- 5 SCHEDULE SCRIPT ---
To schedule the scripts for execution at night you needed to use a tool to schedule it.
One easy to use tool is winat. This tool is included in the windows recourse kit.
When you install this tool, make sure the service schedule is running and also starts
automaticly. To check this after the installation open your control panel with subtopic services
and check the service schedule it must be up and running.
After all these steps you have a good hotbackup senario. Of course you have to
test your script for errors.
At the destination location there will be a subfolder with the name log containing
a file called backup.log. This file will display the result of the backup. If there
is any kind of error during the execution check this file and solve the problem.
Bookmark Go to End
Subject: Scripts To Perform Dynamic Hot/Online Backups
Doc ID: Note:152111.1 Type: BULLETIN
Last Revision Date: 20-MAY-2007 Status: PUBLISHED
PURPOSE
-------
For this article basis database knowledge is necessary.
This article will explain how you can create online backups and it contains 2
scripts which can be used to create you hotbackup's.
In this article the terms hotbackup and online backup will be used.
The topics explained are:
1) check archive log mode
2) change archive log mode
3) script hotbackup.bat + explanation
4) script hotbackup.sql + explanation
5) Scheduling
How to create online backups.
-----------------------------
--- 1 CHECK ARCHIVE LOG MODE ---
To create online backups you database has to be in archivelog mode.
To check if this is the case execute the next query in sqlplus connecting as
system
select log_mode from v$database;
If the the result is NOARCHIVELOG this must be changed, see topic 2. If the
result is ARCHIVELOG go to topic 3.
--- 2 CHANGE ARCHIVE LOG MODE ---
To change you settings from noarchivelog mode to archive log mode read Note 69739.1
This note explains in detail how to change from noarchive log to archivelog and visa versa.
--- 3 SCRIPT HOTBACKUP.BAT ---
The script will start with setting 4 parameters. These parameters must be changed to the values
you would like to use.
parameter SCRIPTS => location of the hotbackup.bat and hotbackup.sql
parameter ORACLE_SID => SID name of your database
parameter TEMP_DIR => location of a temporary directory (example c:\temp)
parameter DEST_DIR => location where the copys of the datafiles, controlfile will be created
After this the script will check for old logfiles, datafiles and will create the destination
directory if it doesn't exists.
The next part of deleting or renaming old datafiles can be changed. In this script the renaming of old
datafiles isn't used. But if you want to save a hotbackup of the day before remove the REM and added the
rem the next part.
Now the hotbackup.sql script is called and the parameters are also passed. For more info about this
script see topic 4.
When the hotbackup.sql is executed, the log files (2 in total) will be merged together.
When done the program will exit.
Rem ****************************************************************************************
Rem
Rem Script to create hot backup of database
Rem
Rem 09-07-2001 B. de Cock Buning
Rem created script for hotbackup.
Rem 11-07-2001 B. de Cock Buning
Rem changed script for dynamic locations (add parameters)
Rem
Rem
Set SCRIPTS=d:\oracle\admin
Set ORACLE_SID=prod
Set TEMP_DIR=c:\temp
Set DEST_DIR=E:\oracle\backup
Rem
Rem Delete old hotbackup.log file
Rem
if exist %TEMP_DIR%\hotbackup.log del %TEMP_DIR%\hotbackup.log /q
if exist %DEST_DIR%\%ORACLE_SID%\Log\backup.log del %DEST_DIR%\%ORACLE_SID%\Log\backup.log
Rem
Rem Create backup location
Rem
if not exist %DEST_DIR%\%ORACLE_SID% md %DEST_DIR%\%ORACLE_SID% >> %TEMP_DIR%\hotbackup.log
Rem
Rem Go to the log directory
Rem
if not exist %DEST_DIR%\%ORACLE_SID%\Log md %DEST_DIR%\%ORACLE_SID%\Log >> %TEMP_DIR%\hotbackup.log
cd %DEST_DIR%\%ORACLE_SID%\Log
cd
Rem
Rem Delete old log files
Rem
if exist *.log del *.log /q >> %TEMP_DIR%\hotbackup.log
if exist *.lst del *.lst /q >> %TEMP_DIR%\hotbackup.log
Rem
Rem Rename the old backup files
Rem Create a copy of the current backup files
Rem
Rem if exist %DEST_DIR%\%ORACLE_SID%\*.ctl ren %DEST_DIR%\%ORACLE_SID%\*.ctl *.ctl.old >> %TEMP_DIR%\hotbackup.log
Rem if exist %DEST_DIR%\%ORACLE_SID%\*.log ren %DEST_DIR%\%ORACLE_SID%\*.log *.log.old >> %TEMP_DIR%\hotbackup.log
Rem if exist %DEST_DIR%\%ORACLE_SID%\*.dbf ren %DEST_DIR%\%ORACLE_SID%\*.dbf *.dbf.old >> %TEMP_DIR%\hotbackup.log
Rem
Rem Delete the old backup files
Rem Saves no copy of current backup
Rem
if exist %DEST_DIR%\%ORACLE_SID%\*.ctl del %DEST_DIR%\%ORACLE_SID%\*.ctl /q >> %TEMP_DIR%\hotbackup.log
if exist %DEST_DIR%\%ORACLE_SID%\*.log del %DEST_DIR%\%ORACLE_SID%\*.log /q >> %TEMP_DIR%\hotbackup.log
if exist %DEST_DIR%\%ORACLE_SID%\*.dbf del %DEST_DIR%\%ORACLE_SID%\*.dbf /q >> %TEMP_DIR%\hotbackup.log
Rem
Rem Start the hot backup script
Rem
D:\Oracle\Oracle817\Bin\sqlplus "sys/oracle@%ORACLE_SID% as sysdba" @%SCRIPTS%\hotbackup.sql %ORACLE_SID% %TEMP_DIR% %DEST_DIR%
type %TEMP_DIR%\hotbackup.log >> %DEST_DIR%\%ORACLE_SID%\Log\backup.log
exit
Rem ******************************************************************************************************************************
Rem end of script
--- 4 SCRIPT HOTBACKUP.SQL ---
This script will create a spool file wich will be save in your temporary location.
The spool file will be used to finally execute the statement for the backup.
The spool file is filled with the result of the queries.
NOTE: when you have more then 2 controlfiles or the names are different then mentioned
in the script (remark 3) change the names to the names you use or add a controlfile.
This because when recovery is needed you will not have any problem with the naming used
and the names define in your parameter file of the parameter control_files.
remark HOTBACKUP.sql
remark
remark Make a hot backup of the database.
remark
remark Parameters: 1 - SID for the database
remark 2 - Temp for temporary directory
remark 3 - Destination for locatie of the backup
remark
remark
remark 1- for all tablespaces which are not ACTIVE
remark make tablespaces active
remark 2- for all loggroups
remark logswitch, so all changes/ inserts in redologfiles are archiveerd
remark 3- for every controlfile
remark make backup of the controlfile
remark 4- backup of the controlfile to trace directory
remark 5- for every tablespace
remark set tablespace in backup mode
remark copy datafile to destination directory
remark set tablespace in active mode
remark 6- show status of tablespaces
remark
remark 09-07-2001 B. de Cock Buning
remark Create script for dynamic hotbackup
remark
set pagesize 0
set feedback off
set linesize 132
set trimspool on
set verify off
define dbsid=&1
define temp =&2
define destination=&3
spool &temp\backup_prod.sql
select 'Rem Start tijd:' || to_char( sysdate, 'dd-mm-yyyy hh24:mi' ) from dual;
prompt Spool &destination\&dbsid\log\backup.log;
remark 1
select 'alter tablespace '||t.name||' end backup;'
from sys.file$ f, v$backup v, sys.ts$ t
where v.file# = f.file#
and f.ts# = t.ts#
and v.status = 'ACTIVE';
remark 2
select 'alter system switch logfile;'
from sys.v_$log;
remark 3
prompt alter database backup controlfile to '&destination\&dbsid\control01.ctl';;
prompt alter database backup controlfile to '&destination\&dbsid\control02.ctl';;
remark 4
prompt alter database backup controlfile to trace;;
remark 5
select 'alter tablespace '||tablespace_name||' begin backup;' || chr(10) ||
'host copy '||file_name||' &destination\&dbsid\ >> &temp\hotbackup.log' || chr(10) ||
'alter tablespace '||tablespace_name||' end backup;' || chr(10)
from dba_data_files;
remark 6
prompt select file#,status, to_char(time,'DD-MM-YYYY HH24:Mi') TIME from v$backup;;
prompt Spool off;;
prompt exit;;
spool off
set feedback on
set heading on
set pagesize 40
set linesize 80
--- 5 SCHEDULE SCRIPT ---
To schedule the scripts for execution at night you needed to use a tool to schedule it.
One easy to use tool is winat. This tool is included in the windows recourse kit.
When you install this tool, make sure the service schedule is running and also starts
automaticly. To check this after the installation open your control panel with subtopic services
and check the service schedule it must be up and running.
After all these steps you have a good hotbackup senario. Of course you have to
test your script for errors.
At the destination location there will be a subfolder with the name log containing
a file called backup.log. This file will display the result of the backup. If there
is any kind of error during the execution check this file and solve the problem.
Monday, March 10, 2008
Data Pump (Oracle 10g New Feature)
***********************************************************************************For DataPump YOu need to create the Directory where BAckup will be stored.
CREATE DIRECTORY ABC_dir AS '/oradata/backup/backup_ABC';
Then Grant the User who will perfomr the Export (sys is by default granted).
grant read, write on directory ABC_dir to system;grant read, write on directory ABC_dir to ilrpoly;
expdp system/sys@ilrpoly directory=ABC_dir DUMPFILE=ABC_FULLEXP_TODAY.dmp LOGFILE=ABC_FULLEXP_TODAY.log full=y;****************************************************************************************************************For Importing user need to copy the file in the Creted directory .This will Not Import from any path.
impdp system/sys directory=ABC_backup dumpfile=EXP_ABC_WED_MORNING.DMP tables="USSTATE" *****************************************************************************************Ref = Part No. B10825-01
*******************************************************************************************
CREATE DIRECTORY ABC_dir AS '/oradata/backup/backup_ABC';
Then Grant the User who will perfomr the Export (sys is by default granted).
grant read, write on directory ABC_dir to system;grant read, write on directory ABC_dir to ilrpoly;
expdp system/sys@ilrpoly directory=ABC_dir DUMPFILE=ABC_FULLEXP_TODAY.dmp LOGFILE=ABC_FULLEXP_TODAY.log full=y;****************************************************************************************************************For Importing user need to copy the file in the Creted directory .This will Not Import from any path.
impdp system/sys directory=ABC_backup dumpfile=EXP_ABC_WED_MORNING.DMP tables="USSTATE" *****************************************************************************************Ref = Part No. B10825-01
*******************************************************************************************
Friday, March 7, 2008
To Check the Performance
rem ***********************************************************************rem Program: dba_dbperf_w.sqlrem Purpose: Oracle 9.X Weekly Reportrem Author: Aleksandr Lembrikov, Ernst & Young, LLP, IT IAS (created)rem Aramando Plascencia, WEA Corporation, Time Warner Music (tested)rem Date: 31-Aug-2006rem Comments:rem This script will run against Oracle Version 9.x and is usedrem to assist the auditor or DBA in documenting the databaserem environment. Most statements use dba tables or the v$ viewsrem so it is recommended this script be run by a DBA.remrem Note: Some sql scripts have been gathered from Oracle manuals andrem the author wishes to acknowledge this fact upfront.remrem Parameters:rem This script takes as a parameter the database sid.rem Audit Trail:rem **************************************************************************remset linesize 90set pagesize 999set verify offset feedback offset maxdata 4000set arraysize 1rem
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
accept s_id prompt 'Enter Oracle SID:'
spool dba_dbperf_w_&&s_id
set heading offselect 'V9 Weekly Database Report for SID=&&s_id generated on ' to_char(sysdate,'DD-MON-YY HH24:MI:SS')' by ' userfrom sys.dual/set heading onpromptprompt *********************************************************************prompt * Tuning: Contention for dispatchersprompt *** Add more dispatchers if busy rate is > 50%prompt *prompt *********************************************************************column name format a15column status format a8 heading "STATUS"column pct_busy format 999.99 heading "%Busy"
select name, network, status, (busy/(busy+idle))*100 Pct_Busyfrom v$dispatcherorder by name, network/promptprompt *********************************************************************prompt * Tuning: Contention for multi-threaded serversprompt * Add more mts servers if busy rate is > 50%prompt * prompt *********************************************************************column name format a15column status format a8 heading "STATUS"column pct_busy format 999.99 heading "%Busy"
select name, status, requests, (busy/(busy+idle))*100 Pct_Busyfrom v$shared_serverorder by name/promptprompt *********************************************************************prompt * Database export informationprompt *********************************************************************
select * from sys.dba_exp_filesorder by timestamp/promptprompt *********************************************************************prompt * Buffer cache hit ratioprompt * If hit ratio is less than 70% then increase db_block_buffers.prompt * HitRatio=(1-(physical reads/(db block gets+consistent gets)))*100prompt *********************************************************************column name format a33 heading "TYPE"column value format 999,999,999,999column hitratio format 999.99 heading "Hit Ratio"
select name, valuefrom v$sysstatwhere name in ('db block gets', 'consistent gets', 'physical reads')order by name/select ((cur.value+con.value)/(( cur.value+con.value)+phy.value))*100 hitratiofrom v$sysstat cur, v$sysstat con, v$sysstat phywhere cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads'/promptprompt *********************************************************************prompt * Library cache reload ratioprompt * This ratio should be very low indicating that the library cacheprompt * is large enough. If high, increase shared_pool_size.prompt *********************************************************************column libcache format 99.99 heading 'Library CacheReload %'
select sum(reloads)/sum(pins) *100 libcachefrom v$librarycache/promptprompt *********************************************************************prompt * Library cache hit ratioprompt * GETHITRATIO is number of GETHITS/GETSprompt * PINHIT RATIO is number of PINHITS/PINS prompt * A number close to 1 indicates that most objects have been cachedprompt *********************************************************************column namespace format a20 heading 'NAME'column gets format 9999999999 heading 'GETS'column gethits format 9999999999 heading 'GETHITS'column gethitratio format 999.99 heading 'GET HITRATIO'column pins format 9999999999 heading 'PINS'column pinhits format 999999999 heading 'PINHITS'column pinhitratio format 999.99 heading 'PIN HITRATIO'
select namespace, gets, gethits, gethitratio, pins, pinhits, pinhitratiofrom v$librarycache/promptprompt *********************************************************************prompt * Data dictionary cache miss ratioprompt * Miss ratio should be below 5% to keep the data dictionaryprompt * cached in the SGA. Increase shared_pool_size to improve.prompt *********************************************************************column dictcache format 999.99 heading 'Dictionary CacheMiss Ratio %'
select sum(getmisses)/sum(gets) * 100 dictcachefrom v$rowcache/promptprompt *********************************************************************prompt * Data dictionary cache hit ratioprompt * Hit Ratio = sum(gets)/(sum(gets)+sum(getmisses))prompt *********************************************************************column hitratio format 999.99 heading "Hit Ratio"
select sum(gets)/(sum(gets)+sum(getmisses)) hitratiofrom v$rowcache/promptprompt *********************************************************************prompt * Data dictionary cache hit ratio by componentprompt *********************************************************************column a format a17 heading 'Cache'column b format 99999990 heading 'Gets'column c format 99999990 heading 'Misses'column d format 990 heading '%Miss'column e format 9990 heading 'Usage'column f format 99990 heading 'Count'column g format 990 heading '%Usage'select substr(parameter,4,17) a, gets b, getmisses c, decode(gets,0,0,(getmisses*100)/gets) d, usage e, count f, (usage*100)/count gfrom v$rowcachewhere count <> 0order by 1/promptprompt *********************************************************************prompt * In memory and disk sortsprompt * To make best use of sort memory the initial extent of yourprompt * sort-work tablespace should be sufficient to hold atprompt * least one sort run from memory, to reduce dynamic spaceprompt * allocation. Ratio of disk sorts to memory sorts shouldprompt * be less than 5%. Increase sort area size if higher.prompt *********************************************************************column name format a33 heading "SORT TYPE"column value format 999,999,999,999column parmval format a20
select name, valuefrom v$sysstatwhere name in ('sorts (memory)', 'sorts (disk)','sorts (rows)')/select round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2) "Pct Memory Sorts"from v$sysstat a, v$sysstat bwhere a.name = 'sorts (disk)' and b.name = 'sorts (memory)'/set heading offselect 'init.ora sort_area_size :', value "parmval"from v$parameter where name = 'sort_area_size'/promptprompt *********************************************************************prompt * Display cursor memoryprompt *********************************************************************
set heading onselect sum(250 * users_opening) "Open Cursor Memory"from v$sqlarea/promptprompt *********************************************************************prompt * Display memory set aside for packages prompt *********************************************************************
select sum(sharable_mem) "Shareable Memory"from v$db_object_cachewhere type = 'PACKAGE' OR type = 'PACKAGE_BODY'/promptprompt *********************************************************************prompt * Loads to db object cacheprompt *********************************************************************
select sum(loads), sum(executions), round((sum(loads) / sum(executions)) * 100, 4) Load_Percentfrom sys.v_$db_object_cache/promptprompt *********************************************************************prompt * Free buffer waitsprompt * The following script lists the number of waits forprompt * free buffers to be made available for new data being prompt * brought into the buffer cache. The output is often a sign prompt * of an untuned DBWR or a buffer cache that is too small. prompt *********************************************************************
select name, value from v$sysstat where name = 'free buffer waits'/promptprompt *********************************************************************prompt * Library cache contentionprompt *********************************************************************column name format a25 heading " TYPE"
select substr(name,1,25) name, gets, misses,immediate_gets, immediate_misses from v$latchwhere (misses > 0 or immediate_misses > 0) and name like 'library cach%'/promptprompt *********************************************************************prompt * Redo latch contentionprompt * There is contention if ratio of misses to getsprompt * exceeds 1%, or ratio of immediate_misses to theprompt * sum of immediate_gets + immediate_misses exceeds 1%prompt *********************************************************************column name format a25 heading 'Latch Type'column gets format 9999999999 heading 'Gets'column misses format 9999999 heading 'Misses'column immediate_gets format 999999999 heading 'Immediate Gets 'column immediate_misses format 999999999 heading 'Immediate Misses'
select name, gets, misses, immediate_gets, immediate_missesfrom v$latchwhere name like 'redo%'/promptprompt *********************************************************************prompt * Wait statisticsprompt * These numbers will tell you where there is contention inprompt * in the system. There will usually be some contention inprompt * any system, but if the ratio of waits for a particular operationprompt * starts to rise, you may need to add additional resources,prompt * such as more database buffers, log buffers, or rollbacks.prompt * Waits for any class should not be more 1% of total requests.prompt *********************************************************************column class heading 'Class Type'column count format 99,999,999 heading 'Occurrences'
select class, countfrom v$waitstatwhere count > 0order by class/promptprompt *********************************************************************prompt * Tablespace storage allocation and percent of space used ifprompt * used greater than 60%prompt *********************************************************************column "TBLSPC" format A25 heading 'TABLESPACE'column "ALLOC" format 999,999,999,999 heading 'ALLOCATED'column "USED" format 999,999,999,999 heading 'USED'column "UNUSED" format 999,999,999,999 heading 'UNUSED'column "USEDPCT" format 999.99
select u.tblspc "TBLSPC", a.fbytes "ALLOC", u.ebytes "USED", a.fbytes - u.ebytes "UNUSED", (u.ebytes/a.fbytes)*100 "USEDPCT"from (select tablespace_name tblspc, sum(bytes) ebytes from sys.dba_extents group by tablespace_name) u, (select tablespace_name tblspc, sum(bytes) fbytes from sys.dba_data_files group by tablespace_name) awhere u.tblspc = a.tblspc and (u.ebytes/a.fbytes)*100 > 60order by USEDPCT desc/promptprompt **************************************************************prompt * Tablespace fragmentation information (chunks > 2)prompt * If a ts has a large number of free chunks, thisprompt * indicates that it is a candidate for re-organization, asprompt * there may still be a lot of free space within the ts butprompt * it is all in small pieces (a Swiss cheese ts )!! Oracleprompt * will attempt to coalesce small contiguous free spaceprompt * extents but it incurs significant overhead to do so.prompt**************************************************************column fbytes format 999,999,999,999 heading 'Free Bytes'column fblocks format 999,999,999 heading 'Free DB Blocks'column kount format 99,999 heading 'Free Chunks'
select tablespace_name, sum(bytes) fbytes, sum(blocks) fblocks, count(*) kountfrom dba_free_spacegroup by tablespace_namehaving count(*) > 2order by tablespace_name/promptprompt**************************************************************prompt* Log history for the past dayprompt**************************************************************
select sequence#, first_change#, first_time, next_change#from sys.v_$log_historywhere first_time > sysdate-1/promptprompt *********************************************************************prompt * Tuning I/O: Physical reads, writesprompt * If phyblkrd is significantly larger than phyrds then a fullprompt * tablespace scan is occurring......prompt * prompt ********************************************************************* column fname format a25 trunc heading "Database Data Files"column phyrds format 999999999column phyblkrd format 999999999column phywrts format 999999999column phyblkwrt format 999999999column IO format 999,999,999 heading "TOTAL IO"
select substr(df.name,1,6)substr(df.name,15,19) fname, fs.phyrds, fs.phyblkrd, fs.phywrts, fs.phyblkwrt, fs.phyrds+fs.phywrts IOfrom v$datafile df, v$filestat fswhere df.file# = fs.file#order by IO desc/promptprompt *********************************************************************prompt * Detailed information on SGA and memoryprompt *********************************************************************column name format a33 heading "SGA Component"
select * from v$sgastatorder by pool, name/promptprompt **********************************************************************prompt * Executions of objects in the shared pool - most executions firstprompt **********************************************************************column owner format a10column oname format a40 trunc heading "OBJECT"
select owner, name' - 'type oname, executions, decode(substr(kept,1,1),'Y', ' ', '<<<> 50 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by executions desc/promptprompt *********************************************************************prompt * Loads of objects in the shared pool - most loads firstprompt *********************************************************************column owner format a10column oname format a40 heading "OBJECT"
select owner, name' - 'type oname, loads, sharable_mem from v$db_object_cachewhere loads > 3 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by loads desc/promptprompt *********************************************************************prompt * Memory usage of shared pool - biggest firstprompt *********************************************************************column owner format a10column oname format a40 heading "OBJECT"
select owner, name' - 'type oname, sharable_memfrom v$db_object_cachewhere sharable_mem > 10000 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by sharable_mem desc/promptprompt *********************************************************************prompt * Objects that are keptprompt *********************************************************************column owner format a10column name format a25 heading "OBJECT"
select owner, name, executions, pins, keptfrom v$db_object_cachewhere kept = 'YES'order by owner, name/promptprompt *********************************************************************prompt * Rollback segment contentionprompt *********************************************************************column name format a20 heading "ROLLBACK SEGMENT"
select name, gets, waits, ((gets - waits) * 100)/gets hitratiofrom v$rollstat S, v$rollname Rwhere S.usn = R.usnorder by 1;promptprompt *********************************************************************prompt * Recommendation to add rollback segmentsprompt *********************************************************************column n1 format a15set heading off
select 'Try adding ' sum(decode(waits,0,0,1)) nl, 'rollback segments to avoid rollback header contention. 'from v$rollstat/promptprompt *********************************************************************prompt * Total number of rollback waits since the instance startedprompt *********************************************************************set heading on
select class, count from v$waitstatwhere class like '%undo%'/promptprompt *********************************************************************prompt * Rollback segment extends/shrinksprompt *********************************************************************
select usn, extends, shrinks, wraps, hwmsize, aveshrinkfrom v$rollstat;promptprompt *********************************************************************prompt * Redo log size problems (values should be near 0)prompt *********************************************************************column name format a35 heading 'Redo Log Stats'
select name, valuefrom v$sysstatwhere name in ('redo log space requests','redo buffer allocation retries')/promptprompt *********************************************************************prompt * Oracle User Ids with SYSTEM as their default or temp tablespacesprompt *prompt * Ensure that the default tablespace is NOT SYSTEM for any userprompt * except for SYSprompt *********************************************************************column username format a15 heading "USER"column default_ts format a15 heading "DEFLTTS"column temp_ts format a10 heading "TEMPTS"
select username, user_id, default_tablespace default_ts, temporary_tablespace temp_ts, createdfrom sys.dba_userswhere default_tablespace = 'SYSTEM' OR temporary_tablespace = 'SYSTEM'order by username/set heading off
select 'V9 End of Weekly Report 'to_char(sysdate,'DD-MON-YY HH24:MI:SS')' by 'userfrom sys.dual/spool offexit
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
accept s_id prompt 'Enter Oracle SID:'
spool dba_dbperf_w_&&s_id
set heading offselect 'V9 Weekly Database Report for SID=&&s_id generated on ' to_char(sysdate,'DD-MON-YY HH24:MI:SS')' by ' userfrom sys.dual/set heading onpromptprompt *********************************************************************prompt * Tuning: Contention for dispatchersprompt *** Add more dispatchers if busy rate is > 50%prompt *prompt *********************************************************************column name format a15column status format a8 heading "STATUS"column pct_busy format 999.99 heading "%Busy"
select name, network, status, (busy/(busy+idle))*100 Pct_Busyfrom v$dispatcherorder by name, network/promptprompt *********************************************************************prompt * Tuning: Contention for multi-threaded serversprompt * Add more mts servers if busy rate is > 50%prompt * prompt *********************************************************************column name format a15column status format a8 heading "STATUS"column pct_busy format 999.99 heading "%Busy"
select name, status, requests, (busy/(busy+idle))*100 Pct_Busyfrom v$shared_serverorder by name/promptprompt *********************************************************************prompt * Database export informationprompt *********************************************************************
select * from sys.dba_exp_filesorder by timestamp/promptprompt *********************************************************************prompt * Buffer cache hit ratioprompt * If hit ratio is less than 70% then increase db_block_buffers.prompt * HitRatio=(1-(physical reads/(db block gets+consistent gets)))*100prompt *********************************************************************column name format a33 heading "TYPE"column value format 999,999,999,999column hitratio format 999.99 heading "Hit Ratio"
select name, valuefrom v$sysstatwhere name in ('db block gets', 'consistent gets', 'physical reads')order by name/select ((cur.value+con.value)/(( cur.value+con.value)+phy.value))*100 hitratiofrom v$sysstat cur, v$sysstat con, v$sysstat phywhere cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads'/promptprompt *********************************************************************prompt * Library cache reload ratioprompt * This ratio should be very low indicating that the library cacheprompt * is large enough. If high, increase shared_pool_size.prompt *********************************************************************column libcache format 99.99 heading 'Library CacheReload %'
select sum(reloads)/sum(pins) *100 libcachefrom v$librarycache/promptprompt *********************************************************************prompt * Library cache hit ratioprompt * GETHITRATIO is number of GETHITS/GETSprompt * PINHIT RATIO is number of PINHITS/PINS prompt * A number close to 1 indicates that most objects have been cachedprompt *********************************************************************column namespace format a20 heading 'NAME'column gets format 9999999999 heading 'GETS'column gethits format 9999999999 heading 'GETHITS'column gethitratio format 999.99 heading 'GET HITRATIO'column pins format 9999999999 heading 'PINS'column pinhits format 999999999 heading 'PINHITS'column pinhitratio format 999.99 heading 'PIN HITRATIO'
select namespace, gets, gethits, gethitratio, pins, pinhits, pinhitratiofrom v$librarycache/promptprompt *********************************************************************prompt * Data dictionary cache miss ratioprompt * Miss ratio should be below 5% to keep the data dictionaryprompt * cached in the SGA. Increase shared_pool_size to improve.prompt *********************************************************************column dictcache format 999.99 heading 'Dictionary CacheMiss Ratio %'
select sum(getmisses)/sum(gets) * 100 dictcachefrom v$rowcache/promptprompt *********************************************************************prompt * Data dictionary cache hit ratioprompt * Hit Ratio = sum(gets)/(sum(gets)+sum(getmisses))prompt *********************************************************************column hitratio format 999.99 heading "Hit Ratio"
select sum(gets)/(sum(gets)+sum(getmisses)) hitratiofrom v$rowcache/promptprompt *********************************************************************prompt * Data dictionary cache hit ratio by componentprompt *********************************************************************column a format a17 heading 'Cache'column b format 99999990 heading 'Gets'column c format 99999990 heading 'Misses'column d format 990 heading '%Miss'column e format 9990 heading 'Usage'column f format 99990 heading 'Count'column g format 990 heading '%Usage'select substr(parameter,4,17) a, gets b, getmisses c, decode(gets,0,0,(getmisses*100)/gets) d, usage e, count f, (usage*100)/count gfrom v$rowcachewhere count <> 0order by 1/promptprompt *********************************************************************prompt * In memory and disk sortsprompt * To make best use of sort memory the initial extent of yourprompt * sort-work tablespace should be sufficient to hold atprompt * least one sort run from memory, to reduce dynamic spaceprompt * allocation. Ratio of disk sorts to memory sorts shouldprompt * be less than 5%. Increase sort area size if higher.prompt *********************************************************************column name format a33 heading "SORT TYPE"column value format 999,999,999,999column parmval format a20
select name, valuefrom v$sysstatwhere name in ('sorts (memory)', 'sorts (disk)','sorts (rows)')/select round((100*b.value)/decode((a.value+b.value),0,1,(a.value+b.value)),2) "Pct Memory Sorts"from v$sysstat a, v$sysstat bwhere a.name = 'sorts (disk)' and b.name = 'sorts (memory)'/set heading offselect 'init.ora sort_area_size :', value "parmval"from v$parameter where name = 'sort_area_size'/promptprompt *********************************************************************prompt * Display cursor memoryprompt *********************************************************************
set heading onselect sum(250 * users_opening) "Open Cursor Memory"from v$sqlarea/promptprompt *********************************************************************prompt * Display memory set aside for packages prompt *********************************************************************
select sum(sharable_mem) "Shareable Memory"from v$db_object_cachewhere type = 'PACKAGE' OR type = 'PACKAGE_BODY'/promptprompt *********************************************************************prompt * Loads to db object cacheprompt *********************************************************************
select sum(loads), sum(executions), round((sum(loads) / sum(executions)) * 100, 4) Load_Percentfrom sys.v_$db_object_cache/promptprompt *********************************************************************prompt * Free buffer waitsprompt * The following script lists the number of waits forprompt * free buffers to be made available for new data being prompt * brought into the buffer cache. The output is often a sign prompt * of an untuned DBWR or a buffer cache that is too small. prompt *********************************************************************
select name, value from v$sysstat where name = 'free buffer waits'/promptprompt *********************************************************************prompt * Library cache contentionprompt *********************************************************************column name format a25 heading " TYPE"
select substr(name,1,25) name, gets, misses,immediate_gets, immediate_misses from v$latchwhere (misses > 0 or immediate_misses > 0) and name like 'library cach%'/promptprompt *********************************************************************prompt * Redo latch contentionprompt * There is contention if ratio of misses to getsprompt * exceeds 1%, or ratio of immediate_misses to theprompt * sum of immediate_gets + immediate_misses exceeds 1%prompt *********************************************************************column name format a25 heading 'Latch Type'column gets format 9999999999 heading 'Gets'column misses format 9999999 heading 'Misses'column immediate_gets format 999999999 heading 'Immediate Gets 'column immediate_misses format 999999999 heading 'Immediate Misses'
select name, gets, misses, immediate_gets, immediate_missesfrom v$latchwhere name like 'redo%'/promptprompt *********************************************************************prompt * Wait statisticsprompt * These numbers will tell you where there is contention inprompt * in the system. There will usually be some contention inprompt * any system, but if the ratio of waits for a particular operationprompt * starts to rise, you may need to add additional resources,prompt * such as more database buffers, log buffers, or rollbacks.prompt * Waits for any class should not be more 1% of total requests.prompt *********************************************************************column class heading 'Class Type'column count format 99,999,999 heading 'Occurrences'
select class, countfrom v$waitstatwhere count > 0order by class/promptprompt *********************************************************************prompt * Tablespace storage allocation and percent of space used ifprompt * used greater than 60%prompt *********************************************************************column "TBLSPC" format A25 heading 'TABLESPACE'column "ALLOC" format 999,999,999,999 heading 'ALLOCATED'column "USED" format 999,999,999,999 heading 'USED'column "UNUSED" format 999,999,999,999 heading 'UNUSED'column "USEDPCT" format 999.99
select u.tblspc "TBLSPC", a.fbytes "ALLOC", u.ebytes "USED", a.fbytes - u.ebytes "UNUSED", (u.ebytes/a.fbytes)*100 "USEDPCT"from (select tablespace_name tblspc, sum(bytes) ebytes from sys.dba_extents group by tablespace_name) u, (select tablespace_name tblspc, sum(bytes) fbytes from sys.dba_data_files group by tablespace_name) awhere u.tblspc = a.tblspc and (u.ebytes/a.fbytes)*100 > 60order by USEDPCT desc/promptprompt **************************************************************prompt * Tablespace fragmentation information (chunks > 2)prompt * If a ts has a large number of free chunks, thisprompt * indicates that it is a candidate for re-organization, asprompt * there may still be a lot of free space within the ts butprompt * it is all in small pieces (a Swiss cheese ts )!! Oracleprompt * will attempt to coalesce small contiguous free spaceprompt * extents but it incurs significant overhead to do so.prompt**************************************************************column fbytes format 999,999,999,999 heading 'Free Bytes'column fblocks format 999,999,999 heading 'Free DB Blocks'column kount format 99,999 heading 'Free Chunks'
select tablespace_name, sum(bytes) fbytes, sum(blocks) fblocks, count(*) kountfrom dba_free_spacegroup by tablespace_namehaving count(*) > 2order by tablespace_name/promptprompt**************************************************************prompt* Log history for the past dayprompt**************************************************************
select sequence#, first_change#, first_time, next_change#from sys.v_$log_historywhere first_time > sysdate-1/promptprompt *********************************************************************prompt * Tuning I/O: Physical reads, writesprompt * If phyblkrd is significantly larger than phyrds then a fullprompt * tablespace scan is occurring......prompt * prompt ********************************************************************* column fname format a25 trunc heading "Database Data Files"column phyrds format 999999999column phyblkrd format 999999999column phywrts format 999999999column phyblkwrt format 999999999column IO format 999,999,999 heading "TOTAL IO"
select substr(df.name,1,6)substr(df.name,15,19) fname, fs.phyrds, fs.phyblkrd, fs.phywrts, fs.phyblkwrt, fs.phyrds+fs.phywrts IOfrom v$datafile df, v$filestat fswhere df.file# = fs.file#order by IO desc/promptprompt *********************************************************************prompt * Detailed information on SGA and memoryprompt *********************************************************************column name format a33 heading "SGA Component"
select * from v$sgastatorder by pool, name/promptprompt **********************************************************************prompt * Executions of objects in the shared pool - most executions firstprompt **********************************************************************column owner format a10column oname format a40 trunc heading "OBJECT"
select owner, name' - 'type oname, executions, decode(substr(kept,1,1),'Y', ' ', '<<<> 50 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by executions desc/promptprompt *********************************************************************prompt * Loads of objects in the shared pool - most loads firstprompt *********************************************************************column owner format a10column oname format a40 heading "OBJECT"
select owner, name' - 'type oname, loads, sharable_mem from v$db_object_cachewhere loads > 3 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by loads desc/promptprompt *********************************************************************prompt * Memory usage of shared pool - biggest firstprompt *********************************************************************column owner format a10column oname format a40 heading "OBJECT"
select owner, name' - 'type oname, sharable_memfrom v$db_object_cachewhere sharable_mem > 10000 and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')order by sharable_mem desc/promptprompt *********************************************************************prompt * Objects that are keptprompt *********************************************************************column owner format a10column name format a25 heading "OBJECT"
select owner, name, executions, pins, keptfrom v$db_object_cachewhere kept = 'YES'order by owner, name/promptprompt *********************************************************************prompt * Rollback segment contentionprompt *********************************************************************column name format a20 heading "ROLLBACK SEGMENT"
select name, gets, waits, ((gets - waits) * 100)/gets hitratiofrom v$rollstat S, v$rollname Rwhere S.usn = R.usnorder by 1;promptprompt *********************************************************************prompt * Recommendation to add rollback segmentsprompt *********************************************************************column n1 format a15set heading off
select 'Try adding ' sum(decode(waits,0,0,1)) nl, 'rollback segments to avoid rollback header contention. 'from v$rollstat/promptprompt *********************************************************************prompt * Total number of rollback waits since the instance startedprompt *********************************************************************set heading on
select class, count from v$waitstatwhere class like '%undo%'/promptprompt *********************************************************************prompt * Rollback segment extends/shrinksprompt *********************************************************************
select usn, extends, shrinks, wraps, hwmsize, aveshrinkfrom v$rollstat;promptprompt *********************************************************************prompt * Redo log size problems (values should be near 0)prompt *********************************************************************column name format a35 heading 'Redo Log Stats'
select name, valuefrom v$sysstatwhere name in ('redo log space requests','redo buffer allocation retries')/promptprompt *********************************************************************prompt * Oracle User Ids with SYSTEM as their default or temp tablespacesprompt *prompt * Ensure that the default tablespace is NOT SYSTEM for any userprompt * except for SYSprompt *********************************************************************column username format a15 heading "USER"column default_ts format a15 heading "DEFLTTS"column temp_ts format a10 heading "TEMPTS"
select username, user_id, default_tablespace default_ts, temporary_tablespace temp_ts, createdfrom sys.dba_userswhere default_tablespace = 'SYSTEM' OR temporary_tablespace = 'SYSTEM'order by username/set heading off
select 'V9 End of Weekly Report 'to_char(sysdate,'DD-MON-YY HH24:MI:SS')' by 'userfrom sys.dual/spool offexit
IMP- EXP
CREATE DIRECTORY data_pump_dir AS '/usr/dba/dpumpfiles'
expdp hr/hr DUMPFILE=emp.dmp LOGFILE=emp.log TABLES=hr.employees
create directory pilot_bkp as 'I:\Pilot_Backup';select DIRECTORY_NAME,substr(DIRECTORY_PATH,1,100) from DBA_DIRECTORIES;
create directory bkpilrpoly as '/oradata/backup/backup_ilrpoly/';grant
grant read, write on directory ORCL_bkp to system;
CREATE DIRECTORY ilrpoly_dir AS '/oradata/backup/backup_ilrpoly';grant ilrpoly read, write on directory ilrpoly_dir to system;grant read, write on directory exp_dump to ilrpoly;
expdp system/sys@ilrpoly directory=ilrpoly_dir DUMPFILE=ILRPOLY_FULLEXP_TODAY.dmp LOGFILE=ILRPOLY_FULLEXP_TODAY.log full=y;
expdp system/sys@pilot directory=pilot_bkp DUMPFILE=Pilot_test.dmp LOGFILE=Pilot_test.log full=y;
SELECT OBJECT_PATH, COMMENTS FROM SCHEMA_EXPORT_OBJECTS2 WHERE OBJECT_PATH LIKE '%GRANT';
impdp sde/sde network_link=osiidb2rds tables=rds_photo.USSTATE remap_schema=rds_photo:sde EXCLUDE=CONSTRAINT REMAP_TABLESPACE=PHOTO_rds:sde
impdp wg5386/wg5386 network_link=pilot2rds tables=rds_photo.USSTATE remap_schema=rds_photo:wg5386 EXCLUDE=CONSTRAINT REMAP_TABLESPACE=PHOTO_rds:wg5386
create directory rds_backup as 'd:\rds_Backup';
create directory rds_backup as 'L:\';
create directory rds_backup as '/prod/backup/RDS/logical_backup/';grant read, write on directory rds_backup to system;
create directory pizza_backup as '/prod/backup/pizzahut/logical_backup/';grant read, write on directory rds_backup to system;
create directory rdsbackup as '/prod/data_backup/rds/';grant read, write on directory rdsbackup to system;
create directory ligbackup as '/prod/data_backup/lig/';grant read, write on directory rdsbackup to system;
grant read, write on directory pilot_bkp to system;
create directory geozip_bkp as 'F:\GEOZIP_BKP'grant read, write on directory geozip_bkp to system;expdp system/sys@pilot directory=geozip_bkp DUMPFILE=geozip_bkp_test.dmp LOGFILE=geozip_bkp_test.log schemas=GEOZIP
impdp system/sys directory=rds_backup dumpfile=EXP_RDS_WED_MORNING.DMP tables="USSTATE" REMAP_SCHEMA=RDS_PHOTO:sde REMAP_TABLESPACE=PHOTO_rds:sde
impdp system/sys directory=rds_backup dumpfile=EXP_RDS_WED_MORNING.DMP tables=rds_photo.USSTATE REMAP_SCHEMA=RDS_PHOTO:sde REMAP_TABLESPACE=PHOTO_rds:sde
exp system/system@admin1 file=fullexp.dmp log=fullexp.log owner='att','subtest','s7_db','sb_db','depr'
impdp rds/rds@rds directory=rds_backup dumpfile=EXP_RDS_MON_MORNING.DMP tables=rds.FARES_SC_KY_0207_V3 cONTENT=DATA_ONLY
exp system/sys@ilrpoly file=I:\ilrpoly_fullexp_18th_Apr.dmp log=I:\ilrpoly_fullexp_18th_Apr.log full=y buffer=200000 direct=y;imp sde/sde@osiiidb fromuser = sde touser=sde file=D:\OSIIIGC1_BACKUP\exp_OSIIIGC1_Mon_morning.dmp log =d:\uks_report.txt tables=tblinputvision ,tblgelsvision,tblerrorvision,tblqcerrorvision,tbluservision,tbloutputvision,tblprojectdetailvision
expdp hr/hr DUMPFILE=emp.dmp LOGFILE=emp.log TABLES=hr.employees
create directory pilot_bkp as 'I:\Pilot_Backup';select DIRECTORY_NAME,substr(DIRECTORY_PATH,1,100) from DBA_DIRECTORIES;
create directory bkpilrpoly as '/oradata/backup/backup_ilrpoly/';grant
grant read, write on directory ORCL_bkp to system;
CREATE DIRECTORY ilrpoly_dir AS '/oradata/backup/backup_ilrpoly';grant ilrpoly read, write on directory ilrpoly_dir to system;grant read, write on directory exp_dump to ilrpoly;
expdp system/sys@ilrpoly directory=ilrpoly_dir DUMPFILE=ILRPOLY_FULLEXP_TODAY.dmp LOGFILE=ILRPOLY_FULLEXP_TODAY.log full=y;
expdp system/sys@pilot directory=pilot_bkp DUMPFILE=Pilot_test.dmp LOGFILE=Pilot_test.log full=y;
SELECT OBJECT_PATH, COMMENTS FROM SCHEMA_EXPORT_OBJECTS2 WHERE OBJECT_PATH LIKE '%GRANT';
impdp sde/sde network_link=osiidb2rds tables=rds_photo.USSTATE remap_schema=rds_photo:sde EXCLUDE=CONSTRAINT REMAP_TABLESPACE=PHOTO_rds:sde
impdp wg5386/wg5386 network_link=pilot2rds tables=rds_photo.USSTATE remap_schema=rds_photo:wg5386 EXCLUDE=CONSTRAINT REMAP_TABLESPACE=PHOTO_rds:wg5386
create directory rds_backup as 'd:\rds_Backup';
create directory rds_backup as 'L:\';
create directory rds_backup as '/prod/backup/RDS/logical_backup/';grant read, write on directory rds_backup to system;
create directory pizza_backup as '/prod/backup/pizzahut/logical_backup/';grant read, write on directory rds_backup to system;
create directory rdsbackup as '/prod/data_backup/rds/';grant read, write on directory rdsbackup to system;
create directory ligbackup as '/prod/data_backup/lig/';grant read, write on directory rdsbackup to system;
grant read, write on directory pilot_bkp to system;
create directory geozip_bkp as 'F:\GEOZIP_BKP'grant read, write on directory geozip_bkp to system;expdp system/sys@pilot directory=geozip_bkp DUMPFILE=geozip_bkp_test.dmp LOGFILE=geozip_bkp_test.log schemas=GEOZIP
impdp system/sys directory=rds_backup dumpfile=EXP_RDS_WED_MORNING.DMP tables="USSTATE" REMAP_SCHEMA=RDS_PHOTO:sde REMAP_TABLESPACE=PHOTO_rds:sde
impdp system/sys directory=rds_backup dumpfile=EXP_RDS_WED_MORNING.DMP tables=rds_photo.USSTATE REMAP_SCHEMA=RDS_PHOTO:sde REMAP_TABLESPACE=PHOTO_rds:sde
exp system/system@admin1 file=fullexp.dmp log=fullexp.log owner='att','subtest','s7_db','sb_db','depr'
impdp rds/rds@rds directory=rds_backup dumpfile=EXP_RDS_MON_MORNING.DMP tables=rds.FARES_SC_KY_0207_V3 cONTENT=DATA_ONLY
exp system/sys@ilrpoly file=I:\ilrpoly_fullexp_18th_Apr.dmp log=I:\ilrpoly_fullexp_18th_Apr.log full=y buffer=200000 direct=y;imp sde/sde@osiiidb fromuser = sde touser=sde file=D:\OSIIIGC1_BACKUP\exp_OSIIIGC1_Mon_morning.dmp log =d:\uks_report.txt tables=tblinputvision ,tblgelsvision,tblerrorvision,tblqcerrorvision,tbluservision,tbloutputvision,tblprojectdetailvision
Trouble Shooting Script
create index IDX_COLMUN_NAME on FARES_INPUT_NATIONWIDE_V3(COLMUN_NAME);ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.DBF' AUTOEXTEND ON MAXSIZE UNLIMITEDALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS4.DBF' SIZE 10000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
/*/*/*/*/*/*11:48 AM 6/8/2007*/*/*/*/*/*/CREATE SMALLFILE TABLESPACE "FOREST" DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HLUD\FOREST.DBF' SIZE 200M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
drop index IDX_COLMUN_NAM;
create table Feb_19_FARES_INPUT_NATIONWIDE_V3 as select * from FARES_INPUT_NATIONWIDE_V3;
SELECT DBMS_METADATA.GET_DDL('INDEX',index_name) cr_index, index_name FROM user_INDEXES;SELECT dbms_metadata.get_ddl('INDEX', 'IDX_TBLTIMETRACKER_EMP_LOC') from dual;set long 600
select OWNER,object_name,object_type,status from dba_objects where status='INVALID' and OBJECT_TYPE <>'PROCEDURE';
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.dbf' SIZE 2000M
ALTER TABLESPACE "RDS" DEFAULT STORAGE ( INITIAL 5M MAXEXTENTS UNLIMITED );
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.dbf' SIZE 2000M
alter tablespace temp add tempfile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\TEMP04.DBF' SIZE 9000M autoextend on ;
ALTER TABLESPACE "WH5847" DATAFILE 'D:\AAWORK\OSIIIDBF\WH5847_1.DBF' RESIZE 3000M;
ALTER DATABASE DATAFILE 'D:\AAWORK\OSIIIDBF\WVTES32.DBF' RESIZE 1000M
Wed Feb 21 11:36:23 2007Errors in file d:\oracle\product\10.1.0\admin\timeinc\udump\timeinc_ora_1708.trc:ORA-12571: TNS:packet writer failure
FARES_INPUT_NATIONWIDE_V3
ALTER TABLESPACE "TIMEINC" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED )
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TIMEINC\TIMEINC.DBF' AUTOEXTEND ON MAXSIZE UNLIMITED
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TIMEINC\TIMEINC.DBF' RESIZE 1000M/*/*/*/*/*/*/*/*/*/*/*/3:17 PM 2/22/2007/*/*/*/*/*/orakill rds 1416/*/*/**/*/*/*/**/*/*/*/*/*/*/*/3:17 PM 2/22/2007/*/*/*/
CREATE USER "TELCOMWMS" ENTIFIED BY "TELCOMWMS" DEFAULT TABLESPACE "WVTEST6" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT CONNECT,resource,dba TO "TELCOMWMS";GRANT "DBA" TO "TELCOMWMS";GRANT "RESOURCE" TO "TELCOMWMS";
CREATE TABLESPACE "WG5386" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\WG5386.dbf' SIZE 100M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER TABLESPACE "WX" ADD DATAFILE '/oradata/ilrpolydata/wx1.dbf.dbf' SIZE 500M MAXEXTENTS UNLIMITED;
ALTER TABLESPACE "WX" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "WG5386" DEFAULT TABLESPACE "WG5386";
/*/*/*/*/*/*/*/*3:17 PM 2/22/2007/*/*/*/*/*/*/*/*/*/select max(maxquerylen) from v$undostat where begin_time>sysdate-10;/*/*/*/*/*/*///*3:18 PM 2/22/2007/*/*/*/*/*/*/*/*/*/
select round(bytes/1024/1024) from dba_segments where segment_name='FARES_INPUT_NATIONWIDE_V3';
analyze table FARES_INPUT_NATIONWIDE_V3 compute statistics;
dbms_stats.gather_table_stats(RDS, 'FARES_INPUT_NATIONWIDE_V3', granularity =>'DEFAULT',estimate_percent =>1);
SELECT 'rm ' name FROM v$archived_log WHERE registrar='RFS' AND applied='YES' AND completion_time < ownname="">'MY_USER',tabname=>'MY_TABLE',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,DEGREE=>2);
In Oracle 10 It Automatic Gather Statistics for the table who is not analyzed.
/*/*/*/*/*/*/*/*/**/*/*/*//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
CREATE TABLESPACE "ATT" LOGGING DATAFILE 'D:\oracle\oradata\ilr\att1.dbf' SIZE 50M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "ATT" DEFAULT TABLESPACE "ATT";
ALTER TABLESPACE "WX" ADD DATAFILE '/oradata/ilrpolydata/wx1.dbf.dbf' SIZE 500M MAXEXTENTS UNLIMITED;
ALTER TABLESPACE "TEMP" ADD DATAFILE D:\ORACLE\ORADATA\ILR\TEMP02.DBF SIZE 500M MAXEXTENTS UNLIMITED autoextend on;
ALTER TABLESPACE "TEMP" ADD TEMPFILE 'D:\ORACLE\ORADATA\ILR\TEMP02.DBF' SIZE 500 M ;
ALTER TABLESPACE "ATT" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
CREATE TABLESPACE "att" LOGGING DATAFILE 'D:\aawork\osiiidbf\att.dbf' SIZE 5M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "att" DEFAULT TABLESPACE "att";
CREATE TABLESPACE "RDSTEST" LOGGING DATAFILE 'D:\aawork\osiiidbf\RDSTEST1.dbf' SIZE 7000M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "RDSTEST" DEFAULT TABLESPACE "RDSTEST";
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\TEMP02.DBF' OFFLINE
/*/*/*/*/*/*/*/*/*/12:07 PM 2/26/2007/*/*/*/**/*/*/**/*/*/*/*/**/*/*/*/*/CREATE TABLESPACE "RDS_INDEX" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_INDEX.dbf' SIZE 100M DEFAULT STORAGE ( NEXT 5M MAXEXTENTS UNLIMITED )
/*/*/*/*/*/*
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.DBF' SIZE 1000M AUTOEXTEND ON ;
ALTER TABLESPACE "RDS_INDEX" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_INDEX2.DBF' SIZE 1000M AUTOEXTEND ON ;
/*/*/*/*/*/*
CREATE INDEX ord_customer_ix_demo ON orders (order_mode) nOSORT NOLOGGING;
tablespace rds_index
/*/*/*/*/*/*/*/*/*/*
i/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**/
CREATE PUBLIC DATABASE LINK ilr2databank CONNECT TO regp IDENTIFIED BY regp USING 'databank';
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/
If you have access to the dba_users, then it's possible to brute force the password, how long it would take depends on how complicated it is.
Here is a list of brute force tools: http://www.red-database-security.com/whitepaper/oracle_password_cracker.html
Here is the best one: http://www.toolcrypt.org/index.html?orabf
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/ALTER SYSTEM SET pga_aggregate_target = 104857600 SCOPE=BOTHALTER SYSTEM DISCONNECT SESSION '158,3868' IMMEDIATE/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
exec dbms_stats.gather_table_stats(ownname='RDS',tabname='FARES_0207_WORK_V1')
DECLARE X NUMBER;BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'DELETE TBLFINALDIC;COMMIT;' ,next_date => TO_DATE('13/03/2007 12:28:15','dd/mm/yyyy hh24:mi:ss') ,INTERVAL => 'SYSDATE+05/1440 ' ,no_parse => TRUE );END;/
begin
dbms_stats.gather_schema_stats( ownname=> 'RDS', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',options=> 'GATHER AUTO');
end;
/*/*/*/*/*5:44 PM 3/14/2007/*/*/*/*/*
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Recover table from recyclebin /*/*/*/*/*/*/*/*/*/*/*/*/*/*
select * from user_recyclebin;
Flashback table "%^^%$^$##32333" to before drop;
ALTER TABLE TBLBUILDINGHISTORY_QC ENABLE ROW MOVEMENT;
FLASHBACK TABLE TBLBUILDINGHISTORY_QC TO TIMESTAMP to_timestamp('2007-12-14 05:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
FLASHBACK TABLE DC_ENTRIES TO TIMESTAMP to_timestamp('2008-03-01 07:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
196 rows selected.
SQL> create table TBLBUILDINGHISTORY_QC_1 as select * from TBLBUILDINGHISTORY_QC;
Table created.
SQL> ALTER TABLE TBLBUILDINGHISTORY_QC ENABLE ROW MOVEMENT;
Table altered.
SQL> FLASHBACK TABLE TBLBUILDINGHISTORY_QC TO TIMESTAMP to_timestamp('2007-12-14 05:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
ALTER TABLE rds_photo.TBLRMSIFILEINFO ENABLE ROW MOVEMENT
ALTER TABLE timeinc.pra_folioinfo ENABLE ROW MOVEMENT;
ALTER TABLE RDS_RS.TBLFINALINFOTEST ENABLE ROW MOVEMENT;
FLASHBACK TABLE RDS_RS."BIN$O7606UdjIgPgRAAUTz/7kA==$0" TO BEFORE DROP
FLASHBACK TABLE RDS_PHOTO.TBLRMSIFILEINFO TO TIMESTAMP t5:56 PMo_timestamp('2007-03-21 11:30:05 AM', 'YYYY-MM-DD HH:MI:SS AM');FLASHBACK TABLE sde.TBLSTATUS TO TIMESTAMP to_timestamp('2007--13 11:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM');
select versions_startscn, to_char(versions_starttime, 'YYYY-MM-DD HH:MI:SS AM'), versions_xid, versions_operation, USERRIGHTS from RDS_TEST.BKP_TEST versions between scn minvalue and maxvalue where userrights=5 and versions_startscn is not null order by versions_startscn desc
select versions_startscn, to_char(versions_starttime, 'YYYY-MM-DD HH:MI:SS AM'), versions_xid, versions_operation, USERRIGHTS from RDS_TEST.BKP_TEST versions between scn minvalue and maxvalue where userrights=5 and versions_startscn is not null order by versions_startscn desc;
FLASHBACK TABLE RDS_TEST.BKP_TEST TO SCN 4325768411
FLASHBACK TABLE RDS_TEST.BKP_TEST TO TIMESTAMP to_timestamp('2007-04-07 03:01:05 PM', 'YYYY-MM-DD HH:MI:SS AM')FLASHBACK TABLE RDS_TEST.BKP_TEST TO TIMESTAMP to_timestamp('2007-04-07 04:15:36 PM', 'YYYY-MM-DD HH:MI:SS AM')FLASHBACK TABLE timeinc.pra_folioinfo TO TIMESTAMP to_timestamp('2007-11-22 23:30:00 PM', 'YYYY-MM-DD HH:MI:SS AM')
FLASHBACK TABLE RDS.TBLPOSTCLEANINGLOG, RDS.TBLPRECLEANINGLOG TO TIMESTAMP to_timestamp('2007-11-19 09:00:47', 'YYYY-MM-DD HH24:MI:SS')
ALTER TABLE RDS.TBLPOSTCLEANINGLOG ENABLE ROW MOVEMENT;ALTER TABLE RDS.TBLPRECLEANINGLOG ENABLE ROW MOVEMENT;impdp rds/rds@rds directory=rds_backup dumpfile=EXP_RDS_MON_MORNING.DMP tables=rds.FARES_SC_KY_0207_V3 cONTENT=DATA_ONLY
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Flashback table "BIN$OD/gAPw9RwqZPUNt6B4z5w==$0" to before drop;Flashback table "BIN$n4V4DqACSlajRt8zXSQiNA==$0" to before drop;Flashback table "BIN$mw6Gq8vmSSaKXZszSwZBmg==$0" to before drop;Flashback table "BIN$kJnBEQnkQVSMsJuAdJFoFw==$0" to before drop;Flashback table "BIN$zijqO4wMQyOwt+iZGxHa5A==$0" to before drop;Flashback table "BIN$k3z2akzmQAKSBOElpsAoIg==$0" to before drop;Flashback table "BIN$Q/EsIWP9RAixKtEnOdMlaQ==$0" to before drop;Flashback table "BIN$l/3q25hKR9ixexbKY0k68A==$0" to before drop;Flashback table "BIN$MzW5BHK6RpSlFbq1nB1e6g==$0" to before drop;Flashback table "BIN$7AHt0aeWRY2UZ/+UAw86MA==$0" to before drop;Flashback table "BIN$/6lHeRUvTeGAsovq8NNcVw==$0" to before drop;Flashback table "BIN$F7k9GY45RZ611FCX0Deejw==$0" to before drop;Flashback table "BIN$c10hh6E2T1WgccbGB6D/Ug==$0" to before drop;Flashback table "BIN$DyUzVpAvTCyMtEema0nD3A==$0" to before drop;Flashback table "BIN$Vow8HBfDTuGMSIG70WlRmw==$0" to before drop;Flashback table "BIN$McQcdSZrTpWQU2uuGoOrjw==$0" to before drop;Flashback table "BIN$l/M5pYh6TgCG5qLg4FG1Hg==$0" to before drop;Flashback table "BIN$Inz27n++Svie2QyWdAC+5g==$0" to before drop;Flashback table "BIN$Fj01j+4jRcaIxjkQakWCjQ==$0" to before drop;/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/
declarecmd varchar2(400);begincmd := 'create index RDS.IDX$_00001 on RDS.SANBORN_BLDG_ALT_ADD("ROWNO")';EXECUTE IMMEDIATE cmd;END;
/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/**/*/*/**/*/*/12:33 PM 3/19/2007/*/*/*/*/*/*/
CREATE TABLESPACE "IR_WATER" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\IR_WATER.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "IR_WATER" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\IR_WATER.DBF' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
ALTER USER "IR_WATER" DEFAULT TABLESPACE "IR_WATER"
/*/*/*/*/*/*/*
CREATE USER "IR_WATER" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCKGRANT "CONNECT" TO "IR_WATER"
ALTER USER "IR_WATER" DEFAULT TABLESPACE "IR_WATER"
ALTER SYSTEM KILL SESSION '112,1250' IMMEDIATE;
CREATE USER "ENBRIDGE" PROFILE "DEFAULT" IDENTIFIED BY "enbridge" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;GRANT "CONNECT" TO "ENBRIDGE";GRANT "DBA" TO "ENBRIDGE";GRANT "RESOURCE" TO "ENBRIDGE";
CREATE TABLESPACE "ENBRIDGE" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\ENBRIDGE1.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
select 'create index' ' ' INDEX_NAME ' ' 'on' ' ' TABLE_NAME'('COLUMN_NAME')'';' from user_ind_columns where INDEX_NAME not like '%$%';select 'ALter index' ' ' INDEX_NAME ' Rebuild'';' from user_ind_columns where INDEX_NAME not like '%$%';
ALTER SYSTEM SET sga_max_size = 1468006400 SCOPE=SPFILE;ALTER SYSTEM SET pga_aggregate_target = 130023424 SCOPE=BOTH;ALTER SYSTEM SET shared_pool_size = 423624704 SCOPE=BOTH;select CONSTRAINT_NAME , CONSTRAINT_TYPE , R_CONSTRAINT_NAME,TABLE_NAME from user_constraints where table_name not like '%$%' and R_CONSTRAINT_NAME is not null order by table_name;
select a.owner,a.table_name,a.constraint_name,b.table_name,b.constraint_name from all_constraints a,user_constraints b where a.constraint_name=b.r_constraint_name and b.table_name='&CHILD_TABLE' ;
select a.owner,a.table_name,a.constraint_name,b.table_name,b.constraint_name from all_constraints a,user_constraints b where a.constraint_name=b.r_constraint_name and b.constraint_name='&FOREIGN_KEY'
GRANT SELECT ON "PHI"."TBLNEWPROJECT" TO "SUSER";GRANT SELECT ON "PHI"."TBLUSER" TO "SUSER";
CREATE USER "APPDEV" PROFILE "DEFAULT" IDENTIFIED BY "appdev" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;GRANT "CONNECT" TO "APPDEV";GRANT "DBA" TO "APPDEV";GRANT "RESOURCE" TO "APPDEV";/*/*/*/*/*/*/
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\DUMP_NEW2.DBF' RESIZE 10000M
CREATE TABLESPACE "EEE" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\EEE.ora' SIZE 5M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED TEMPORARY
CREATE TEMPORARY TABLESPACE dump_new_temp tempfile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\dump_new_temp1.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED ;
ALTER USER "DUMP_NEW" DEFAULT TABLESPACE "DUMP_NEW" TEMPORARY TABLESPACE "DUMP_NEW_TEMP"/*/*/*/*/6:19 PM 4/5/2007/*/*/*/*/*/*/SQLNET.AUTHENTICATION_SERVICES= (NTS)
ALTER USER "RDS_TEST" DEFAULT TABLESPACE "RDS_TEST" TEMPORARY TABLESPACE "TEMP_RDS"/*/*/*/*/*/*/*/*/*/dbms_stats.gather_index_stats(ownname => 'DUMP_DATA', indname => 'BIN$ys9ufsdaQTWogIsk6x1UaQ==$0', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\TEMP.DBF' RESIZE 4000M;
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\TEMP.DBF' AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED;
ALTER TABLESPACE "TEMP" ADD TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\temp2.dbf' SIZE 1000M AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED;
/*/*/*
begin
dbms_stats.gather_schema_stats( ownname=> 'RDS', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDSUSER', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_FARES', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_PARTY', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_PHOTO', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_REMOTE', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
end;
/*/*/*/*/*/*/*/*///*/*/*/*11:30 AM 4/11/2007/*/*//*/*/*/*CREATE USER "SB_DB" PROFILE "DEFAULT" IDENTIFIED BY "sb_db" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;GRANT "CONNECT" TO "SB_DB";GRANT "DBA" TO "SB_DB";GRANT "RESOURCE" TO "SB_DB";/*/*/*/*/*/*/*/*/*/*/*/*/*CREATE TABLESPACE "SB_DB" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\SB_DB.dbf' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;/*/*/*/*/*/*/
ALTER SYSTEM SET db_cache_size = 738197504 SCOPE=BOTHALTER SYSTEM SET shared_pool_size = 423624704 SCOPE=BOTH
CREATE SMALLFILE TABLESPACE "RDS_BAVS" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_BAVS.DBF' SIZE 10000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BEGIN DBMS_SERVER_ALERT.SET_THRESHOLD(9000,NULL,NULL,NULL,NULL,1,1,NULL,5,'RDS_BAVS'); END;
ALTER USER "RDS_BAVS" DEFAULT TABLESPACE "RDS_BAVS" TEMPORARY TABLESPACE "TEMP_RDS"
/*/*/*/*//*/*/*/*/*//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*//*
ALTER TABLESPACE "RDS_BAVS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\rds_bavs1.dbf' SIZE 10000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
begindbms_stats.gather_table_stats(ownname => 'RDS_BAVS', tabname => 'CLIENT_DATA2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'RDS_BAVS', tabname => 'CLIENT_DATA2_DUP1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER TABLE RDS_BAVS.CLIENT_DATA2_TEMP ADD CONSTRAINT PK_SNO_CLIENT_DATA2_TEMP PRIMARY KEY (SNO)end;/*/*/*/**/*/*/*/*/**/*/*/*/*/*/*/9:47 AM 4/15/2007/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
CREATE PUBLIC DATABASE LINK RDS_DATAVALCONNECT TO RDSIDENTIFIED BY RDSUSING 'dataval';
/*/*/*/*/*/*/**/*/*/*/*/*/*/9:59 AM 4/15/2007/*/*/*/*/
impdp system/sys@pilot directory=rds_backup dumpfile=EXP_RDS_WED_MORNING.DMP tables="USSTATE" REMAP_SCHEMA=RDS_PHOTO:wg5386
select OWNER,NEXT_RUN_DATE , JOB_NAME, JOB_TYPE from dba_scheduler_jobs;
/*/*/*/**/*/*/*/*/**/*/*/*/*/*/*/12:19 PM 4/15/2007/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
oradim.exe -edit -SID ilrpoly -startmode auto -srvcstart system
You will have to edit you oracle service by using oradim and execute this command:
oradim.exe -edit -sid (YOUR SID) -startmode auto -srvcstart system
Or even delete your current oracle service and create a new one:
Example: Delete oracle service: oradim -del -sid (your sid)
Creation:
oradim.exe -new -sid (YOUR SID) -startmode manual -spfile oradim.exe -edit -sid (YOUR SID) -startmode auto -srvcstart system oradim.exe -edit -sid osiiigc1 -startmode auto -srvcstart system oradim.exe -edit -sid OMS -startmode auto -srvcstart system
Now your database will start after rebooting your windows server.
Kind regards
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/select * from dba_scheduler_jobs;
stop the job: begin dbms_scheduler.stop_job( 'HLUDLAYER2.NEW_STATUS_REPORT' , TRUE); end; /
remove the job: begin DBMS_JOB.REMOVE(id); end; /
BEGINsys.dbms_scheduler.set_attribute( name => '"SYS"."DAILY_PURGE_SCHEDULE"', attribute => 'repeat_interval', value => 'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0'); END;
DBMS_SCHEDULER.disable (name => 'HLUDLAYER2.NEW_STATUS_REPORT');
Elapsed: 00:00:00.0011:33:20 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER1.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.0011:33:48 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER3.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.0211:34:00 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER4.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
exec DBMS_SCHEDULER.disable (name => 'TIMEINC.UJB_UPDATE_TIME_INVENTORY');
exec DBMS_SCHEDULER.enable (name => 'TIMEINC.UJB_UPDATE_TIME_INVENTORY');
select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3') /*/*/*/*/*/*/*/*/*/*/*/*/*/***/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/exec dbms_stats.gather_table_stats('RDS_BAVS','FARES_FINAL1')create index idx_ADDRESS_fares_f on FARES_FINAL35381098(trim(ADDRESS)) tablespaces rds_index NOLOGGING;/*/*/*/*/*/*/*/*/*/*/8*//**/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/Consider gathering system statistics with sql > exec dbms_stats.gather_system_stats('start'); after a couple of hours with representative db activity sql > exec dbms_stats.gather_system_stats('stop');
create index idx_ADDRESS_fares_final2 on FARES_FINAL2(trim(ADDRESS)) tablespace rds_index nologging;
update --+ index(idx_ADDRESS_fares_final2) FARES_FINAL2 set address=trim(address) where
CREATE BITMAP INDEX IDX_property_state ON FARES_FINAL2(property_state) tablespace rds_index nologging;
CREATE BITMAP INDEX IDX_property_state ON FARES_FINAL2(property_state) tablespace rds_index nologging;/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
begindbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'LOCCVG', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'TBLMDF_RECORD', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'EDM_MASTER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);end;exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_LOCID_LOCCVG')exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_MDF_UID_LOCCVG')exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_MDF_UID')exec dbms_stats.gather_index_stats('DUMP_NEW','idx_labelid_loccvg')
drop index IDX_LOCID_LOCCVG; drop IDX_MDF_UID_LOCCVG; drop IDX_MDF_UID;
create bitmap index IDX_LOCID_LOCCVG on LOCCVG (LOCID);create bitmap index IDX_MDF_UID_LOCCVG on LOCCVG (MDF_UID);create bitmap index IDX_MDF_UID on EDM_MASTER (MDF_UID);opt/oracle/oracle/product/10.2.0/db_2/rdbms/admin/@/opt/oracle/oracle/product/10.2.0/db_2/rdbms/admin/spreport.sql
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Query To know how many update is being Done.
select c.username,a.sid,b.name,a.value,c.LOGON_TIME,c.OSUSER,c.status from v$sesstat a,v$statname b, v$session c whereb.statistic#=a.statistic# and b.statistic#=191 and a.sid=c.sid and c.type!='BACKGROUND';
SELECT sofar, totalwork FROM v$session_longops/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/***/*****/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
SELECT substr(OS_USERNAME,1,20) , substr(USERHOST,1,25) , ACTION_NAME,TO_CHAR(TIMESTAMP,'DD-MON-YY hh:MI:SS') FROM DBA_AUDIT_TRAIL;SELECT substr(owner,1,10), substr(OS_USERNAME,1,10) , substr(USERHOST,1,15) , substr(ACTION_NAME,1,15),substr(OBJ_NAME,1,35),TO_CHAR(TIMESTAMP,'DD-MON-YY hh:MI:SS') FROM DBA_AUDIT_TRAIL where action_name like '%TABLE%' and obj_name not like '%$%'
col OBJ_NAME format a25col OS_USERNAME format a15col ACTION_NAME format a15`col USERHOST format a15col username format a15delete from sys.aud$ where USERID='DBSNMP';SELECT username,OS_USERNAME,USERHOST,OBJ_NAME,ACTION_NAME,EXTENDED_TIMESTAMP FROM DBA_AUDIT_TRAIL;SELECT username,OS_USERNAME,USERHOST,OBJ_NAME,ACTION_NAME,EXTENDED_TIMESTAMP FROM uks_sys_audit where ACTION_NAME like '%USER%';select USERNAME,USER_ID,PASSWORD,LOCK_DATE from DBA_USERS order by username;SELECT OS_USERNAME , USERHOST , ACTION_NAME,EXTENDED_TIMESTAMP FROM DBA_AUDIT_TRAIL;AUDIT UPDATE table,INSERT TABLE , DELETE TABLE BY sde BY ACCESS;Audit update , delete , insert on phi.tbluser by access whenever successful;audit drop any table,create any table,alter any table,truncate any table by rds by access;audit drop any table,create any table,alter any table,truncate any table by dump_data by access;NOAUDIT UPDATE table,INSERT TABLE , DELETE TABLE BY phmap BY ACCESS;NOaudit drop any table,create any table,alter any table,truncate any table by phmap by access;noaudit drop any table,create any table,alter any table by rds_bavs by access;audit drop any table,create any table,alter any table by rds by access;audit table;audit ALTER DATABASE;Audit succeeded.Elapsed: 00:00:0000.00delete from sys.aud$ where TERMINAL='CP658GIS';delete from aud$ where USERHOST='Earth';truncate table sys.aud$;
12:28:31 SQL> audit drop any table,create any table by rds_bavs by access;Audit succeeded.Elapsed: 00:00:00.0012:28:44 SQL> show userUSER is "SYS"
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/alter system set audit_trail='db' scope=spfile;
ALTER SYSTEM SET pga_aggregate_target = 207618048 SCOPE=BOTH;
REVOKE "DBA" FROM "RMSIPMS";ALTER USER "RMSIPMS" DEFAULT ROLE ALL
/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*//*/*/*/*/*/*http://cp763gis.rmsi.com:5500/emCREATE USER "VISION" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCKGRANT "CONNECT" TO "VISION"CREATE SMALLFILE TABLESPACE "VISION" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\OSIIIGC1\VISION.DBF' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BEGIN DBMS_SERVER_ALERT.SET_THRESHOLD(9000,NULL,NULL,NULL,NULL,1,1,NULL,5,'VISION'); END;
emca -config dbcontrol db -repos create
dbms_stats.gather_index_stats(ownname => 'DUMP_DATA', indname => 'IDX_FLGPROCESS_TBLMFD_RECORD', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER DATABASE ADD LOGFILE GROUP 5 ( '/opt/oracle/oradata/ilrpoly/log5.ora') SIZE 512000KALTER DATABASE ADD LOGFILE GROUP 7 ( '/opt/oracle/oradata/ilrpoly/redo7.log')SIZE 512000KALTER SYSTEM SET log_buffer=2048576 SCOPE=SPFILE;
Recover database until cancel using backup controlfile;alter database open RESETLOGS; should be after the database is mounted.ALTER SYSTEM KILL SESSION '44,22298' IMMEDIATE
alter system flush shared_pool;
select /*+ parallel(
,4) */ count(1) from
;
/*/*/*/*/*/*11:48 AM 6/8/2007*/*/*/*/*/*/CREATE SMALLFILE TABLESPACE "FOREST" DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HLUD\FOREST.DBF' SIZE 200M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
drop index IDX_COLMUN_NAM;
create table Feb_19_FARES_INPUT_NATIONWIDE_V3 as select * from FARES_INPUT_NATIONWIDE_V3;
SELECT DBMS_METADATA.GET_DDL('INDEX',index_name) cr_index, index_name FROM user_INDEXES;SELECT dbms_metadata.get_ddl('INDEX', 'IDX_TBLTIMETRACKER_EMP_LOC') from dual;set long 600
select OWNER,object_name,object_type,status from dba_objects where status='INVALID' and OBJECT_TYPE <>'PROCEDURE';
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.dbf' SIZE 2000M
ALTER TABLESPACE "RDS" DEFAULT STORAGE ( INITIAL 5M MAXEXTENTS UNLIMITED );
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.dbf' SIZE 2000M
alter tablespace temp add tempfile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\TEMP04.DBF' SIZE 9000M autoextend on ;
ALTER TABLESPACE "WH5847" DATAFILE 'D:\AAWORK\OSIIIDBF\WH5847_1.DBF' RESIZE 3000M;
ALTER DATABASE DATAFILE 'D:\AAWORK\OSIIIDBF\WVTES32.DBF' RESIZE 1000M
Wed Feb 21 11:36:23 2007Errors in file d:\oracle\product\10.1.0\admin\timeinc\udump\timeinc_ora_1708.trc:ORA-12571: TNS:packet writer failure
FARES_INPUT_NATIONWIDE_V3
ALTER TABLESPACE "TIMEINC" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED )
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TIMEINC\TIMEINC.DBF' AUTOEXTEND ON MAXSIZE UNLIMITED
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TIMEINC\TIMEINC.DBF' RESIZE 1000M/*/*/*/*/*/*/*/*/*/*/*/3:17 PM 2/22/2007/*/*/*/*/*/orakill rds 1416/*/*/**/*/*/*/**/*/*/*/*/*/*/*/3:17 PM 2/22/2007/*/*/*/
CREATE USER "TELCOMWMS" ENTIFIED BY "TELCOMWMS" DEFAULT TABLESPACE "WVTEST6" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT CONNECT,resource,dba TO "TELCOMWMS";GRANT "DBA" TO "TELCOMWMS";GRANT "RESOURCE" TO "TELCOMWMS";
CREATE TABLESPACE "WG5386" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\WG5386.dbf' SIZE 100M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER TABLESPACE "WX" ADD DATAFILE '/oradata/ilrpolydata/wx1.dbf.dbf' SIZE 500M MAXEXTENTS UNLIMITED;
ALTER TABLESPACE "WX" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "WG5386" DEFAULT TABLESPACE "WG5386";
/*/*/*/*/*/*/*/*3:17 PM 2/22/2007/*/*/*/*/*/*/*/*/*/select max(maxquerylen) from v$undostat where begin_time>sysdate-10;/*/*/*/*/*/*///*3:18 PM 2/22/2007/*/*/*/*/*/*/*/*/*/
select round(bytes/1024/1024) from dba_segments where segment_name='FARES_INPUT_NATIONWIDE_V3';
analyze table FARES_INPUT_NATIONWIDE_V3 compute statistics;
dbms_stats.gather_table_stats(RDS, 'FARES_INPUT_NATIONWIDE_V3', granularity =>'DEFAULT',estimate_percent =>1);
SELECT 'rm ' name FROM v$archived_log WHERE registrar='RFS' AND applied='YES' AND completion_time < ownname="">'MY_USER',tabname=>'MY_TABLE',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,DEGREE=>2);
In Oracle 10 It Automatic Gather Statistics for the table who is not analyzed.
/*/*/*/*/*/*/*/*/**/*/*/*//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
CREATE TABLESPACE "ATT" LOGGING DATAFILE 'D:\oracle\oradata\ilr\att1.dbf' SIZE 50M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "ATT" DEFAULT TABLESPACE "ATT";
ALTER TABLESPACE "WX" ADD DATAFILE '/oradata/ilrpolydata/wx1.dbf.dbf' SIZE 500M MAXEXTENTS UNLIMITED;
ALTER TABLESPACE "TEMP" ADD DATAFILE D:\ORACLE\ORADATA\ILR\TEMP02.DBF SIZE 500M MAXEXTENTS UNLIMITED autoextend on;
ALTER TABLESPACE "TEMP" ADD TEMPFILE 'D:\ORACLE\ORADATA\ILR\TEMP02.DBF' SIZE 500 M ;
ALTER TABLESPACE "ATT" DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
CREATE TABLESPACE "att" LOGGING DATAFILE 'D:\aawork\osiiidbf\att.dbf' SIZE 5M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "att" DEFAULT TABLESPACE "att";
CREATE TABLESPACE "RDSTEST" LOGGING DATAFILE 'D:\aawork\osiiidbf\RDSTEST1.dbf' SIZE 7000M DEFAULT STORAGE ( MAXEXTENTS UNLIMITED );
ALTER USER "RDSTEST" DEFAULT TABLESPACE "RDSTEST";
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\TEMP02.DBF' OFFLINE
/*/*/*/*/*/*/*/*/*/12:07 PM 2/26/2007/*/*/*/**/*/*/**/*/*/*/*/**/*/*/*/*/CREATE TABLESPACE "RDS_INDEX" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_INDEX.dbf' SIZE 100M DEFAULT STORAGE ( NEXT 5M MAXEXTENTS UNLIMITED )
/*/*/*/*/*/*
ALTER TABLESPACE "RDS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS1.DBF' SIZE 1000M AUTOEXTEND ON ;
ALTER TABLESPACE "RDS_INDEX" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_INDEX2.DBF' SIZE 1000M AUTOEXTEND ON ;
/*/*/*/*/*/*
CREATE INDEX ord_customer_ix_demo ON orders (order_mode) nOSORT NOLOGGING;
tablespace rds_index
/*/*/*/*/*/*/*/*/*/*
i/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**/
CREATE PUBLIC DATABASE LINK ilr2databank CONNECT TO regp IDENTIFIED BY regp USING 'databank';
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/*/*/**/*/*/**//*/*/*/*/
If you have access to the dba_users, then it's possible to brute force the password, how long it would take depends on how complicated it is.
Here is a list of brute force tools: http://www.red-database-security.com/whitepaper/oracle_password_cracker.html
Here is the best one: http://www.toolcrypt.org/index.html?orabf
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/ALTER SYSTEM SET pga_aggregate_target = 104857600 SCOPE=BOTHALTER SYSTEM DISCONNECT SESSION '158,3868' IMMEDIATE/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
exec dbms_stats.gather_table_stats(ownname='RDS',tabname='FARES_0207_WORK_V1')
DECLARE X NUMBER;BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'DELETE TBLFINALDIC;COMMIT;' ,next_date => TO_DATE('13/03/2007 12:28:15','dd/mm/yyyy hh24:mi:ss') ,INTERVAL => 'SYSDATE+05/1440 ' ,no_parse => TRUE );END;/
begin
dbms_stats.gather_schema_stats( ownname=> 'RDS', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',options=> 'GATHER AUTO');
end;
/*/*/*/*/*5:44 PM 3/14/2007/*/*/*/*/*
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Recover table from recyclebin /*/*/*/*/*/*/*/*/*/*/*/*/*/*
select * from user_recyclebin;
Flashback table "%^^%$^$##32333" to before drop;
ALTER TABLE TBLBUILDINGHISTORY_QC ENABLE ROW MOVEMENT;
FLASHBACK TABLE TBLBUILDINGHISTORY_QC TO TIMESTAMP to_timestamp('2007-12-14 05:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
FLASHBACK TABLE DC_ENTRIES TO TIMESTAMP to_timestamp('2008-03-01 07:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
196 rows selected.
SQL> create table TBLBUILDINGHISTORY_QC_1 as select * from TBLBUILDINGHISTORY_QC;
Table created.
SQL> ALTER TABLE TBLBUILDINGHISTORY_QC ENABLE ROW MOVEMENT;
Table altered.
SQL> FLASHBACK TABLE TBLBUILDINGHISTORY_QC TO TIMESTAMP to_timestamp('2007-12-14 05:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM');
ALTER TABLE rds_photo.TBLRMSIFILEINFO ENABLE ROW MOVEMENT
ALTER TABLE timeinc.pra_folioinfo ENABLE ROW MOVEMENT;
ALTER TABLE RDS_RS.TBLFINALINFOTEST ENABLE ROW MOVEMENT;
FLASHBACK TABLE RDS_RS."BIN$O7606UdjIgPgRAAUTz/7kA==$0" TO BEFORE DROP
FLASHBACK TABLE RDS_PHOTO.TBLRMSIFILEINFO TO TIMESTAMP t5:56 PMo_timestamp('2007-03-21 11:30:05 AM', 'YYYY-MM-DD HH:MI:SS AM');FLASHBACK TABLE sde.TBLSTATUS TO TIMESTAMP to_timestamp('2007--13 11:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM');
select versions_startscn, to_char(versions_starttime, 'YYYY-MM-DD HH:MI:SS AM'), versions_xid, versions_operation, USERRIGHTS from RDS_TEST.BKP_TEST versions between scn minvalue and maxvalue where userrights=5 and versions_startscn is not null order by versions_startscn desc
select versions_startscn, to_char(versions_starttime, 'YYYY-MM-DD HH:MI:SS AM'), versions_xid, versions_operation, USERRIGHTS from RDS_TEST.BKP_TEST versions between scn minvalue and maxvalue where userrights=5 and versions_startscn is not null order by versions_startscn desc;
FLASHBACK TABLE RDS_TEST.BKP_TEST TO SCN 4325768411
FLASHBACK TABLE RDS_TEST.BKP_TEST TO TIMESTAMP to_timestamp('2007-04-07 03:01:05 PM', 'YYYY-MM-DD HH:MI:SS AM')FLASHBACK TABLE RDS_TEST.BKP_TEST TO TIMESTAMP to_timestamp('2007-04-07 04:15:36 PM', 'YYYY-MM-DD HH:MI:SS AM')FLASHBACK TABLE timeinc.pra_folioinfo TO TIMESTAMP to_timestamp('2007-11-22 23:30:00 PM', 'YYYY-MM-DD HH:MI:SS AM')
FLASHBACK TABLE RDS.TBLPOSTCLEANINGLOG, RDS.TBLPRECLEANINGLOG TO TIMESTAMP to_timestamp('2007-11-19 09:00:47', 'YYYY-MM-DD HH24:MI:SS')
ALTER TABLE RDS.TBLPOSTCLEANINGLOG ENABLE ROW MOVEMENT;ALTER TABLE RDS.TBLPRECLEANINGLOG ENABLE ROW MOVEMENT;impdp rds/rds@rds directory=rds_backup dumpfile=EXP_RDS_MON_MORNING.DMP tables=rds.FARES_SC_KY_0207_V3 cONTENT=DATA_ONLY
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Flashback table "BIN$OD/gAPw9RwqZPUNt6B4z5w==$0" to before drop;Flashback table "BIN$n4V4DqACSlajRt8zXSQiNA==$0" to before drop;Flashback table "BIN$mw6Gq8vmSSaKXZszSwZBmg==$0" to before drop;Flashback table "BIN$kJnBEQnkQVSMsJuAdJFoFw==$0" to before drop;Flashback table "BIN$zijqO4wMQyOwt+iZGxHa5A==$0" to before drop;Flashback table "BIN$k3z2akzmQAKSBOElpsAoIg==$0" to before drop;Flashback table "BIN$Q/EsIWP9RAixKtEnOdMlaQ==$0" to before drop;Flashback table "BIN$l/3q25hKR9ixexbKY0k68A==$0" to before drop;Flashback table "BIN$MzW5BHK6RpSlFbq1nB1e6g==$0" to before drop;Flashback table "BIN$7AHt0aeWRY2UZ/+UAw86MA==$0" to before drop;Flashback table "BIN$/6lHeRUvTeGAsovq8NNcVw==$0" to before drop;Flashback table "BIN$F7k9GY45RZ611FCX0Deejw==$0" to before drop;Flashback table "BIN$c10hh6E2T1WgccbGB6D/Ug==$0" to before drop;Flashback table "BIN$DyUzVpAvTCyMtEema0nD3A==$0" to before drop;Flashback table "BIN$Vow8HBfDTuGMSIG70WlRmw==$0" to before drop;Flashback table "BIN$McQcdSZrTpWQU2uuGoOrjw==$0" to before drop;Flashback table "BIN$l/M5pYh6TgCG5qLg4FG1Hg==$0" to before drop;Flashback table "BIN$Inz27n++Svie2QyWdAC+5g==$0" to before drop;Flashback table "BIN$Fj01j+4jRcaIxjkQakWCjQ==$0" to before drop;/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/
declarecmd varchar2(400);begincmd := 'create index RDS.IDX$_00001 on RDS.SANBORN_BLDG_ALT_ADD("ROWNO")';EXECUTE IMMEDIATE cmd;END;
/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/**/*/*/**/*/*/12:33 PM 3/19/2007/*/*/*/*/*/*/
CREATE TABLESPACE "IR_WATER" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\IR_WATER.DBF' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
CREATE TABLESPACE "IR_WATER" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\IR_WATER.DBF' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
ALTER USER "IR_WATER" DEFAULT TABLESPACE "IR_WATER"
/*/*/*/*/*/*/*
CREATE USER "IR_WATER" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCKGRANT "CONNECT" TO "IR_WATER"
ALTER USER "IR_WATER" DEFAULT TABLESPACE "IR_WATER"
ALTER SYSTEM KILL SESSION '112,1250' IMMEDIATE;
CREATE USER "ENBRIDGE" PROFILE "DEFAULT" IDENTIFIED BY "enbridge" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;GRANT "CONNECT" TO "ENBRIDGE";GRANT "DBA" TO "ENBRIDGE";GRANT "RESOURCE" TO "ENBRIDGE";
CREATE TABLESPACE "ENBRIDGE" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\ENBRIDGE1.dbf' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
select 'create index' ' ' INDEX_NAME ' ' 'on' ' ' TABLE_NAME'('COLUMN_NAME')'';' from user_ind_columns where INDEX_NAME not like '%$%';select 'ALter index' ' ' INDEX_NAME ' Rebuild'';' from user_ind_columns where INDEX_NAME not like '%$%';
ALTER SYSTEM SET sga_max_size = 1468006400 SCOPE=SPFILE;ALTER SYSTEM SET pga_aggregate_target = 130023424 SCOPE=BOTH;ALTER SYSTEM SET shared_pool_size = 423624704 SCOPE=BOTH;select CONSTRAINT_NAME , CONSTRAINT_TYPE , R_CONSTRAINT_NAME,TABLE_NAME from user_constraints where table_name not like '%$%' and R_CONSTRAINT_NAME is not null order by table_name;
select a.owner,a.table_name,a.constraint_name,b.table_name,b.constraint_name from all_constraints a,user_constraints b where a.constraint_name=b.r_constraint_name and b.table_name='&CHILD_TABLE' ;
select a.owner,a.table_name,a.constraint_name,b.table_name,b.constraint_name from all_constraints a,user_constraints b where a.constraint_name=b.r_constraint_name and b.constraint_name='&FOREIGN_KEY'
GRANT SELECT ON "PHI"."TBLNEWPROJECT" TO "SUSER";GRANT SELECT ON "PHI"."TBLUSER" TO "SUSER";
CREATE USER "APPDEV" PROFILE "DEFAULT" IDENTIFIED BY "appdev" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;GRANT "CONNECT" TO "APPDEV";GRANT "DBA" TO "APPDEV";GRANT "RESOURCE" TO "APPDEV";/*/*/*/*/*/*/
ALTER DATABASE DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\DUMP_NEW2.DBF' RESIZE 10000M
CREATE TABLESPACE "EEE" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\EEE.ora' SIZE 5M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED TEMPORARY
CREATE TEMPORARY TABLESPACE dump_new_temp tempfile 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\dump_new_temp1.dbf' SIZE 1000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED ;
ALTER USER "DUMP_NEW" DEFAULT TABLESPACE "DUMP_NEW" TEMPORARY TABLESPACE "DUMP_NEW_TEMP"/*/*/*/*/6:19 PM 4/5/2007/*/*/*/*/*/*/SQLNET.AUTHENTICATION_SERVICES= (NTS)
ALTER USER "RDS_TEST" DEFAULT TABLESPACE "RDS_TEST" TEMPORARY TABLESPACE "TEMP_RDS"/*/*/*/*/*/*/*/*/*/dbms_stats.gather_index_stats(ownname => 'DUMP_DATA', indname => 'BIN$ys9ufsdaQTWogIsk6x1UaQ==$0', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\TEMP.DBF' RESIZE 4000M;
ALTER DATABASE TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\TEMP.DBF' AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED;
ALTER TABLESPACE "TEMP" ADD TEMPFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\DATABANK\temp2.dbf' SIZE 1000M AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED;
/*/*/*
begin
dbms_stats.gather_schema_stats( ownname=> 'RDS', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDSUSER', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_FARES', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_PARTY', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_PHOTO', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
dbms_stats.gather_schema_stats( ownname=> 'RDS_REMOTE', granularity=> 'DEFAULT', block_sample=> FALSE, cascade=> TRUE, degree=> DBMS_STATS.DEFAULT_DEGREE, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER AUTO');
end;
/*/*/*/*/*/*/*/*///*/*/*/*11:30 AM 4/11/2007/*/*//*/*/*/*CREATE USER "SB_DB" PROFILE "DEFAULT" IDENTIFIED BY "sb_db" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;GRANT "CONNECT" TO "SB_DB";GRANT "DBA" TO "SB_DB";GRANT "RESOURCE" TO "SB_DB";/*/*/*/*/*/*/*/*/*/*/*/*/*CREATE TABLESPACE "SB_DB" LOGGING DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\PILOT\SB_DB.dbf' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;/*/*/*/*/*/*/
ALTER SYSTEM SET db_cache_size = 738197504 SCOPE=BOTHALTER SYSTEM SET shared_pool_size = 423624704 SCOPE=BOTH
CREATE SMALLFILE TABLESPACE "RDS_BAVS" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\RDS_BAVS.DBF' SIZE 10000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BEGIN DBMS_SERVER_ALERT.SET_THRESHOLD(9000,NULL,NULL,NULL,NULL,1,1,NULL,5,'RDS_BAVS'); END;
ALTER USER "RDS_BAVS" DEFAULT TABLESPACE "RDS_BAVS" TEMPORARY TABLESPACE "TEMP_RDS"
/*/*/*/*//*/*/*/*/*//*/*/*/*/*/*/*/*/*/*/*/*/*/*/*//*
ALTER TABLESPACE "RDS_BAVS" ADD DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\RDS\rds_bavs1.dbf' SIZE 10000M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
begindbms_stats.gather_table_stats(ownname => 'RDS_BAVS', tabname => 'CLIENT_DATA2', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'RDS_BAVS', tabname => 'CLIENT_DATA2_DUP1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER TABLE RDS_BAVS.CLIENT_DATA2_TEMP ADD CONSTRAINT PK_SNO_CLIENT_DATA2_TEMP PRIMARY KEY (SNO)end;/*/*/*/**/*/*/*/*/**/*/*/*/*/*/*/9:47 AM 4/15/2007/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
CREATE PUBLIC DATABASE LINK RDS_DATAVALCONNECT TO RDSIDENTIFIED BY RDSUSING 'dataval';
/*/*/*/*/*/*/**/*/*/*/*/*/*/9:59 AM 4/15/2007/*/*/*/*/
impdp system/sys@pilot directory=rds_backup dumpfile=EXP_RDS_WED_MORNING.DMP tables="USSTATE" REMAP_SCHEMA=RDS_PHOTO:wg5386
select OWNER,NEXT_RUN_DATE , JOB_NAME, JOB_TYPE from dba_scheduler_jobs;
/*/*/*/**/*/*/*/*/**/*/*/*/*/*/*/12:19 PM 4/15/2007/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
oradim.exe -edit -SID ilrpoly -startmode auto -srvcstart system
You will have to edit you oracle service by using oradim and execute this command:
oradim.exe -edit -sid (YOUR SID) -startmode auto -srvcstart system
Or even delete your current oracle service and create a new one:
Example: Delete oracle service: oradim -del -sid (your sid)
Creation:
oradim.exe -new -sid (YOUR SID) -startmode manual -spfile oradim.exe -edit -sid (YOUR SID) -startmode auto -srvcstart system oradim.exe -edit -sid osiiigc1 -startmode auto -srvcstart system oradim.exe -edit -sid OMS -startmode auto -srvcstart system
Now your database will start after rebooting your windows server.
Kind regards
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/**/*/select * from dba_scheduler_jobs;
stop the job: begin dbms_scheduler.stop_job( 'HLUDLAYER2.NEW_STATUS_REPORT' , TRUE); end; /
remove the job: begin DBMS_JOB.REMOVE(id); end; /
BEGINsys.dbms_scheduler.set_attribute( name => '"SYS"."DAILY_PURGE_SCHEDULE"', attribute => 'repeat_interval', value => 'FREQ=DAILY;BYHOUR=3;BYMINUTE=0;BYSECOND=0'); END;
DBMS_SCHEDULER.disable (name => 'HLUDLAYER2.NEW_STATUS_REPORT');
Elapsed: 00:00:00.0011:33:20 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER1.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.0011:33:48 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER3.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.0211:34:00 SQL> exec DBMS_SCHEDULER.disable (name => 'HLUDLAYER4.NEW_STATUS_REPORT');
PL/SQL procedure successfully completed.
exec DBMS_SCHEDULER.disable (name => 'TIMEINC.UJB_UPDATE_TIME_INVENTORY');
exec DBMS_SCHEDULER.enable (name => 'TIMEINC.UJB_UPDATE_TIME_INVENTORY');
select dbms_xmlquery.getxml('select * from emp where rownum <= 3') from dual;DBMS_XMLQUERY.GETXML('SELECT*FROMEMPWHEREROWNUM<=3') /*/*/*/*/*/*/*/*/*/*/*/*/*/***/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/exec dbms_stats.gather_table_stats('RDS_BAVS','FARES_FINAL1')create index idx_ADDRESS_fares_f on FARES_FINAL35381098(trim(ADDRESS)) tablespaces rds_index NOLOGGING;/*/*/*/*/*/*/*/*/*/*/8*//**/*/*/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/Consider gathering system statistics with sql > exec dbms_stats.gather_system_stats('start'); after a couple of hours with representative db activity sql > exec dbms_stats.gather_system_stats('stop');
create index idx_ADDRESS_fares_final2 on FARES_FINAL2(trim(ADDRESS)) tablespace rds_index nologging;
update --+ index(idx_ADDRESS_fares_final2) FARES_FINAL2 set address=trim(address) where
CREATE BITMAP INDEX IDX_property_state ON FARES_FINAL2(property_state) tablespace rds_index nologging;
CREATE BITMAP INDEX IDX_property_state ON FARES_FINAL2(property_state) tablespace rds_index nologging;/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
begindbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'LOCCVG', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'TBLMDF_RECORD', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);dbms_stats.gather_table_stats(ownname => 'DUMP_NEW', tabname => 'EDM_MASTER', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);end;exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_LOCID_LOCCVG')exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_MDF_UID_LOCCVG')exec dbms_stats.gather_index_stats('DUMP_NEW','IDX_MDF_UID')exec dbms_stats.gather_index_stats('DUMP_NEW','idx_labelid_loccvg')
drop index IDX_LOCID_LOCCVG; drop IDX_MDF_UID_LOCCVG; drop IDX_MDF_UID;
create bitmap index IDX_LOCID_LOCCVG on LOCCVG (LOCID);create bitmap index IDX_MDF_UID_LOCCVG on LOCCVG (MDF_UID);create bitmap index IDX_MDF_UID on EDM_MASTER (MDF_UID);opt/oracle/oracle/product/10.2.0/db_2/rdbms/admin/@/opt/oracle/oracle/product/10.2.0/db_2/rdbms/admin/spreport.sql
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*Query To know how many update is being Done.
select c.username,a.sid,b.name,a.value,c.LOGON_TIME,c.OSUSER,c.status from v$sesstat a,v$statname b, v$session c whereb.statistic#=a.statistic# and b.statistic#=191 and a.sid=c.sid and c.type!='BACKGROUND';
SELECT sofar, totalwork FROM v$session_longops/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/***/*****/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/
SELECT substr(OS_USERNAME,1,20) , substr(USERHOST,1,25) , ACTION_NAME,TO_CHAR(TIMESTAMP,'DD-MON-YY hh:MI:SS') FROM DBA_AUDIT_TRAIL;SELECT substr(owner,1,10), substr(OS_USERNAME,1,10) , substr(USERHOST,1,15) , substr(ACTION_NAME,1,15),substr(OBJ_NAME,1,35),TO_CHAR(TIMESTAMP,'DD-MON-YY hh:MI:SS') FROM DBA_AUDIT_TRAIL where action_name like '%TABLE%' and obj_name not like '%$%'
col OBJ_NAME format a25col OS_USERNAME format a15col ACTION_NAME format a15`col USERHOST format a15col username format a15delete from sys.aud$ where USERID='DBSNMP';SELECT username,OS_USERNAME,USERHOST,OBJ_NAME,ACTION_NAME,EXTENDED_TIMESTAMP FROM DBA_AUDIT_TRAIL;SELECT username,OS_USERNAME,USERHOST,OBJ_NAME,ACTION_NAME,EXTENDED_TIMESTAMP FROM uks_sys_audit where ACTION_NAME like '%USER%';select USERNAME,USER_ID,PASSWORD,LOCK_DATE from DBA_USERS order by username;SELECT OS_USERNAME , USERHOST , ACTION_NAME,EXTENDED_TIMESTAMP FROM DBA_AUDIT_TRAIL;AUDIT UPDATE table,INSERT TABLE , DELETE TABLE BY sde BY ACCESS;Audit update , delete , insert on phi.tbluser by access whenever successful;audit drop any table,create any table,alter any table,truncate any table by rds by access;audit drop any table,create any table,alter any table,truncate any table by dump_data by access;NOAUDIT UPDATE table,INSERT TABLE , DELETE TABLE BY phmap BY ACCESS;NOaudit drop any table,create any table,alter any table,truncate any table by phmap by access;noaudit drop any table,create any table,alter any table by rds_bavs by access;audit drop any table,create any table,alter any table by rds by access;audit table;audit ALTER DATABASE;Audit succeeded.Elapsed: 00:00:0000.00delete from sys.aud$ where TERMINAL='CP658GIS';delete from aud$ where USERHOST='Earth';truncate table sys.aud$;
12:28:31 SQL> audit drop any table,create any table by rds_bavs by access;Audit succeeded.Elapsed: 00:00:00.0012:28:44 SQL> show userUSER is "SYS"
/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/alter system set audit_trail='db' scope=spfile;
ALTER SYSTEM SET pga_aggregate_target = 207618048 SCOPE=BOTH;
REVOKE "DBA" FROM "RMSIPMS";ALTER USER "RMSIPMS" DEFAULT ROLE ALL
/*/*/*/*/*/*/*/*/*/*/*/**/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*/*//*/*/*/*/*/*http://cp763gis.rmsi.com:5500/emCREATE USER "VISION" PROFILE "DEFAULT" IDENTIFIED BY "*******" ACCOUNT UNLOCKGRANT "CONNECT" TO "VISION"CREATE SMALLFILE TABLESPACE "VISION" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\OSIIIGC1\VISION.DBF' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO BEGIN DBMS_SERVER_ALERT.SET_THRESHOLD(9000,NULL,NULL,NULL,NULL,1,1,NULL,5,'VISION'); END;
emca -config dbcontrol db -repos create
dbms_stats.gather_index_stats(ownname => 'DUMP_DATA', indname => 'IDX_FLGPROCESS_TBLMFD_RECORD', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
ALTER DATABASE ADD LOGFILE GROUP 5 ( '/opt/oracle/oradata/ilrpoly/log5.ora') SIZE 512000KALTER DATABASE ADD LOGFILE GROUP 7 ( '/opt/oracle/oradata/ilrpoly/redo7.log')SIZE 512000KALTER SYSTEM SET log_buffer=2048576 SCOPE=SPFILE;
Recover database until cancel using backup controlfile;alter database open RESETLOGS; should be after the database is mounted.ALTER SYSTEM KILL SESSION '44,22298' IMMEDIATE
alter system flush shared_pool;
select /*+ parallel(
Subscribe to:
Comments (Atom)