DBA Blogs

data guard drift

Tom Kyte - 18 hours 15 min ago
I have setup DR with maximum performance mode, at any time of instance, how to find the duration of time of data difference between DC and DR.
Categories: DBA Blogs

DATABASE STORAGE USAGE IN ASM

Tom Kyte - 18 hours 15 min ago
Hey Guys, I am working in a RAC environment and have been struggling to get a perfect query to view ASM Storage usage based on specific and each databases on the ASM. <b>Simply, i want a result that shows a database and how much size that databas...
Categories: DBA Blogs

Materialized View Add Columns

Tom Kyte - 18 hours 15 min ago
Hi Tom, I have added a new column in my master table and i want this column to be reflected in my Materialized view is this possible without Re creating it i have to fetch all columns from my master table in my view.please suggest.
Categories: DBA Blogs

Event SQL*Net break/reset to client in refresh of materialized view

Tom Kyte - 18 hours 15 min ago
Dear, I have had event of "SQL*Net break/reset to client" in session (job) with refresh of materialized view Same after kill it the session, kill the running job, the new session again remains with this event. The solution always is re-create th...
Categories: DBA Blogs

oracle utl_file encoding from utf8; missing character

Tom Kyte - Sat, 2018-08-18 14:46
I like to export large amount of text data from db to file. The characterset in db is UTF8. The excepted result in the file is ISO8859P2 or MSWIN1250. My db settings: <code> SELECT * FROM v$nls_parameters; 1 NLS_LANGUAGE HUNGARIAN 0 ...
Categories: DBA Blogs

How do I determine how much storage will be required for NUMBER(p, s)?

Tom Kyte - Sat, 2018-08-18 14:46
<i>Oracle Database SQL Language Reference</i> states, "Each NUMBER value requires from 1 to 22 bytes." I was curious how much space a NUMBER(10, 0) would take, so I did: <code>SQL> create table t (a number(10, 0)) 2 / Table created. SQL>...
Categories: DBA Blogs

Exception when executing Bulk Collect

Tom Kyte - Sat, 2018-08-18 14:46
I am using bulk collect with save exceptions, it runs fine sometimes and sometimes it errors out. The error is as follows - <code>BEGIN * ERROR at line 1: ORA-00028: your session has been killed ORA-00028: your session has been killed ORA-...
Categories: DBA Blogs

Performance issue after database upgrade from 11.2.0.4 to 12.1.0.2

Tom Kyte - Sat, 2018-08-18 14:46
Hi Team, We have recently upgraded a SAP database from 11.2.0.4 to 12.1.0.2, After encountered performance issues that did not exist on the previous version(11.2.0.4). Proper keeping sga_target/sga_maxsize did not help Proper keeping db_file_m...
Categories: DBA Blogs

DBMS_SQLTUNE_UTIL0

Tom Kyte - Sat, 2018-08-18 14:46
Hi Tom I have a question regarding DBMS_SQLTUNE_UTIL0 package. This seems to be helper package. Is it okay to use this package directly in code or use of such internal packages should be avoided? I did not find any documentation on this packa...
Categories: DBA Blogs

Space Fragmentation in a table which has only inserts

Tom Kyte - Sat, 2018-08-18 14:46
Hi Tom, This is Praveen. 1)I have table x which has only inserts in it and updates rarely,so my doubt is till now i have heard and read that there will be space fragmentation for delete operation done on a table and i couldn't find out how the ...
Categories: DBA Blogs

definition of v$lock table

Tom Kyte - Sat, 2018-08-18 14:46
please find below query output.. select OWNER,OBJECT_NAME,OBJECT_TYPE from DBA_OBJECTS where OBJECT_NAME in ('V$LOCK'); ---PUBLIC V$LOCK SYNONYM as per above query output V$LOCK is SYNONYM. so i checked in DBA_SYNONYMS for further informa...
Categories: DBA Blogs

Rownum = 1 no longer returning results after upgrade to 12.1

Tom Kyte - Sat, 2018-08-18 14:46
Hi, I have a view that I created in 11g, after we upgraded the database to 12.1 it is no longer returning the dates for the view. I pulled the SQL for the view and confirmed that it no longer works. It does return the comment. I need to use the r...
Categories: DBA Blogs

Partner Webcast – Monitoring your cloud-based Infrastructure with Oracle Management Cloud

Today’s Modern IT supports today’s requirements by making IT infrastructure more accessible and available based on demand.  IT infrastructure can be made available both in your...

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

Partitioning -- 3b : More Indexes on Partitioned Table

Hemant K Chitale - Sat, 2018-08-18 03:23
In the previous blog post, I had demonstrated a Global (Non-Partitioned) Index and a Local (Partitioned) Index.  A Global Index itself may be partitioned.  A Local Index is actually Equi-Partitioned with the Tablee.

Let me demonstrate a Global Partitioned Index on the same table now.

Given the SALES_DATA table which is partitioned by SALE_DATE, I might want to support queries by CUSTOMER_ID.  What if I have millions of customers?  My CUSTOMERS table might be partitioned by CUSTOMER_ID.  Can I have an Index on SALES_DATA that is also partitioned by CUSTOMER_ID ?

SQL> l
1 create index sales_data_glbl_part_ndx_2
2 on sales_data (customer_id)
3 global
4 partition by range (customer_id)
5 (
6 partition p_1 values less than (501000) tablespace new_indexes,
7 partition p_2 values less than (1000001) tablespace new_indexes,
8 partition p_3 values less than (1500001) tablespace new_indexes,
9 partition p_4 values less than (maxvalue)
10* )
SQL> /

Index created.

SQL>


This is a Global Partitioned Index which is prefixed on a left prefix (CUSTOMER_ID) of the Index Column(s). Oracle does not support Global Partitioned Indexes that are non-prefixed.  Let me demonstrated the difference between the two.

SQL> drop index sales_data_glbl_part_ndx_2;

Index dropped.

SQL>
SQL> l
1 create index sales_data_glbl_part_ndx_2
2 on sales_data (customer_id, sale_date)
3 global
4 partition by range (customer_id)
5 (
6 partition p_1 values less than (501000) tablespace new_indexes,
7 partition p_2 values less than (1000001) tablespace new_indexes,
8 partition p_3 values less than (1500001) tablespace new_indexes,
9 partition p_4 values less than (maxvalue)
10* )
SQL> /

Index created.

SQL>
SQL>
SQL> l
1 create index sales_data_glbl_part_ndx_3
2 on sales_data (sale_date)
3 global
4 partition by range (customer_id)
5 (
6 partition p_1 values less than (501000) tablespace new_indexes,
7 partition p_2 values less than (1000001) tablespace new_indexes,
8 partition p_3 values less than (1500001) tablespace new_indexes,
9 partition p_4 values less than (maxvalue)
10* )
SQL> /
partition by range (customer_id)
*
ERROR at line 4:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL>
SQL> !oerr ora 14038
14038, 00000, "GLOBAL partitioned index must be prefixed"
// *Cause: User attempted to create a GLOBAL non-prefixed partitioned index
// which is illegal
// *Action: If the user, indeed, desired to create a non-prefixed
// index, it must be created as LOCAL; otherwise, correct the list
// of key and/or partitioning columns to ensure that the index is
// prefixed

SQL>


My first attempt above is valid because the Index is partitioned on CUSTOMER_ID although it is defined on (CUSTOMER_ID, SALE_DATE).  Thus, it is partitioned on a left prefix. The second index attempt fails because the Partition Key (CUSTOMER_ID) is not a left prefix (part of) the Index key (SALE_DATE alone).

Remember : The Index I've created is  partitioned on CUSTOMER_ID while the Table itself is partitioned on SALE_DATE.
This Index is a Global Index so any Partition Maintenance operation (TRUNCATE, DROP, SPLIT, MERGE) may result in the *entire* Index becoming UNUSABLE unless the UPDATE INDEXES clause is used in the Partition Maintenance DDL statement.  This was demonstrated with the Primary Key index being a Global Index in my earlier blog post.


What about Bitmap Indexes ?  Can a Bitmap Index on a partitioned Table be either Global or Local ?  Oracle does not support Global Bitmap Indexes.  A Bitmap Index on a Partitioned Table has to be Local.

SQL> create bitmap index sales_data_glbl_ndx_4
2 on sales_data(product_id)
3 /
on sales_data(product_id)
*
ERROR at line 2:
ORA-25122: Only LOCAL bitmap indexes are permitted on partitioned tables


SQL> !oerr ora 25122
25122, 00000, "Only LOCAL bitmap indexes are permitted on partitioned tables"
// *Cause: An attempt was made to create a global bitmap index on a partioned
// table.
// *Action: create a local bitmap index instead.

SQL>
SQL> create bitmap index sales_data_lcl_ndx_2
2 on sales_data(product_id) local
3 /

Index created.

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

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS

SQL> select partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name = 'SALES_DATA_LCL_NDX_2'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ --------
P_2018 TBS_YEAR_2018 USABLE
P_2019 TBS_YEAR_2019 USABLE
P_2020 TBS_YEAR_2020 USABLE
P_MAXVALUE USERS USABLE

SQL>


As demonstrated earlier, a Local Index automatically inherits the Partition Name and Tablespace Name from the Table Partitions.

To summarise, these are the Indexes on my SALES_DATA table :

SQL> l        
1 select index_name, index_type, partitioned, tablespace_name, status
2 from user_indexes
3* where table_name = 'SALES_DATA'
SQL> /

INDEX_NAME INDEX_TYPE PAR
------------------------------ --------------------------- ---
TABLESPACE_NAME STATUS
------------------------------ --------
SYS_C0017514 NORMAL NO
HEMANT VALID

SALES_DATA_LCL_NDX_1 NORMAL YES
N/A

SALES_DATA_GLBL_PART_NDX_2 NORMAL YES
N/A

SALES_DATA_LCL_NDX_2 BITMAP YES
N/A


SQL>


Remember that for a Partitioned Index (either Global or Local), the Tablespace Name and Status are attributes at the Partition definition level not at the Index definition.


Categories: DBA Blogs

Oracle Database Configurations using Docker and Vagrant

Hemant K Chitale - Fri, 2018-08-17 02:16
Oracle now makes available configurations for the Database (and other products) on both Docker and Vagrant via GitHub.

Good time to familiarize oneself with GitHub, Docker and/or Vagrant.


For the Docker configuration see : https://github.com/oracle/docker-images/tree/master/OracleDatabase


For the Vagrant configuration see : https://github.com/oracle/vagrant-boxes/tree/master/OracleDatabase


(Note : The Examples have been available on GitHub for quite some time at https://github.com/oracle/oracle-db-examples )



Categories: DBA Blogs

Subtract hours and show milliseconds for a TIMESTAMP(6) column

Tom Kyte - Fri, 2018-08-17 02:06
I want to SELECT a TIMESTAMP(6) with milliseconds, but at the same time I need to subtract 3 hours (0.125 of a day) from that TIMESTAMP to convert it to my timezone. So I tried: <code>SELECT To_Char(UTCSCANTIME-0.125,'YYYY-MM-DD HH24:MI:SS') AS LO...
Categories: DBA Blogs

“Let’s Talk Database” coming to Wellington, Auckland, Brisbane and Perth in September.

Richard Foote - Thu, 2018-08-16 19:08
Due to popular demand, I’ve been asked by Oracle to again run some more “Let’s Talk Database” events in September. Dates and venues are as follows: Monday, 2 September – Wellington (Wellington Oracle Office): Registration Link. Tuesday, 3 September – Auckland (Auckland Oracle Office): Registration Link. Wednesday, 12 September – Brisbane (Brisbane Oracle Office): Registration Link. Thursday, 13 September – Perth (Perth Oracle Office): Registration Link. […]
Categories: DBA Blogs

Unable to gather table stats in parallel

Tom Kyte - Thu, 2018-08-16 07:46
Hi, We're running gather_table_stats with the following arguments: dbms_stats.gather_table_stats(ownname=>'&owner', tabname=>'&table',estimate_percent=>0.1,block_sample=>true, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>fals...
Categories: DBA Blogs

Merge 2 PDF/png/jpg Files?

Tom Kyte - Thu, 2018-08-16 07:46
Good Evening Tom Is it possible to merge 2 binary files using oracle PL/SQL? I have seen examples using Java but not PL/SQL. Thanks for your time.
Categories: DBA Blogs

Scripts for Batch-Processing using the Data Dictionary in #Exasol

The Oracle Instructor - Mon, 2018-08-13 05:59

If you want to batch-process over a number of objects in Exasol, scripts that work with the Data Dictionary might do the trick. Let’s say I want to drop all of my tables that start with the letter P:

 

SQL_EXA> open schema fred;
EXA: open schema fred;

Rows affected: 0

SQL_EXA> create or replace script droptables
          as
          res=query([[select * from exa_user_tables where table_name like 'P%']])
          for i=1, #res
          do
           query([[drop table ::t]],{t=res[i].TABLE_NAME})
          end
        /
EXA: create or replace script droptables...

Rows affected: 0

SQL_EXA> select table_name from exa_user_tables;
EXA: select table_name from exa_user_tables;

TABLE_NAME
--------------------------------------------------
P9_TAB
P8_TAB

2 rows in resultset.

Amazingly enough, DROP TABLE is an operation that can be rolled back in Exasol. So to safeguard, I can disable autocommit here.

SQL_EXA> set autocommit off;
SQL_EXA> execute script droptables;
EXA: execute script droptables;

Rows affected: 0

SQL_EXA> select table_name from exa_user_tables;
EXA: select table_name from exa_user_tables;

TABLE_NAME
--------------------------------------------------

0 rows in resultset.

SQL_EXA> rollback;
EXA: rollback;

Rows affected: 0

SQL_EXA> select table_name from exa_user_tables;
EXA: select table_name from exa_user_tables;

TABLE_NAME
---------------------------------------------------
P9_TAB
P8_TAB

2 rows in resultset.
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs