Tom Kyte

Subscribe to Tom Kyte feed Tom Kyte
These are the most recently asked questions on Ask Tom
Updated: 4 hours 17 min ago

Autonomous Database Strategic Customer Program

Wed, 2022-09-21 05:26
Is there a link to information about the ADB-S Strategic Customer Program?
Categories: DBA Blogs

Analytical Functions- a brief introduction

Mon, 2022-09-12 01:26
Tom One of our main dissapointment is that , we cannot use analytical functions in sql from within a procedure, as they are not compatible with the pl/sql engine. Further, when I try to update my knowledge with the analytical functions, I get the doubt as to the total number of analytical functions available with 8i. Can you give a brief introduction to the analytical functions, giving the simplest example possible for each analytical function( as you have done in case of CASE). As far as I am concerned, the total number of analytical functions are 1.group by 2.rollbup 3.cube 4.partition 5.Rollover Tom, kindly give the simples of examples possible, so that we understand the concept first, and can then use them to reosolve complex functionalities. ( If we can use these above functions in pl/sql , I am curious, how much of coding it would reduce, I guess a lot, a lot lot infact. Thank you
Categories: DBA Blogs

Analytic Functions

Mon, 2022-09-12 01:26
Tom, Thanks for providing this forum for answering questions. I'm trying to get the hang of analytic functions and I'm having a problem with the following code. I have to comment out "where dr <= 3)" to get it to work. "dr" shows up as the column heading but I add my conditional statement I get invalid column name. Help! Ps. I have your book and I hope you sign on the dotted line for the next one. select * FROM (select candidate_id, deptid, actual_start_date, annual_sal_amt, dense_rank() over (partition by deptid order by annual_sal_amt desc) dr from obapp1.offer where actual_start_date >= '01-JAN-2002' and annual_sal_amt > 0) -- where dr <= 3) order by deptid, annual_sal_amt desc
Categories: DBA Blogs

Converting columns into rows

Wed, 2022-09-07 11:26
<code>Hi, I have a requirement to implement the following: Create table marker(code varchar2(4),sid integer (10),mrk1 char(4),mrk2 char(4),mrk3 char(4),mrk4 char(4),mrk5 char(4),mrk6 char(4)) Create table candidate (code varchar2(4),sid integer(10)) Create table candidate_marker(code varchar2(4),sid integer(10),mrk char(4)) Eg: data Marker: Abcd 12345678 0 A B Dp Null Null Sdfg 3456218 A B C Dr Dp Null Ftgh 56784932 Null 1 T Null Null Dp Yuio 7896543 T Null G Null Null Null INSERT INTO MARKER VALUES (`Abcd?,12345678,?0?,?A?,?B?,?DP?,NULL,NULL); INSERT INTO MARKER VALUES (`Sdfg?, 3456218,?A?,?B?,?C?,?DR?,?DP?,NULL); INSERT INTO MARKER VALUES(`Ftgh?, 56784932,NULL,?1?,?T?,NULL,NULL,?DP?); INSERT INTO MARKER VALUES(`Yuio?, 7896543,?T?,NULL,?G?,NULL,NULL,NULL); Candidate: Abcd 12345678 Sdfg 3456218 Ftgh 56784932 Yuio 7896543 INSERT INTO CANDIDATE VALUES(`Abcd?, 12345678); INSERT INTO CANDIDATE VALUES(`Sdfg?, 3456218); INSERT INTO CANDIDATE VALUES(`Ftgh?, 56784932); INSERT INTO CANDIDATE VALUES(`Yuio?, 7896543); The candidate marker should look like this: Abcd 12345678 H0 Abcd 12345678 CA Abcd 12345678 TB Abcd 12345678 DP Sdfg 3456218 HA Sdfg 3456218 CB Sdfg 3456218 TC Sdfg 3456218 DR Sdfg 3456218 DP Ftgh 56784932 DEF Ftgh 56784932 C1 Ftgh 56784932 TT Ftgh 56784932 DP Yuio 7896543 HT Yuio 7896543 DEF1 Yuio 7896543 TG To explain the logic, The candidate and marker tables will be joined on code and sid. From the marker table, the first three markers are mandatory and last three markers are optional. All the mandatory markers should be fixed with a letter and if any value is null, it should be replaced with a default value. From the optional markers, only DP and DR need to be populated in the candidate_marker table. Please could some one help me in implementing this. Thank you</code>
Categories: DBA Blogs

v$flashback_database_logfile

