converting TIMESTAMP(6) to TIMESTAMP(0)
Currently I have a column with datatype TIMESTAMP(6) but now i have a requirement to change it to TIMESTAMP(0).Because we cannot decrease the precision, ORA-30082: datetime/interval column to be modified must be empty to decrease fractional second or leading f... | 23 hours ago | 2 days ago |
Mail Restrictions using UTL_SMTP
Hi Tom,I have a requirement to send email to particular domain mail id’s. But My Mail server is global mail server we can send mail to any mail ids. Is there any options in Oracle to restrict the mail send as global.For example: My mail host is, mail.abc.com w... | 29 hours ago | 3 days ago |
problem of inserting a long string of characters
Hello Team , I'm trying to insert into a table " TEST COM " the result of selecting rows of another table.I used the wm_concat function ./**********/insert into COMMENTAIRE_TEST (SELECT wm_concat((DBMS_LOB.SUBSTR(COM_TEXTE,4000,1))) as testFROM comment... | 29 hours ago | 4 days ago |
What is the relationship of CPU, Memories against DB performances?
Hi Tom,Frequently I get asked quite a number of times when planning for a new server setup for the creation of databases. How much CPU cores should I get? How much Memories should I get.Normally I'll answer them, just get the highest cores & memories which you... | 30 hours ago | 2 weeks ago |
How to detect if insert transactions in oracle db are really slow?
At work, I have an Oracle DB (11g) in which I want to detect if there's slow performance while inserting data. Here's the situation:Some production devices send data results from tests to Server A, this server is a important server and it replicates the info t... | 30 hours ago | 4 days ago |
DBA_HIST_SQLSTAT and GV$SQL
Hi,I was trying to create a dashboard comparing historical executions and current executions of multiple SQL statements. I have noticed some differences between stats in GV$SQL and DBA_HIST_SQLSTAT. Could you please help us to understand below point.For eg: Fo... | 2 days ago | 3 days ago |
Need help in formulating query to fetch previous quote times
Hi AskTom Team, I have been a big fan of this site since 1999 around the time it came up. First of all, again a big Thank you for your support to Oracle Community since past two decades. I have immensely benefited from this. This time around I have a de... | 2 days ago | 4 days ago |
Partitioned table cleanup
HiI have a table that was created for debugging purposes. Every night a jobs kicks off creating a partition of the days inserts on the table based on date.Needless to say have the partitions grown rapidly and have taken up a lot space in the tablespace.What i... | 2 days ago | 3 days ago |
how to reset a sequence
Create sequence with no options, and the current value of the sequence is 10. Specify the statements in order to reset the sequence to 8, so that the next value will be generated after 8 is 11. Find out logic.... | 3 days ago | 3 days ago |
Log of switchover/failover/open
What data dictionary view can be used to determine the number of times that a switchover/failover/open has occurred for a standby database?... | 3 days ago | 8.9 years ago |
Difference Between Unique Index and Primary Key Index
IS fetching row(s) using Primary key Index (in where clause) is better then Fetching row(s) using Unique Index (in where clause)?Is there any internal difference between those Unique Index and Primary Key Index? ... | 3 days ago | 15.9 years ago |
connection pooling
Tom,What is connection pooling ? Please, can you give an example(s) that show thorough understanding of the subject matter as related to either ODBC OR JDBC application connections to the oracle database. Your site is more important and most value to the li... | 3 days ago | 13.9 years ago |
Use RESULT_CACHE in subqueries
Dear Tom,I am thinking to use the new feature "RESULT_CACHE" to optimize some search queries for my paginated pages.So far, for a search page I have :1.) a count query and 2.) the query that returns a page from the result setBoth 1 and 2 queries use a common s... | 3 days ago | 2.2 years ago |
Generating large json in 12.2 using json_object and json_arrayagg
Is it possible to get a result from the following query?<code>select JSON_OBJECT( KEY 'objects' VALUE (SELECT JSON_ARRAYAGG( JSON_OBJECT( KEY 'object_type' VALUE object_type, ... | 3 days ago | 9 months ago |
How i can optimize this operation DELETE if the values ares setted in codehard.
Hi, I'm a bit new to the development of plsql.I would like to know how I could optimize the delete operation with a forall if my query is the following: DELETE FROM SCH.TA_DELETE WHERE FIACUM < 1 AND FIPAIS = 1 AND FIPROC = 24 AND FI... | 4 days ago | 4 days ago |
After logon on database
<code>CREATE OR REPLACE TRIGGER LOG_T_LOGONAFTER LOGON ON DATABASEDECLARE osUser VARCHAR2(30); machine VARCHAR2(100); prog VARCHAR2(100); ip_user VARCHAR2(15);BEGIN SELECT OSUSER, MACHINE, PROGRAM, ora_client_ip_address INTO osUser, machine,... | 4 days ago | 7 days ago |
database links
how can i create database links to access remote databases.please tell me the procedure of creating database links. ... | 4 days ago | 17.9 years ago |
How to avoid repeated function call for multiple columns' values.
HiI'm refactoring an old procedure that calls a function for determining whether passed in values consist of only characters allowed in the front end app on top of the database. The procedure has a cursor that gathers all records it needs to check, loops throu... | 4 days ago | 7 days ago |
Get a JSON from a SQL query
Hello!Just a question. Is it possible to write a query that returns a JSON code?If yes, could you give me a brief example?Thanks!... | 4 days ago | 8 days ago |
SQLERRM:ORA-06531: Reference to uninitialized collection
<code>Hi I am facing this error. my script is like below. please suggest:/create table address_test(Addr_id number,addr_cus_id number,street_name varchar2(100),town varchar2(100),county varchar2(100),sub_county_state_province varchar2(100),country varchar2(100... | 4 days ago | 5 days ago |