otterhackerxyz

[otterhackerxyz]

How to fix undo block corruption (UNDOTBS) of database

One of users written today that he got the undo block corruption while he is trying to start the database.  The reason he explain that due to power fluctuation the database is abnormally shutdown. When he checks the alert log file then finds the following error:ORA-01578: ORACLE data block corrupted (file # 8, block # 39) ORA-01110: data file 8: 'E:ORACLEPRODUCT10.1.0ORADATASAD1UNDOTBS01.DBF'Error 1578 happened during db open, shutting down database USER: terminating instance due to error 1578 Errors in file E:oracleproduct10.1.0adminsad1bdumpsad1_arc3_1400.trc: The Primarily you have to check the alert log file for any type of error or corruption or you can use the utility dbv (database Verify utility) which provides a mechanism to validate the structure of oracle data files at the operating system level. You can use it on regular basis to inspect data files for sings of corruption but you can use this utility only with datafiles not with control or redolog file.DBVERIFY - Verification starting: FILE = E:ORACLEPRODUCT10.1.0ORADATASAD1UNDOTBS01.DBFDBV-00200: Block, dba 887245421, already marked corruptedDBVERIFY - Verification completeTotal Pages Examined         : 104340Total Pages Processed (Data) : 0Total Pages Failing   (Data) : 0Total Pages Processed (Index): 0Total Pages Failing   (Index): 0Total Pages Processed (Other): 101216Total Pages Processed (Seg)  : 0Total Pages Failing   (Seg)  : 0Total Pages Empty            : 3124Total Pages Marked Corrupt   : 1Total Pages Influx           : 0Highest block SCN            : 2144459844 (15.24)Step1: If you have Cold backupConnect with sys as sysdbaShutdown immediate;Startup mount;Restore datafile 8;Recover datafile 8;Alter database open;Step2: If you have RMAN backuprman target sys/oracle@sad1.worldblockrecover datafile 8 block 22;Step3: If you do not have backupCreate another tablespaceCreate or edit pfile and change tablespace as:Create pfile=’d:sad_pfile.sql’ from spfile’;undo_management=manualundo_tablespace=UNDOTBS2 Startup Nomount pfile='d:sad_pfile.sql'; Startup mount; alter database open; Create spfile from 

[otterhackerxyz]

When your boss is Wrong: Should you speak up or Shutup?

In every organization generally intelligent people are bound to comment, and intelligent superiors will want to hear all perspectives but in practice, politics come into play, and these things drive in opposite direction. So it is better to questioning a manager's plan or pointing out a miss-calculation worth behind him. Try to speak in specific situation to alarm him, whenever needed.Below are some ideas or tips (when, where and how to speak up):If your manager is ready to listen every aspects either professional or personal then try to speaks up the things which is wrongly pointing you or you are not responsible for that, check the things whether or not identifying these things would bring potential benefits. If nothing will change even you speak up then there is no point complaining about the things that can't be changed but in circumstances where action now can prevent consequences later, the momentary uneasiness of drawing attention to a possible mistake is often far preferable to the damage of letting it slide.May be your boss's thinking might be in-correct because he is missing some of the information that could away his view point and decision. If you have knowledge of such information either formally or technically gathered through the unofficial communication channels within your organization, it is better to share this information privately with your boss either immediate or later.If you are not speaking up that means you are giving the boss the wrong impression because silence can cause harm when the boss interprets it as a signal of agreement. Sometimes if you are not speaking up the Boss considering that you have no intention of actually following his plan.But If you decide to say something, lower the potential for embracement and  defensiveness by holding the conversation in private, remember that “you are wrong” it is difficult for any one to hear, so focus on the actual issue rather than on the satisfaction of being correct.Some of the technical point of view while going to speak up:§

[otterhackerxyz]

DBA interview Question and Answer part 23

What is basic difference between V$ view to GV$ or V$ and V_$ view?The V_$ (V$ is the public synonym for V_$ views) view are called dynamic performance views. They are continuously updated while a database is open in use and their contents related primary to performance.Select object_type  from '%SESSION' 'V%';OWNER OBJECT_NAME    OBJECT_TYPE-----  ----------- -------------SYS    V_$HS_SESSION VIEWSYS    V_$LOGMNR_SESSION    VIEWSYS    V_$PX_SESSION VIEWSYS    V_$SESSION    VIEWWhere as GV$ views are called Global dynamic performance view and retrieve information about all started instance accessing one RAC database in contrast with dynamic performance views which retrieves information about local instance only. The GV$ views having the additional column INST_ID which indicates the instance in RAC environment.GV$ views use a special form of parallel execution. The parallel execution co-ordinator runs on the instance that the client connects to and one slave is allocated in each instance to query the underlying V$ view for that instance.What is the Purpose of default Tablespace in oracle database?Each user should have a default tablespace. When a user creates a schema objects and specifies no tablespace to contain it, oracle database stores the object in default user tablespace.The default setting for default tablespace of all users is the SYSTEM tablespace. If a user likely to create any type of objects then you should specify and assign the user a default tablespace. Note: Using the tablespace other than SYSTEM reduces contention between data dictionary objects and the user objects for the same data files. Thus it is not advisable for user data to be stored in the SYSTEM tablesapce.SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='EDSS';SQL> Alter user EDSS default tablespace XYZ;SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='EDSS';Once you change the tablespace for a user the previous/existing objects stay the same,I suppose that you never specified a tablespace when you created the objects and let to use the default tablespace from the user, the objects stay stored in the previous tablespace(tablespace A) and new objects will be created in the new default tablespace (tablespace B). Like in the example above, the objects for EDSS stay in the ORAJDA_DB tablespace and any new object will be stored in the ORAJDA_DB1 tablespace.What is Identity Columns Feature in oracle 12c?Before Oracle 12c there was no direct equivalent of the AutoNumberor Identityfunctionality, when needed it will implemented using a combination of sequences and triggers. The oracle 12c database introduces the ability to define an identity clause for a table column defined using a numeric type. Using ALWAYS keyword will force the use of the identity.GENERATED ]AS IDENTITY Using BY DEFAULT allows you to use the identity if the column isn't referenced in the insert statement.Using BY DEFAULT ON NULL allows the identity to be used even when the identity column is referenced and NULL value is specified.How to find Truncated Table user information?If you have already configure the data mining concept with your database then there is nothing to do you can query with v$logmnr_contents view and find the list, otherwise you need to do some more step to configure it first with your database.Why used Materialized view instead of Table?Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.How does Session communicate with the server process?Server processes executes SQL received from user processes.Which SGA memory structure cannot re-size dynamically after instance startup?Log BufferWhich Activity will generate less UNDO data?InsertWhat happens when a user issue a COMMIT?The LGWR flushes the log buffer to the online redo log.When the SMON processes perform ICR?Only at the time of startup after abort shutdown.What is the purpose of synonym in oracle?Synonym permits application to function without modification regardless of which user owns table or view or regardless of which database holds the table or view. It masks the real name and owner of an object and provides location transparency for tables, views or program units of a remote database.CREATE SYNONYM pay_payment_master FOR HRMS.pay_payment_master;CREATE PUBLIC SYNONYM pay_payment_master FOR HRMS.pay_payment_master@sadhan.world;How many memory layers are in the shared pool?The shared pool of SGA having three layers: Library cache which contains parsed sql statement, cursor information, execution plan etc; dictionary cache contains cache user account information, privilege information, datafiles, segments and extent information; buffer for parallel execution messages and control structure.What is the cache hit ratio, what impact does it have on performance?It calculates how often a requested block has been found in the buffer cache without requiring disk space. This ratio is computed using view V$SYSSTAT. The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE.select From in 'physical reads');The cache-hit ratio can be calculated as follows: Hit ratio = 1 – (physical reads / (db block gets + consistent gets)) If the cache-hit ratio goes below 90% then:  increase the initialization parameter DB_CACHE_SIZE.Which environment variables are critical to run OUI?ORACLE_BASE; ORACLE_HOME; ORACLE_SIDWhat is Cluster verification utility in RAC env.The cluster verification utility (CVU) is a validation tools that you can use to check all the important component that need to verified at different stage of deployment in a RAC environment.How to identify the voting disk in RAC env. and why it is always in odd number?As we know every node are interconnected with each other and pinging voting disk in cluster to check whether they are alive. If voting disks are in even count then both nodes are survival node and it is created multiple brains in same cluster. If it is odd number in that case only one node ping greater count of voting disk and cluster can be saved from multiple brain syndrome. You can identify voting disk by using the below command line:#crsctl query css votediskWhat are the components of physical database structure? What is the use of control files?Oracle database consists of three main categories of files: one or more datafiles, two or more redo log files; one or more control files.When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.What is difference between database Refreshing and Cloning?DB refreshing means the data in the target environment has been synchronized with a copy of production. This can be done by restoring with a backup of production database where as cloning means that an identical copy of production has been taken and restore to the target environment.When we need to Clone or Refresh the database? There are a couple of scenarios when cloning should be performed: 1. Creating a new environment with the same or different DBNAME. 2. Sometimes we need to apply patches or other major configuration changes thus a copy of environment is needed to test the effect of this change.3. Normally in software development environment before any major development efforts take place, it is always good to re-clone dev, test environments to keep environment sync. The refreshment is needed only when you sure that the environment are already sync and you need to apply only change of latest data.What is OERR utility?The OERR (Oracle Error) utility is provided only with Oracle databases on  UNIX  platforms.  OERR is not an executable, but instead, a shell script that retrieves messages from installed message files. OERR is an Oracle utility that extracts error messages with suggested actions from the standard Oracle message files. This utility is very useful as it can extract OS-specific errors that are not in the generic Error Messages and Codes Manual.What do you mean by logfile mirroring?The Process of having copy of redolog file is called mirroring. It is done by creating group by log file together. This ensures that LGWR automatically writes them to all the member of the current online redo log group. In case a group fails the database automatically switch over the next group. It diminishes the performance.What is the use of large pool? Which case you need to use the large pool?You need to set large pool if you are using multi thread server and RMAN backup. It prevents RMAN and MTS server from competing with other subsystem for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled then oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.What will be your first steps if you get the message Application is running slow?Gather the statistics (statspack, AWR) report to find TOP 5 wait event or run a Top command in Linux to see CPU usage. Later run VMSTAT, SAR and PRSTAT command to get more information on CPU and Memory usage and possible blocking.If poor written statements then run EXPLAIN PLAN on these statements and see whether new index or use of HINT brings the cost of SQL down.How do you add more or subsequent block size specification? Re-create the CONTROLFILE to specify the new BLOCK SIZE for specific data files or Take the database OFFLINE and bring back online with a new BLOCK SIZE specification.

[otterhackerxyz]

How to Convert NetTotal Amount in words from digit in oracle Report

Use the Summary column to find the sum of the details report amount then later use the formula column to convert this NetTotal amount into words.Create Summary Column CS_1Number 14,2Function: SumSource : Amount (base table column)Reset at: Block_nameThrough this way you are able to find the sum of the details column Amount.Now to convert Amount in words from digit, Create a formula column function CF_AMOUNT_IN_WORDSFormula return Char is vRiyals NUMBER; VHalala NUMBER; begin VRIYALS := TRUNC(:cs_1); VHalala := TRUNC((:CS_1 - VRIYALS)*100); IF VRIYALS > 0 THEN IF VHALALA > 0 THEN RETURN('SAR '||NumberInWords(VRIYALS)|| ' & ' || NumberInWords(VHalala)||' Halala Only'); ELSE return('SAR '||NumberInWords(VRIYALS)||' Only'); END IF; ELSE IF VHALALA > 0 THEN RETURN('SAR ' || NumberInWords(VHalala)||' Halala Only'); ELSE return('SAR Zero Only'); END IF; END IF; end;  Then later write Procedure to convert Amount from digit to words in ‘Program unit’ FUNCTION NumberInWords(NumberArg IN NUMBER) RETURN VARCHAR2 IS   TYPE v_arr19 IS VARRAY(19) of VARCHAR2(12);   TYPE v_arr9 IS VARRAY(9) of VARCHAR2(12);   W19 v_arr19 := v_arr19('One','Two','Three','Four','Five','Six','Seven','Eight','Nine','Ten',            'Eleven','Twelve','Thirteen','Fourteen','Fifteen','Sixteen','Seventeen','Eighteen','Nineteen');   W90 v_arr9 := V_ARR9('Ten','Twenty','Thirty','Forty','Fifty','Sixty','Seventy','Eighty','Ninety');   Billions NUMBER;   Millions NUMBER;   Thousands NUMBER;   Hundreds NUMBER;   TENS NUMBER;   VNumberInWords VARCHAR2(300);   vNumberArg NUMBER; BEGIN                 If NumberArg > 999999999999999 Then         return('Number Too High!!!');     End If;     vNumberArg := NumberArg;         VNumberInWords := '';     ------ Billion?      Billions := TRUNC(VNumberArg / 1000000000000);      If Billions > 0 Then         VNumberInWords := NumberInWords(Billions) || 'Billion ';         VNumberArg := MOD(VNumberArg, 1000000000000);      End If;     ------ Million?      Millions := TRUNC(VNumberArg / 1000000);      If Millions > 0 Then         VNumberInWords := VNumberInWords || NumberInWords(Millions) ||'Million ';         VNumberArg := MOD(VNumberArg, 1000000);      End If;     ------ Thousands?      Thousands := TRUNC(VNumberArg / 1000);      If Thousands > 0 Then         VNumberInWords := VNumberInWords || NumberInWords(Thousands) ||'Thousand ';         VNumberArg := MOD(VNumberArg,1000);      End If;     ------ Hundreds?      Hundreds := TRUNC(VNumberArg / 100);      If Hundreds > 0 Then         VNumberInWords := VNumberInWords || NumberInWords(Hundreds) ||'Hundred ';         VNumberArg := MOD(VNumberArg, 100);      End If;     ------ Tens?      tens := TRUNC(VNumberArg / 10);      If tens > 1 Then         If LENGTH(VNumberInWords) IS NULL THEN--VNumberInWords = '' Then             VNumberInWords := W90(tens)||' ';         Else             VNumberInWords := VNumberInWords ||'and ' || W90(tens)||' ';         End If;         VNumberArg := MOD(VNumberArg,10);      End If;      If VNumberArg > 0 Then         If LENGTH(VNumberInWords) IS NULL Then             VNumberInWords := W19(VNumberArg)||' ';         Else             If tens > 1 Then                 VNumberInWords := VNumberInWords || W19(VNumberArg) || ' ';             Else              VNumberInWords := VNumberInWords ||'and ' || W19(VNumberArg) || ' ';             End If;         End If;      End If;     RETURN(vNumberInWords); END NumberInWords;

[otterhackerxyz]

To Extract Value in Oracle Report from exiting another Table

Create one place holder column of the related type value then write the select statement into the formula column.For Example: Display the Supplier_name from Existing another table: GL_MasterItem Name: CP_SUPPLIER_NAMEItem Type: Place holderData type: Char 100 Item Name: CF_CALCItem Type: Formula ColumnData type: Number – 10PL/SQL Formula:function CF_CALCFormula return Number isbeginSELECT UPPER(M_DESC)into   :CP_SUPPLIER_NAMEFROM GL_MASTERWHERE GL_MASTER.VEN_NUMBER = :SUPP_CODE;RETURN 0;end;To Extract Check flag and List item value in Oracle ReportCreate a place holder column to store the value and then write the code into formula column.IF :FMCG_FLAG = 1 THEN      :CP_FMCG := 'X';else      :CP_FMCG := ' ';END IF;IF :NON_FOOD = 1 THEN      :CP_NON_FOOD := 'X';else:CP_NON_FOOD := ' ';END IF; IF :REBATE_DED_MONTHLY = 1 THEN      :CP_REBATE_DEDUCTION := 'MONTHLY';ELSE IF :REBATE_DED_MONTHLY = 2 THEN                        :CP_REBATE_DEDUCTION := 'QUATERLY';ELSE IF :REBATE_DED_MONTHLY = 3 THEN                  :CP_REBATE_DEDUCTION := 'YEARLY';END IF;END IF; END IF;