Wed, 2022-09-07 11:26
I looked at v$flashback_database_log and noted there were 300+ logs and all of them except the oldest had a "TYPE" of NORMAL. The oldest log had a type of "RESERVED". That confuses me, and the doc that is supposed to explain what the values in the TYPE column mean is unhelpful -- Doc ID 2351182.1. That doc says "Reserved is the next log file that will be used". What does "used" mean in this context? The entire explanation in that doc is as follows: <code>The four status types are: -- "Normal" is just what it means a normal log file. -- "Reserved" is the next log file that will be used. -- "Free" is just that the log is free to be used. -- "To delete" means the log will deleted when the FRA need space.</code> Can you shed some light on this?
Categories: DBA Blogs

BUG 7538546 COMMIT is NOT DISABLED by command "ALTER SESSION DISABLE COMMIT IN PROCEDURE"

Wed, 2022-09-07 11:26
Hello, We hit non-public Oracle BUG 7538546 that describes the scenario where COMMIT is NOT DISABLED by command ""ALTER SESSION DISABLE COMMIT IN PROCEDURE" that runs before "AUTHID CURRENT_USER" command in the same package. OracleSupport says this is not a priority, so there is NO ETA on the FIX. Here is the question: Is there know way of disabling/enabling commits where it's needed without rewriting entire application ? Thank you and regards, Shushana TEST1 where COMMIT worked despite having "ALTER SESSION DISABLE COMMIT IN PROCEDURE" before package with "AUTHID CURRENT_USER" : ========================================================================================= <code> SQL> CREATE TABLE test_tbl (USER_ID CHAR(15 BYTE) NOT NULL ) ; Table created. SQL> create or replace PACKAGE AAA_KD 2 AUTHID CURRENT_USER 3 AS 4 PROCEDURE COMMIT_TEST(REQUEST_REASON_CALLED IN VARCHAR2); 5 END AAA_KD; 6 7 / Package created. SQL> create or replace PACKAGE BODY AAA_KD 2 AS 3 PROCEDURE COMMIT_TEST(REQUEST_REASON_CALLED IN VARCHAR2) 4 IS 5 BEGIN 6 delete test_tbl; 7 insert into test_tbl select 'KEITH' from dual; 8 commit; 9 END COMMIT_TEST; 10 END AAA_KD; 11 12 / Package body created. SQL> ALTER SESSION DISABLE COMMIT IN PROCEDURE; Session altered. SQL> EXEC AAA_KD.COMMIT_TEST(NULL); PL/SQL procedure successfully completed. SQL> select * from test_tbl; USER_ID --------------- KEITH TEST2 where COMMIT produces EXPECTED ORA- error after running ALTER SESSION DISABLE COMMIT IN PROCEDURE; and package with commented out line "--AUTHID CURRENT_USER": ========================================================================================================= SQL> create or replace PACKAGE AAA_KD 2 --AUTHID CURRENT_USER 3 AS 4 PROCEDURE COMMIT_TEST(REQUEST_REASON_CALLED IN VARCHAR2); 5 END AAA_KD; 6 7 / Package created. SQL> ALTER SESSION DISABLE COMMIT IN PROCEDURE; Session altered. SQL> EXEC AAA_KD.COMMIT_TEST(NULL); BEGIN AAA_KD.COMMIT_TEST(NULL); END; * ERROR at line 1: ORA-00034: cannot COMMIT in current PL/SQL session ORA-06512: at "TMGRB9TS.AAA_KD", line 8 ORA-06512: at line 1 SQL> select * from test_tbl; USER_ID --------------- KEITH </code>
Categories: DBA Blogs

Update big table

Tue, 2022-09-06 17:06
We have a table with around 150+ million records and our database is setup with two nodes on rac. We can?t do no logging as it?s default setup with logging in prod. We have a requirement to add two columns for this big table and we need to perform update operation for existing records based on another table. What is the best approach to achieve this with logging.
Categories: DBA Blogs

Add tables automatically to audit policy

Tue, 2022-09-06 17:06
Hi Tom, Is there a way to automatically add tables a user creates to an audit policy? I'm thinking a trigger could do this, but can we do it without giving the user the AUDIT_ADMIN role? What I am really after is a way to audit any action against a schema. <code> create audit policy audit_schema_names_objects actions all on schema_name.* only toplevel; </code>
Categories: DBA Blogs

How to get weekly data counts

Tue, 2022-09-06 17:06
How to calculate weekly counts with in a year. I want the week to start from Monday to Sunday. Thanks.
Categories: DBA Blogs

UPDATE Statement produces ORA-00933 when terminated with semicolon

Mon, 2022-09-05 04:26
Hi, I have simple parametrised UPDATE statement (.NET core application, Oracle .NET Drivers) <code>UPDATE T_SLMGM_SLPLAN SET BEGINN = :Beginn, ENDE = :Ende, UPDATE_TIMESTAMP = :Update_Timestamp, OPTLOCK = :Optlock, CREATE_TIMESTAMP = :Create_Timestamp, CREATE_USER = :Create_User, STATUS = :Status, UPDATE_USER = :Update_User, LADESTELLE = :Ladestelle WHERE ID = :Id</code> However if the statement is terminated with Semicolon - it will reproducibly fail with ORA-00933 "not properly terminated" So problem is located and easily fixed, but still want to know why is that so. Isnt Semicolon at the end of statement SQL standart? SELECT, INSERT and DELETE statements does not seem to be affected and can be terminated with semicolon. Thank you very much for the clarification! Best regards Emil
Categories: DBA Blogs

Regarding the Time management

Mon, 2022-09-05 04:26
Team, You being busy (answering questions at Asktom, creating technical videos, persenting at user group conference, office hours, blogpost, spotify podcast, replying to technical questions over various internal forums like slack channels etc) in the Oracle space, How do you manage your time efficiently to get to know all new exciting stuffs in the Oracle database world ( general database features, Autonomous database, APEX, ORDS etc) How do you start your day and proritize your day to day time for all these activies ? do you have any organized notes/timeframe that you put to work to know all these areas? any tips on time managment, that would help us to improve our time utilization
Categories: DBA Blogs

mean, median, mode - analytic

Thu, 2022-09-01 08:46
Tom, I am trying to calculate mean, median, and mode of salary within each department, and display it on all rows (corresponding to the department). It's fine to ignore rows with a null salary. I believe I've got mean and median, but I need help with mode. By "mode", I mean the most frequently occurring value for salary within each department. I know I can do this with a self join, but I'm looking for an analytic solution (for performance evaluation). Pretty basic, but I haven't been able to find the answer - the word "mode" is too common. Please assist. Thanks. - Jack SQL> ----------------------- SQL> -- Create sample table. SQL> ----------------------- SQL> SQL> create table temp_01_tb 2 as 3 select 1 dept, 1 salary from dual 4 union all 5 select 1 dept, 1 salary from dual 6 union all 7 select 1 dept, 1 salary from dual 8 union all 9 select 1 dept, 2 salary from dual 10 union all 11 select 1 dept, 2 salary from dual 12 union all 13 select 1 dept, 8 salary from dual 14 union all 15 select 2 dept, 1 salary from dual 16 union all 17 select 2 dept, 1 salary from dual 18 union all 19 select 2 dept, 2 salary from dual 20 union all 21 select 2 dept, 2 salary from dual 22 union all 23 select 2 dept, null salary from dual 24 union all 25 select 2 dept, 3 salary from dual 26 ; Table created. SQL> SQL> --------------------- SQL> -- View sample table. SQL> --------------------- SQL> SQL> select * from temp_01_tb; DEPT SALARY ---------- ---------- 1 1 1 1 1 1 1 2 1 2 1 8 2 1 2 1 2 2 2 2 2 2 3 12 rows selected. SQL> SQL> ----------------------------------- SQL> -- Show all rows, plus mean, median SQL> -- and mode of salary within dept. SQL> ----------------------------------- SQL> SQL> select dept, 2 salary, 3 avg(salary) over (partition by dept) mean_sal, 4 percentile_disc (0.5) within group (order by salary) 5 over (partition by dept) median_sal, 6 ' ?' mode_sal 7 from temp_01_tb 8 ; DEPT SALARY MEAN_SAL MEDIAN_SAL MODE_SAL ---------- ---------- ---------- ---------- -------- 1 1 2.5 1 ? 1 1 2.5 1 ? 1 1 2.5 1 ? 1 2 2.5 1 ? 1 2 2.5 1 ? 1 8 2.5 1 ? 2 1 1.8 2 ? 2 1 1.8 2 ? 2 2 1.8 2 ? 2 2 1.8 2 ? 2 3 1.8 2 ? 2 1.8 2 ? 12 rows selected.
Categories: DBA Blogs

detecting De-supported and deprecated feature usage in Oracle database.

Wed, 2022-08-31 14:26
Have a customer database that has been upgraded through various databases releases? it currently is 19c. I have found some tablespaces are still dictionary managed tablespaces. This was deprecated in 8i. Is there a oracle auditing option, or script or pre-existing tool (Auto Upgrade with analyze), that can be used to identify deprecated \ de-supported features? With SQL Server I can create an extended event on sqlserver.deprecation_final_support, is there something similar for Oracle database products? I have previously raised a SR with oracle, I was directed back to the database upgrade guide "10 Behavior Changes, Deprecated and De-supported Features for Oracle Database"
Categories: DBA Blogs

Hardware Planning for Oracle 19c Database SE2

Tue, 2022-08-30 01:46
Hi, We are recently considering to migrate very old Oracle database to 19c, and now planning what hardware should get us the best processing efficiency per $. Basically, we run "many database instances" on a single Oracle server, and know that Oracle SE2 will automatically limit the use of 16 CPU threads at a time. However, I don't understand what would be best in our CPU choices: E.g. 1) If we buy system with 1 CPU with 16 cores VS system with 1 CPU with 48 cores (suppose the cores are of same frequency), do we get better "scalability" with the 48 cores system? We have many database instances, do they spread across different CPU threads, thus making the CPU not as busy as the 16 cores system, and thus allow us to run "more" instances? 2) Following question 1), when choosing CPUs, should we prefer 16 cores but higher CPU frequency (faster on each single core) system, or 48 cores (or may be just 32 cores) but lower CPU frequency system? 3) In case 16 cores systems is best because more cores do not help us scale out, since modern CPUs are with hyper-threading (i.e. a 16 cores CPU are seen as 32 threading in the operating system), should we disable the hyper-threading feature to maximize the speed of using each of the Oracle limited 16 threading? Thanks in advance for your attention, Jeff
Categories: DBA Blogs

impdp fails with virtual column that uses external function

Tue, 2022-08-30 01:46
I'm trying to import a schema from a dump file created in another schema (same database). The liveSql script has the objects for which I'm interested, and in the case of this test, that's all that exists besides the standard Oracle stuff. The schema named "uname2" was created with the script for the schema "uname," and has had no additions. I executed the following command line operations. <code> >expdp userid=uname/uname >impdp userid=uname/uname dumpfile=uname REMAP_SCHEMA=uname:uname2 </code> emppdp ran fine. impdp had the following errors: <code> ORA-39083: Object type TABLE:"UNAME2"."TEST_TABLE" failed to create with error: ORA-00904: "UNAME"."CHECK_IS_ACTIVE": invalid identifier Failing sql is: ALTER TABLE "UNAME2"."TEST_TABLE" MODIFY ("IS_ACTIVE" NUMBER(*,0) GENERATED ALWAYS AS ("UNAME"."CHECK_IS_ACTIVE"("ACTIVE_FROM","ACTIVE_TO")) VIRTUAL ) </code> It's not clear if the problem is because the tables are imported before the functions are, or if it has to do with the (apparent) mismatch of user/schema name (uname and uname2).
Categories: DBA Blogs

Foreign key locking + indexes with partitioned tables/indexes

