DBA Blogs

UKOUG “Lifetime Achievement Award” Speaker Award

Richard Foote - Tue, 2018-12-18 17:32
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

Tom Kyte - Tue, 2018-12-18 16:26
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

Tom Kyte - Tue, 2018-12-18 16:26
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?

Tom Kyte - Tue, 2018-12-18 16:26
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)

Tom Kyte - Tue, 2018-12-18 16:26
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

Tom Kyte - Tue, 2018-12-18 16:26
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

Tom Kyte - Tue, 2018-12-18 16:26
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

Tom Kyte - Mon, 2018-12-17 22:06
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

Tom Kyte - Mon, 2018-12-17 22:06
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

Tom Kyte - Mon, 2018-12-17 22:06
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

Tom Kyte - Mon, 2018-12-17 22:06
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

Tom Kyte - Mon, 2018-12-17 22:06
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$

Tom Kyte - Mon, 2018-12-17 03:46
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.

Tom Kyte - Mon, 2018-12-17 03:46
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

Tom Kyte - Mon, 2018-12-17 03:46
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

Tom Kyte - Mon, 2018-12-17 03:46
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

Tom Kyte - Mon, 2018-12-17 03:46
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

Tom Kyte - Mon, 2018-12-17 03:46
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

Tom Kyte - Mon, 2018-12-17 03:46
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

Hemant K Chitale - Sun, 2018-12-16 05:19
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 :

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

Subscribe to Oracle FAQ aggregator - DBA Blogs