Ameer Khan

The following script will report if temporary tablespace  hits 80% of its usage. From oracle user:[oracle@server1 ~]$ vi /home/oracle/check_temp_tbs.sql set head offset wrap offselect (s.tot_used_blocks/f.total_blocks)*100 as “percent used”from (select sum(used_blocks) tot_used_blocksfrom v$sort_segment where tablespace_name=’TEMP’) s,(select sum(blocks) total_blocksfrom dba_temp_files where tablespace_name=’TEMP’) f;exit; Now lets create the shell-script which will monitor the space usage of temp tablespaces:-From… Continue Reading

Oracle Version:- 11g Make sure all the directories mentioned in the script is present.Just copy the following lines mentioned below and save it as .bat file. Schedule it on task scheduleras per your requirement.Also make sure you have 7zip installed on your machine for the zip purpose. @ECHO OFFFor /f “tokens=2-4 delims=/ ” %%a in… Continue Reading

Oracle 11.2.0.1 download link for  Windows 7 64bit :- win64_11gR2_database_1of2 – https://onedrive.live.com/redir?resid=EB956C947D7F25EA!117&authkey=!AC89kV_gQ4Zh6Ug&ithint=file%2czip win64_11gR2_database_2of2 – https://onedrive.live.com/redir?resid=EB956C947D7F25EA!118&authkey=!AIH8tdcmUzPK9XM&ithint=file%2czip Continue Reading

Oracle Version : oracle 11g R2 What is Transportable Tablespaces(TTS)?Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles.Oracle 8i supports tablespacetransportation between databases that run on same OS platforms and use the same database block size. But in oracle… Continue Reading

Make sure all the directories mentioned in the script is present.Just copy the following lines mentioned below and save it as .bat file and schedule it in task scheduler. @ECHO OFFFor /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set mydate=%%c-%%b-%%a)For /f “tokens=1-2 delims=/:” %%a in (“%TIME%”) do (set mytime=%%a%%b)set mytime=%mytime: =0% set ORACLE_SID=orclset… Continue Reading

Create the following backup path:-[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/inc_level_1[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/ Make sure database is in archivelog mode , if its not put it on archivelog mode. SQL> sqlplus / as sysdbaSQL> startup mount;SQL> alter database archivelog;SQL> alter database open; [oracle@server1 ~]$ vi  /u01/backups/scripts/IncLevel_1_rman_backup.sh #!/bin/bashexport PS1=”`/bin/hostname s`> ” export ORACLE_SID=primexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/binexport path1=/u01/backups/rman_backup/inc_level_1date1=`date +%d%m%y_%H%M%S`mkdir $path1/$date1chown… Continue Reading

SQL> SELECT STARTUP_TIME FROM dba_hist_database_instance ORDER BY startup_time DESC; STARTUP_TIME—————————————————————————29-SEP-13 03.43.07.000 AM29-SEP-13 03.42.31.000 AM29-SEP-13 03.12.45.000 AM29-SEP-13 03.11.28.000 AM28-SEP-13 11.56.35.000 PM07-SEP-13 10.04.07.000 PM Continue Reading

SQL> create table xyz (id number);Table created. SQL> create table t1 as select * from xyz;Table created. SQL> SELECT * FROM TAB; TNAME                          TABTYPE  CLUSTERID—————————— ——- ———-T1                             TABLEXYZ  … Continue Reading

Create the following backup path:-[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/inc_level_1_cum[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/ Make sure database is in archivelog mode , if its not put it on archivelog mode. SQL> sqlplus / as sysdbaSQL> startup mount;SQL> alter database archivelog;SQL> alter database open; [oracle@server1 ~]$  vi /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh #!/bin/bashexport PS1=”`/bin/hostname s`> ” export ORACLE_SID=primexport ORACLE_BASE=/u01/app/oracleexport ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/binexport path1=/u01/backups/rman_backup/inc_level_1_cumdate1=`date +%d%m%y_%H%M%S`mkdir $path1/$date1chown oracle:oinstall -R… Continue Reading

SQL> create table xyz (id number);Table created. SQL> create table t1 as select * from xyz;Table created. SQL>BEGIN  FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN (‘T1′,’XYZ’))  LOOP       EXECUTE IMMEDIATE ‘TRUNCATE TABLE ‘ || i.table_name;   END LOOP; END;/ SQL> select * from xyz; no rows selected SQL> select *… Continue Reading