Tue, 2022-08-30 01:46
Howdy, Just wondering what the limitations of having a partitioned table and a foreign key are in terms of the locking required to protect said foreign key. If we have a table that's hash partitioned by primary key (with a local index for that primary key) are we looking to run into any locking issues when we set up an index on a foreign key that's GLOBALLY hash partitioned by the FK value? I assume not (but asking in case I assume wrong); but I'm more curious about the same question in the presence of a local index to protect the foreign key. So for example (globally hash partitioned)... <code> create table parent (parent_pk_column number(38) primary key, junk_col varchar2(100)); create table child ( child_pk_column number(38) not null , parent_pk_column number(38) not null , junk_col varchar2(100) , constraint child_pk primary key (child_pk_column) using index , constraint child_fk_001 foreign key (parent_pk_column) references parent(parent_pk_column) ); create index child_fk_idx_001 on child (parent_pk_column); ALTER TABLE child MODIFY PARTITION BY hash (child_pk_column ) ( partition p01,partition p02,partition p03,partition p04,partition p05,partition p06,partition p07,partition p08,partition p09,partition p10,partition p11,partition p12, partition p13,partition p14,partition p15,partition p16,partition p17,partition p18,partition p19,partition p20,partition p21,partition p22,partition p23,partition p24, partition p25,partition p26,partition p27,partition p28,partition p29,partition p30,partition p31,partition p32,partition p33,partition p34,partition p35,partition p36, partition p37,partition p38,partition p39,partition p40,partition p41,partition p42,partition p43,partition p44,partition p45,partition p46,partition p47,partition p48, partition p49,partition p50,partition p51,partition p52,partition p53,partition p54,partition p55,partition p56,partition p57,partition p58,partition p59,partition p60, partition p61,partition p62,partition p63,partition p64 ) online update indexes ( child_pk local, child_fk_idx_001 global partition by hash (parent_pk_column) ( partition p01,partition p02,partition p03,partition p04,partition p05,partition p06,partition p07,partition p08,partition p09,partition p10,partition p11,partition p12, partition p13,partition p14,partition p15,partition p16,partition p17,partition p18,partition p19,partition p20,partition p21,partition p22,partition p23,partition p24, partition p25,partition p26,partition p27,partition p28,partition p29,partition p30,partition p31,partition p32,partition p33,partition p34,partition p35,partition p36, partition p37,partition p38,partition p39,partition p40,partition p41,partition p42,partition p43,partition p44,partition p45,partition p46,partition p47,partition p48, partition p49,partition p50,partition p51,partition p52,partition p53,partition p54,partition p55,partition p56,partition p57,partition p58,partition p59,partition p60, partition p61,partition p62,partition p63,partition p64 ) ); </code> And with the local index protecting the foreign key. <code> drop table child purge; drop table parent purge; create table parent (parent_pk_column number(38) primary key, junk_col varchar2(100)); create table child ( child_pk_column number(38) not null , parent_pk_column number(38) not null , junk_col varchar2(100) , constraint child_pk primary key (child_pk_column) using index , constraint child_fk_001 foreign key (parent_pk_column) re...
Categories: DBA Blogs

sqlplus

Tue, 2022-08-30 01:46
I received error msg SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 25 10:18:09 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. SQL> SQL> SQL> SQL> 2 3 4 5 SP2-0640: Not connected SQL> SQL> 2 3 4 SP2-0640: Not connected SQL> SQL> SQL> 2 SP2-0640: Not connected SQL> SP2-0640: Not connected SQL> June month close has been completed successfully APPCTRL: ACCT_PAY_PD_APD has been updated to 13 AAPDC : DOC CODE IPOF, PER 13 have been inserted and PER 12 deleted DMFXJNCL,2022-08-25 10:18:09,INFO, Script DMFXJNCL Completed. my code has sqlplus 2 times: result_fy_per_flags=`sqlplus /nolog <<-EOF>> ${JIC_LOG_PATH}/${PROGRAM_LOG_FILE} connect ${CWA_DB_USR}/${CWA_DB_PSWD}@${CWA_DB_NM} set pagesize 0 feedback off verify off heading off echo off termout off heading off linesize 80 spool /tmp/result_with_data.txt whenever sqlerror exit 12 SELECT FY, PER, CLSD_FL, CLSNG_PROC_RUN_FL FROM FMS01.R_APD WHERE FY = (SELECT PARM_VL FROM ADM01.BS_CATALOG_PARM WHERE PARM_NM = 'FY_CLOSE') AND PER = (SELECT PARM_VL FROM ADM01.BS_CATALOG_PARM WHERE PARM_NM = 'ACTGPRD_CLOSE'); spool off; exit; EOF` tail -2 /tmp/result_with_data.txt>/tmp/last2lines.txt head -1 /tmp/last2lines.txt>/tmp/only4var typeset -i myYEAR=`awk '{print $1}' /tmp/only4var` period=`awk '{print $2}' /tmp/only4var` echo "period was $period">/tmp/period typeset -i clsdFL=`awk '{print $3}' /tmp/only4var` typeset -i clsRUN=`awk '{print $4}' /tmp/only4var` echo "\n** YEAR: $myYEAR *****PERIOD: $period ***********" >> $JIC_LOG_PATH/$PROGRAM_LOG_FILE echo "************************END SQL************************************\n" >> $JIC_LOG_PATH/$PROGRAM_LOG_FILE runTmpValidation if [ ${rc} -eq 0 ] then echo "$SCRIPT,`date +%Y-%m-%d' '%H:%M:%S`,INFO,SQL ${JOB_NAME} ran successfully. Return code = [$rc]" >> $JIC_LOG_PATH/$PROGRAM_LOG_FILE rc=0 else echo "$SCRIPT,`date +%Y-%m-%d' '%H:%M:%S`,INFO,SQL ${JOB_NAME} Failed. Return code = [$rc]" >> $JIC_LOG_PATH/$PROGRAM_LOG_FILE rc=12 fi if [[ $period==12 && $clsdFL=1 && $clsRUN==1 ]]; then echo " if was satisfied $result_fy_per_flags , $period ">/tmp/insidE_if result_fy_per_flags=`sqlplus /nolog <<-EOF1> ${JIC_LOG_PATH}/${PROGRAM_LOG_FILE} set pagesize 0 feedback off verify off heading off echo off termout off heading off linesize 80 whenever sqlerror exit 12 UPDATE IN_APP_CTRL SET PARM_VL='13' WHERE PARM_NM='ACCT_PAY_PD_APD' echo " sqlcode for update of IN_APP_CTRL was $? " commit; INSERT INTO R_DOC_ALW_APD SELECT DOC_TYP_SH_NM, DOC_CD, DOC_CD_SH_NM, DOC_CAT_SH_NM, FY, 13, 1, SYSDATE FROM R_DOC_ALW_APD WHERE DOC_CD='IPOF' AND FPD=12; commit; DELETE FROM R_DOC_ALW_APD WHERE DOC_CD='IPOF' AND FPD=12; commit; exit EOF1` The EOF1 part did not work and produced error msg in the beginning, the connect connect ${CWA_DB_USR}/${CWA_DB_PSWD}@${CWA_DB_NM} copied from before caused script to hanged, should i write disconnect after select ... which worked and before update ...?
Categories: DBA Blogs

