Jump to content

All Activity

This stream auto-updates     

  1. Last week
  2. tipsu

    Multiset

    Hi Dearest Mr.Burleson! Wondering if You can give me some advice on the subject.. 12.1.0.2 db. package call spinning. profiler showed the particular place where it on cpu : list_a := all_list multiset union distinct price_list_a; We planning to re-write the block, are You aware on limitations of CBO/func in oracle of multisets ? Thx! L.
  3. burleson

    10046 trace

    Hi Raj, and welcome to the forum! Depending on your OS, you will find the file in your trace/dump directories. see here for details; http://www.dba-oracle.com/t_find_trace_file.htm Hope this helps. . .
  4. Raj314

    10046 trace

    Hi, I am not able to find trace file after applying 10046 trace to session of particular Schema. Please find screen shot of trigger.
  5. burleson

    AWR snapshots on ADG

    Hi Zaw, and welcome to the forum! You can easily check your AWR setting on the ADG instance: http://www.dba-oracle.com/t_awr_automatic_snapshot_settings_modify.htm AWR is usually enabled at install time and you can check your retention period. Please read: http://www.dba-oracle.com/t_awr_retention_period.htm Hope this helps . . .
  6. Earlier
  7. zaw

    AWR snapshots on ADG

    1. I have my primary database on 11.2.0.4.0 and have an Active data guard 11.2.0.4.0 setup as well. 2. I have given the ADG connect strings to my users who run quiet large reports on daily/weekly bases on ADG. 1. Will there be AWR snapshot running on my ADG? 2. I try to use awrrpti.sql but it only show primary database ID and instance ID Thanks
  8. Hi, and welcome to the forum! First you need to test your connectivity via tnspimg and sqlplus. Please read: http://www.dba-oracle.com/teas_prae_util23.htm If you can connect in sqlplus, you will need to check other factors: http://www.dba-oracle.com/t_enterprise_manager_unable_to_connect_to_db_instance.htm Finally, see MOSC, there are known issues with Firefox in 12c: http://support.oracle.com Hope this helps. . .
  9. hi to all hi to all, i've centos 7.5 in VM. i installed oracle 12c 12.2.0.1 in centos linux 7.5. after installation. it suggested to connect with oracle server via https://server1.db.net:5500/em but firefox showing a message " unable to connect."
  10. burleson

    Index Full Scan

    Hi Vidhu, and welcome to the forum! Based the specific where clause in your SQL, the optimizer realized that an index range scan was less expensive than an index fast full scan. Also, what release are you on, and what are your optimizer parameter values? http://www.dba-oracle.com/t_sql_optimizer_parameters.htm You can verify that the optimizer made the best choice by forcing an index fast full scan with the index_ffs hint! http://www.dba-oracle.com/t_index_fast_full_scan.htm Hope this helps . . .
  11. vidhuvs

    Index Full Scan

    Hi Team, As per Oracle docs When the Optimizer Considers Index Full Scans The optimizer considers an index full scan in a variety of situations. The situations include the following: A predicate references a column in the index. This column need not be the leading column. No predicate is specified, but all of the following conditions are met: All columns in the table and in the query are in the index. At least one indexed column is not null. A query includes an ORDER BY on indexed non-nullable columns. Now to check this scenario I performed the following CREATE TABLE Test_Table ( Eid NUMBER NOT NULL, First_Name VARCHAR2(100) NOT NULL, Last_Name VARCHAR2(100) NOT NULL, Description VARCHAR2(100) NULL ); BEGIN FOR counter IN 1 .. 10000 LOOP INSERT INTO Test_Table (EId, First_Name, Last_Name, Description) VALUES (CustomerSequence.NEXTVAL, DBMS_RANDOM.STRING('A', 10), DBMS_RANDOM.STRING('A', 20), DBMS_RANDOM.STRING('A', 100)); END LOOP; END; / CREATE INDEX test_tbl_idx1 ON Test_Table (eId, first_name); However when I generated the explain plan for the below query it showed Index range scan instead of Index Full Scan. Can you please explain why? SELECT * FROM Test_Table where eid = 40005; Because as per Oracle docs "A predicate references a column in the index. This column need not be the leading column." should show Index Full scan. Thanks, Vidhu V S
  12. ID is a broken key, I need reset for new id
  13. does ord reset for a new id? so if the next row in your data set were ID = 2, or would ord be 8?
  14. create table TEST_LAST_VALUE ( ID NUMBER(2), VAL NUMBER(5), ORD NUMBER(2) ); INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 1000, 1); INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 1000, 2); INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 2000, 3); INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 2000, 4); INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 2000, 5); INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 1000, 6); INSERT INTO TEST_LAST_VALUE (id, val, ord) VALUES (1, 1000, 7); COMMIT; SELECT K.ID, K.VAL, K.ORD FROM ( SELECT T.*, LAST_VALUE(ORD) OVER (PARTITION BY ID, VAL ORDER BY ORD ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LV FROM TEST_LAST_VALUE T ) K WHERE K.ORD = K.LV ORDER BY 1,2
  15. Thank you, glad to be here The table is small, 1.7 million records. The CTAS from a straight select of the external staging table completes fast, so disk IO is not an issue here I think. The issue is creating that intermediary table, using the script i uploaded originally, that one was the one that was never completing. The only thing being done there was a regex_substr on a CLOB, and a to_char from that CLOB. I've had issues with this server and clobs before, specifically to_char conversion from clob. I found it amazing that after playing with the memory settings, the CTAS went from never finishing, to actually doing something. I'm convinced there's a configuration problem in this server, but I don't know where to start.
  16. Hi, and welcome to the forum! Just curious, how big was this table? A CTAS always does a full table scan, but not setting db_cache_size defaults to only 64K! Always remember, Disk I/O is in milliseconds, the most expensive operation in any database! Hope this helps!
  17. Hi Franz, and welcome to the forum! Can you please post your query syntax? This might help, have a gander: http://www.dba-oracle.com/t_advanced_sql_first_last_value.htm
  18. I decided to run the CTAS command again, and leave it running with some metrics, looks like it did complete, after 1 hour, 8 minutes. Definitely an improvement over never, but still pretty slow. Looking forward to some advice from the experts, thanks!
  19. I wanted to start out by saying that I, by no means, consider myself a DBA, but I've been put into a situation where more often than not, I have to fix my own problems...I guess that's how everyone gets started? That being said, the Burleson website has been a great tool for me through my years of learning. I'm having an issue with one of our oracle servers, a simple CTAS script refuses to complete. Everything I could find points to a memory issue, I've been able to replicate the issue on two different servers, both servers have similar specs hardware wise, and both are running a similar DB configuration, in terms of data and config. Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production (Prod) Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production (Test) I left the query running 8 hours, and SQL Developer would eventually hang, thinking it was still processing, but EM showed the job as failed. I made a few changes, and again left it running over night, same result, the query would never finish in SQL Developer, but it had actually failed and not finish running. At one point, while trying to figure out the issue, I also received ORA-12805: parallel query server died unexpectedly, I decided to remove all parallel hints from the CTAS and from the source table in the select. However, the issues persisted After reviewing the articles below, I decided to look into potential memory issues on the Oracle side: https://lessonsfromoracle.wordpress.com/2014/02/23/parallel-query-server-died-unexpectedly-but-why/ http://www.dba-oracle.com/oracle11g/oracle_11g_memory_target_parameter.htm Starting out on the 11g server, I noticed that db_cache_size was 0, sga_target was 0. memory_max_target was 6560M and memory_target were 6560M, the server has 8GB of memory. In trying to fix the db_cache_size, I issues the following commands: ALTER SYSTEM SET MEMORY_TARGET = 0; ALTER SYSTEM SET db_cache_size = 2G; ALTER SYSTEM SET SGA_TARGET = 0; ALTER SYSTEM SET MEMORY_TARGET = 6560M; I left the query running, and went to lunch, half an hour later when I returned, to my surprise, the query had completed, I don't exactly how long it took to complete, but I wasn't gone long. I was able to finish the archiving task and proceeded to try and replicate the fix on the 12c server....to no avail. I then went back to the 11g server, reran the CTAS script, and of course...now it refuses to complete again. I've pretty much given up here, I played a little bit more with the memory settings, but I'm completely lost at this point. Hoping for an assist from the group, I'm attaching some details to this post, and I'll be willing to provide anything else you guys ask for! ADDITIONAL INFO.txt
  20. I have a table like this: ID | Val | Ord ---------------------- 1 | 1000 | 1 1 | 1000 | 2 1 | 2000 | 3 1 | 2000 | 4 1 | 2000 | 5 1 | 1000 | 6 1 | 1000 | 7 I want to make a SELECT that will return Sample output: ID | Val | Ord ---------------------- 1 | 1000 | 2 1 | 2000 | 5 1 | 1000 | 7 the output I can produce is ID | Val | Ord ---------------------- 1 | 1000 | 7 1 | 2000 | 5 I would like to use the last_value function but I have problems when the value assigned to the same key repeats alternated with a different value How can I build this query? Thanks for your help Franz
  21. burleson

    Advantage of Oracle Goldengate over Streams

    Hi, and welcome to the forum! Good question! Goldengate is like a Ferrari, and Streams is a Volkswagen. They will both get you to your destination, but in different ways! See here: http://dba-oracle.com/googlesearchsite_proc.htm?cx=000522505899594707971%3A4-ldikxixw4&cof=FORID%3A10&ie=UTF-8&q=Streams+GoldenGate+&sa=Search&siteurl=www.dba-oracle.com%2Ft_streams_desupported_goldengate.htm&ref=www.google.com%2F&ss=9595j6481019j18 Good luck!
  22. What is the advantage of Goldengate over Stream? Oracle Goldengate has high license cost compared to Streams. So, why an organization should use Goldengate for their data replication need and not Streams? Does Goldengate has advantage, which is worth the high license cost?
  23. burleson

    Exporting 700gb data - Recommendations Needed

    Hi again, What is the mapping of data files to tablespaces to tables? I would expect that this monster table resides in it’s own tablespace, and the tablespace consists of many data files. Please advise . . . Thanks!
  24. burleson

    RMAN bkp full "stupid" question

    Hi, The rule is to only nuke archived redo logs after you have a fully recoverable savepoint. Disk is super cheap, just keep them!
  25. burleson

    Exporting 700gb data - Recommendations Needed

    Hi Richard, and welcome to the forum! Wow! 700 gig is a monster! expdp is not a fast table copy method. Will making a copy of the table over a database link work for you? http://www.dba-oracle.com/t_fast_copy_data_oracle_table.htm If the table is in it’s own tablespace, then it is easier! Did you set parallelism to cpu_count-1? http://www.dba-oracle.com/t_optimal_parallel_degree.htm Sad to say, but “time takes time”, but I don’t know how the data blocks are laid out? Are you using ASM? Partitioned tables? This is a tough question! I have a fast clone method: http://www.dba-oracle.com/oracle_tips_db_copy.htm But maybe you need a third party tool like Delphix? I have never used it, but I heave that it is great for high speed data cloning. https://www.delphix.com/blog/data-virtualization/what-delphix Once you get a copy, have you considered SSD storage? Copying from SSD is 300 to 600 times faster than platter disk: http://www.dba-oracle.com/t_ssd.htm Hope this helps. . .
  26. We have a table that contains blobs (images). It's about 700gb worth and we need to export it. Transportable tablespace is out. We're using Data Pump. DB is 11.2. I don't see this version of expdp having the old 'consistent=n'. It's okay that the export is not consistent. We're also using parallel. Testing 10% of the export (2 1/2 hrs) computes to about a 28-hour export for the entire table. We're looking for recommendations on speeding this up. Thanks in advance!!
  27. ORA-LO

    RMAN bkp full "stupid" question

    Thank you, in my "test case" i use daily FULL HOT BKP ; so my question is: what happen if i delete some archive between this FULL BKP? if i understand what you wrote ahead i think the backup and eventually restore to that point in time is valid. What happen instead the delete of the archive happens between an L0 FULL BACKUP and an L1 incremental backup? is better redo an L0? THX again.
  1. Load more activity
×