DBA Blogs

Python Practice Produced Pretty Pictures

Bobby Durrett's DBA Blog - Sat, 2019-03-23 18:57

I wrote a Python program that made some fun pictures so I thought I would share them even though this is not really a database post.

I practice Python programming by doing Rosetta Code programming tasks that no one has implemented in Python. This is a fun way of keeping up my Python skills. My most recent contribution made pretty pictures so I thought I would show them here. The code takes a cube and breaks up the faces into smaller and smaller pieces that change the cube into a rounder shape.

Here is the input:

Input to the program, a cube

Here is the output after one subdivision:

After one subdivision. Chunky.

Here is the output after two subdivisions:

Two subdivisions. Pretty round.

Note that it is getting rounder. Lastly, after four subdivisions it is remarkably round considering that it started as a cube:

Four subdivisions. Quite round.

The main point of this post was to show the pretty pictures. But, to be more serious, if someone is reading this blog and looking for a programming task to do for practice you can do what I do and find a Rosetta Code task for the language you are learning and you can get some good practice.


Categories: DBA Blogs

Migration of a very large warehouse database

Tom Kyte - Fri, 2019-03-22 13:46
This question is more of a solicitation for advice than an actual question. I've been tasked with migrating a large warehouse database from one set of hardware in one datacenter to a new set of hardware in a different datacenter. Excluding temp, ...
Categories: DBA Blogs

How to show special/unseen characters from a column in a plsql ?

Tom Kyte - Fri, 2019-03-22 13:46
Hi Tom, I have a table with special characters in a column. The column values are like this with the plsql below. <code>set serveroutput on; declare c varchar2 (100); a number; begin for i in ( select ekd0756_cur...
Categories: DBA Blogs

how to copy chrome bookmarks from one computer to another

Matt Penny - Fri, 2019-03-22 12:20

copy "C:\Users\matty\AppData\Local\Google\Chrome\User Data\Default\Bookmarks"
"\sh00001\c$\Users\matty\AppData\Local\Google\Chrome\User Data\Default" -verbose

Categories: DBA Blogs

When AWS SCT Unable to Connect to MySQL in RDS or EC2

Pakistan's First Oracle Blog - Fri, 2019-03-22 05:27
AWS Schema Conversion Tool (SCT) is one of the must tool for a successful migration of databases to AWS RDS.

If you are trying to connect to MySQL hosted on EC2 instance or RDS and unable to make the connection despite of setting the security groups correctly and making sure that port, hostname, username and password are right, then first check the log of SCT.

2019-03-22 19:40:16.866 [   1]     GENERAL INFO    global_settings:
    app_folder=AWS Schema Conversion Tool
    console_log_folder=C:\Users\mirza\AWS Schema Conversion Tool\Log
    facade_log_folder=C:\Users\mirza\AWS Schema Conversion Tool\Log
    log_folder=C:\Users\mirza\AWS Schema Conversion Tool\Log
    log_folder_extractors=C:\Users\mirza\AWS Schema Conversion Tool\Extractor Log
    log_folder_extractors_cassandra=C:\Users\mirza\AWS Schema Conversion Tool\Extractor Log\Cassandra
Caused by: com.amazon.sct.dbloader.DbLoaderHandledException: Connection wasn't established. Check connection properties.
at com.amazon.sct.dbloader.DbLoader.checkConnection(DbLoader.java:512)
at com.amazon.sct.dbloader.DbLoader.connect(DbLoader.java:349)
at com.amazon.sct.dbloader.DbLoaderContainer.checkAccessibility(DbLoaderContainer.java:25)
at com.amazon.sct.task.TestConnectionTask.call(TestConnectionTask.java:26)
at com.amazon.sct.task.TestConnectionTask.call(TestConnectionTask.java:12)
at javafx.concurrent.Task$TaskCallable.call(Task.java:1423)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
... 1 more

In this case, there are no errors about time out which commonly refers to security group configuration or any about the credential issue. Here the issue is not with SCT or any of AWS component, rather its about the user which you are using to connect to MySQL.

Create user MySQL as follows and try and it should work:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

Then first test connection using MySQL workbench and then try with SCT.

Hope it helps.

Categories: DBA Blogs

Intro: Initial Thoughts On Oracle Autonomous Database Cloud Services (Automatic For The People)

Richard Foote - Thu, 2019-03-21 22:37
I’m currently writing up a few blog pieces on indexing in the Oracle Autonomous Database environments, but I thought I’ll begin by introducing what exactly are Oracle Autonomous Database Cloud Services and some of my initial thoughts, as there’s still some confusion on all this. Introduced by Uncle Larry at Oracle OpenWorld 2017, Oracle Autonomous […]
Categories: DBA Blogs

ORA-02396: exceeded maximum idle time, please connect again

Tom Kyte - Thu, 2019-03-21 19:26
Dear Mr. Tom, i have an issue according the IDLE_TIME parameter.. I used to open more than one session to the database (different scheam's on same Database)... and our dba set IDLE_TIME as 15 minutes, and even if the session is not idle (i'm ...
Categories: DBA Blogs

truncating empty table generate ORA-02266:

Tom Kyte - Thu, 2019-03-21 19:26
Tom , why truncating empty table generates the error ORA-02266 ? SQL> select count(*) from t_data; COUNT(*) ---------- 0 SQL> truncate table t_data * ERROR at line 1: ORA-02266: unique/primary keys in t...
Categories: DBA Blogs

SQL query to find FK IDs with a series of values

Tom Kyte - Thu, 2019-03-21 19:26
we have below tables <code>create table bca(id number(2)); insert into bca(10); insert into bca(11); insert into bca(7); create TABLE abc( di NUMBER(2), fk_id NUMBER(3), yek VARCHAR2(20), elv VARCHAR(15...
Categories: DBA Blogs

Announcement: “Oracle Performance Diagnostics and Tuning” Webinar – 9-12 July 2019 !!

Richard Foote - Thu, 2019-03-21 02:30
I’m very excited to announce the first public running of my new “Oracle Performance Diagnostics and Tuning” Webinar will run between 9-12 July 2019 (6pm-10pm AEST): Webinar Series 9-12 July 2019 (start 6pm AEST, end 10pm AEST):  This is a must attend seminar aimed at Oracle professionals (both DBAs and Developers) who are interested in Performance Tuning.  […]
Categories: DBA Blogs

Date constraint to validate all bookings are in the future

Tom Kyte - Thu, 2019-03-21 01:06
Hi, I need to create a constraint which will not allow appointments to be booked in the past. I wonder if someone could help me with this please. Thank you. Juliana
Categories: DBA Blogs

Monitoring parallel excution of FULL table scan

Tom Kyte - Thu, 2019-03-21 01:06
Hi I'm on 12.2 EE on Win 2016 I have the following SQL which selects from a 550 GB table (yes, it is GB due to massive GDPR logging) <code> create table GFAUDIT.fga_log$_kopi_201809 as select /*+ PARALLEL (8)*/ (select instance_name from v...
Categories: DBA Blogs


Tom Kyte - Thu, 2019-03-21 01:06
Why is this statement returning value - <code>select * from ( SELECT 'AAaaanders4n' name FROM dual ) WHERE REGEXP_LIKE (name, '^[A]{1}');</code> I have given {1} in regexp_like, still this statement returns 'AAaaanders4n'
Categories: DBA Blogs

Speed of Light

Bobby Durrett's DBA Blog - Wed, 2019-03-20 16:30

Looking at cloud databases has me thinking about the speed of light. Wikipedia says that the speed of light is about 186,000 miles per second. If my calculations are correct that is 5.37 microseconds per mile. The United States is about 2680 miles wide so it would take light about 14.4 milliseconds to cross the US. If I ping one of my favorite web sites it takes tens of milliseconds to ping so that kind of makes sense because those sites are in other cities and I am going through various routers. I did some tests with my company’s storage and found that reading from our storage when the data is cached in the storage server takes around 200 microseconds. That is 200 microseconds for a round trip. I’m sure that our database servers and storage are a lot less than a mile apart so most of that time has nothing to do with the speed of light. I heard about a cloud vendor whose fast network connection took 100 microseconds plus the speed of light. I guess 100 microseconds is the cost of getting your data to fiber and light does the rest. If your cloud database was on the other side of the country, I guess it could take 14 milliseconds each way at least for each SQL request. If the cloud database was in your own city and say 10 miles away that would only tack on about 53.7 microseconds each way to the 100 microseconds overhead. I guess it makes sense. Maybe 100 microseconds plus the speed of light is the cost of moving data in the best case?


Categories: DBA Blogs

Partitioning -- 13d : TRUNCATE and DROP Partitions and Global Indexes

Hemant K Chitale - Wed, 2019-03-20 07:11
A TRUNCATE or DROP Partition makes Global Indexes on a Partitioned Table UNUSABLE.

You may be lucky if the target partition was empty, resulting in Oracle maintaining Global Indexes as valid.  However, the accepted rule is that you either (a) use the UPDATE INDEXES clause [resulting in the TRUNCATE or DROP taking longer to run, effectively locking the table partitions] OR  (b) do a REBUILD of the Indexes that become UNUSABLE after the TRUNCATE or DROP.

12c has introduced what it calls Asynchronous Global Index Maintenance.  With this feature present, the TRUNCATE or DROP runs much faster as a DDL without actually removing the target rows from the Global Indexes [but still requires the UPDATE INDEXES clause to be specified]

So, now in my 12.2 database I have these two Indexes on SALES_DATA :

SQL> select index_name, partitioned, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by 2,1
5 /

------------------------------ --- --------


I then TRUNCATE a non-empty Partition and check the Indexes

SQL> alter table sales_data truncate partition P_2015 update indexes;

Table truncated.

SQL> select index_name, partitioned, status, orphaned_entries
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by 2,1
5 /

------------------------------ --- -------- ---


The ORPHANED_ENTRIES column indicates that SALES_DATA_PK is subject to Asynchronous Index Maintenance.

This is the job that will do the Index Maintenance at 2am  :

SQL> l
1 select owner, job_name, last_start_date, next_run_Date
2 from dba_scheduler_jobs
3* where job_name = 'PMO_DEFERRED_GIDX_MAINT_JOB'
SQL> /

20-MAR-19 AM UTC
21-MAR-19 AM UTC

SQL> !date
Wed Mar 20 20:05:24 SGT 2019


So, I could
(1) wait for the next run of the job OR
(2) manually trigger the job (which will scan the entire database for all indexes that require such maintenance) OR
(3) Execute  DBMS_PART.CLEANUP_GIDX  to initiate the maintenance for the specific index OR
(4) Execute an ALTER INDEX REBUILD to make the Index USABLE again.

SQL> execute dbms_part.cleanup_gidx('HEMANT','SALES_DATA');

PL/SQL procedure successfully completed.

SQL> select index_name, partitioned, status, orphaned_entries
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by 2,1
5 /

------------------------------ --- -------- ---


Note that the argument to CLEANUP_GIDX is the *Table Name*, not an Index Name.

Here I have demonstrated a TRUNCATE Partition, but the same method would be usable for a DROP Partition.

Categories: DBA Blogs

Generate number based on start and end columns.

Tom Kyte - Wed, 2019-03-20 06:46
Generate value based on start and end columns without using procedure. How to modify the select query. <i>select key_column, start_point, end_point FROM tab1 WHERE key_column='10254';</i> key_column start_point end_point 10254 -2 ...
Categories: DBA Blogs

How to recover the whole database with RMAN Backup

Tom Kyte - Wed, 2019-03-20 06:46
Hi Team, First off all a big Thanks for your supports Now i wanna know the steps to recover a fully operational database with RMAN backup. I haven't done this scenario before,So i am going for a Test case here. My requirement is 1) I have dat...
Categories: DBA Blogs

cannot access objects in different schema

Tom Kyte - Wed, 2019-03-20 06:46
I am the admin user and can create tables and procedures in any schema. I have few tables in Schema B which I am referencing in a package i am creating in Schema A however upon compiling it does not see the tables in Schema B. Schema B does not ha...
Categories: DBA Blogs

Virtual columns in Oracle 11g

Tom Kyte - Wed, 2019-03-20 06:46
hi tom what is virtual column in 11g. Why oracle has introduce it. Can you give us its possible usages. regards Amir Riaz
Categories: DBA Blogs

New utility Python scripts for DBAs

Bobby Durrett's DBA Blog - Tue, 2019-03-19 14:45

I pushed out three new Python scripts that might be helpful to Oracle DBAs. They are in my miscpython repository.

Might be helpful to some people.


Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator - DBA Blogs