DBA Blogs

DBMS_AQ.LISTEN to listen to a Single/Multi-Consumer Queue

Tom Kyte - Sun, 2018-01-14 11:06
Dear Experts, Need your guidance/suggestions to resolve this issue: Part of oracle advance queueing implementation, we've to dequeue the message as soon as it has been enqueued into the queue. This should happen immediately without any manual inter...
Categories: DBA Blogs

Doing DB upgrade RAC , via DBUA, from 11gr2 to 12cr2 . Using TDE (tablespace level) on source database

Tom Kyte - Sun, 2018-01-14 11:06
I am running a DB 11.2.0.4 (RAC db) that has TDE implemented - Tablespace level. Source db (11.2.0.4) has TDE implemented. sqlnet.ora file on each node has the entry ENCRYPTION_WALLET_LOCATION. Also each node has the wallet and auto login file (t...
Categories: DBA Blogs

Audit Trail : Disable my bash script audit

Tom Kyte - Sun, 2018-01-14 11:06
Hello Tom. I set audit trail to "XML,EXTENDED" , because my $AUD table was growing to much. I have a lot of 4kb files generated. I have several scripts in my crontab, and that is what is being audited. The content of the files are like this:...
Categories: DBA Blogs

YTD logic using analytic functions

Tom Kyte - Sun, 2018-01-14 11:06
Hi Tom, I am trying to get YTD in a view. I have below view, <code>create or replace view billsummary as select szRegionCode, szState, szPartitionCode, szProduct, TO_CHAR(dtSnapshot,'YYYY.MM') szMonthYear, szJioCenter, ...
Categories: DBA Blogs

Configuring a SQL Loader control File to exclude the second row

Tom Kyte - Sun, 2018-01-14 11:06
Hi, I am trying to configure a control file that excludes the second line of data from the load. The system is automated and I have been tasked to see if there is a solution to this. I am very new at this. I have been told about a discard file of ...
Categories: DBA Blogs

Dynamic query to print out any table

Tom Kyte - Sun, 2018-01-14 11:06
Hi Tom How i can use procedure have a parameter type of query 'any query' and print the data looks like comma separated ? please help ..
Categories: DBA Blogs

Truncate statement in data dictionary,

Tom Kyte - Sun, 2018-01-14 11:06
Hello, I have observed truncate statement (command_type = 85) doesn't appear in V$SQL. However, it does in V$SQLTEXT and V$SQLTEXT_WITH_NEWLINES. My intention is to extract the time of the truncate statement. How can I achieve this task witho...
Categories: DBA Blogs

Tracking User logins between 7:00 pm and 7:00 am

Tom Kyte - Sun, 2018-01-14 11:06
Hello Sir, I have a requirement to track and generate a report of the users logging into the database after office hours, i.e., between 7:00 pm and 7:00 am on a daily basis. We have audit_trail set to 'DB' I appreciate if you can help me in ...
Categories: DBA Blogs

Loading CLOB Columns from File

Tom Kyte - Sun, 2018-01-14 11:06
Loading table data from external source to oracle for data mining and analysis. One particular table has 7 8000 byte character fields which must be loaded into CLOB columns. Most of these are empty. The data is provided as a tab delimited text file w...
Categories: DBA Blogs

Not getting connection with database in cmd window

Tom Kyte - Sun, 2018-01-14 11:06
Hi, I have just installed the Oracle Database 11g Express Edition from www.oracle.com. When I was connecting database in command prompt window I am getting error while entering paasword for user name "system". I am typing below the exact error w...
Categories: DBA Blogs

Global temporary table clears on commit

Tom Kyte - Fri, 2018-01-12 22:26
Hi, Please, help me to understand - whats happenging?: I've GTT with ON COMMIT PRESERVE ROWS and after inserting values with procedure: " insert into gtt ... select ...; commit; " next sql " select * from gtt " returns nothing!!! but,...
Categories: DBA Blogs

Fast wild-card searching

Tom Kyte - Fri, 2018-01-12 22:26
What is the best way to implement a solution to the following problem so that wild-card searching is very fast. PROBLEM: Two column tabular data with ~100 million rows of the form given below. Searching is on the first column. The number of sear...
Categories: DBA Blogs

Make an aggregate for statistics

Tom Kyte - Fri, 2018-01-12 22:26
Hello Tom. I've to translate a mathematical formula from "Google Sheet" to a "trigger in Oracle". A part of this formula in Google sheet is <b>MOYENNE(3;1;MAX(1;1;9;1);MAX(1;1;9;1))</b> I know translate this formula like that : <code>SELE...
Categories: DBA Blogs

Possibility of functional index/key in foreign key

Tom Kyte - Fri, 2018-01-12 22:26
Hi, I want to ask if there is possibility to use functional index/key in foreign key. As an example : In T1 there is opera_user_id column, with index upper(opera_user_id), in T2 a column ID, indexed as PK. From T1 there is FK defined by the column ...
Categories: DBA Blogs

Index on null column

Tom Kyte - Fri, 2018-01-12 22:26
We have created a functional index on one column which is always null. Other application queries our table with this column and query take more than 3 min to complete.. Table - My_Table Columns -UserId, UserName,ChinesName, Country Index - lowe...
Categories: DBA Blogs

Automate export of result data to defined path

Tom Kyte - Fri, 2018-01-12 22:26
Hi, I am using Oracle 11g and mostly works on tasks which requires data extraction for clients. I have been trying to automate the process of extraction and I found the below code in one of your answers. <code> --------------------------------...
Categories: DBA Blogs

How the compression works for Direct path loading with Parallel session

Tom Kyte - Fri, 2018-01-12 04:06
Hi Team, We have implementing the row level compression with applying the Advance compression at tablespace level. but I have observed that the my table size is getting higher even though compression level is Advance. We are loading the data fro...
Categories: DBA Blogs

Is there a way to search .fmb or .rdf for a string

Tom Kyte - Fri, 2018-01-12 04:06
Is there a way to search .fmb or .rdf for a string outside of the development tools?
Categories: DBA Blogs

Anything better than UNION

Tom Kyte - Fri, 2018-01-12 04:06
Hi TOM, First of all, Thank you very much for helping the Oracle community. Well, I write SQL queries, but they are limited to entry level only. My question is related with UNION. Below is my SQL query. It works fine, but I am struggling to find a...
Categories: DBA Blogs

"KISS series on Analytics: 17 The LAG / LEAD clauses" - returning wrong FROM_DATE and TO_DATE

Tom Kyte - Fri, 2018-01-12 04:06
Hello AskTOM, this is referring to "KISS series on Analytics: 17 The LAG / LEAD clauses": https://youtu.be/r7AM-1qX7Vs The testcase for that can be found in https://livesql.oracle.com/apex/livesql/file/content_CZUCT0MCOQZMJM7TI553HC8S9.html I ha...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs