DBA Blogs

ORA-06533: Subscript Beyond Count error

Tom Kyte - Mon, 2019-03-18 18:06
Hi I have the following PLSQL code - if run 1st time - it works fine - running 2nd or 3rd time it fails with "Subscript beyond count" error If I make the declaration of g_response private to the procedure (not globally in the package) - it works...
Categories: DBA Blogs

Partner Webcast – Oracle Cloud Business Analytics Data Visualizations

Providing fast and flexible analysis of any data from any source is a business requirement these days. Oracle Analytics Cloud is a cloud-first analytics platform, built on the industry-leading Oracle...

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

Data Pump Exit Codes

Learn oracle 12c database management - Mon, 2019-03-18 11:48


oracle@Linux01:[/u01/oracle/DPUMP] $ exp atoorpu file=abcd.dmp logfile=test.log table=sys.aud$
About to export specified tables via Conventional Path ...
. . exporting table                           AUD$     494321 rows exported
Export terminated successfully without warnings.

oracle@qpdbuat211:[/d01/oracle/DPUMP] $ echo $?
0


oracle@Linux01:[/u01/oracle/DPUMP] $ imp atoorpu file=abcd.dmp logifle=test.log
LRM-00101: unknown parameter name 'logifle'

IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
IMP-00000: Import terminated unsuccessfully

oracle@Linux01:[/u01/oracle/DPUMP] $ echo $?
1
Can be used in export shell scripts for status verification:
if test $status -eq 0
then
echo "export was successfull."
else
echo "export was not successfull."
fi
Also check below page fore reference :
Categories: DBA Blogs

Automate recyclebin purge in oracle

Learn oracle 12c database management - Mon, 2019-03-18 11:46


Setup this simple scheduler job as sysdba to purge the objects in the recycbin.
This is one of the most space cosuming location that often dba's forget to cleanup and the
objects get piled up occupying lot of space. Based on how long you want to save these dropped object setup a job under scheduler to run below plsql block either daily, weekly or monthly. 


 I suggest to run weekly.


--For user_recycbin purge--
-- plsql --

declare
VSQL varchar2(500);
begin
VSQL:='purge user_recyclebin';
execute immediate VSQL;
dbms_output.put_line('USER RECYCLEBIN has been purged.');
end;

/




--For dba_recycbin purge--
-- plsql --

declare
VSQL varchar2(500);
begin
VSQL:='purge dba_recyclebin';
execute immediate VSQL;
dbms_output.put_line('DBA RECYCLEBIN has been purged.');
end;





Prerequisites
The database object must reside in your own schema or you must have the DROP ANY ... system privilege for the type of object to be purged, or you must have the SYSDBA system privilege. To perform the PURGE DBA_RECYCLEBIN operation, you must have the SYSDBA or PURGE DBA_RECYCLEBINsystem privilege.
Categories: DBA Blogs

Monitoring Database in AWS Aurora After Migrating from Oracle to PostgreSQL

Pakistan's First Oracle Blog - Fri, 2019-03-15 19:08
Suppose you have an Oracle database on-premise, which you have now moved over to AWS Cloud in AWS Aurora PostgreSQL. 
For your Oracle database, you have been using v$ views to monitor your runtime performance of instance, long running operations, top SQLs from ASH, blocking etc. How do you continue doing that when you migrate your database to cloud especially in AWS Aurora based PostgreSQL?

Well, PostgreSQL provides statistics collection views, which is a subsystem that collects runtime dynamic information about certain server activities such as statistical performance information. For example, you can use  pg_stat_activity view to check for long running queries.

There are various other statistics views too in PostgreSQL such as pg_stat_all_tables to see size of table, access method like FTS or index scan, and so on. There are other views to check IO on tables and indexes and plethora of others.

In addition to these statistics views, Aurora PostgreSQL provides a nifty tool called as Performance Insights. Performance insights monitors Amazon RDS or Aurora databases (both MySQL and PostgreSQL) and captures workloads so that you can analyze and troubleshoot database performance. Performance insights visualizes the database load and provides very useful filtering using various attributes such as: waits, SQL statements, hosts, or users.

As part of operational excellence, its imperative after a database migration that performance is monitored, documented and continuously improved. Performance Insights and the statistics views are great for proactive and reactive database tuning in AWS RDS and AWS Aurora.
Categories: DBA Blogs

Explicitly providing values in a WHERE clause showing much better performance compared to using sub query

Tom Kyte - Fri, 2019-03-15 16:46
Hi I am new to oracle and not sure how to provide the liveSQL link. I have 2 tables to join huge_table contains about 1 billion rows big_table contains about 100 million rows and small tables contains 999 rows providing the condition to fil...
Categories: DBA Blogs

Compare columns in two tables and report which column is different

Tom Kyte - Fri, 2019-03-15 16:46
Compare columns in two tables and list out column names which are different. for ex:- create table t1(c1 number(2), c2 varchar2(10)); create table t2(c1 number(2), c2 varchar2(10)); insert into t1 values(1,'a'); insert into t2 values(1,'b'); result ...
Categories: DBA Blogs

Taking More Indexing Time on ORACLE database While Performing QUERYS

Tom Kyte - Thu, 2019-03-14 22:26
Hi Team, Very impressive for your way of explaining. So we have ORACLE db production server,from that we are trying to perform indexing with 40 Querys on every day.For Example i am showing few Querys:: <code>DROP INDEX GINQCON_ix1 FORCE; ...
Categories: DBA Blogs

I want to move from DBA to Developer, how to develop my skills

Tom Kyte - Thu, 2019-03-14 22:26
I am working in my DBA role for the past 5 years,vexed with on call support,shift 24*7. I want to switch to developer role and much more I am interested in it. I also want to know the roles and responsibilities what do developer do, which tool...
Categories: DBA Blogs

Number of Chunks in DBMS_PARALLEL_EXECUTE

Tom Kyte - Thu, 2019-03-14 22:26
Hi TOM, I am having a data movement activity from one table to another table. Due to Huge data size in both tables, i have planned to use DBMS_PARALLEL_EXECUTE functionality, so that i can finish it quickly. I am creating chunks based on ROWID. ...
Categories: DBA Blogs

Is there a nowait clause for an insert statement

Tom Kyte - Thu, 2019-03-14 22:26
I have inserted the same primary key data but the Second Session is locking forever! Is there an option to alter user session to raise timeout after 60seconds or NOWAIT for the second sessions INSERT command. In our environment session 1 is a lo...
Categories: DBA Blogs

A table has a owner but also a creator?

Tom Kyte - Thu, 2019-03-14 22:26
Hello Masters, I saw something weird last time and I need your expertise to understand. I create two users. <code>[ora1210[XXX] /home/ora1210]$ sqlplus / as sysdba SQL> create user ZZ01 identified by xxxxxxxxxxxxxxxxxxxx; SQL> create...
Categories: DBA Blogs

Slides from March AZORA meeting

Bobby Durrett's DBA Blog - Thu, 2019-03-14 16:27

Here are the slides from our March Arizona Oracle User Group (AZORA) meeting:

Daniel Morgan Security Master Class

We really appreciate Daniel Morgan taking the time to share this information about the increasingly important topic of database security.

Also, AZORA is always looking for people to present at future meetings. We have one more meeting in May before the blazing hot Arizona summer and then we start up again in September. Email be at bobby@bobbydurrettdba.com if you would like to speak at a future meeting.

Bobby

Categories: DBA Blogs

Announcement: “Oracle Performance Diagnostics and Tuning” Seminar – Australia/NZ Winter Dates

Richard Foote - Wed, 2019-03-13 22:57
I’m very excited to announce the first public running of my new “Oracle Performance Diagnostics and Tuning” Seminar throughout Australia and New Zealand this coming winter 2019. (See my Oracle Performance Diagnostics and Tuning Seminar page for all the seminar content and details). This is a must attend seminar aimed at Oracle professionals (both DBAs […]
Categories: DBA Blogs

Weighted Average Inventory calculation.

Tom Kyte - Tue, 2019-03-12 15:26
Hello, Hope you are at the best of you health. I am working in Microsoft Windows environment and installed database is Oracle 11gR2. I have following two table with sample data. <code>CREATE TABLE stock_master ( vno INTEGER, vdate DATE, vtype...
Categories: DBA Blogs

How to decide to create index on column

Tom Kyte - Tue, 2019-03-12 15:26
Dear, I talk about single table contain approx 20 to 25 columns. And in production database this table used with different column combination or single column also used in different where clause at different query. When some time query run slow s...
Categories: DBA Blogs

Create table replicate along with dependents in different schema

Tom Kyte - Tue, 2019-03-12 15:26
Hi Chris/Connor, I have one requirement where we need to create a replica of table in different schema and different table space (schema and table space will be inputs to the scripts)in same database server.. This we need to do using plsql only so ...
Categories: DBA Blogs

How to distinguish "Semantic error" and "Syntactic error"?

Tom Kyte - Tue, 2019-03-12 15:26
Hello Tom, Is there a way, in a PL/SQL programm, to manage differently "Semantic error" and "Syntactic error"? In the bloc "EXCEPTION", I wish to count these kind of errors, how can I do? I found nothing in the documentation and I don't t...
Categories: DBA Blogs

Need rank based on person_no column

Tom Kyte - Tue, 2019-03-12 15:26
Hi All, I have column ord which is common for all the records , then person_no. This can be repeated. I have a column called flag which is a sequence. I was trying populate a new column(NEW_FLAG) which will basically rank the person_no according...
Categories: DBA Blogs

Using connect by level to generate dates and times

Tom Kyte - Tue, 2019-03-12 15:26
I have two SQL Queries: SQL Query 1: <code>select to_date(:sDate,'dd-mm-rrrr')+(level-1)DateRange from dual connect by level <= ((to_date(:endDate,'dd-mm-rrrr')-to_date(:sDate,'dd-mm-rrrr')) + 1);</code> SQL Query 2: <code>select level,to...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs