DBA Blogs

I dropped a table in oracle but when i saw the indexes became like 'BIN$...' i rebuild them the state is still VALID

Tom Kyte - Sun, 2017-12-03 23:46
I dropped the table with cascade option, after importing the table the indexes are there with BIN$... name and the state is VALID. Are they really valid i try to rebuild its rebuilding but name is not changing.
Categories: DBA Blogs

Forms Builder won't connect to database - ORA 28040

Tom Kyte - Fri, 2017-12-01 16:46
I've installed an Oracle 12.2 database on my PC running Windows 10. That seems to be fine and I can connect to the database and the sample HR database is all there. I have then installed the Oracle Developer Suite v10.1.2. That installation appears t...
Categories: DBA Blogs

Service and module

Tom Kyte - Fri, 2017-12-01 16:46
Dear Team, Hope doing well..!!!! please help me to understand difference between service and module in oracle database. Whats use of module? Thanks Pradeep
Categories: DBA Blogs

Enters Amazon Aurora Serverless

Pakistan's First Oracle Blog - Thu, 2017-11-30 23:06
More often than not, database administrators around the technologies have to fight out high load on their databases. It could be ad hoc queries, urgent reports, overflown jobs, or simply high frequency and volume of queries by the end users.

DBAs try their best to do a generous capacity planning to ensure optimal response time and throughput for the end users. There are various scenarios where it becomes very hard to predict the demand. Storage and processing needs in case of unpredictable load are hard to foretell in advance.





Cloud computing offers the promise of unmatched scalability for processing and storage needs. Amazon AWS has introduced a new service which gets closer to that ultimate scalability. Amazon Aurora is hosted relational database service by AWS. You set your instance size and storage need while setting Aurora up. If your processing requirements change, you change your instance size and if you need more read throughput you add more read replicas.

But that is good for the loads we know about and can more ore less predict. What about the loads which appear out of blue? May be for a blogging site, where some post has suddenly gone viral and it has started getting million of views instead of hundreds? And then the traffic disappears after some time suddenly just like it appeared out of nowhere and may be after some days it happens for some another post?

In this case if you are running Amazon Aurora, it would be fairly expensive to just increase the instance size or read replicas in the anticipation that some traffic burst would come. It might not, but then it might.

In front of this uncertainty, enters Amazon Aurora Serverless. With this Serverless Aurora, you don't select your instance size. You simply specify an endpoint and then all the queries are routed to that endpoint. Behind that endpoint lies a a warm proxy fleet of database capacity which can scale as per your requirements within 5 seconds.

It's all on-demand and ideal for transient spiky loads. What's more sweet is that billing is on second by second basis and deals in Aurora capacity units and minimum is 1-minute for each newly address resource.
Categories: DBA Blogs

Oracle provider for OLE DB (OraOLEDB) 11.2.0.1.0 unable to connect to Oracle DB 10 Release 2

Tom Kyte - Thu, 2017-11-30 22:26
I have installed Oracle provider for OLE DB (OraOLEDB) 11.2.0.1.0 on a server to allow our SIEM to connect to our customer's Oracle DB 10G R2 for monitoring purpose. However, I'm still getting error saying "ORA-12541: TNS:no listener" when I test...
Categories: DBA Blogs

pragma autonomous_transaction; and database links

Tom Kyte - Thu, 2017-11-30 22:26
I have a package of functions that return data from a SqlServer database through a link. Usually the results are just displayed in optional fields on a web page or client program. They take the form of: <code> function get_info(ar_key number) ...
Categories: DBA Blogs

How can i read a csv file

Tom Kyte - Wed, 2017-11-29 09:46
Hi TOM :) Resourse: 1) i have a table that was milions of records of the clients 2) and i have a CSV with only 1,200 clients 3) i don't have permitions to create a table. Problem: how can i read from the CSV to join with the pr...
Categories: DBA Blogs

UTL_FILE or EXTERNAL Table operation on files in Application Server

Tom Kyte - Tue, 2017-11-28 15:26
Hi Tom, We are running Oracle EBS R12.2.6. We have application server and database server hosted on two separate physical servers. They are not having any shared file locations. We are receiving the inbound files from a third party system in...
Categories: DBA Blogs

Validate constraint recursively run a SELECT with an "ordered" hint

Tom Kyte - Tue, 2017-11-28 15:26
I have been looking into minimizing the time it takes to validate a referential constraint. We want it to be validated for the optimizer to utilize join elimination during query rewrite. However, for a big table it takes a lot of time to validate ? a...
Categories: DBA Blogs

Oracle Live SQL

Tom Kyte - Mon, 2017-11-27 21:06
Hello, Is there a way I could run explain plan on Oracle Live SQL? Apparently, it gives an error that PLAN_TABLE doesn't exist. I ran utlxplan.sql in my session but still could not execute statement SELECT * FROM TABLE(DBMS_XPLAN.display) W...
Categories: 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

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs