World of Oracle DBA

1)Only appropriate privileged user can do delete operation on SYS.AUD$ table. The user must have either of the following privileges. -SYS user. -DELETE ANY TABLE system privilege. (If O7_DICTIONARY_ACCESSIBILITY=TRUE) -A user to whom SYS has granted the object privilege DELETE on SYS.AUD$ table. 2)Before deleting any rows you may want to archive the table. You can achive this by creating a table from SYS.AUD$ and export that. Don't export SYS.AUD$ directly. SQL>CREATE TABLE AUDIT_RECORD TABLESPACE users as select * from SYS.AUD$; Now export the table as, SQL> host exp tables=AUDIT_RECORD file=audit_record.dmp 3)To delete all records from audit trail table SYS.AUD$ issue, SQL>DELETE FROM SYS.AUD$; To delete all records of particular audited table from the audit trail issue, SQL>DELETE FROM sys.aud$ WHERE obj$name='&table_nmae'; But deleting in this way will not reduce size on the system tablespace or aud$ table. In order to reduce size follow section 4. 4)Tru...

You may want to reorganize your auditing table if you optionally delete records from it regularly. In the following steps it is described. 1)Enable restricted session. In order to ensure consistency in the auditing table temporary disable auditing activity. You can do this by opening database with STARTUP RESTRICT or during open make the system as restricted session. SQL> connect / as sysdba Connected. SQL> alter system enable restricted session; System altered. Check if sessions are still connected by, SQL> select sid, serial#, username from v$session; If necessary kill these sessions with, SQL> alter system kill session 'sid , serial#'; 2)Copy SYS.AUD$ table. SQL>CREATE TABLE audit_record TABLESPACE USERS as select * from SYS.AUD$; You now can take a dump of audit_record. 3)Truncate SYS.AUD$ table. SQL> truncate table sys.aud$; Table truncated. SQL> select count(*) from SYS.AUD$; COUNT(*) -------- 0 4)Copy the rows ...

As the table size grows and full table scans happens more frequently then there is no alternative than partitioning in oracle. Partition greatly enhance performance of a query. I will start my partitioning system simply with an example. In my database the following query took 30~35 seconds to complete. After partitioning the performance increased amazingly and it then took only 1 second. My query was, SELECT DISTINCT fs.type, fs.id , fs.pid, fs.cid , fs.cr_id, fs.created_date FROM summary fs where fs.id in (select fa.id from forms fa where fa.sar in (select la.sar from login la where la.login_id=1 and fa.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') and fs.created_date BETWEEN to_date('1-MAY-2008') AND to_date('21-SEP-2008') ) )and mode=0; After seeing above query I decide to make range partition on column created_date both in summary table and forms table. Below is the list of procedur...

We know with the ALTER TABLE .. MOVE clause we can relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment. If you want to make no other changes to the table other than rebuilding it then your statement is simply, SQL>ALTER TABLE table_name MOVE; Or if you want to move it to another tablespace then specify, SQL>ALTER TABLE table_name MOVE TABLESPACE tablespace_name; With this statement it does not affect any of the lob segments associated with the lob columns in the table. If you want to move only lob segment to a new tablespace then your command will be, ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name); Along with the log segment you can also move the table as well as storage attribute of table and log by following query, ALTER TABLE table_name MOVE TABLESPACE new_tablespace STORAGE(new_storage) LOB (lobcol) STORE AS (TABLESPACE new_tablespace STORAGE (new_storage)); If you want to move all t...

Catalog Backup Whenever we take any backup through RMAN, in the repository information of the backup is recorded. The RMAN respository can be either controlfile or recovery catalog. However if I take a backup through OS command then RMAN does not aware of that and hence recorded are not reflected in the repository. This is also true whenever we create a new controlfile or a backup taken by RMAN is transferred to another place using OS command then controlfile/recovery catalog does not know about the prior backups of the database. So in order to restore database with a new created controlfile we need to inform RMAN about the backups taken before so that it can pick one to restore. This task can be done by catalog command in RMAN. With catalog command it can -Add information of backup pieces and image copies in the repository that are on disk. -Record a datafile copy as a level 0 incremental backup in the RMAN repository. -Record of a datafile copy that was taken by OS. But CATALOG comma...

Suppose I have done some wrong-edit inside spfile using ALTER SYSTEM SET ... SCOPE=SPFILE from database. Now the next startup process of my database will fail with oracle errors. The conventional approach of solution to this problem is, 1)Connect to SQL*plus as sysdba 2)Create pfile from the spfile. 3)Edit the pfile and correct the parameter settings. 4)Create spfile from the edited correct pfile. 5)Startup your database with the spfile. But we can omit these steps easily by just creating a pfile and invoke the name of the spfile inside the pfile. After invoking spfile parameter inside pfile in the next line specify the name of the parameter which is cause problem of startup. In this case we gave multiple values of the parameter and oracle will use the last one. So override the invalid parameter value with the valid one and thus will startup the create. After startup we can set valid value in the spfile. The following example will make you a clear idea. Step 1: I have set background_du...

You can get your portlist information of enterprise manger or isqlplus or others web services in the location of $ORACLE_HOME/install/portlist.ini. On my system the output is, bash-3.00$ cat /oracle/app/oracle/product/10.2.0/db_1/install/portlist.ini iSQL*Plus HTTP port number =5560 Enterprise Manager Console HTTP Port (DBASE) = 1158 Enterprise Manager Agent Port (DBASE) = 3938 Enterprise Manager Console HTTP Port (shaikdba) = 5500 Enterprise Manager Agent Port (shaikdba) = 1830 Enterprise Manager Console HTTP Port (shaik) = 5501 Enterprise Manager Agent Port (shaik) = 1831 But note that the ports that are listed in portlist.ini file were assigned during the installation. This file is not updated if port numbers are changed after the installation.

In database you may discover that your temporary tablespace is deleted from OS or it might got corrupted. In order to get it back you might think about recover it. The recovery process is simply recover the temporary file from backup and roll the file forward using archived log files if you are in archivelog mode. Another solution is simply drop the temporary tablespace and then create a new one and assign new one to the database users. In order to do that follow the steps here. 1)Find out the temporary datafiles. SQL> col file_name format a50 SQL> set linesize 200 SQL> select file_name,file_id, tablespace_name from dba_temp_files; FILE_NAME FILE_ID TABLESPACE_NAME -------------------------------------------------- ---------- ------------------------------ /oradata2/temp2.dbf 1 TEMP2 /oradata2/temp.dbf 2 TEMP /oradata2/temp3.dbf ...

External table to load data rather than Sql*Loader because in external table we can load data parallel. In this post I will show how we can generate external table easily with help of sql loader. With this method we can easily get rid of writing too many codes for external tables. Suppose my data file is 3.txt which is under C drive on my windows machine and contains a single record, 1, momin The next step is to create a control file for SQL loader. I named it control.ctl made it as follows, LOAD DATA infile 'c:\3.txt' into table std fields terminated by ',' (id , name) Now invoke the Sql loader as below. Note that addition word that need is external_table=generate_only. With this keyword only externa table will be created but will not load any data actually on table. sqlldr user_id/password control=c:\control.ctl.txt log=5.txt external_table=generate_only After invoking I open the log file and important contents from logfile is, CREATE DIRECTORY statements needed for f...

External table to load data rather than Sql*Loader because in external table we can load data parallel. In this post I will show how we can generate external table easily with help of sql loader. With this method we can easily get rid of writing too many codes for external tables. Suppose my data file is 3.txt which is under C drive on my windows machine and contains a single record, 1, momin The next step is to create a control file for SQL loader. I named it control.ctl made it as follows, LOAD DATA infile 'c:\3.txt' into table std fields terminated by ',' (id , name) Now invoke the Sql loader as below. Note that addition word that need is external_table=generate_only. With this keyword only externa table will be created but will not load any data actually on table. sqlldr user_id/password control=c:\control.ctl.txt log=5.txt external_table=generate_only After invoking I open the log file and important contents from logfile is, CREATE DIRECTORY statements needed for f...

If you decide to delete all objects of a certain schema then better drop the schema. It will save timing as well as easy process. However inside a schema a user can manually drop all his objects. Below is the procedure about how a user can drop all object under his schema. 1)Connect to database as the user that you want to drop all his objects. Suppose I want to drop test schema objects. $sqlplus test/test 2)Script for dropping all objects. declare cursor c_get_objects is select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name from user_objects where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','PROCEDURE','FUNCTION', 'SYNONYM', 'MATERIALIZED VIEW') order by object_type; cursor c_get_objects_type is select object_type, '"'||object_name||'"' obj_name from user_objects where object_type in ('TYPE...

In this I will show what can happen if Oracle is unable to write to the trace/alert.log to bdump or cdump & udump directories. It can happen if you loss your background_dump_dest or user_dump_dest or core_dump_dest ormay the the partition containing bdump or udump folder is full. The answer is if oracle is unable to write to the trace/alert.log to bdump & udump directories then the oracle behavior depends on which process is attempting to write to alert.log/trace/coredump file. Based on the process the instance may or may not crash immediately. If a foreground process corresponding to user process wants to write, but is unable to do so, the process may hang/terminate but there will not be any impact on database. But if the background process wants to write it may hang and eventually crash the instance if that background process terminates. In the following section I demonstrate this behavior on 10.2g 1.Start database with spfile Create one bdump directory SQL> !mkdir /oradat...

One thing you need to remember before read this post is there is no easy or shortcut way to make a read only user of another schema. Like grant select on username to another_username- there is no such single command like this. However you may have several alternatives to make read only user for a schema. I will demonstrate the procedure with examples to make a read only user for a schema. In the example I will make devels user which will have read only permission on prod schema. Let's start by creating PROD user. SQL> CREATE USER PROD IDENTIFIED BY P; User created. SQL> GRANT DBA TO PROD; Grant succeeded. SQL> CONN PROD/P; Connected. SQL> CREATE TABLE PROD_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER); Table created. SQL> INSERT INTO PROD_TAB1 VALUES(1,2); 1 row created. SQL> CREATE TABLE PROD_TAB2(DATE_COL DATE); Table created. SQL> CREATE OR REPLACE TRIGGER PROD_TAB2_T AFTER INSERT ON PROD_TAB1 BEGIN INSERT INTO PROD_TAB2 VALUES(SYSDATE); END; / Trigger created. SQL...

DBA wants that in the database certain user will not be able structural change. That is no DDL operation can be performed by the user. In that case DBA can achieve his goal simply by making a trigger on the schema. Suppose we want user faruk will not be able to perform any DDL. Then create trigger as below. SQL> conn shaik/abdul Connected. SQL> create table before_trigger(a number); Table created. SQL>conn system/manager Connected. SQL> CREATE OR REPLACE 2 TRIGGER BEFORE_DDL_SHAIK 3 BEFORE DDL 4 ON SHAIK.SCHEMA 5 BEGIN 6 RAISE_APPLICATION_ERROR(-30900,'DDL Operation is not Permitted.' ); 7 END; 8 / Trigger created. SQL> conn shaik/abdul Connected. SQL> create table after_trigger(a number); create table after_trigger(a number) * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-21000: error number argument to raise_application_error of -30900 is out of range ORA-06512: at line 2

If someone ask for top sql they from enterprise manager he can find some expensive queries. But the result may vary based on the metrics or criteria of expensive. Measurement may vary on, a) Logical IO's per execution? b) Physical IO's per execution? c) Cpu usage? d) Based on number of parse calls? e) Elapsed time used? f) Number of executions? g) Size consumed in shared pool? h) Number of child versions found? i) Based on Wait time? However from DBA_HIST_SQLSTAT we can get a complete picture of historical SQL statistics. AWR does a pretty job. Between two snapshot it displays the top sql statements in the enterprise manager. We can also see it from sql queries. In fact Enterprise Manager displays information of SQL text from DBA_HIST_SQLTEXT view which captures information from V$SQL and is used with the DBA_HIST_SQLSTAT view. Along with the DBA_HIST_SQLSTAT and DBA_HIST_SQLSTAT we can use the view DBA_HIST_SNAPSHOT in order to specify the range of snapshots in between analysi...

We know in a UNIX system with tail -f we can monitor alert log file to see how alert log file is populated. For example the latest happenings in alert log file can be seen by, $tail -f background_dump_dest location/alert_$ORACLE_SID.ora But in windows there is not tail command. We can monitor the alert log within oracle itself regardless of platform. This can be archived by following steps. 1)Create an External Table to read the alert log. column a_log new_value ALOG noprint column value new_value bkgd_dmp noprint select 'alert_'||instance_name||'.log' a_log from v$instance; select value from v$parameter where name = 'background_dump_dest'; create or replace directory data_dir as '&&bkgd_dmp'; CREATE TABLE alert_t ( text_line varchar2(255) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir ACCESS PARAMETERS ( records delimited by newline fields REJECT ROWS WITH ALL NULL FIELDS ) LOCATION...

Schedule or Automate Backup is a needed thing almost in all environment. We can do automate or scheduling tasks through two ways, one is DBMS_SCHEDULER packager which will be discusses in another topic and another is OS scheduler. If you think about OS scheduler then on unix box use crontab and on windows box use scheduling jobs. In this topic I have shown how we can take automate backup through crontab tool. To avoid error Verify that ORACLE_HOME is set properly error like as below Message file RMAN.msb not found Verify that ORACLE_HOME is set properly set proper ORACLE_HOME and ORACLE_SID in your backup file. So I created my backup_job.sh as below. bash-3.00$ vi backup_job.sh #!/bin/bash export ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1 export ORACLE_SID=dbase /oracle/app/oracle/product/10.2.0/db_1/bin/rman target sys/a@moon:1522/dbase cmdfile=/oradata1/ backup/backup_job2.sh If you don't set $ORACLE_HOME and don't give full path of rman then possibly in the output fi...

With the command netstat -i we can monitor the traffic in the network nodes. The sample output of netstat -i is below. # netstat -i Kernel Interface table Iface MTU Met RX-OK RX-ERR RX-DRP RX-OVR TX-OK TX-ERR TX-DRP TX-OVR Flg eth0 1500 0 226707 0 0 0 68280 0 0 0 BMRU lo 16436 0 13 0 0 0 13 0 0 0 LRU vmnet 1500 0 0 0 0 0 6 0 0 0 BMRU We can get the the elaboration of the preceding terms below. * Name - The type of the Ethernet interface * Mtu - The maximum size of the packets that are transmitted on the network. * Net/Dest - The subnet address * Address - The name of the system * Ipkts - The number of packets received * Ierrs - The number of received packets that have errors * Opkts - The number of packets sent * Oerrs - The number of sent packets that have errors * Collis - The number of output packets that result in coll...

Kernel Parameters, Recommended Values to install Oracle and Associated Files 1)semmsl semmns semopm semmni: Corresponding recommended values to install oracle are 250 32000 100 128 or over and associated the default settings of these kernel parameter are in file /proc/sys/kernel/sem 2)shmmax: Recommended value to install oracle is the half the size of physical memory (in bytes) or over. The default setting of this kernel parameter is in /proc/sys/kernel/shmmax 3)shmmni: Recommended value to install oracle is 4096 or over. The default setting of this kernel parameter is in /proc/sys/kernel/shmmni 4)shmall: Recommended value to install oracle is 2097152 or over. The default setting of this kernel parameter is in /proc/sys/kernel/shmall 5)ip_local_port_range: Recommended value to install oracle is Minimum: 1024 and Maximum: 65000 or over. The default setting of this kernel parameter is in /proc/sys/net/ipv4/ip_local_port_range 6)rmem_default: Recommended value to install oracle is 1...

The parameter SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is set to a value greater than zero then DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE and JAVA_POOL_SIZE are automatically sized. are automatic sized components. On the other hand, LOG_BUFFER, BUFFER_POOL_KEEP, BUFFER_POOL_RECYCLE, DB_nK_CACHE_SIZE other than DB_CACHE_SIZE, STREAMS_POOL_SIZE , Fixed SGA and other internal components are manually sized components. However setting the value to manually sized components automatically reduce the value from SGA_TARGET if SGA_TARGET is set to value greater than zero. When SGA_TARGET>0 then automatic sized components of SGA are automatically allocated and this is called Automatic Shared Memory Management. Both the automatic and manually sized components of SGA are dynamic components because they dynamically can be changed. You can see them from V$SGA_DYNAMIC_COMPONENTS.