#how to drop global temporary table in oracle | Explore Tumblr posts and blogs

siva3155 · 5 years ago

Text

300+ TOP Memory Management Interview Questions and Answers

Memory Management Interview Questions for freshers experienced :-

1. What is the significance of having storage clause? We can plan the storage for a table as how much initial extents are required, how much can be extended next, how much % should leave free for managing row updations etc. 2. What is the functionality of SYSTEM table space? To manage the database level transactions such as modifications of the data dictionary table that record information about the free space usage. 3. How does Space allocation table place within a block? Each block contains entries as follows Fixed block header Variable block header Row Header,row date (multiple rows may exists) PCTEREE (% of free space for row updation in future). 4. What is the role of PCTFREE parameter is storage clause? This is used to reserve certain amount of space in a block for expansion of rows. 5. What is the OPTIMAL parameter? To avoid the space wastage we use OPTIMAL parameter. 6. What is a shared pool? The Shared Pool environment contains both fixed and variable structures. The Fixed structures remain relatively the same size, whereas the variable structures grow and shrink based on user and program requirements. Used To Store Most Recently Executed SQL Statements Most Recently used Data definitions It Consists of two Key performance - related memory structures Library Cache & Data Dictionary Cache Shared Pool is sized by SHARED_POOL_SIZE 7. What is mean by Program Global Area (PGA)? It is area in memory that is used by a Single Oracle User Process. 8. What is a data segment? Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored. 9. What are the factors causing the reparsing of SQL statements in SGA? Due to insufficient Shared SQL pool size. Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE. LOGICAL & PHYSICAL ARCHITECTURE OF DATABASE. 10. What is Database Buffers? Database buffers are cache in the SGA used to hold the data blocks that are read from the data segments in the database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.

Memory Management Interview Questions 11. What is dictionary cache? Dictionary cache is information about the databse objects stored in a data dictionary table. 12. Which parameter in Storage clause will reduce number of rows per block? PCTFREE parameter Row size also reduces no of rows per block. 13. What is meant by free extent? A free extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as free. 14. How will you force database to use particular rollback segment? For perticular transaction Alter system set rollback segment 'name'; For database, we can set in pfile. Rollback_segment='name' . 15. How can we organize the tablespaces in Oracle database to have maximum performance? Store data in tablespaces to avoid disk contension.system tablespace-recursive callsuserdata-users objectsindex tablespace-for indexesrollback segmnets-undo tablespace or manual rollback segmentsplace application specific data in respective tablespaces.Place all these tablespaces in saperate disks.Try to implement raid-0 (striping) for better performance. 16. How will you swap objects into a different table space for an existing database? Export the user Perform import using the command imp system/manager file=export.dmp indexfile=newrite.sql. This will create all definitions into newfile.sql. Drop necessary objects. Run the script newfile.sql after altering the tablespaces. Import from the backup for the necessary objects. 17. What is redo log buffer? Changes made to entries are written to the on-line redo log files. So that they can be used in roll forward operations during database recoveries. Before writing them into the redo log files, they will first brought to redo log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size. 18. What is meant by recursive hints? Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of Data Dictionary Cache. 19. How can we plan storage for very large tables? Limit the number of extents in the table Separate Table from its indexes. Allocate Sufficient temporary storage. 20. How will you estimate the space required by a non-clustered tables? Calculate the total header size Calculate the available dataspace per data block Calculate the combined column lengths of the average row Calculate the total average row size. Calculate the average number rows that can fit in a block Calculate the number of blocks and bytes required for the table. After arriving the calculation, add 10 % additional space to calculate the initial extent size for a working table. 21. It is possible to use raw devices as data files and what are the advantages over file system files? Yes. The advantages over file system files. I/O will be improved because Oracle is bye-passing the kernnel which writing into disk. Disk Corruption will be very less. 22. What is a Control file? The Control File is a small binary file necessary for the database to start and operate successfully. Each Control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to USE. The Control file is not accessible, the database does not function properly. 23. How will you monitor rollback segment status? By using dictionaray view's called v$rollstat,dba_rollback_segs. 24. How will you monitor the space allocation? This can be monitored in DB_data_files. 25. Why query fails sometimes? Due to syntax errors. 26. How the space utilization takes place within rollback segments? By correctly fixing optimal size. 27. How will you create multiple rollback segments in a database? create rollback segment roll1tablespace roll1. 28. What is a rollback segment entry? When ever changes happend to the database previous change will be there in the rollback segment. 29. What is hit ratio? Hit Ratio is the ratio of shared SQL and PL/SQL items found in the Library Cache versus physical storage.It can also be defined in a mathematical expression as 1 - ((physical reads) / (db block gets + consistent reads)). 30. What are disadvantages of having raw devices? We should depend on export/import utility for backup/recovery (fully reliable) The tar command cannot be used for physical file backup, instead we can use dd command which is less flexible and has limited recoveries. StumbleUpon Digg Delicious Twitter FaceBook LinkedIn Google Yahoo MySpace Tell Your Friend 31. What is use of rollback segments in Oracle database? When a user updated a particular table (for example 100 rows) the old value will be retained in the roll back segments(Oracle 8) and now it is Undo segment (oracle 9i). If the user issue a rollback command the old value will be taken from the rollback segment(that too if undo_retention parameter set properly in the parameter file). 32. What is advantage of having disk shadowing / mirroring? Shadow set of disks save as a backup in the event of disk failure. In most Operating System if any disk failure occurs it automatically switchover to place of failed disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks. 33. How redo logs can be achieved? LGWR process wirtes all change vectors from theredo log buffer to online redo log file sequentially. 34. What is redo log file mirroring? Multiplexing Redo log file called Mirroing. ( Keeping multiple copies in different disks) 35. How to implement the multiple control files for an existing database? Edit init.ora file set control_files parameter with multiple location shutdown immediate copy control file to multiple locations & confirm from init.ora contol_files parameter start the database. run this query for changes confirmation - select name from v$controlfile; 36. What is SGA? How it is different from Ver 6.0 and Ver 7.0? The System Global Area in a Oracle database is the area in memory to facilitates the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is Database buffers, Dictionary cache, Redo Log Buffer and Shared SQL pool (ver 7.0 only) area. 37. What is a Shared SQL pool? The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users. 38. What is mean by Program Global Area (PGA)? It is area in memory that is used by a Single Oracle User Process. 39. List the factors that can affect the accuracy of the estimations? The space used transaction entries and a deleted record does not become free immediately after completion due to delayed cleanout. Trailing nulls and length bytes are not stored. Inserts of, updates to and deletes of rows as well as columns larger than a single datablock, can cause fragmentation and chained row pieces. 40. What are the different kind of export backups? Full back - Complete database Incremental - Only affected tables from last incremental date/full backup date. Cumulative backup - Only affected table from the last cumulative date/full backup date. 41. What is cold backup? What are the elements of it? Cold backup is taking backup of all physical files after normal shutdown of database. We need to take. All Data files. All Control files. All on-line redo log files. The init.ora file (Optional) 42. What is a logical backup? Logical backup involves reading a set of database records and writing them into a file. Export utility is used for taking backup and Import utility is used to recover from backup. 43. What is hot backup and how it can be taken? Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. All control files. 44. What is the use of FILE option in EXP command? To give the export file name. 45. What is the use of GRANT option in EXP command? A flag to indicate whether grants on databse objects will be exported or not. Value is 'Y' or 'N'. 46. What is the use of INDEXES option in EXP command? A flag to indicate whether indexes on tables will be exported. 47. What is the use of ROWS option in EXP command? Flag to indicate whether table rows should be exported. If 'N' only DDL statements for the database objects will be created. 48. What is the use of PARFILE option in EXP command? Name of the parameter file to be passed for export. 49. What is the use of ANALYSE ( Ver 7) option in EXP command? A flag to indicate whether statistical information about the exported objects should be written to export dump file. 50. What is the use of FULL option in EXP command? A flag to indicate whether full databse export should be performed. 51. What is the use of OWNER option in EXP command? List of table accounts should be exported. 52. What is the use of TABLES option in EXP command? List of tables should be exported. 53. What is the use of RECORD LENGTH option in EXP command? Record length in bytes. 54. What is the use of INCTYPE option in EXP command? Type export should be performed COMPLETE,CUMULATIVE,INCREMENTAL 55. What is the use of RECORD option in EXP command? For Incremental exports, the flag indirects whether a record will be stores data dictionary tables recording the export. 56. What is the use of ROWS option in IMP command? A flag to indicate whether rows should be imported. If this is set to 'N' then only DDL for database objects will be executed. 57. What is the use of INDEXES option in IMP command? A flag to indicate whether import should import index on tables or not. 58. What is the use of GRANT option in IMP command? A flag to indicate whether grants on database objects will be imported. 59. What is the use of SHOW option in IMP command? A flag to indicate whether file content should be displayed or not. 60. What is the use of FILE option in IMP command? The name of the file from which import should be performed. 61. What is use of LOG (Ver 7) option in EXP command? The name of the file which log of the export will be written. Memory Management Questions and Answers Pdf Download Read the full article

0 notes

stokedevwebsite · 6 years ago

Text

DBA Interview Questions with Answers Part19

Why we look for CHUNKS_FREE space while tracking fragmentation details query? The CHUNK_FREE return the number of chunks of contiguous free space based on dba_free_space table. The motive is to find the largest size chunks of free space within a tableapce. This is because as we know oracle server allocates space for segments in unit of one extent. When the existing extent of segment is full, the server allocates another extent for the segment. In order to do oracle searches free space in the tablespace (contiguous set of data block sufficient to meet the required extent). If sufficient space not found then an error is returned by the oracle server.What is the impact of NLS/Characterset in database?NLS is a National language support and encompasses how to display currency, whenever we use a comma or a dot to separate numbers, how the name of the day is spelled etc.Charactersets are how we store data. For Example: US7ASCII is a 7bit characterset and WE8ISO8859P18 bit character set. It can store 2 times as many characters as the 7bit characterset. If you try to export from 8 bit characterset database and import into 7bit database then there is chance to loose data in 7bit characterset that have the high bit set and if you try from 7bit to 8bit would not encounter any issues since the 7bit characterset is a subset of the 8bit characterset and can hold more types of characters and can support many countries.Can we perform RMAN level 1 backup without level 0?If no level 0 is available, then the behavior depends upon the compatibility mode setting (oracle version). If the compatibility mode less than 10.0.0, RMAN generates a level 0 backup of files contents at the time of backup. If compatibility is greater than 10.0.0 RMAN copies all block changes since the file was created, and stores the results as level 1 backup.What will happen if ARCHIVE process cannot copy an archive redolog to a mandatory archive log destination?Oracle will continue with cycle to the other online redolog groups until it return to the group that the ARCH process is trying to copy to the mandatory archive log destination. If the mandatory archive log destination copy has not occurred, the database operation will suspend until the copy is successful or the DBA has intervened to perform force log switching.Can you differentiate between HOTBACKUP and RMAN backup?For hotbackup we have to put database in begin backup mode, then take backup where as RMAN would not put database in begin backup mode. In fact RMAN has a number of advantages over general backup. For more information please check: Benefit of RMAN BackupHow to put Manual/User managed backup in RMAN?In case of recovery catalog, you can put by using catalog command:RMAN> CATALOG START WITH ‘/oraback/backup.ctl’;When you put any SQL statement how oracle responds them internally?First it will check the syntax and semantics in library cache, after that it will created execution plan. If already data in buffer cache (in case of identical query) it will directly return to the client. If not it write the fetch to the database buffer cache after that it will send server and finally server send to the client.Can we use Same target database as Catalog?No, the recovery catalog should not reside in the target database (database to be backed up) because the database can not be recovered in the mounted state.Differentiate the use of what are PGA and UGA?When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).How do you automatically force the oracle to perform a checkpoint?The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hourLOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.What is Cluster table in Oracle database?A Cluster is a schema object that contains one or more tables that all have one or more common columns. Rows of one or more tables that share the same value in these common columns are physically stored together within the database. Generally, you should only cluster tables that are frequently joined on the cluster key columns in SQL statements. Clustering multiple tables improves the performance of joins, but it is likely to reduce the performance of full table scans, INSERT and UPDATE statements that modify cluster key values.Can you differentiate between complete and incomplete recovery?An incomplete database recovery is a recovery that it does not reach to the point of failure. The recovery can be either point of time or particular SCN or Particular archive log specially incase of missing archive log or redolog failure where as a complete recovery recovers to the point of failure possibly when having all archive log backup.What is difference between RMAN and Traditional Backup?RMAN is faster can perform incremental (changes only) backup, and does not place tablespace in hotbackup mode. Check: Benefit of RMAN BackupWhat are bind variables and why are they important?With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.How to recover database without backup?If flash recovery is enabled then we can recover database without having backup? Otherwise we cannot recover database without backup.How to write explicit cursor to avoid oracle exception: no_data_found and too_many_rows?In PL/SQL if you try to write select statement with into clause it may return two exception no_data_found and too_many_rowsto avoid this exception you have to write explicit cursor.Exception Block,When no_data_found// Put your codeWhen_too_many_rows// put your codeWhen others then// put your codeEnd;What are differences between Reference cursor and Normal cursor?Reference cursor gives the address of the location instead of putting item directly. It holds the different type of structures. Normal cursor holds one structure of table.Reference cursor is a dynamic cursor where as normal cursor is static cursor. In dynamic cursor single statement are process multiple select statement dynamically at run time where as in normal cursor we process only one select statement.What is Pipeline view?In case of normal views whenever you call the view it will get data from the base table where as in case of pipeline view if you call the view it will get data from another intermediate view.How would you find the performance issue of SQL queries?– Enable the trace file before running your queries– Then check the trace file using tkprofcreate output file.– According to explain plan check the elapsed time for each query– Then tune them respectively.What is difference between Recovery and Restoring of database?Restoring means copying the database object from the backup media to the destination where actually it is required where as recovery means to apply the database object copied earlier (roll forward) in order to bring the database into consistent state.What are the Jobs of SMON and PMON processes?SMON – System Monitor performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default.PMON – Process Monitor failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.When you should rebuild index?In fact in 90% case never. When the data in index is sparse (lot of holes in index, due to delete and updates) and your query is usually ranged based. Also index BLEVEL is one of the key indicators of performance of SQL queries doing index range scan.What is key preserved table?A table is set to be key preserved table if every key of the table can also be the key of the result of the join. It guarantees to return only one copy of each row from the base table.Which of the following is NOT an oracle supported trigger?BeforeDuringAfterInstead ofAnswer: BWhich of the following is NOT true about modifying table column?You can drop a column at any time.You can add a column at any time as long as it is a NULL column.You can increase the number of characters in character columns or number of digits in numeric columns.You can not increase or decrease the number of decimal places.Answer: DHow can you find SQL of the Currently Active Sessions? Compare tables shutdown abort.How do you move table from one tablespace to another tablespace?You can use any of the below method to do this:1. Export the table, drop the table, create definition of table in new tablespace and then import the data using (imp ignore=y).2. Create new table in new tablespace then drop the original table and rename temporary table with original table name.CREATE TABLE temp_name TABLESPACE new_tablespace as select * from ‘source_table’;DROP TABLE real_table;RENAME temp_name to real_table;For More Interview Preparation Click on Link:DBA Interview Questions with Answers Part1, DBA Interview Questions with Answers Part2DBA Interview Questions with Answers Part3, DBA Interview Questions with Answers Part4‎DBA Interview Questions with Answers Part5, DBA Interview Questions with Answers Part6DBA Interview Questions with Answers Part7, DBA Interview Questions with Answers Part8‎DBA Interview Questions with Answers Part9, DBA Interview Questions with Answers Part10DBA Interview Questions with Answers Part11, DBA Interview Questions with Answers Part12DBA interview Questions with Answers Part13, DBA Interview Questions with Answers Part14DBA Interview Questions with Answers Part 15, DBA Interview Questions with Answer Part 16DBA Interview Questions with Answer Part17, Basic SQL Question & Answer SessionDiscussion on SCN and Checkpoint, Discussion: Import/Export Utility?Points to be Remember before Online Interview

0 notes