Posts by Date

March 2015
« Feb    

Today’s activity

  1. How do I find out who is currently using the space in the temporary tablespace ?
  2. How can I perform bitwise operations in Oracle ?
  3. UTL_RAW package
  4. How can I tune a tablescan?
  5. Can you make the scan times faster?
  6. Would parallelism help ?
  7. Would partitioning (on the colX column in this case) work for you?
  8. Why is dbms_stats so much slower than Analyze?
  9. Why would a reverse index be useful when the leading column of the index is generated from a sequence ?
  10. Why does AUTOTRACE not show partition pruning in the explain plan ?
  11. How to generate an Execution Plan with Bind Variable Peeking?
  12. Why and when should one tune?
  13. When should one rebuild an index?
  14. How do you create an ‘auto number’ or ‘auto increment’ column similar to the offering from Microsoft’s SQL Server ?
  15. How do I change the default password of internal ?
  16. Queries against dba_free_space are slow – is there any way to speed them up ?
  17. How can I maintain a history of user logins?
  18. Why does AUTOTRACE not show partition pruning in the explain plan ?
  19. How can an end-user see whether a row is locked, and who is locking it, before they try to lock it themselves ?
  20. What’s the difference between DELETE ROWS and TRUNCATE TABLE ?
  21. What is the time unit used in the v$ views ?
  22. Is there a way to suspend a task, or make it sleep, for a period of time ?
  23. My locally managed tablespaces seem to have lost some space according to dba_free_space. Where has it gone ?
  24. How do I find distributed queries / transactions (either issued from or connecting to this instance)?
  25. What happens if I drop a partition from a partitioned table whilst a query is running ?
  26. Why does a global index on my partitioned table work faster than a local index ?
  27. How can I track structural changes to my database ?
  28. What’s my best strategy for laying out an Oracle database across my available I/O devices ?
  29. Is it possible to re-set a sequence without dropping and recreating it
  30. How do I retrieve information about partitioned tables from the data dictionary ?
  31. Why is my import so much slower than my export ? ?
  32. What is an object of type ‘Non-existent’ ?
  33. I need to delete a lot of small records from LOB-tablespace then load some larger ones. How can I avoid the fragmentation?
  34. How have the log_checkpoint_interval and log_checkpoint_timeout changed from version 7 ?
  35. Why isn’t my output from tkprof with the explain option showing row counts ?
  36. How does Oracle handle updating a row that causes it to move from one partition to another ?
  37. Where is the UGA located, and how does sorting affect its use ?
  38. Is there a way to get the Date/Time when a table was last updated ?
  39. What’s the difference between DELETE ROWS and TRUNCATE TABLE ?
  40. Every time when I restart the database I have to bring all my rollback segments back online manually. Why ?
  41. Is there a way to get the Date/Time when a row was last updated?
  42. What is table fragmentation, and should I worry about it ?
  43. What is table fragmentation? Should I worry about it?
  44. What is an object of type ‘Undefined’ ?
  45. Which of my indexes are redundant and can be dropped ?
  46. What is the difference between a unique index and a unique constraint?
  47. I’ve got a corrupted data file, and Oracle won’t start. I don’t need the data so how do I open the database and drop data file?
  48. I have taken over an existing tablespace and need some ideas for setting extent sizes when re-organizing it ?
  49. How do I rename a data file ?
  50. What would be a simple list of events a DBA should be alerted about?
  51. How do I find out which users have the rights, or privileges, to access a given object ?
  52. How do I find out which tables are accessed with a full table scan, and the number of times this occurred ?
  53. If I store text in a column, can I index individual words ?.
  54. I am unable to add a PK constraint to a table – what is the best way of finding the problem rows ?
  55. Dropping a tablespace seems to take a very long time – how can I speed it up ?
  56. I have created a table with the nologging option, but any insert, update, or delete still generates redo log , why?
  57. What is the recoverability of objects created as NOLOGGING?
  58. Does number of extents matter ?
  59. Does extent size matter ?
  60. The number of redo copy latch misses reported in V$LATCH is a large fraction of the gets. What should I do ?
  61. Further reading: Is there a simple way to produce a report of all tables in the database with current number of rows ?
  62. Why do some queries go very fast and others very slowly when the optimizer_mode is choose ?
  63. Are composite indexes more useful than single column indexes ?
  64. Is is possible to allow Microsoft Query receive the output from the dbms_output package ? If not, what are the alternatives ?
  65. Is there any way to identify which init.ora parameters are relevant to tuning the database ?
  66. How do I decide on a good value for pga_aggregate_target in 9.2 ?
  67. I have a query that I run on two identical databases – on one it is very quick on the other it is very slow – what’s up ?
  68. Is there any way of identifying how parameters in the Oracle INIT.ORA control file can be used for tuning the database including: (1) Memory usage (2) CPU usage (3) Disk usage
  69. What is the fastest way of dumping a database table to a flat file – is utl_file a good idea ?
  70. How do I get global histograms onto partitioned tables in Oracle 8.1 – apparently they can only be generated properly in v9.
  71. In documentation about tuning SQL, I see references to parse trees. What is a parse tree ?
  72. Are there any benefits or drawbacks to regular reboot of a machine running an Oracle database ?
  73. Is there any way to keep track of when datafiles are resized.
  74. Should I worry about having some relationship between extent sizes and db_file_multiblock_read_count ?
  75. How can I apply an Oracle database (application) change to many equal instances located across the country on a single date ?
  76. We’re looking for he most efficient way to apply our database releases to dozens of Oracle instances on the same day.
  77. Are there any good strategies for backing up a data warehouse ?
  78. Why is the Comment_Text Column on my Sys.Dba_Audit_Trail Table is always empty ?
  79. I am getting error while restarting the database (9.0.1) after changing the parameter db_block_buffers in SPFILE.ora file. The error was “could not start the database with old and new parameter”. What is wrong ?
  80. I have audit_trail=db – but want to capture the program name from v$session as well. How can I do this ?
  81. Why do I keep getting Oracle error – ORA-1002: FETCH OUT OF SEQUENCE in my batch code ?
  82. I lock my table before truncating it, but I still get ORA-00054: resource busy … Why ?
  83. How do I determine the degree of fragmentation of a table ?
  84. How do I determine how much space I could reclaim by deleting a chunk of data from a table (and cleaning up afterwards)
  85. How do I decide on a sensible size for my redo log buffer
  86. I created a user with the wrong default tablespace – How can I move his entire schema from one tablespace to another.
  87. I have created a trigger on a table, and this seems to have wrecked the performance of some bulk inserts – why ?
  88. v$sysstat shows the number of sorts that have occurred. How do I find the number of hash joins that have occurred ?
  89. I have a table partitioned on column timestamp, with a primary key on column id. How do I create a locally partitioned index for this PK ?
  90. How do I calculate suitable values for PCTFREE and PCTUSED for a table.
  91. What is the best way to move a 500GB from 32 bit to 64 bit 9.2 ? exp/imp, mig, SQLLDR, or CTAS ?
  92. Is it only committed transactions that are written into the redo log ?
  93. I wish to update certain portions inside long column in 8.1.7. If this were a varchar2 field I would use the replace function. How do I achieve the same thing in with a long ?

———— PL/SQL ——————————–

  1. Is there a way to read the names of a set of files with a given extension from a directory as if from a SQL cursor?!q=tacos+near+mountain+view&data=!1m4!1m3!1d18254!2d-122.2026819!3d37.3473175!4m10!1m9!4m8!1m3!1d609023!2d-118.4117325!3d34.0204989!3m2!1i1440!2i1105!4f13.1

— 08/29/14 ———-
1. added sql snippet formatting by css in oracle questions 2.html in lessons folder.

Leave a Reply