[otterhackerxyz]

How to prevent table from Altering

If you need to secure your table from any kind of DDL actions then following example will help you to understand actually what happens when locks are disabled on the TABLE.SQL> alter table temp_payroll disable table lock; Table altered.Now no one can drop as well as truncate the table as table locks are disabling. SQL> drop table temp_payroll; drop table temp_payroll ORA-00069: cannot acquire lock — table locks disabled for temp_payroll SQL> truncate table temp_payroll; truncate table temp_payroll ORA-00069: cannot acquire lock — table locks disabled for temp_payroll Also you are not able to modify and drop the column but you are able to add the column.SQL> alter table temp_payroll modify amount Number (10,2); ORA-00069: cannot acquire lock — table locks disabled for temp_payroll SQL> alter table temp_payroll drop column Amount Number (10,2) alter table temp_payroll drop column Amount Number (10,2) ORA-00069: cannot acquire lock — table locks disabled for temp_payroll But you are able to run the DML operation such as insert/update/delete. SQL> delete from temp_payroll; 1 row deleted. SQL> alter table temp_payroll add amount Number (10,2); Table altered. You can easily enable the table lock to perform any kind of DDL operation again.SQL> alter table temp_payroll enable table lock; Table altered.SQL> drop table temp_payroll; Table dropped.Enable table lock allowing DDL operations on the table. All currently executing statements must commit or rollbacks before oracle database enable the table lock. To check the pending transactions:Select * from dba_2pc_pending;Select * from dba_2pc_neighbors;

[otterhackerxyz]

DBA interview Question and Answer Part 22

I have configured the RMAN with Recovery window of 3 days but on my backup destination only one days archive log is visible while 3 days database backup is available there why?I go through the issue by checking the backup details using the list command. I found there is already 3 days database as well as archivelog backup list is available. Also the backup is in Recoverable backup. Thus it is clear due to any reason the backup is not stored on Backup place.Connect rman target database with catalogList backup Summary;List Archivelog All;List Backup Recoverable;When I check the db_recovery_dest_size, it is 5 GB and our flash-recovery area is almost full because of that it will automatically delete archive logs from backup location. When I increase the db_recovery_dest_sizethen it is working fine.If one or all of control file is get corrupted and you are unable to start database then how can you perform recovery?If one of your control file is missing or corrupted then you have two options to recover it either delete corrupted CONTROLFILE manually from the location and copy the available rest of CONTROLFILE and rename it as per the deleted one. You can check the alert.log for exact name and location of the control file. Another option is delete the corrupted CONTROLFILE and remove the location from Pfile/Spfile. After removing said control file from spfile and start your database.In another scenario if all of your CONTROLFILE is get corrupted then you need to restore them using RMAN.As currently none of the CONTROLFILE is mounted so RMAN does not know about the backup or any pre-configured RMAN setting. In order to use the backup we need to pass the DBID (SET DBID=691421794‎) to the RMAN.RMAN>Restore Controlfile from ‘H:oracleBackup C-1239150297-20130418’ You are working as a DBA and usually taking HOTBACKUP every night. But one day around 3.00 PM one table is dropped and that table is very useful then how will you recover that table?If your database is running on oracle 10g version and you already enable the recyclebin configuration then you can easily recover dropped table from user_recyclebin or dba_recyclebin by using flashback feature of oracle 10g.SQL> select object_name,original_name from user_recyclebin;BIN$T0xRBK9YSomiRRmhwn/xPA==$0 PAY_PAYMENT_MASTERSQL> flashback table table2 to before drop;Flashback complete.In that case when no recyclebin is enabled with your database then you need to restore your backup on TEST database and enable time based recovery for applying all archives before drop command execution. For an instance, apply archives up to 2:55 PM here.It is not recommended to perform such recovery on production database directly because it is a huge database will take time.Note: If you are using SYS user to drop any table then user’s object will not go to the recyclebin for SYSTEM tablespace, even you have already set recyclebin parameter ‘true’. And If you database is running on oracle 9i you require in-complete recovery for the same.Sometimes why more archivelog is Generating?There are many reasons such as: if more database changes were performed either using any import/export work or batch jobs or any special task or taking hot backup (For more details why hot backup generating more archive check my separate post).You can check it using enabling log Minor utility.How can I know my require table is available in export dump file or not?You can create index file for export dump file using ‘import with index file’ command. A text file will be generating with all table and index object name with number of rows. You can confirm your require table object from this text file.What is Cache Fusion Technology?Cache fusion provides a service that allows oracle to keep track of which nodes are writing to which block and ensure that two nodes do not updates duplicates copies of the same block. Cache fusion technology can provides more resource and increase concurrency of users internally. Here multiple caches can able to join and act into one global cache. Thus solving the issues like data consistency internally without any impact on the application code or design.Why we should we need to open database using RESETLOGS after finishing incomplete recovery?When we are performing incomplete recovery that means, it is clear we are bringing our database to past time or re-wind period of time. Thus this recovery makes database in prior state of database. The forward sequence of number already available after performing recovery, due to mismatching of this sequence numbers and prior state of database, it needs open database with new sequence number of redo log and archive log.Why export backup is called as logical backup?Export dump file doesn’t backup or contain any physical structure of database such as datafiles, redolog files, pfile and password file etc. Instead of physical structure, export dump contains logical structure of database like definition of tablespace, segment, schema etc. Due to these reason export dump is call logical backup.What are difference between 9i and 10g OEM?In oracle 9i OEM having limited capability or resource compares to oracle 10g grids. There are too many enhancements in 10g OEM over 9i, several tools such as AWR and ADDM has been incorporated and there is SQL Tuning advisor also available.Can we use same target database as catalog DB?The recovery catalog should not reside in the target database because recovery catalog must be protected in the event of loss of the target database.What is difference between CROSSCHECK and VALIDATE command?Validate command is to examine a backup set and report whether it can be restored successfully where as crosscheck command is to verify the status of backup and copies recorded in the RMAN repository against the media such as disk or tape. How do you identify or fix block Corruption in RMAN database?You can use the v$block_corruption view to identify which block is corrupted then use the ‘blockrecover’ command to recover it.SQL>select file# block# from v$database_block_corruption;file# block10 1435RMAN>blockrecover datafile 10 block 1435;What is auxiliary channel in RMAN? When it is required?An auxiliary channel is a link to auxiliary instance. If you do not have automatic channel configured, then before issuing the DUPLICATE command, manually allocate at least one auxiliary channel within the same RUN command.Explain the use of Setting GLOBAL_NAME equal to true?Setting GLOBAL_NAMES indicates how you might connect to the database. This variable is either ‘TRUE’ or ‘FALSE’ and if it is set to ‘TRUE’ which enforces database links to have the same name as the remote database to which they are linking.How can you say your data in database is Valid or secure?If data of the database is validated we can say that our database is secured. There is different way to validate the data:1. Accept only valid data2. Reject bad data.3. Sanitize bad data. Write a query to display all the odd number from table.Select * from (select employee_number, rownum rn from pay_employee_personal_info)where MOD (rn, 2) 0;-or- you can perform the same things through the below function.set serveroutput on; begin for v_c1 in (select num from tab_no) loop if mod(v_c1.num,2) = 1 then dbms_output.put_line(v_c1.num); end if; end loop; end;What is difference between Trim and Truncate?Truncate is a DDL command which delete the contents of a table completely, without affecting the table structures where as Trim is a function which changes the column output in select statement or to remove the blank space from left and right of the string.When to use the option clause "PASSWORD FILE" in the RMAN DUPLICATE command? If you create a duplicate DB not a standby DB, then RMAN does not copy the password file by default. You can specify the PASSWORD FILE option to indicate that RMAN should overwrite the existing password file on the auxiliary instance and if you create a standby DB, then RMAN copies the password file by default to the standby host overwriting the existing password file. What is Oracle Golden Gate?Oracle GoldenGate is oracle’s strategic solution for real time data integration. Oracle GoldenGate captures, filters, routes, verifies, transforms, and delivers transactional data in real-time, across Oracle and heterogeneous environments with very low impact and preserved transaction integrity. The transaction data management provides read consistency, maintaining referential integrity between source and target systems.What is meaning of LGWR SYNC and LGWR ASYNC in log archive destination parameter for standby configuration.When use LGWR with SYNC, it means once network I/O initiated, LGWR has to wait for completion of network I/O before write processing. LGWR with ASYNC means LGWR doesn’t wait to finish network I/O and continuing write processing.What is the truncate command enhancement in Oracle 12c?In the previous release, there was not a direct option available to truncate a master table while child table exist and having records.Now the truncate table with cascade option in 12c truncates the records in master as well as all referenced child table with an enabled ON DELETE constraint.

[otterhackerxyz]

Using 'RETURN_RESULT' WITH Clause in procedure/Function in Oracle 12c

Oracle 12c allows a procedure to return a list of rows by using RETURN_RESULTprocedure in the

[otterhackerxyz]

How to send E-mail from Oracle database

sqlplus / as sysdba @?/rdbms/admin/utlmail.sql @?/rdbms/admin/prvtmail.plb Grant execute on UTL_MAIL to public; ALTER SYSTEM SET smtp_out_server = 'mailhost' scope=both; For Example: BEGIN UTL_MAIL.send(sender => 'shahid.ahmed@al-sadhan.com', recipients => 'you@address.com', subject => 'Test Mail', message => 'Everythings is OK', mime_type => 'text; charset=us-ascii'); END; / Note: You must have an outgoing SMTP server IP to configure sending mail from the database.  Oracle allows you to send mail using procedure may be just message or message from tables. CREATE TABLE EMP ( ENO NUMBER(4), ENAME VARCHAR2(50), BASIC NUMBER(10,2)); CREATE OR REPLACE PROCEDURE SENDMAIL(TOO IN VARCHAR, FROMM IN VARCHAR, SUBJECT IN VARCHAR, BODY IN VARCHAR) AS SMTP_HOST VARCHAR2(50) :='212.12.164.2'; PORT varchar2(2) :='25'; CONN UTL_SMTP.CONNECTION; BEGIN CONN:=UTL_SMTP.OPEN_CONNECTION('212.12.164.2', '25'); UTL_SMTP.HELO(CONN,'212.12.164.2'); UTL_SMTP.MAIL(CONN,'shahid.ahmedt@al-sadhan.com'); UTL_SMTP.RCPT(CONN,'xyz_abc@al-sadhan.com'); UTL_SMTP.DATA(CONN,'Test'); UTL_SMTP.QUIT(CONN); END; / CREATE OR REPLACE TRIGGER EMP_TRIGGER AFTER INSERT ON EMP FOR EACH ROW BEGIN sendmail('shahid.ahmedt@al-sadhan.com','xyz.abc@al-sadhan.com','Test',:new.ename); END; Desc emp; insert into emp values (4, 'ahmed', 400); The Above procedure will send a mail from oracle. When you call this procedure we need to pass To/From Address , Subject and Body of the message. For Example To_address:shahid.ahmedt@al-sadhan.com From_address: xyz.abc@al-sadhan.com Subject and Body of the Message: Test To Test this Procedure i created a temp table Emp with fields eno number(4), ename varchar2(50), basic number(10,2). Our aim is whenever some one insert record into emp table it needs to send an email with the Ename he entered. So we created Trigger after insert record into Emp table Call mail sending procedure with Employee name For More info: If you face any issue related to ORA-29278: SMTP transient error: 421 Service not available: http://www.ora00600.com/scripts/databaseconfig/ORA-29278.html

[otterhackerxyz]

Script: To Find Archivelog Generation daywise Report.

select thread#, to_char(first_time,'YYYY-MM-DD') day, to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22", to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23", count(*) Total from v$log_history where first_time > sysdate - &No_of_Days group by thread#, to_char(first_time,'YYYY-MM-DD') order by 2 ;

[otterhackerxyz]

How to display database define ‘LOGO’ or ‘COMP_NAME’ or ‘CUSTOM_ERROR_MESSAGE’ through Oracle Forms.

Display Database Defined ‘LOGO’Create an image Item: Logo on the form set with required image sizeItem_Type: ImageEnabled: YesImage Format: TIFFImage Depth: OriginalDisplay Quality: HighDatabase Item: NoWrite Form Level Trigger ‘When_New_Form_Instancedeclare      logo_path varchar2(250);beginselect logo into   logo_path from   logo;      Read_Image_File(logo_path, 'ANY', 'logo'); exception      when   no_data_found then             null;  when   others then         show_message(2,2);end;Display System Date and Time on the FormCreate two Item ‘Date’ and ‘Time’ on the Top of the formItem_Name: DateItem_Type: Text_ItemData Type: DateInitial Value: $$DATE$$Format Mask: Dy  DD-MM-YYYY Item_Name: TimeItem_Type: Text_ItemData Type: charInitial Value: $$TIME$$Format Mask: Dy  DD-MM-YYYYDisplay Database Defined ‘COMP_NAME’ Create an Item on Form: COMP_NAMEItem_Type: Display ItemData type = 40 charDatabase item: NOWrite a procedure GET_COMP_NAME on Program UnitPROCEDURE get_comp_name(p_comp in out varchar2) IS  BEGIN  SELECT COMP_ENAME   into   p_comp  FROM   COMP_PROFIL;exception      when   no_data_found then             p_comp := null;  when   others then         show_message(2,2);END;Write a form level trigger ‘When_New_Form_Instance’ to fetch above procedure Get_comp_name(:comp_name);Display Login User’s into form:Create an Item on Form: USERNAMEItem_Type: Display ItemData type = 60 charDatabase item: NOInitial value: :GLOBAL.USERThen simply write into form level trigger ‘When_New_Form_Instance’:block_name.USERNAME := :GLOBAL.USER;Display Custom Error Message:PROCEDURE get_mess(mess_code in number) ISv_message varchar2(70);s_message varchar2(70);BEGIN      s_message := 'message code not defined';    select mes_desc into v_message  from   error_message  where  mes_no  = mess_code;  show_message(1,v_message);-- Error_message is a database table defining all the error_message code and description  raise  form_trigger_failure;EXCEPTION      when   no_data_found then             show_message(1,s_message);             raise form_trigger_failure;  when   form_trigger_failure then         raise form_trigger_failure;  when   others then         show_message(2,s_message);         raise form_trigger_failure;END;PROCEDURE show_message(mess_code in number, mess_desc in varchar2) ISBEGIN      if mess_code = 1 then   message(mess_desc);   message(mess_desc);      elsif mess_code = 2 then   message(sqlerrm);   message(sqlerrm);      end if;      END;

[otterhackerxyz]

How to Create or Modify Master/Details Report

Create two different database tables to store Master and Details data. Please don’t forget to create constraint on table as per the requirement on that table.To Create Master Block click on File – New – Report or (+) sign directly and use to “Build Report Manually”. Then go to the ToolsMenu and choose Data Wizard concept and follow the Next instruction to write the Query for Report.To Create Detail Block follow the same instruction while using Data Wizard change the Query name (Q_2) to (Q_3).SELECT * FROM INVOICE_DETAILSWHERE INVOICE# BETWEEN :FR_INVOICE# AND :TO_INVOICE#AND INV_DATE BETWEEN :FR_IN_DATE AND :TO_IN_DATE;SELECT * FROM INVOICE_HeaderWHERE INVOICE# BETWEEN :FR_INVOICE# AND :TO_INVOICE#AND INV_DATE BETWEEN :FR_IN_DATE AND :TO_IN_DATE; Now you’re Report Editor – Data Model look like as

Now to Create the Relation between two block select ‘data link’ from Report Editor and drag from invoice# of G_invoice_Detailsblock to G_Invoice_Master block invoice#. Repeat the same process for to create other links. Now you’re Report Editor – Data Model look like as:

Now use the Report Wizard to design the report for Master as well as Details block. To modify the report just click on header button

 button then later edit header button

 To modify parent frame design select the ‘flex mode’ to pick the individual items.

Now you can move or change the location, size of the individual item inside the parent frame. 

[otterhackerxyz]

How to Manage Serial# through database Sequence

Create sequence invoice_serial MINVALUE 1 maxvalue 99999999 increment by 1; If you need to modify the sequence to start with particular number Create sequence invoice_serial start with 8 increment by 1 maxvalue 99999999; Now write a PRE_INSERT trigger to access the sequence through form. select invoice_serial.nextval  into :invoice_details.serial# from dual; Note: In the above case database sequence updated and serial is displayed when you save the record. How to Control the sequence through database Table: Create a form as well as database table and sequence from where from where you want to handle the sequence. CREATE TABLE INVOICE_SEQUENCE ( SEQ_COMP NUMBER(1), SEQ_YEAR NUMBER(4), SEQ_TYPE NUMBER(2), SEQ_SEQ NUMBER(10)); PRIMARY KEY: SEQ_COMP, SEQ_YEAR, SEQ_TYPE CREATE SEQUENCE INVOICE_SEQ    MINVALUE 1    MAXVALUE 99999999    START WITH 1    INCREMENT BY 1    CACHE 20; Then create the required form to handle the sequence. Here in below example we have created the ‘INVOICE’ form in which we will handle the invoice# number automatically incremented with each transaction. Then write a PRE-INSERT TRIGGER on block to update/insert the sequence. ---- update the sequence ---- BEGIN       UPDATE INVOICE_SEQUENCE SET  SEQ_SEQ=SEQ_SEQ+1       WHERE SEQ_YEAR=TO_NUMBER(TO_CHAR(:INVOICE_HEADER.INV_DATE,'YYYY'))       AND SEQ_COMP = 1 AND SEQ_TYPE = 0; exception       when   others then       show_message(2,2); END; BEGIN   SELECT MAX(TO_NUMBER(TO_CHAR(:INVOICE_HEADER.INV_DATE, 'YY')))   into   :INVOICE_HEADER.YY   FROM   INVOICE_HEADER; exception       when   others then       show_message(2,2); END; --Get the new sequence---- BEGIN       select SEQ_SEQ       into   :INVOICE_HEADER.INVOICE#       from   INVOICE_SEQUENCE WHERE SEQ_YEAR=TO_NUMBER(TO_CHAR(:INVOICE_HEADER.INV_DATE,'YYYY'))       AND SEQ_COMP = 1 AND SEQ_TYPE = 0;  exception       when   no_data_found then             show_message(1,'Could not retrieve the Serial Number!');             raise form_trigger_failure;       when   others then              show_message(2,2); END; How Manage Form details auto Serial Number: To display Master/Details Form serial number automatically in the form. Just open the property of the item and change item_name: serial# item_type: display item database item: yes Calculation Mode: Formula Formula: :system.trigger_record; or system.cursor_record; -or- you can write the same things into POST_INSERT or WHEN_NEW_RECORD_INSTANCE trigger :invoice_details.serial# = :system.trigger_record; Note: It will display your serial automatically when inserting new record before save.

