Learn Database Concepts

Question:   I get this error when starting my Oracle database.  What is the ORA-01113 error? SQL> ALTER TABLESPACE sample  ONLINE  2  ; ALTER TABLESPACE sample * ERROR at line 1: ORA-01113: file 27 needs media recovery ORA-01110: data file 27: '/u02/oracle/oradata/ORCL/sample.dbf' SQL> recover datafile '/u02/oracle/oradata/ORCL/sample.dbf'; Media recovery complete. SQL> ALTER TABLESPACE sample ONLINE; ALTER TABLESPACE sample ONLINE * ERROR at line 1: ORA-01113: file 29 needs media recovery ORA-01110: data file 29: '/u02/oracle/oradata/ORCL/SAMPLE10.dbf' SQL> recover datafile '/u02/oracle/oradata/ORCL/SAMPLE10.dbf'; Media recovery complete. SQL> ALTER TABLESPACE sample ONLINE; ALTER TABLESPACE sample ONLINE * ERROR at line 1: ORA-01113: file 30 needs media recovery ORA-01110: data file 30: '/u01/app/QPDEV/SAMPLE11.dbf' SQL> recover datafile '/u01/app/QPDEV/SAMPLE11.dbf'; Media recovery complete. SQL> ALTER TABLESPACE s...

ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener When you start or stop your oracle service using dbstart / dbshut scripts on Unix/Linux system and you may get the error ORACLE_HOME_LISTNER is not SET , unable to auto-stop Oracle Net Listener error. [oracle@linux1bin]$ echo $ORACLE_SID qptest [oracle@linux1 bin]$ . dbshut ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener Usage: -bash ORACLE_HOME Then you need to edit the “ dbstart ” & “ dbshut ” file, the should be located at $ORACLE_HOME\bin Go through the file and find line ORACLE_HOME_LISTNER=$1 and change to ORACLE_HOME_LISTNER=$ORACLE_HOME

When you start or stop you oracle service in Unix/Linux system and you get the prompt of ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener error. [oracle@linux1bin]$ echo $ORACLE_SID qptest [oracle@linux1 bin]$ . dbshut ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener Usage: -bash ORACLE_HOME Then you need to edit the “dbstart” & “dbshut” file, the should be located at $ORACLE_HOME\bin Go through the file and find line ORACLE_HOME_LISTNER=$1 and change to ORACLE_HOME_LISTNER=$ORACLE_HOME

SQL to find the  LAST_DDL_TIME change time of an Oracle object in the database. -- Get the name, type, date of change of the DDL of a user object. select OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME from dba_objects where owner not in ('SYS','SYSTEM');

alter table table_name set unused  There may be a situation where you want to drop a column that has a huge data 10 Million rows .It will take lot of time to drop that column and the worst part is that Oracle will place a lock on that tables until With the " alter table set unused " command you can make that column invisible to users. at a later point of time. when you set the column to unused it will be stored in sys as unused. MARKING UNUSED COLUMN sql>  desc abc_test Name       Null Type          ---------- ---- ------------  NAME            VARCHAR2(20)  TOTAL_ROWS      NUMBER                                                                                   ...

There may be a situation where you want to drop a column that has a huge data 10 Million rows .It will take lot of time to drop that column and the worst part is that Oracle will place a lock on that tables until With the " alter table set unused " command you can make that column invisible to users. at a later point of time. when you set the column to unused it will be stored in sys as unused. MARKING UNUSED COLUMN sql>  desc abc_test Name       Null Type          ---------- ---- ------------  NAME            VARCHAR2(20)  TOTAL_ROWS      NUMBER                                                                                           ...

Sample for adding scheduler e-mail notification     Connected to SQL*PLUS using a privileged user.Using the set_scheduler_attribute procedure we have set the email_sender attribute to the SMTP server IP address, and specified the port to 25: SQL> connect / as sysdba Connected. SQL> exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('email_server','10.155.252.333:25'); PL/SQL procedure successfully completed.         where:              host is the host name or IP address of the SMTP server.             port is the TCP port on which the SMTP server listens. If not specified, the default port of 25 is used.         If this attribute is not specified, set to NULL, or set to an invalid SMTP server address, the Scheduler cannot send job state e-mail notifications. SMTP servers that require secure...