DBA Blogs

How to avoid multiple scanning of same tables again and again

Tom Kyte - Mon, 2017-09-04 18:26
Hi Tom This query is scanning the below same table again and again and hence a Table SALES_CASE is referenced 5 times in this query. This table has 3 Million rows. Table MDM_KC_ORGANIZATION is referenced 5 times. This table has 300,000 records....
Categories: DBA Blogs

Deriving/Creating date span's from a set of begin & end dates.

Tom Kyte - Mon, 2017-09-04 18:26
Hi, I am trying to write a query using analytical functions that derive or create date spans using a set of begin & end date. The eventual spans that gets created should have over-lapping spans. Attached LiveSQL link contains the test case (with samp...
Categories: DBA Blogs

Value of time in a column with type DATE changes to 00:00:00 after more than 96 hours.

Tom Kyte - Mon, 2017-09-04 18:26
I have a table with a column (CREATED_ON) with type DATE. I insert a row into that today with the value 04-09-2017 12:33:43. However, after 96 hours, if I query for the row, the value of CREATED_ON changes to 04-09-2017 00:00:00. We scanned our co...
Categories: DBA Blogs

SQL*Loader-926

Tom Kyte - Mon, 2017-09-04 18:26
I ran sqlldr to import the data to table .i got the following error. control file: OPTIONS (SKIP=1) LOAD DATA INFILE '.\..\DATA\APPLICATION_MASTER.sql' BADFILE '.\..\CONTROL\APPLICATION_MASTER\APPLICATION_MASTER' DISCARDFILE '.\..\CONTROL\...
Categories: DBA Blogs

ORA-4030 error caused by pga

Tom Kyte - Mon, 2017-09-04 00:06
Hi , in our environment , from few days we are receiving ora-4030 for pga. I just wanted to know how much pga maximum can use system memory ? As far as my basic understanding , pga can use , full system memory including swap as well or it c...
Categories: DBA Blogs

Rman Error: dbgc_init_all failed with ORA-48141

Tom Kyte - Sun, 2017-09-03 05:46
Hi Tom, We getting a weird error, while netbackup is trying to take backup using rman for an oracle instance PMTN5O1, below is the error: dbgc_init_all failed with ORA-48141 RMAN-00571: =========================================================...
Categories: DBA Blogs

Setup Disaster Recovery for Oracle 11g Standard Edition on Azure

Tom Kyte - Sat, 2017-09-02 11:26
Hi, We have Oracle 11g Standard Edition database on on-premise connected with Web Application.For Which, we want to setup a DR Site on Azure. As per my knowledge Active Data guard is not provided with Oracle 11g standard edition. In this case, ca...
Categories: DBA Blogs

avoid 'Checkpoint not complete'?

Tom Kyte - Sat, 2017-09-02 11:26
Hi Gentlemen, if i have heavy DML operations, got this in alert_XE.log: <code> Current log# 1 seq# 2063 mem# 0: /u01/app/oracle/fast_recovery_area/XE/onlinelog/o1_mf_1_d2fs5dqt_.log Fri Sep 01 18:42:25 2017 Archived Log entry 2059 added for...
Categories: DBA Blogs

Standby Redo Logs Required For All Protection Modes ?

Tom Kyte - Sat, 2017-09-02 11:26
Hi TOM, Why do we need standby redo logs on standby database when the protection mode is MAX PERFORMANCE? Oracle documentation (http://docs.oracle.com/database/121/DGBKR/dbresource.htm#DGBKR270) says : "You must add standby redo log files on a...
Categories: DBA Blogs

Diffrence between varchar and varchar2

Tom Kyte - Sat, 2017-09-02 11:26
Hi Tom, Please let me the diff between varchar and varchar2
Categories: DBA Blogs

Extract only numbers from a string

Tom Kyte - Fri, 2017-09-01 17:06
I wanted to extract all the numbers from this string '12gfsda@3fg,f' and want to display. Like for example i want 123 to be extracted from 12gfsda@3fg,f and display only 123.
Categories: DBA Blogs

oracle catsearch not query

Tom Kyte - Fri, 2017-09-01 17:06
Hi, I am using oracle 12.1, Catsearch query "a but not b" working with catsearch(column, 'a - b', null)>0. But I am unable to only query "not b". Here is a test: <code>create table test_catsearch(name varchar2(100)); insert into test_catsea...
Categories: DBA Blogs

Oracle Sequence Cache x Numbers not always sequentially

Tom Kyte - Fri, 2017-09-01 17:06
Hello, I would know if it is possible that a sequence with cache 20 generates a sequence, for instance now with value 235 and after an hour, generates a number minor than 235, 150 for example. I thought that sequence always generates numbers highe...
Categories: DBA Blogs

opt_param - good, bad or ugly ?

Tom Kyte - Fri, 2017-09-01 17:06
I like your classification of hints into good and bad hints and try to follow it. Today, I learnt about the opt_param hint, which is undocumented in 10, but documented in 11. I encountered a situation, where optimizer_index_caching was set to the ...
Categories: DBA Blogs

Regular Expression using + symbol

Tom Kyte - Fri, 2017-09-01 17:06
I am trying to understand the meta character of + symbol in regular expressions. I got a doubt that <code>SELECT REGEXP_COUNT('RADHA SRI SESHU KOLLA','A') FROM DUAL; Output is 3 SELECT REGEXP_COUNT('RADHA SRI SESHU KOLLA','A+') FROM DUAL; Output i...
Categories: DBA Blogs

display of binary_float/binary_double in SQL*Plus

Tom Kyte - Fri, 2017-09-01 17:06
Hi Sir, Why the following binary_float/binary_double columns are display using ### ? Why they are displayed using scientific notation ? <code> SQL> create table t (c1bf binary_float, c2bd binary_float, c3on number); Table created. SQL> in...
Categories: DBA Blogs

REGEXP_REPLACE or Simple functions

Tom Kyte - Fri, 2017-09-01 17:06
Hi Tom, I need to replace a word from a String with null from a text field with the following rules. I am trying to frame the rule: So the rule is replace the word TEST with null but if the word TEST is towards the end of the string al...
Categories: DBA Blogs

ASM, DiskGroup, AU Size, Tablespace and Table Extents

Hemant K Chitale - Fri, 2017-09-01 10:49
Creating a new DiskGroup, specifying the Allocation Unit Size for it, creating a Tablespace in a Pluggable Database and creating multiple Segments (Tables) with multiple Extents.

I have added a new disk to my 12.1 VM.

SQL> select path, name, total_mb, header_status, state, group_number
2 from v$asm_disk
3 order by 1
4 /

PATH
--------------------------------------------------------------------------------
NAME TOTAL_MB HEADER_STATU STATE GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000 5114 MEMBER NORMAL 1

/dev/asm-disk2
DATA_0001 5114 MEMBER NORMAL 1

/dev/asm-disk3
FRA_0000 5114 MEMBER NORMAL 2

/dev/asm-disk4
OCRVOTE_0000 5114 MEMBER NORMAL 3

/dev/asm-disk5
DATA_0002 12284 MEMBER NORMAL 1

/dev/asm-disk6
0 CANDIDATE NORMAL 0


6 rows selected.

SQL>

The new disk is /dev/asm-disk6.
I create an ASM DiskGroup with AU Size of 1MB.

SQL> create diskgroup NEWDG_1M_AU external redundancy
2 disk '/dev/asm-disk6'
3 attribute 'compatible.asm'='12.1', 'compatible.rdbms'='12.1','au_size'='1M';

Diskgroup created.

SQL>
SQL> select path, name, total_mb, header_status, state, group_number
2 from v$asm_disk
3 order by 1
4 /

PATH
--------------------------------------------------------------------------------
NAME TOTAL_MB HEADER_STATU STATE GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000 5114 MEMBER NORMAL 1

/dev/asm-disk2
DATA_0001 5114 MEMBER NORMAL 1

/dev/asm-disk3
FRA_0000 5114 MEMBER NORMAL 2

/dev/asm-disk4
OCRVOTE_0000 5114 MEMBER NORMAL 3

/dev/asm-disk5
DATA_0002 12284 MEMBER NORMAL 1

/dev/asm-disk6
NEWDG_1M_AU_0000 2149 MEMBER NORMAL 4


6 rows selected.

SQL>
SQL> select group_number, name, sector_size, block_size, allocation_unit_size, state
2 from v$asm_diskgroup
3 order by 1
4 /

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE
-------------------- -----------
1 DATA 512 4096
1048576 MOUNTED

2 FRA 512 4096
1048576 MOUNTED

3 OCRVOTE 512 4096
1048576 MOUNTED

4 NEWDG_1M_AU 512 4096
1048576 MOUNTED


SQL>

I then create a Tablespace in a Pluggable Database specifically on this DG, instead of the default location for new Tablespace datafiles.

SQL> alter session set container=PDB;

Session altered.

SQL> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA
SQL>
SQL> create tablespace NEWTS_ON_1M_AU
2 datafile '+NEWDG_1M_AU'
3 extent management local autoallocate segment space management auto;

Tablespace created.

SQL>
SQL> select group_number, name, total_mb, usable_file_mb
2 from v$asm_diskgroup
3 order by 1
4 /

GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2851
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 1987

SQL>
SQL> drop tablespace NEWTS_ON_1M_AU including contents and datafiles;

Tablespace dropped.

SQL> select group_number, name, total_mb, usable_file_mb
2 from v$asm_diskgroup
3 order by 1
4 /

GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2851
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 2089

SQL>
SQL> create tablespace NEWTS_ON_1M_AU
2 datafile '+NEWDG_1M_AU'
3 extent management local autoallocate segment space management auto;

Tablespace created.

SQL> select group_number, name, total_mb, usable_file_mb
2 from v$asm_diskgroup
3 order by 1
4 /

GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2851
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 1987

SQL>
SQL> select file_name, bytes/1048576, user_bytes/1048576
2 from dba_data_files
3 where tablespace_name = 'NEWTS_ON_1M_AU'
4 /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
100 99


SQL>

Thus, we can see that the Usable Space in the Disk Group is less than the DiskGroup size, even with External Redundancy that specifies no mirroring of ASM Extents. The Usable space in the Disk Group is 1987MB after creating a 100MB datafile.

Bytes available in the datafile are 99MB because a Locally Managed Tablespace reserves space for the Extent Map in the datafile header blocks.  The datafile, belonging to a Tablespace in a Pluggable Database has a path that is identified by the DB_UNIQUE_NAME (RAC) and the PDB GUID as the identifier (44BBC69CE8F552AEE053334EA8C07365)

Now, I create 5 small tables, each with 5 extents of 64KB in the Tablespace.  I use DEFERRED_SEGMENT_CREATION=FALSE  to force creation of the Segment and allocation of all the Extents.

SQL> connect hemant/hemant@PDB
Connected.
SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL> create table t1 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t2 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t3 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t4 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t5 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL>
SQL>
SQL> connect system/manager@PDB

Connected.
SQL> select sum(bytes)/1024, count(*)
2 from dba_extents
3 where tablespace_name = 'NEWTS_ON_1M_AU'
4 /

SUM(BYTES)/1024 COUNT(*)
--------------- ----------
25600 25

SQL> select file_name, bytes/1048576, user_bytes/1048576
2 from dba_data_files
3 where tablespace_name = 'NEWTS_ON_1M_AU'
4 /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
100 99


SQL> select group_number, name, total_mb, usable_file_mb
2 from v$asm_diskgroup
3 order by 1
4 /

GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2832
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 1987

SQL>


Thus, a short demo of :
1.  Creating a new ASM DiskGroup with a specified AU Size
2.  Creating a Tablespace in the new DG (not being the default location)
3.  Creating multiple tables with pre-allocated Extents
4.  Verifying the Usable Space in the DiskGroup and Datafile
.
.
.


Categories: DBA Blogs

TABLE operator on local plsql type

Tom Kyte - Thu, 2017-08-31 22:46
Hello AskTom Team, I am using TABLE operator on locally defined nested table type. As per my understanding of 12c plsql manual, it is supported I am doing following: - Defined a type in package specification "type t1_tblType is TABLE of ...
Categories: DBA Blogs

Table Statistics - Can massive updates on table leave stats stale for subsequent Selects ?

Tom Kyte - Thu, 2017-08-31 22:46
[Scenario] A table, in an OLTP environment, has ~40 columns and ~1,00,000 rows. One of the column stores timestamp values, like last maintenance date time for entity represented by that row. This column is not used in access or filter predicates ....
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs