#how to refresh materialized view in oracle automatically

[oraclerider]

How to Generate table DDL

in this practice we are going to learn how to generate table DDL, view DDL, Materialized View DDL and user DDL.

Hi, in this practice we are going to learn how to generate table DDL, view DDL, Materialized View DDL and user DDL. You also read below articles: DB LINK DDL GET TABLESPACE DDLGET ROLE DDL Get more oracle scritps How do you get DDL of a table in Oracle? In real time environment some times we need to DDL of an existing table. We can perfo this activity with the help of dbms_metadata.get_ddl…

[siva3155]

300+ TOP SAP HANA Objective Questions and Answers

SAP HANA Multiple Choice Questions :-

1. Which of the following is NOT a replication method for data replication from a source system to SAP Hana? a. ETL based b. Trigger based c. Time based d. Log based Answer: c 2. Which of the following statements are true? a. There are four types of information views: attribute view, analytic view, hierarchy view and calculation view. All these views are non-materialized views. b. An analytic view is used to model data that includes measures. c. Calculated attributes are derived from one or more existing attributes or constants. d. Calculation views can include measures and be used for multidimensional reporting or can contain no measures and used for list-type of reporting Answer: d 3. HANA supports which of the following hierarchies? a. Time Based hierarchies b. Level Hierarchies c. Parent/Child Hierarchies d. Matrix organization Hierarchies Ans: b, c 4. SAP HANA modeler is a graphical data modeling tool which allows you to design analytical models and later analytical privileges, that governs the access to those models. Which of the following represents a logic flow of activities? a. Import source system metadata -> Create Information Models -> Provision Data -> Deploy -> Consume b. Import source system metadata -> Provision Data ->Create Information Models -> Deploy -> Consume c. Import source system metadata -> Provision Data -> Deploy -> Create Information Models -> Consume d. Import source system metadata -> Deploy ->Create Information Models -> Provision Data -> Consume Ans: b 5. The two queries below, which is more efficient? a. Matmoves = SELECT * FROM MSEG FOR EACH matmove in matmoves IF matmove.whichPlant = “I” THEN Plant_text = SELECT plant_text FROM WERKS WHERE id = matmove.plant ELSE Plant_text = SELECT plant_text FROM WERKS_EXT WHERE id=matmove. plant END b. If which_plant = “I” THEN Plant_text = plant_int_text ELSE Plant_text = plant_ext_text Ans: b 6. Which of the following statements are true? a. One table may appear in exactly one schema b. It is possible to grant authorizations on a table level. c. Authorizations granted on a schema are automatically propagated to all objects within the schema Ans: c 7. You have a Web Intelligence report that requires data from SAP HANA filtered for a specific year. Where do you apply the filter for optimal performance? A. In the universe B. In the Web Intelligence report C. In the analytic view D. In the calculation view using an attribute value variable Ans: C 8. What are the key benefits of using SAP BusinessObjects Explorer with SAP HANA? (Choose three) A. You do not need to create your own queries to access information. B. You can easily create your own queries to access information. C. You can access information for data discovery intuitively. D. You can execute ad-hoc business analytics quickly. E. You can easily create an IDT business layer. Ans: A,C,D 9. Which of the following properties of a derived attribute view can you modify? A. Description B. Description mapping C. Calculated attributes D. Filters Ans: A 10. How can you evaluate the speed of a calculation within the database? (Choose two) A. Use the Data Preview function in the SAP HANA studio. B. Execute a Web Intelligence report and check the duration of the previous refresh. C. Execute an information space using SAP Business Objects Explorer. D. Write and execute an appropriate SQL statement. Ans: A,D

SAP HANA MCQs 11. If you want to create or drop tables in a schema, which privilege type do you need? A. SYSTEM B. ANALYTIC C. PACKAGE D. SQL Ans: D 13. Which SAP BusinessObjects reporting tool should you use to answer ad-hoc questions and interact with shared information? A. Crystal Reports B. Dashboards C. Explorer D. WebIntelligence Ans: D 14. Which new connection type is available as of SAP BusinessObjects Data Services 4.0? A. Operational Data Provider B. BAPI function calls C. IDOCs D. Read table via ABAP data flows Ans: A 15. Which of the following tasks must you perform before you build a business layer within the SAP BusinessObjects Information Design Tool (IDT) on top of SAP HANA? A. Publish the connection to CMS. B. Build a query to test the connection. C. Build a relational connection to SAP HANA. D. Build an OLAP connection to SAP HANA. E. Build a data foundation. Ans: A,C,E 16. Which delivered role is mandatory for a system administrator in SAP HANA? A. SYS_ADMIN B. CONTENT_ADMIN C. SAP_ALL D. IC MODELER Ans: B 17. What are benefits of using a business layer on top of SAP HANA? (Choose two) A. An extra layer of granular security B. Improved database calculation speed C. Fast throughput via BICS connectivity D. Users can use business names instead of technical names Ans: A,D 18. SAP HANA database is ____ A. A sort of SAP GUI used to access SAP servers. B. A general purpose ANSI compliant in-memory database. C. Used to store mountains of OLAP data. D. A hardware product made by hardware manufacturers. Ans: B 19. What is SAP HANA Appliance? A. It is a combination of software from SAP and certified hardware from vendors like Dell, IBM, Cisco, Fujitsu. B. It is a piece of hardware from vendors like Dell, IBM, Cisco, Fujitsu. C. Is solely produced by SAP AG only. D. It is a combination of BI, BOBJ and HANA. Ans: A 20. What BI standards does HANA support? (3 correct answers) A. ODBC B. JDBC C. MDX D. MIME Ans: A, B, C 21. Can SAP HANA database be used by any application, other than SAP? A. Yes B. No Ans: B 22. SAP HANA server can be accessed by _______ A. SAP GUI B. Any web browser C. Only Internet Explorer D. HANA Studio Ans: D 23. What method is used to extract tables from ECC server in real time? A. SLT based table data provisioning B. Information Design Tool (IDT) C. BI R3 extraction services D. None of the above Ans: A 24. Which one of the following are components of SAP HANA Studio? A. Navigator B. Job Log C. Where-Used List D. All of the above Ans: D 25. How many perspectives are available in SAP HANA Studio (SPS03)? A. 4 B. 8 C. 9 D. 10 Ans: B SAP HANA Objective type Questions with Answers 26. Which perspective is used to create joins? A. Administration & Modeling B. Debug & Modelling C. Modeler D. All of the above Ans: C 27. The SAP HANA system can be created within _____ A. Modeler B. Resource C. Navigator D. SAP GUI Ans: C 28. Can we create multiple connections to the same SAP HANA system? A. Yes B. No Ans: A 29. The login user who has maximum authorizations in a SAP HANA database is ______ A. SYS B. SYSTEM C. Administrator D. Root Ans: B 30. Can the attribute views be created with 1 table? A. Yes B. No Ans: A 31. By default, the maximum number of rows displayed from a table is ______ A. 1000 B. 5000 C. Unlimited D. None of the Above Ans: B 32. For faster reads, SAP HANA reads the tables column wise. A. True B. False Ans: A 33. Using SAP HANA Studio, table can be _______, ________, _______ (3 correct answers) A. Created manually B. Created using SQL C. Replicated from other systems D. Just copied from other systems Ans: A,B,C 34. When Administration Console is the perspective selected, which of the following can be viewed under HANA system? A. Catalog B. Content C. Both of them D. None of them Ans: A 35. Ultimately, SAP HANA database will replace all databases used in BI/BW, ECC and all other SAP Systems. A. True B. False Ans: A 36. In the SAP HANA Studio the physical tables are stored ______ A. Under Catalogs B. Under Content C. In both D. Nowhere Ans: A 37. When deleting a table, what options can be selected? (2 correct answers) A. Delete Catalog Object B. Delete All Rows C. Drop The Table D. All of the above Ans: A,B 38. Users are created under ______ A. Catalog B. Content C. Any of them D. None of them Ans: A 39. Can SAP HANA run on SUSE Linux? A. Yes B. No Ans: A 40. The option to send a table from Quality Assurance System to Production System is _______ A. Use CTS+ B. Use Import from Quality Assurance System C. Use Export from Quality Assurance System D. Use Transport Management System 41. To create a user, we have to ________ A. Use T-Code SU01 B. Use T-Code SU10 C. Use Users folder under Authorization in Catalog D. Use Users folder under Authorization in Content Ans: C 42. Which user has privileges to Root Package? A. SYS B. SYSTEM C. Both of the above D. Administrator Ans: B 43. Which role is specifically assigned to a user involved in creating views? A. SAPSR3_POWER_USER B. PUBLIC C. MODELING D. None of the above Ans: C 44. SAP HANA system is memory based and persistent storage is not necessary . A. True B. False Ans: B 45. SAP HANA contains OLAP data (only) in the memory. A. True B. False Ans: B 46. When you are creating a SAP HANA system in the SAP HANA Studio, a user should be used as the logon user. SYSTEM can be the only user for this purpose. A. True B. False Ans: B 47. If you have only one SAP HANA system physically, then you can create only one HANA system icon in the SAP HANA Studio. A. True B. False Ans: B 48. SAP HANA database is ______ A. Oracle based B. Sybase based C. MS SQL based D. Informix based Ans: B 49. Joins are created under ______ A. Content B. Catalog C. Schema D. Authorization Ans: A 50. In SAP HANA Studio, HANA systems can be created under nested folders identical to the departments in your company. A. True B. False Ans: A SAP HANA Questions and Answers pdf Download Read the full article

[youngprogrammersclub]

DBA Interview Questions with Answer Part14

Why drop table is not going into Recycle bin? If you are using SYS user to drop any table then user’s object will not go to the recyclebin as there is no recyclebin for SYSTEM tablespace, even we have already SET recycle bin parameter TRUE.Select * from v$parameter where name = 'recyclebin';Show parameter recyclebin; How to recover password in oracle 10g?You can query with the table user_history$. The password history is store in this table.How to detect inactive session to kill automatically?You can use the SQLNET.EXPIRE_TIME for the dead connections (for abnormal disconnections) by specifying a time interval in minute to send a problem message that verify client/server connections are active. Setting the value greater than 0 to this parameter ensures that connection is not left open indefinitely, due to abnormal client termination. If probe finds a terminated connection, or connection that is no longer in use, it returns an error, causing the server process to exit. SQLNET.EXPIRE_TIME=10Why we need CASCADE option with DROP USER command whenever dropping a user and why "DROP USER" commands fails when we don't use it?If a user having any object then ‘YES’ in that case you are not able to drop that user without using CASCADE option. The DROP USER with CASCADE option command drops user along with its all associated objects. Remember it is a DDL command after the execution of this command rollback cannot be performed.Can you suggest the best steps to refresh a Database?Refreshing the database is nothing but applying the change on one database (PROD) to another (Test). You can use import/export and RMAN method for this purpose.Import/Export Method:If you database is small and if you need to refresh particular schema only then it is always better to use this method.Export the dump file from source DBDrop and recreate Test environment User.Import the dump to destination DB.RMAN Method: Now days RMAN is most likely to be used for backup and recovery. It is relatively easier and better method for full database refresh to be refreshed. It is taking less time as compare to import/export method. Here also you can use particular SCN based refreshing.#!/usr/bin/kshexport ORAENV_ASK='NO'export ORACLE_SID=PRD/usr/local/bin/oraenvexport NLS_LANG=American_america.us7ascii;export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS";$ORACLE_HOME/bin/rman target / nocatalog log=/tmp/duplicate_tape_TEST.log connect auxiliary sys/PASSWORD@TEST;run{allocate auxiliary channel aux1 device type disk;set until SCN 42612597059;duplicate target database to "TEST" pfile='/u01/app/xxxx/product/10.2.0/db_1/dbs/initTEST.ora' NOFILENAMECHECK;}EOFHow will we know the IP address of our system in Linux environment?Either use ipconfig command or ip addr showIt will give you all IP address and if you have oracle 9i you can query from SQL prompt.SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;Can we create Bigfile Tablespace for all databases?Infact your question do we create bigfile tablespace for every database is not clear for me. If you are asking can we create bigfile for every database?Yes you can but it is not ideal for every datafile if your work is suitable for small file then why you create bigfile but if your mean is impact of bigfile that depends on your requirements and storage.A bigfile tablespace is having single very big datafile which can store 4GB to 128 TB.Creating single large datafile reducing the requirement of SGA and also it will allow you modification at tablespace level. In fact it is ideal for ASM, logical device supporting stripping. Avoid using bigfile tablespace where there is limited space availability. For more details impact, advantage, disadvantage of bigfile on my blog.Can you gice more explanation on logfile states?“CURRENT” state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.If a redo group containing redo’s of a dirty buffer that redo group is said to be ‘ACTIVE’ state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten.One more state ‘UNUSED’ initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.What is difference between oracle SID and Oracle service name?Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID. How to find session for Remote users?-- To return session id on remote session:‎SELECT distinct sid FROM v$mystat;-- Return session id of you in remote Environment:‎Select sid from v$mystat@remot_db where rownum=1;We have a complete cold Backup taken on Sunday. The database crashed on Wednesday. None of the database files are available. The only files we have are the taped backup archive files till Wednesday. Is there a possibility of recovering the database until the recent archive which we have in the tape using the cold backup.Yes, if you have all the archive logs since the cold backup then you can recover to your last logSteps:1) Restore all backup datafiles, and controlfile. Also restore the password file and init.ora if you lost those too. Don't restore your redo logs if you backed them up. 2) Make sure that ORACLE_SID is set to the database you want to recover 3) startup mount;4) Recover database using backup controlfile; At this point Oracle should start applying all your archive logs, assuming that they're in log_archive_dest5) alter database open resetlogs;How to check RMAN version in oracle?If you want to check RMAN catalog version then use the below query from SQL*plusSQL> Select * from rcver;If you want to check simply database version.SQL> Select * from v$version;What is the minimum size of Temporary Tablespace?1041 KBDifference b/w image copies and backup sets?An image copy is identical, byte by byte, to the original datafile, control file, or archived redo log file. RMAN can write blocks from many files into the same backup set but can’t do so in the case of an image copy.An RMAN image copy and a copy you make with an operating system copy command such as dd (which makes image copies) are identical. Since RMAN image copies are identical to copies made with operating system copy commands, you may use user-made image copies for an RMAN restore and recovery operation after first making the copies “known” to RMAN by using the catalog command.You can make image copies only on disk but not on a tape device. "backup as copy database;" Therefore, you can use the backup as copy option only for disk backups, and the backup as backupset option is the only option you have for making tape backups.How can we see the C: drive free space capacity from SQL?create an external table to read data from a file that will be as below create BAT file free.bat as @setlocal enableextensions enable delayedexpansion @echo off for /f "tokens=3" %%a in ('dir c:') do ( set bytesfree=%%a ) set bytesfree=%bytesfree:,=% echo %bytesfree% endlocal && set bytesfree=%bytesfree% You can create a schedular to run the above free.bat, free_space.txt inside the oracle directory.Differentiate between Tuning Advisor and Access Advisor?The tuning Advisor:–        It suggests indexes that might be very useful.–        It suggests query rewrites.–        It suggests SQL profileThe Access Advisor:–        It suggest indexes that may be useful–        Suggestion about materialized view.–        Suggestion about table partitions also in latest version of oracle.How to give Access of particular table for particular user?GRANT SELECT (EMPLOYEE_NUMBER), UPDATE (AMOUNT) ON HRMS.PAY_PAYMENT_MASTER TO SHAHID;The Below command checks the SELECT privilege on the table PAY_PAYMENT_MASTER on the HRMS schema (if connected user is different than the schema)SELECT PRIVILEGEFROM ALL_TAB_PRIVS_RECDWHERE PRIVILEGE = 'SELECT'AND TABLE_NAME = 'PAY_PAYMENT_MASTER'AND OWNER = 'HRMS'UNION ALLSELECT PRIVILEGEFROM SESSION_PRIVSWHERE PRIVILEGE = 'SELECT ANY TABLE';What are the problem and complexities if we use SQL Tuning Advisor and Access Advisor together?I think both the tools are useful for resolving SQL tuning issues. SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics and the SQL Access Advisor does suggest good data access paths, that is mainly work which can be done better on disk.Both SQL Tuning Advisor and SQL Access Advisor tools are quite powerful as they can source the SQL they will tune automatically from multiple different sources, including SQL cache, AWR, SQL tuning Sets and user defined workloads. Related with the argument complexity and problem of using these tools or how you can use these tools together better to check oracle documentation.

[windows10trainingclub]

DBA Interview Questions with Answer Part14

Why drop table is not going into Recycle bin? If you are using SYS user to drop any table then user’s object will not go to the recyclebin as there is no recyclebin for SYSTEM tablespace, even we have already SET recycle bin parameter TRUE.Select * from v$parameter where name = 'recyclebin';Show parameter recyclebin; How to recover password in oracle 10g?You can query with the table user_history$. The password history is store in this table.How to detect inactive session to kill automatically?You can use the SQLNET.EXPIRE_TIME for the dead connections (for abnormal disconnections) by specifying a time interval in minute to send a problem message that verify client/server connections are active. Setting the value greater than 0 to this parameter ensures that connection is not left open indefinitely, due to abnormal client termination. If probe finds a terminated connection, or connection that is no longer in use, it returns an error, causing the server process to exit. SQLNET.EXPIRE_TIME=10Why we need CASCADE option with DROP USER command whenever dropping a user and why "DROP USER" commands fails when we don't use it?If a user having any object then ‘YES’ in that case you are not able to drop that user without using CASCADE option. The DROP USER with CASCADE option command drops user along with its all associated objects. Remember it is a DDL command after the execution of this command rollback cannot be performed.Can you suggest the best steps to refresh a Database?Refreshing the database is nothing but applying the change on one database (PROD) to another (Test). You can use import/export and RMAN method for this purpose.Import/Export Method:If you database is small and if you need to refresh particular schema only then it is always better to use this method.Export the dump file from source DBDrop and recreate Test environment User.Import the dump to destination DB.RMAN Method: Now days RMAN is most likely to be used for backup and recovery. It is relatively easier and better method for full database refresh to be refreshed. It is taking less time as compare to import/export method. Here also you can use particular SCN based refreshing.#!/usr/bin/kshexport ORAENV_ASK='NO'export ORACLE_SID=PRD/usr/local/bin/oraenvexport NLS_LANG=American_america.us7ascii;export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS";$ORACLE_HOME/bin/rman target / nocatalog log=/tmp/duplicate_tape_TEST.log connect auxiliary sys/PASSWORD@TEST;run{allocate auxiliary channel aux1 device type disk;set until SCN 42612597059;duplicate target database to "TEST" pfile='/u01/app/xxxx/product/10.2.0/db_1/dbs/initTEST.ora' NOFILENAMECHECK;}EOFHow will we know the IP address of our system in Linux environment?Either use ipconfig command or ip addr showIt will give you all IP address and if you have oracle 9i you can query from SQL prompt.SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;Can we create Bigfile Tablespace for all databases?Infact your question do we create bigfile tablespace for every database is not clear for me. If you are asking can we create bigfile for every database?Yes you can but it is not ideal for every datafile if your work is suitable for small file then why you create bigfile but if your mean is impact of bigfile that depends on your requirements and storage.A bigfile tablespace is having single very big datafile which can store 4GB to 128 TB.Creating single large datafile reducing the requirement of SGA and also it will allow you modification at tablespace level. In fact it is ideal for ASM, logical device supporting stripping. Avoid using bigfile tablespace where there is limited space availability. For more details impact, advantage, disadvantage of bigfile on my blog.Can you gice more explanation on logfile states?“CURRENT” state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.If a redo group containing redo’s of a dirty buffer that redo group is said to be ‘ACTIVE’ state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten.One more state ‘UNUSED’ initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.What is difference between oracle SID and Oracle service name?Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID. How to find session for Remote users?-- To return session id on remote session:‎SELECT distinct sid FROM v$mystat;-- Return session id of you in remote Environment:‎Select sid from v$mystat@remot_db where rownum=1;We have a complete cold Backup taken on Sunday. The database crashed on Wednesday. None of the database files are available. The only files we have are the taped backup archive files till Wednesday. Is there a possibility of recovering the database until the recent archive which we have in the tape using the cold backup.Yes, if you have all the archive logs since the cold backup then you can recover to your last logSteps:1) Restore all backup datafiles, and controlfile. Also restore the password file and init.ora if you lost those too. Don't restore your redo logs if you backed them up. 2) Make sure that ORACLE_SID is set to the database you want to recover 3) startup mount;4) Recover database using backup controlfile; At this point Oracle should start applying all your archive logs, assuming that they're in log_archive_dest5) alter database open resetlogs;How to check RMAN version in oracle?If you want to check RMAN catalog version then use the below query from SQL*plusSQL> Select * from rcver;If you want to check simply database version.SQL> Select * from v$version;What is the minimum size of Temporary Tablespace?1041 KBDifference b/w image copies and backup sets?An image copy is identical, byte by byte, to the original datafile, control file, or archived redo log file. RMAN can write blocks from many files into the same backup set but can’t do so in the case of an image copy.An RMAN image copy and a copy you make with an operating system copy command such as dd (which makes image copies) are identical. Since RMAN image copies are identical to copies made with operating system copy commands, you may use user-made image copies for an RMAN restore and recovery operation after first making the copies “known” to RMAN by using the catalog command.You can make image copies only on disk but not on a tape device. "backup as copy database;" Therefore, you can use the backup as copy option only for disk backups, and the backup as backupset option is the only option you have for making tape backups.How can we see the C: drive free space capacity from SQL?create an external table to read data from a file that will be as below create BAT file free.bat as @setlocal enableextensions enable delayedexpansion @echo off for /f "tokens=3" %%a in ('dir c:') do ( set bytesfree=%%a ) set bytesfree=%bytesfree:,=% echo %bytesfree% endlocal && set bytesfree=%bytesfree% You can create a schedular to run the above free.bat, free_space.txt inside the oracle directory.Differentiate between Tuning Advisor and Access Advisor?The tuning Advisor:–        It suggests indexes that might be very useful.–        It suggests query rewrites.–        It suggests SQL profileThe Access Advisor:–        It suggest indexes that may be useful–        Suggestion about materialized view.–        Suggestion about table partitions also in latest version of oracle.How to give Access of particular table for particular user?GRANT SELECT (EMPLOYEE_NUMBER), UPDATE (AMOUNT) ON HRMS.PAY_PAYMENT_MASTER TO SHAHID;The Below command checks the SELECT privilege on the table PAY_PAYMENT_MASTER on the HRMS schema (if connected user is different than the schema)SELECT PRIVILEGEFROM ALL_TAB_PRIVS_RECDWHERE PRIVILEGE = 'SELECT'AND TABLE_NAME = 'PAY_PAYMENT_MASTER'AND OWNER = 'HRMS'UNION ALLSELECT PRIVILEGEFROM SESSION_PRIVSWHERE PRIVILEGE = 'SELECT ANY TABLE';What are the problem and complexities if we use SQL Tuning Advisor and Access Advisor together?I think both the tools are useful for resolving SQL tuning issues. SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics and the SQL Access Advisor does suggest good data access paths, that is mainly work which can be done better on disk.Both SQL Tuning Advisor and SQL Access Advisor tools are quite powerful as they can source the SQL they will tune automatically from multiple different sources, including SQL cache, AWR, SQL tuning Sets and user defined workloads. Related with the argument complexity and problem of using these tools or how you can use these tools together better to check oracle documentation.

[stokedevwebsite]

DBA Interview Questions with Answer Part 20‎

What is Checkpoint SCN and Checkpoint Count? How we can check it?Checkpoint is an event when the database writer is going to flush the dirty buffers into the datafiles. This an ongoing activity and in the result checkpoint number constantly incremented in the datafile header and controfile and the background process CKPT take care of this responsibility.How can you find length of Username and Password?You can find the length of username with below query. The password is hashed (#) so there is no way to get their length.You can use special characters ($, #, _) without single quotes and any other characters must be enclosed in single quotation.Select length (username), usernamefrom dba_users;The minimum length for password is at least 1 character where as maximum depends on database version. In 10g it is restricted to 17 characters long.What are the restrictions applicable while creating view?– A view can be created referencing tables and views only in the current database.– A view name must not be the same as any table owned by that user.– You can build view on other view and on procedure that references views.For More information you can click on the below link: Common Interview Question & AnswerWhat is difference between Delete/Drop/Truncate?DELETE is a command that only removes data from the table. It is DML statement. Deleted data can be rollback (when you delete all the data get copied into rollback first then deleted). We can use where condition with delete to delete particular data from the table.Where as DROP commands remove the table from data dictionary. This is DDL statement. We cannot recover the table before oracle 10g, but flashback feature of oracle 10g provides the facility to recover the drop table.While TRUNCATE is a DDL command that delete data as well as freed the storage held by this table. This free space can be used by this table or some other table again. This is faster because it performs the deleted operation directly (without copying the data into rollback).Alternatively you can enable the row movement for that table and can use shrink command while using the delete command.SQL> Create table test     (      Number s1, Number s2     );SQL> Select bytes, blocks from user_segments     where segment_name = ‘test’;Bytes       block----------  -------65536       8SQL> insert into t select level, level*3     From dual connect by level  Select bytes, blocks from user_segments     where segment_name = ‘test’;Bytes       block----------  -------131072      16SQL> Delete from test;3000 rows deleted.SQL> select bytes,blocks from user_segments     where segment_name = 'test';Bytes       block----------  -------131072      16SQL> Alter table t enable row movement;SQL> Alter table t shrink space;Table alteredSQL> Select bytes,blocks from user_segments      where segment_name = 'test';Bytes       block----------  -------65536       8What is difference between Varchar and Varchar2?Varchar2 can store upto 4000 bytes where as Varchar can only store upto 2000 bytes. Varchar2 can occupy space for NULL values where as Varchar2 will not specify any space for NULL values.What is difference between Char and Varchar2?A CHAR values have fixed length. They are padded with space characters to match the specified length where as VARCHAR2 values have a variable length. They are not padded with any characters. In which Language oracle has been developed?Oracle is RDBMS package developed using C language.What is difference between Translate and Replace?Translate is used for character by character substitution where as Replace is used to substitute a single character with a word.What is the fastest query method to fetch data from table?Using ROWID is the fastest method to fetch data from table.What is Oracle database Background processes specific to RAC?LCK0—Instance Enqueue Process LMS—Global Cache Service Process LMD—Global Enqueue Service Daemon LMON—Global Enqueue Service Monitor Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES) to ensure that each oracle RAC database instance obtain the block that it needs to satisfy as query or transaction. The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances.What is SCAN in respect of oracle RAC?Single client access name (SCAN) is a new oracle real application clusters (RAC) 11g releases 2 features that provides a single name for client to access an oracle database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the clusters.Why do we have a virtual IP (VIP) in oracle RAC?Without VIP when a node fails the client wait for the timeout before getting error where as with VIP when a node fails, the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP. Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients. This results in the clients getting errors immediately.Why query fails sometimes?Rollback segments dynamically extent to handle large transactions entry loads. A single transaction may occupy all available free space in rollback segment tablespace. This situation prevents other user using rollback segments. You can monitor the rollback segment status by querying DBA_ROLLBACK_SEGS view.What is ADPATCH and OPATCH utility? Can you use both in Application?ADPATCH is a utility to apply application patch and OPATCH is a utility to apply database patch. You have to use both in application for applying in application you have to use ADPATCH and for applying in database you have to use OPATCH.What is Automatic refresh of Materialized view and how you will find last refresh time of Materialized view?Since oracle 10g complete refresh of materialized view can be done with deleted instead of truncate. To force the instance to do the refresh with truncate instead of deleted, parameter AUTOMIC_REFRESH must be set to FALSEWhen it is FALSE Mview will be faster and no UNDO will be generated and whole data will be inserted.When it is TRUE Mview will be slower and UNDO will be generated and whole data will be inserted. Thus we will have access of all time even while it is being refreshed.If you want to find when the last refresh has taken place. You can query with these view: dba_mviews or dba_mview_analysis or dba_mview_refresh_times SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mviews; -or- SQL> select NAME, to_char(LAST_REFRESH,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_refresh_times; -or- SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_analysis;Why more archivelogs are generated, when database is begin backup mode?During begin backup mode datafiles headers get freezed so row information can not be retrieved as a result the entire block is copied to redo logs thus more redo log generated or more log switch occur in turn more archivelogs. Normally only deltas (change vector) are logged to the redo logs.The main reason is to overcome the fractured block. A fractured block is a block in which the header and footer are not consistent at a given SCN. In a user-managed backup, an operating system utility can back up a datafile at the same time that DBWR is updating the file. It is possible for the operating system utility to read a block in a half-updated state, so that the block that is copied to the backup media is updated in its first half, while the second half contains older data. In this case, the block is fractured.For non-RMAN backups, the ALTER TABLESPACE ... BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP when a tablespace is in backup mode, and a change is made to a data block, the database logs a copy of the entire block image before the change so that the database can re-construct this block if media recovery finds that this block was fractured.The block that the operating system reads can be split, that is, the top of the block is written at one point in time while the bottom of the block is written at another point in time. If you restore a file containing a fractured block and Oracle reads the block, then the block is considered a corrupt.Why is UNION ALL faster than UNION?UNION ALL faster than a UNION because UNION ALL will not eliminate the duplicate rows from the base tables instead it access all rows from all tables according to your query where as the UNION command is simply used to select related distinct information from base tables like JOIN command.Thus if you know that all the records of your query returns the unique records then always use UNION ALL instead of UNION. It will give you faster results.How will you find your instance is started with Spfile and Pfile?You can query with V$spparameter viewSQL> Select isspecified, count(*) from v$spparameter     Group by isspecified;ISSPEC   COUNT(*)------   ----------FALSE    221TRUE     39As isspecified is TRUE with some count we can say that instance is running with spfile. Now try to start your database with pfile and run the previous query again.SQL> Select isspecified, count(*) from v$spparameter     Group by isspecified;ISSPEC COUNT(*)------ ----------FALSE  258Then you will not find any parameter isspecified in spfile they all come from pfile thus you can say instance is started with pfile.Alternatively you can use the below querySQL> show parameter spfile;SQL> Select decode(count(*), 1, 'spfile', 'pfile' )     from v$spparameter     where rownum=1 and isspecified='TRUE';Why we need to enable Maintenance Mode?To ensure optimal performance and reduce downtime during patching sessions, enabling this feature shuts down the Workflow Business Events System and sets up function security so that Oracle Applications functions are unavailable to users. This provides a clear separation between normal run time operation and system downtime for patching..

[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.