DBA Blogs
UKOUG “Lifetime Achievement Award” Speaker Award
I was recently very honoured and flattered to have received the “Lifetime Achievement Award” Speaker Award from the UKOUG. I have only managed to get to the excellent UKOUG Tech conferences on a couple of occasions, so it was both a thrill and a surprise to be so honoured. Unfortunately, I wasn’t able to make […]
Categories: DBA Blogs
Database Design for Invoices Table with Many Null Columns
Hello, Ask Tom Team.
I have a 70 columns table storing valid invoices. There are cases where the invoices are rejected because business rules. When the invoices are rejected just a few columns are filled (up to 5), then all 65 remaining columns ar...
Categories: DBA Blogs
Parallel execution of Select .. for update with Insert
Hi Tom
We have a stored proc something like below:
<code>Proc P1 (p_id in varchar2)
is
cursor c1
is
select a,b,c
from t1, t2
where t1.pk = t2.fk
and t1.id = p_id ;
v_id varchar2(10);
Begin
Open c1 (p_id);
fetch c1 into v_...
Categories: DBA Blogs
how to equate substring of one table column to a value in a list from another table column as part of an exists?
Hello Oracle Masters,
I was looking at 'connect by level' (https://asktom.oracle.com/pls/apex/asktom.search?tag=clever-sql) hoping I could make use of it, but don't seem to be able to. Perhaps there is another way?
We have a table of products ...
Categories: DBA Blogs
How to extract distinct group of associated records (not exactly hierarchical in nature)
Hello All,
It would be greatly appreciated if someone can provide a way out on how to extract distinct group/array of associated (they are not of parent-child relationship or hierarchical) records as shown below. The same table has been created wi...
Categories: DBA Blogs
ORA-01779: cannot modify a column which maps to a non key-preserved table
This is on checking below link -
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:273215737113#followup-3016346200346884929
Below answer given by Tom -
"Consider, if T had:
<code>OBJECT_NAME OBJECT_ID
-------------- ----...
Categories: DBA Blogs
How do I Declare/Define a date field in a query
When i was working with MSSQL I could DECLARE a start & End date (or other parameters) to avoid having to change a variable in the query itself.
This is what the table looks like
-------------------- ------ ------
DateTime Action User
--------...
Categories: DBA Blogs
Business case problem
I have a problem with that i have table emp_table(name, salary, department_name)
I want to move data from this table to two tables employees(name,salary,department_id), department(department_name)
And i putted a trigger on departments to make ...
Categories: DBA Blogs
Cannot append more than 32kb data to a clob variable
Hello,
I am trying to write a pl/sql procedure(oracle) in which I am storing the final output in a clob variable, but it gives me the error when I test the procedure by providing the input: [Error] Execution (1: 1): ORA-06502: PL/SQL: numeric or ...
Categories: DBA Blogs
Perf : Insert into Select Vs For All
I need to perform db operations where I need to inserts .5 million reocrds from another complex query involving joins,exists etc:
As of now I am using INSERT INTO SELECT inside a pl/sq package.
but some time I am observing for same data set t...
Categories: DBA Blogs
INSERT IF NOT EXISTS
I HAVE 1 table
Create table TB_COBA1 (ID NUMBER , NIS NUMBER , NILAI_A NUMBER , semester number);
Create table TB_COBA2 (ID NUMBER , NIS NUMBER , NILAI_b NUMBER , semester number);
SQL> Insert into TB_COBA1 values (1,1,1,1);
SQL> Inse...
Categories: DBA Blogs
Error with cursor syntax insert where not exists
i have a new table called jobs_new(job_id , job_title , min_salary , max_salary) and it have only one row with the job_id 'ST_MAN'
i want to make anonymous block to copy the other rows from jobs table except when the job_id is 'ST_MAN' because it...
Categories: DBA Blogs
ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion when reading AUD$
Hello Tom,
I am trying to read sqltext from AUD$. However, I am skipping the 32K and sql is giving me an ORA Error. ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion(actual 4664, maximum 4000). Sql is :
SELECT TO_CHAR(NTIME...
Categories: DBA Blogs
I want to use case in a SQL statement but getting error: too many values.
My Query is this:
<code>
Select cm.CASE_NUM , lrc.AGENCY_NAME , lg.GENDER , (Select PAT_STAT_PREG, CASE PAT_STAT_PREG when 0 then 'No'
when 1 then 'Yes' when 2 then 'Unknown' else 'Not Applicable'
END
from case_pat_info ) from safetyreport sr...
Categories: DBA Blogs
Objects with Stat locked
Hi,
We have noticed that for few tables and indexes in our DB stats are locked and its causing some queries to use bad plan. We are trying to unlock them and do gather stats to try and help them run faster.
We can find the tables and indexes fo...
Categories: DBA Blogs
service issue with pluggable database
I used windows server 2012 with 64 Bit Operating system and I install Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production.
When Create pluggable database as per following link.
https://www.oracle.com/technetwork/articles/da...
Categories: DBA Blogs
ACL created but accessing gives ORA-29273 ORA-12541
I have created a ACL and assigned it to a host. When accessing I get the above erros.
I did the following steps
SQL> exec dbms_network_acl_admin.create_acl(acl=>'testlitle.xml', description=> 'all hctra.net connections',principal=>'TAG_OWNER't=...
Categories: DBA Blogs
LOAD JAVA Erorrs
Hi,
We have default ORACLE8I(8.1.6) server installation.
Following is the JAVA Program.
import java.sql.*;
import java.io.*;
public class DropAny{
public static void object(String object_type,String object_name)
...
Categories: DBA Blogs
Max value to be used in a parallel hint
Hi,
I am working on Oracle Database 11gR2 Enterprise Edition, Single Instance and i want to use parallel hint in one of the query.
Below are the values taken from v$parameter :
parallel_min_time_threshold AUTO
parallel_max_servers...
Categories: DBA Blogs
Partioning -- 13a : Relocating a Partition
When you want to / need to move a Partition to a different Tablespace (e.g. as part of a LifeCycle Management Policy), you may need downtime to relocate the Partition. However, version 12cRelease1 allows Online Relocation of a Partition.
Let's say I have a SALES_DATA table and I need to move the Year 2016 data to a tablespace with datafiles on "cheaper" (lesss-performant) storage :
I then want to move the Year 2016 data to the Tablespace ARCHIVE_SALES_DATA :
When I relocated the P_2016 Partition (to the ARCHIVE_SALES_DATA Tablespace), the ROWIDs for rows in that Partition changed. So the Non-Partitioned Index SYS_C0017514 and the corresponding Local Partitions of the two Partitioned Indexes became "UNUSABLE". These had to be rebuilt. Alternatively, I could have added the UPDATE INDEXES clause to to the ALTER TABLE ... MOVE PARTITION .. statement to reset the Indexes to Usable but this would not have relocated the Local Partitions for those two Indexes to the new Tablespace.
Note that for Table Partitions, the MOVE clause relocates the Partition but for Index Partition the REBUILD clause is used to relocate (as well as make Usable) the Partition.
I would encourage you to view documentation and examples of the MOVE ONLINE facility in 12c to relocate a Table Partition without downtime.
Let's say I have a SALES_DATA table and I need to move the Year 2016 data to a tablespace with datafiles on "cheaper" (lesss-performant) storage :
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_2016 TBS_YEAR_2016
P_2017 TBS_YEAR_2017
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS
6 rows selected.
SQL>
SQL> select index_name, tablespace_name, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 /
INDEX_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ --------
SYS_C0017514 HEMANT VALID
SALES_DATA_LCL_NDX_1 N/A
SALES_DATA_LCL_NDX_2 N/A
SQL>
SQL> l
1 select index_name, partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name in
4 (select index_name
5 from user_indexes
6 where table_name = 'SALES_DATA')
7* order by index_name, partition_position
SQL> /
INDEX_NAME PARTITION_NA TABLESPACE_NAM STATUS
------------------------------ ------------ -------------- --------
SALES_DATA_LCL_NDX_1 P_2016 TBS_YEAR_2016 USABLE
SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE
SALES_DATA_LCL_NDX_2 P_2016 TBS_YEAR_2016 USABLE
SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE
12 rows selected.
SQL>
I then want to move the Year 2016 data to the Tablespace ARCHIVE_SALES_DATA :
SQL> alter table SALES_DATA
2 move partition P_2016
3 tablespace ARCHIVE_SALES_DATA
4 /
Table altered.
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_NA TABLESPACE_NAME
------------ ------------------
P_2016 ARCHIVE_SALES_DATA
P_2017 TBS_YEAR_2017
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS
6 rows selected.
SQL>
SQL> select index_name, tablespace_name, partitioned, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by index_name
5 /
INDEX_NAME TABLESPACE_NAME PAR STATUS
------------------------------ ------------------ --- --------
SALES_DATA_LCL_NDX_1 YES N/A
SALES_DATA_LCL_NDX_2 YES N/A
SYS_C0017514 HEMANT NO UNUSABLE
SQL> alter index SYS_C0017514 rebuild ;
Index altered.
SQL> select index_name, tablespace_name, partitioned, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by index_name
5 /
INDEX_NAME TABLESPACE_NAME PAR STATUS
------------------------------ ------------------ --- --------
SALES_DATA_LCL_NDX_1 YES N/A
SALES_DATA_LCL_NDX_2 YES N/A
SYS_C0017514 HEMANT NO VALID
SQL>
SQL> l
1 select index_name, partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name in
4 (select index_name
5 from user_indexes
6 where table_name = 'SALES_DATA')
7* order by index_name, partition_position
SQL> /
INDEX_NAME PARTITION_NA TABLESPACE_NAME STATUS
------------------------------ ------------ ------------------ --------
SALES_DATA_LCL_NDX_1 P_2016 TBS_YEAR_2016 UNUSABLE
SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE
SALES_DATA_LCL_NDX_2 P_2016 TBS_YEAR_2016 UNUSABLE
SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE
12 rows selected.
SQL>
SQL> alter index SALES_DATA_LCL_NDX_1
2 rebuild partition P_2016
3 tablespace ARCHIVE_SALES_DATA
4 /
Index altered.
SQL>
SQL> alter index SALES_DATA_LCL_NDX_2
2 rebuild partition P_2016
3 tablespace ARCHIVE_SALES_DATA
4 /
Index altered.
SQL>
SQL> l
1 select index_name, partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name in
4 (select index_name
5 from user_indexes
6 where table_name = 'SALES_DATA')
7* order by index_name, partition_position
SQL> /
INDEX_NAME PARTITION_NA TABLESPACE_NAME STATUS
------------------------------ ------------ ------------------ --------
SALES_DATA_LCL_NDX_1 P_2016 ARCHIVE_SALES_DATA USABLE
SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE
SALES_DATA_LCL_NDX_2 P_2016 ARCHIVE_SALES_DATA USABLE
SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE
12 rows selected.
SQL>
When I relocated the P_2016 Partition (to the ARCHIVE_SALES_DATA Tablespace), the ROWIDs for rows in that Partition changed. So the Non-Partitioned Index SYS_C0017514 and the corresponding Local Partitions of the two Partitioned Indexes became "UNUSABLE". These had to be rebuilt. Alternatively, I could have added the UPDATE INDEXES clause to to the ALTER TABLE ... MOVE PARTITION .. statement to reset the Indexes to Usable but this would not have relocated the Local Partitions for those two Indexes to the new Tablespace.
Note that for Table Partitions, the MOVE clause relocates the Partition but for Index Partition the REBUILD clause is used to relocate (as well as make Usable) the Partition.
I would encourage you to view documentation and examples of the MOVE ONLINE facility in 12c to relocate a Table Partition without downtime.
Categories: DBA Blogs
Pages