DROP Not work to run in sqlplus as file ( @file.sql )

Tue, 2022-08-30 01:46
This not work: sqlplus ---> @drop_indexes.sql DROP_INDEXES.sql DECLARE index_count INTEGER; BEGIN SELECT COUNT(*) INTO index_count FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_XX_1' AND TABLE_OWNER = 'TO'; IF index_count > 0 THEN EXECUTE INMEDIATE 'DROP INDEX TO.IDX_XX_1'; END IF; SELECT COUNT(*) INTO index_count FROM USER_INDEXES WHERE INDEX_NAME = 'IDX_XX_2' AND TABLE_OWNER = 'TO'; IF index_count > 0 THEN EXECUTE INMEDIATE 'DROP INDEX TO.IDX_XX_2'; END IF; ............ END; / Then I run @CREATE_INDEX.sql to create the indexes, and it says the indexes already exist. That is, the indexes were not dropped when running DROP_INDEXES.sql However if I run the scripts ( DROP first and CREATE later) directly (not as a file) in SqlDeveloper, it does work. What can I do, if I need to run both as file script from sqlplus Thank you very much for the help you can give me. Waldo Gomez Alvarez - Vi?a del Mar - Chile
Categories: DBA Blogs

Headers are not fully exporting when Oracle SQL to CSV Export with Batch File

Tue, 2022-08-30 01:46
I have created a batch file to export Oracle SQL data to CSV file, when running only the query in SQL developer it's showing as expected but when I am executing the sql script with command prompt file data is accurate but the headers are not fully showing as expected only few characters are displaying. Below are my SQL to extract the data to CSV file, SET VERIFY OFF SERVEROUTPUT ON WRAP OFF COLUMN TXT FORMAT A121 WORD_WRAPPED SET GENERATE_HEADER = 'NO' COL OBJECT_TYPE FORMAT A10000 COL OBJECT_NAME FORMAT A10000 SET TAB OFF Set Newpage none SET TRIMOUT ON SET TRIMSPOOL ON SET FEEDBACK OFF; SET LINESIZE 32767 SET NUMWIDTH 120 SET COLSEP , SET PAGESIZE 50000 EMBEDDED ON spool D:\salesexport\export.csv SELECT * FROM NUM_EMPLOYEES; EXIT spool off
Categories: DBA Blogs

Logging vs NoLogging

Fri, 2022-08-26 06:06
My Prod environments is like this. Three Node RAC, Active Data guard enabled. There is a partitioned table, month based range partition, every month data will be in one partition. Each partition size is 180G to 200G. I have another table for archival purpose. We will move 3 partition to archival table from main table every quarter. After moving a data to archival table, the index rebuild will take longer time.if drop and recreate index will also be like that and take very longer time.This archival table is also used by application.so index is must. It is global index. To move a data from main to archival table and index rebuild on archival, i am afraid to go for NOLOGGING option. If would use this,whay will happen to my SECONDARY db. Will it be sync because no logging and no redo. My window to do this activity is 6hrs( i can avoid application connection). What is the best method, kindly advise.
Categories: DBA Blogs

Pages