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

Number Data Type Precision

Tom Kyte - Tue, 2019-03-12 15:26
Hi, From Oracle Docs "The absence of precision and scale designators specifies the maximum range and precision for an Oracle number." https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020 When i declared number with ou...
Categories: DBA Blogs

Partitioning -- 13c : Merging Partitions

Hemant K Chitale - Tue, 2019-03-12 07:18
The reverse of SPLITting a Partition is to MERGE two adjacent partitions.

I reverse the SPLIT that I did in the previous blog post.

SQL> l
1 select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4* order by partition_position
SQL> /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019_H1 TBS_YEAR_2019 TO_DATE(' 2019-07-01 00:00
P_2019_H2 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

7 rows selected.

SQL>
SQL> alter table sales_data
2 merge partitions P_2019_H1, P_2019_H2
3 into partition P_2019
4 update indexes
5 /

Table altered.

SQL>
SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019 HEMANT TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

6 rows selected.

SQL>


But, we find that the new Partition was created in the default "HEMANT"  tablespace !  So, we have to be careful about specifying target tablespace(s).

Let me reverse the action and try again.

SQL> alter table sales_data
2 split partition P_2019 at (to_date('01-JUL-2019','DD-MON-YYYY'))
3 into (partition P_2019_H1 tablespace TBS_YEAR_2019, partition P_2019_H2 tablespace TBS_YEAR_2019)
4 update indexes
5 /

Table altered.

SQL>
SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019_H1 TBS_YEAR_2019 TO_DATE(' 2019-07-01 00:00
P_2019_H2 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

7 rows selected.

SQL> alter table sales_data
2 merge partitions P_2019_H1, P_2019_H2
3 into partition P_2019 tablespace TBS_YEAR_2019
4 update indexes
5 /

Table altered.

SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

6 rows selected.

SQL>


So, when running Table Partition DDL, be careful about the intended and actual target Tablespace(s).  (What about Index Partitions ?  The UPDATE INDEXES clause can specify target tablespaces for each Index Partition of each Index as well ... something like : (this is from the documentation on the ALTER TABLE command)
UPDATE INDEXES (cost_ix (PARTITION c_p1 tablespace tbs_02, 
PARTITION c_p2 tablespace tbs_03))
Categories: DBA Blogs

Updating a table when a match is found in another table

Tom Kyte - Mon, 2019-03-11 21:06
Hi Tom, I have a table (T1) and a view is created upon T1 by masking few PI information say (V1) T1 area_code integer (100 USA, 200 UK) id integer Name string age integer DOB date type string (Individual/Company) V1 (Masked versi...
Categories: DBA Blogs

Not getting required date after using TO_CHAR function with DATE column

Tom Kyte - Mon, 2019-03-11 21:06
<code> create table demo_tab(id number(2),name varchar2(50),from_date date); Table created. insert into demo_tab values(2,'AUDI','04-MAR-2019'); 1 row created. insert into demo_tab values(3,'BMW','06-MAR-2019'); 1 row created. i...
Categories: DBA Blogs

Date format

Tom Kyte - Mon, 2019-03-11 21:06
Hi, I have data in the format 2019-02-21T13:58:18.000+00:00 in a varchar2 column(loaded into staging table from a CSV file). When merging this staging table I need to put this value into a DATE data type column. How to do this ? Format of the dat...
Categories: DBA Blogs

TO_DATE() on SYSDATE failed in insert all query.

Tom Kyte - Mon, 2019-03-11 21:06
Hello, Yesterday I used insert all query to enter 8 rows in two tables directly from toad. At that time I used TO_DATE(SYSDATE, 'DD-MON-YYYY HH24:MI') for inserting sysdate in my table (I know sysdate was enough but 'coz of since last four m...
Categories: DBA Blogs

Database operations monitor 12c

Tom Kyte - Mon, 2019-03-11 02:46
Team, Was reading about Real time operations monitor in Oracle 12c @ <u>https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/monitoring-database-operations.html#GUID-DC573FB7-40C5-4D6D-BE81-EF356900B444 </u> and here is a quick...
Categories: DBA Blogs

SQL fetch phase need library cache lock/pin

Tom Kyte - Mon, 2019-03-11 02:46
Hi , thks for all your works here,i've learnd much a lot! now, i have some question on <b>SQL fetch phase</b> and <b>library cache lock/pin </b>which stuck me for days. here is something i find from some website, <i> SELECT statement follow...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs