DBA Blogs

Data Compression and Maintenance after archival

Tom Kyte - Mon, 2017-11-27 21:06
Hi , We have implemented archival solution for a client and now exploring further actions. Initial data size was around 25 TB's with major tables partitioned monthly. After archival, the size of active DB is around 13 TB's and archival DB is aroun...
Categories: DBA Blogs

Dynamic Select with Cursor type

Tom Kyte - Mon, 2017-11-27 21:06
How can i get value from sql using dbms_sql type of CURSOR ....COL_TYPE=102 <code>DECLARE RUN_S CLOB; IGNORE NUMBER; SOURCE_CURSOR NUMBER; PWFIELD_COUNT NUMBER DEFAULT 0; L_DESCTBL DBMS_SQL.DESC_TAB2; Z...
Categories: DBA Blogs

sys password change and orapwd file

Tom Kyte - Mon, 2017-11-27 02:46
Hi Tom, What is the relation between SYS user password and orapwd file ? What are the various methods to change the SYS user password. If I change the SYS user password using from sqlplus use password command or use alter user ...
Categories: DBA Blogs

SYS, SYSDBA, SYSOPER, SYSTEM

Tom Kyte - Mon, 2017-11-27 02:46
Hi I am very new to oracle. I have installed Oracle 10g compatible with windows vista. I am confused with what are exactly, SYS, SYSDBA, SYSOPER and SYSTEM? How they differ and what is the specific purpose of these automatically created accounts w...
Categories: DBA Blogs

email in PL/SQL

Tom Kyte - Fri, 2017-11-24 19:46
Tom: 1. If you want to write something in PL/SQL application server that send email automatically to an administrator requesting approval after a user create a record, which oracle package would be using. Would you also use an after insert tri...
Categories: DBA Blogs

How to define a composite primary key

Tom Kyte - Fri, 2017-11-24 19:46
I have a table with two columns that need to be defined as primary keys, order_number and item_number. A table can have only one primary key, but I can define composite primary keys in the table_constraint syntax. How do I define two columns ...
Categories: DBA Blogs

Why SUM(USER_BYTES) in DBA_DATA_FILES is much larger than SUM(BYTES) in dba_free_space ?

Tom Kyte - Thu, 2017-11-23 07:06
Hello, teams:-) Why SUM(USER_BYTES) in DBA_DATA_FILES is much larger than SUM(BYTES) in dba_free_space ? There has an example that I have given in Oracle 11.2.0.4.0. <code> SYS@orcl28> select round(sum(user_bytes)/(1024*1024*1024),2) fro...
Categories: DBA Blogs

Sending HTML using UTL_SMTP

Tom Kyte - Thu, 2017-11-23 07:06
Hi Tom I hope I'm phrasing this correctly... I'd like to be able to send an HTML formatted email from the database using the UTL_SMTP package. I don't see any way of setting the MIME type. Is this beyond the scope of UTL_SMTP? thanks in ...
Categories: DBA Blogs

Conditional index

Tom Kyte - Wed, 2017-11-22 12:46
Tom, Thanks for taking my question. I am trying to conditionally index rows in a table. In SQL Server 2008 there is a feature called filtered indexes that allows you to create an index with a where clause. So I have a table abc: <code>create...
Categories: DBA Blogs

Transfer data from one db to another db over db link using trigger

Tom Kyte - Wed, 2017-11-22 12:46
Hi, I am working on a project in which data marts are involved. We are creating triggers to transfer data from OLTP DB to data mart (Online extraction). Following is the code of a trigger for a table involving clob column. I have seen different solut...
Categories: DBA Blogs

Query Flat Files in S3 with Amazon Athena

Pakistan's First Oracle Blog - Tue, 2017-11-21 21:01
Amazon Athena enables you to access data present in flat files stored in S3 (Simple Storage Service) as if it were in a table in the database. That and you don't have to set up any server or any other software to accomplish that.

That's another glowing example of being 'Serverless.'


So if a telecommunication has hundreds of thousands or more call detail record file in CSV or Apache Parquet or any other supported format, it can just be uploaded to S3 bucket, and then by using AWS Athena, that CDR data can be queried using well known ANSI SQL.

Ease of use, performance, and cost savings are few of the benefits of AWS Athena service. True to the Cloud promise, with Athena you are charged for what you actually do; i.e. you are only charged for the queries. You are charged $5 per terabyte scanned by your queries. Beyond S3 there are no additional storage costs.

So if you have huge amount of formatted data in files and all you want to do is to query that data using familiar ANSI SQL then AWS Athena is the way to go. Beware that Athena is not for enterprise reporting and business intelligence. For that purpose we have AWS Redshift. Athena is also not for running highly distributed processing frameworks such as Hadoop. For that purpose we have AWS EMR. Athena is more suitable for running interactive queries on your supported formatted data in S3.

Remember to keep reading the AWS Athena documentation as it will keep improving, lifting limitations, and changing like everything else in the cloud.
Categories: DBA Blogs

RMAN and archivelogs

Tom Kyte - Tue, 2017-11-21 18:26
Hi, I have read quite a bit on Oracles RMAN utility and know that for hot backups RMAN doesn't use old method of placing tablespaces in Archive log mode freezing datafile headers & writing changes to Redo/ Archive logs. Hence a company with a larg...
Categories: DBA Blogs

Difference between "consistent gets direct" and "physical reads direct"

Tom Kyte - Tue, 2017-11-21 18:26
Hi Tom/Team, Could you explain the difference between "consistent gets direct" and "physical reads direct"? Thanks & Regards
Categories: DBA Blogs

Partner Webcast – Identity Management Update: IDM 12c Release

Oracle Identity Management, a well-recognized offering by Oracle, enables organizations to effectively manage the end-to-end lifecycle of user identities across all enterprise resources, both within...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Partner Webcast – Recovery Appliance (ZDLRA) - Data protection for Oracle Database

Today’s solutions for protecting business data fail to meet the needs of mission critical enterprise databases. They lose up to a day of business data on every restore, place a heavy load on...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Import data from Flat File to two different Table using UTL_FILE.

Tom Kyte - Tue, 2017-11-21 00:06
Hi Please help this Question. Import data from Following Flat File to two different Table using UTL_FILE. a. EMP and b. DEPT Note --- 1. In Last Line NULL Employee Should not Entry into Table. 2. Deptno Should go to both the Table EMP a...
Categories: DBA Blogs

Repeating parent-nodes in hierarchical query

Tom Kyte - Tue, 2017-11-21 00:06
Hello AskTOM Team, with the schema as provided in the LiveSQL Link (which is simply the example EMP/DEPT schema), I ran the following query <code> select case when LEVEL = 1 then ENAME else rp...
Categories: DBA Blogs

What causes a materialized view to get invalidated

Tom Kyte - Mon, 2017-11-20 05:46
Hello, I have a materialized view whose definition looks like this: CREATE MATERIALIZED VIEW <owner>.<materialized view name> (<column list>) TABLESPACE <tablespace name> PCTUSED 0 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (...
Categories: DBA Blogs

Distributed Option in Oracle 7

Tom Kyte - Mon, 2017-11-20 05:46
Hi Tom! Is Oracle Distributed Option required for accessing remote databases? Also, if there is some restriction on database version i.e. the version shuld be same on all nodes?
Categories: DBA Blogs

BEFORE Triggers Fired Multiple Times

Tom Kyte - Sat, 2017-11-18 17:06
Dear Tom, I have a question about triggers execution: according to documentation at http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1169 "BEFORE Triggers Fired Multiple Times If an UPDATE or DELETE ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs