Create and Drop type in Oracle DB

create or replace TYPE “ora_typ_01” AS OBJECT ( delievery_order_number VARCHAR2(20), haulage_number VARCHAR2(20), count_issued NUMBER, count_total NUMBER, document_status VARCHAR2(10) ) Type ora_typ_01 compiled CREATE OR REPLACE EDITIONABLE TYPE ora_typ_01 AS OBJECT ( delievery_order_number VARCHAR2(20), haulage_number VARCHAR2(20), count_issued NUMBER, count_total NUMBER, document_status VARCHAR2(10) ) Type ORA_TYP_01 compiled select * from dba_objects where object_type=’TYPE’ and owner=’LEOSUNNY’; LEOSUNNY ora_typ_01 […]

Archive log mining steps(logminer to html)

1.1        Archive log mining steps(logminer to html): 1.1.1        STEP 1: Execute the below query on the target db alter session set nls_date_format=’dd-mm-yyyy hh24:mi’; select name,thread#,sequence#,first_time,next_time from v$archived_log where first_time between to_date(’05-05-2019 16:00′) and to_date(’05-05-2019 20:00′); <<<<<<<<<<<<<<<<Put the time when the record was inserted on source around example around 2019/05/05 17:43:56.030.651, we need to find corresponding […]

ORA-31623: a job is not attached to this session via the specified handle

ORA-31623: a job is not attached to this session via the specified handle SYMPTOMS UDE-31623: operation generated ORACLE error 31623 ORA-31623: a job is not attached to this session via the specified handle ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3905 ORA-06512: at “SYS.DBMS_DATAPUMP”, line 5203 ORA-06512: at line 1 UDI-31623: operation generated ORACLE error 31623 ORA-31623: a […]

Issues: ACFS-03008: The volume could not be resized

Issues: ACFS-03008: The volume could not be resized Symptoms: oracle@scrboranl007090:/home/oracle % acfsutil size +50G /ogg_home/ acfsutil size: ACFS-03008: The volume could not be resized. The volume expansion limit has been reached. acfsutil size: ACFS-03216: The ADVM compatibility attribute for the diskgroup was below the required version (11.2.0.4.0) for unlimited volume expansions. oracle@scrboranl007090:/home/oracle % source asm.env […]

Exaple: expdp&impdp in parfile

impdp_tab01_user.par directory=DMPDIR dumpfile=expdp_tab01_user.dmp logfile=impdp_tab01_user.log parallel=4 cluster=n EXCLUDE=INDEX EXCLUDE=TABLE_STATISTICS EXCLUDE=INDEX_STATISTICS TABLE_EXISTS_ACTION=REPLACE transform=disable_archive_logging:y transform=storage:n TABLES=USER.tab01 remap_schema=USER:USER remap_tablespace=USERS:USERS impdp c##ogg_user@ORCLPDB1 parfile=impdp_tab01_user.par vi expdp_tab01_user.par DIRECTORY=DMPDIR DUMPFILE=expdp_tab01_user.dmp LOGFILE=expdp_tab01_user.log CLUSTER=N PARALLEL=4 TABLES= USER.tab01 expdp c##ogg_user@pstg parfile=expdp_tab01_user.par vi expdp_orcl_preprod_data_schemas.par DIRECTORY=DMPDIR DUMPFILE=expdp_orcl_preprod_data_schemas_%U.dmp LOGFILE=expdp_orcl_preprod_data_schemas.log FLASHBACK_SCN=1197898541013 CLUSTER=N PARALLEL=4 SCHEMAS=USER,OGG EXCLUDE=TABLE_STATISTICS EXCLUDE=INDEX_STATISTICS vi impdp_orcl_preprod_data_schemas.par directory=DMPDIR dumpfile=expdp_orcl_preprod_data_schemas_1111.dmp logfile=imppdp_orcl_preprod_data_schemas_.log PARALLEL=10 CLUSTER=N SCHEMAS=USER,OGG TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y TRANSFORM=STORAGE:N vi expdp_orcl_preprod_data_user.par DIRECTORY=DMPDIR DUMPFILE=expdp_orcl_preprod_data_user_%U.dmp […]

Alert the usage of tablespace

Alert the usage of tablespace —-Create tablespace for user create tablespace yw_cux_data  datafile ‘/u02/lsdb/yw_cux_data01.dbf’ size 100m; create user lsdb_yw identified by ****** default tablespace yw_cux_data temporary  tablespace temp; grant connect,dba to lsdb_yw; grant select on DBA_FREE_SPACE to lsdb_yw; grant select on  DBA_DATA_FILES to lsdb_yw; grant execute on sys.UTL_TCP to lsdb_yw; grant execute on sys.UTL_SMTP to […]