[otterhackerxyz]

Top-N Queries & Truncate Table Enhancement in Oracle 12c

In fact there is already various method exist to fetch TOP N query results in the previous release. In the oracle 12c retrieving TOP N query simplified and become easier with the new Oracle Database 12c includes support for the ANSI-standard FETCH FIRST/NEXT and OFFSET clauses, together called the row limiting clause. This clause enables you to easily retrieve the first N records from a result set so you can easily retrieve the result set.The row limiting clause is simply added at the end of any SQL statement to fetch a specific set of records:§

[otterhackerxyz]

Identity Columns Feature in Oracle 12c

Before Oracle 12c there was no direct equivalent of the Auto Number or Identity functionality, when needed it will implemented using a combination of sequences and triggers. The oracle 12c database introduces the ability to define an identity clause for a table column defined using a numeric type. This new feature allows you to specify that a column should be automatically populated from a system-created sequence. The Syntax: GENERATED ] AS IDENTITY Using 'ALWAYS' keyword will force the use of the identity. In that case if an insert statement references the identity column, even you specify a NULL value, an error is occurred. CREATE TABLE Tr01 (   empid NUMBER GENERATED ALWAYS AS IDENTITY,  latin_name Varchar2(50)); SQL> DESC Tr01 Name Null? Type ----------------------------------------- -------- ------------------------ EMPID NOT NULL NUMBER lATIN_NAME NUMBER Note that a NOT NULL constraints is automatically created on empid column. A system-generated sequence is automatically created. You can check it from the dba_sequences views. SELECT sequence_owner, sequence_name FROM dba_sequences; SEQUENCE_OWNER SEQUENCE_NAME -------------- ------------- hr ISEQ$$_92214 By default the GENERATED AS IDENTITY clause implicitly includes the ALWAYS keyword. When the ALWAYS keyword is specified it is not possible to explicitly include values for the identity column in INSERT OR UPDATE statements. INSERT INTO Tr01(latin_name)VALUES('JOHN');INSERT INTO Tr01(latin_name)VALUES('JOLLY'); SQL> select * from Tr01;      EMPID LATIN_NAME ---------- --------------------          1 JOHAN          2 JOLLY INSERT INTO Tr01(transaction_id,latin_name) VALUES(10,'KRJNP');               * ERROR at line 1: ORA-32795: cannot insert into a generated always identity column Using BY DEFAULT allows you to use the identity if the column isn't referenced in the insert statement, but if the column is referenced, the specified value will be used in place of the identity. In that case if you try to specify the value NULL then an error occurred, since identity columns are always NOT NULL. CREATE TABLE Tr02 (   empid NUMBER GENERATED BY DEFAULT AS IDENTITY,  latin_name Varchar2(50)); SQL> DESC Tr02 Name Null? Type ----------------------------------------- -------- ------------------------ EMPID NOT NULL NUMBER lATIN_NAME NUMBER INSERT INTO Tr02(latin_name)VALUES('JOHN');INSERT INTO Tr02(empid, latin_name)VALUES(40,'JOLLY'); SQL> select * from Tr02;      EMPID LATIN_NAME ---------- --------------------          1 JOHN         40 JOLLY Using BY DEFAULT ON NULL allows the identity to be used even when the identity column is referenced and NULL value is specified. CREATE TABLE Tr03(EMPID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,  latin_name varchar2(50)); Insert into Tr03 (empid,latin_name) values(NULL,'JOHN'); Insert into Tr03 (empid,latin_name) values(40,'JOLLY'); Insert into Tr03 (latin_name) values('KRJNP'); SQL> select * from Tr03;      EMPID LATIN_NAME ---------- --------------------          1 JOHN         40 JOLLY          2 KRJNP

[otterhackerxyz]

Import Excel Data into Oracle using Oracle form Button

1. Create a button on the form with the name "IMPORT_EXCEL" and write a pl/sql on "WHEN BUTTON PRESSED" trigger.

[otterhackerxyz]

How to Setup Optimal Undo Parameter in Oracle Database

Automatic undo management allows the DBA to specify how long undo information should be retained after commit, which prevent "snapshot too old" errors on long running queries. This is done by setting the UNDO_RETENTION parameter.  The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time. Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy.  All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO. However it is worth to tune the following important parameters 1.  The size of the UNDO tablespace 2.  The UNDO_RETENTION parameter Calculate UNDO_RETENTION  for given UNDO Tablespace: You can choose to allocate a specific size for the UNDO tablespace and then set the UNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter: Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time! To find Actual Undo Size: SELECT SUM(a.bytes) "UNDO_SIZE" FROM v$datafile a, v$tablespace b, dba_tablespaces c Where c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#