Anuj Singh Oracle DBA

Tuesday, 6 May 2025

Oracle Sql_id Stale statistics


SQL  stale optimizer statistics




define sql_id='5u4sk8mw75xxx'

set pagesize 100
set linesize 150
set trims off
set tab off
set verify off
column table_name format a50
column index_name format a50
column object_type format a40
column owner format a15


PROMPT ==========
PROMPT Tables
PROMPT ==========
with plan_tables as (
select distinct object_name,object_owner, object_type 
from v$sql_plan 
where object_type like 'TABLE%' 
and   sql_id      = '&sql_id')
select t.object_owner owner,
       t.object_name table_name,
       t.object_type object_type,
       decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness   
from   dba_tab_statistics s,
       plan_tables        t
where  s.table_name = t.object_name
and    s.owner      = t.object_owner
and    s.partition_name is null
and    s.subpartition_name is null
order by t.object_owner, t.object_name;

PROMPT ==========
PROMPT Indexes
PROMPT ==========
with plan_indexes as (
select distinct object_name,object_owner, object_type
from v$sql_plan
where object_type like 'INDEX%'
and   sql_id      = '&sql_id')
select i.object_owner owner,
       i.object_name index_name,
       i.object_type object_type,
       decode(stale_stats,'NO','OK',NULL, 'NO STATS!', 'STALE!') staleness
from   dba_ind_statistics s,
       plan_indexes       i
where  s.index_name = i.object_name
and    s.owner      = i.object_owner
and    s.partition_name is null
and    s.subpartition_name is null
order by i.object_owner, i.object_name;



from ..
https://github.com/jkstill/oracle-script-lib/blob/master/sql/stats-sqlid.sql




ttitle off
btitle off

define 1='5u4sk8mw75xxx'
define 2='Y'
col s_diag_pack new_value s_diag_pack noprint
col s_sql_id new_value s_sql_id noprint
var v_sql_id varchar2(13)



set feed off term off 
select '&1' s_sql_id from dual;

set term on

whenever sqlerror exit 128

begin 
	:v_sql_id := '&s_sql_id';
	if 
		length(:v_sql_id) < 1
		or 
		:v_sql_id is null
	then
		raise value_error;
	end if;
end;
/

whenever sqlerror continue
set feed on


define 2='Y'

set feed off term off 
select decode(upper('&2'),'Y','','--') s_diag_pack from dual;
set feed on term on


set pagesize 100 linesize 300 trimspool on  heading on

col partition_start format a6 head 'PSTART'
col sql_id format a13
col partition_stop format a6 head 'PSTOP'
col owner format a20
col table_name format a30
col index_name format a40
col phv format a60 wrap
col last_analyzed format a19
col stale_stats format a12 head 'stale_stats'
col num_rows format 99,999,999,999
col blocks format 9,99,999,999
col partition_position format 999999 head 'PP'
col TABLE_NAME  for a27
break on sql_id skip 1

--spool stats-sqlid.txt

with objects as (
	-- extra inline view is to eliminate duplicates in listagg()
	select 
		sql_id
		, listagg(phv,',') within group(order by phv)  phv
		, object_owner
		, object_name
		, object_type
		, partition_start
		, partition_stop
	from (
		select distinct
			sql_id
			, phv
			, object_owner
			, object_name
			, object_type
			, partition_start
			, partition_stop
		from (
			select 
				sql_id
				, plan_hash_value phv
				, object_owner
				, object_name
				, object_type
				, case partition_start
					when 'ROW LOCATION' then 'ROWID'
					else partition_start
				end partition_start
				, case partition_stop
					when 'ROW LOCATION' then 'ROWID'
					else partition_stop
				end partition_stop
			from v$sql_plan
			where sql_id = :v_sql_id
			and object_owner is not null
			and object_type in ('TABLE','INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)','CLUSTER','TABLE (FIXED)')
			&s_diag_pack union all
			&s_diag_pack select 
				&s_diag_pack sql_id
				&s_diag_pack , plan_hash_value phv
				&s_diag_pack , object_owner
				&s_diag_pack , object_name
				&s_diag_pack , object_type
				&s_diag_pack , case partition_start
					&s_diag_pack when 'ROW LOCATION' then 'ROWID'
					&s_diag_pack else partition_start
				&s_diag_pack end partition_start
				&s_diag_pack , case partition_stop
					&s_diag_pack when 'ROW LOCATION' then 'ROWID'
					&s_diag_pack else partition_stop
				&s_diag_pack end partition_stop
			&s_diag_pack from dba_hist_sql_plan
			&s_diag_pack where sql_id = :v_sql_id
			&s_diag_pack and object_owner is not null
			&s_diag_pack and object_type in ('TABLE','INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)','CLUSTER','TABLE (FIXED)')
		)
	)
	group by
		sql_id
		, object_owner
		, object_name
		, object_type
		, partition_start
		, partition_stop
),
indexes as (
	select * from objects where object_type in ('INDEX','INDEX (UNIQUE)','INDEX (CLUSTER)')
),
tables as (
	select * from objects where object_type in ('TABLE','CLUSTER','TABLE (FIXED)')
)
select 
	sql_id
	, phv
	, owner
	, table_name
		|| decode(s.partition_name, null,'','.' || s.partition_name)
		as table_name
	, null index_name
	, partition_position
	, t.partition_start
	, t.partition_stop
	, num_rows
	, blocks
	, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed
	, stale_stats
from dba_tab_statistics s
 join tables t on t.object_owner = s.owner
	and t.object_name = s.table_name
union all
select 
	sql_id
	, phv
	, owner
	, table_name 
	, index_name
		|| decode(s.partition_name, null,'','.' || s.partition_name)
		as index_name
	, partition_position
	, i.partition_start
	, i.partition_stop
	, num_rows
	, leaf_blocks blocks
	, to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') last_analyzed
	, stale_stats
from dba_ind_statistics s
 join indexes i on i.object_owner = s.owner
	and i.object_name = s.index_name
order by sql_id
	, owner
	, table_name
	, index_name nulls first
	, partition_position nulls first
/

--spool off

Tuesday, 25 March 2025

segment type on tablespace

segment type on tablespace
=============================================================================


-- segment_type_per_tbs.sql

set linesize 200 pages 9999
col bytesh for a12
col OWNER for a20
column counted format 99G999
column mb format 9G999G999D99
compute sum of mb on owner report
compute sum of counted on owner report

break on owner skip 1 on tablespace_name on report

select owner, tablespace_name, segment_type, sum(bytes)/1024/1024 MB,dbms_xplan.format_size(sum(bytes)) bytesh ,count(*) counted
from dba_segments
where owner not in ( 'DBSNMP', 'DIP', 'MGMT_VIEW', 'ORACLE_OCM', 'OUTLN', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS' )
group by owner, tablespace_name, segment_type
order by owner, tablespace_name, segment_type;

===================================
--- all the cdb 
set linesize 200 pages 9999
col bytesh for a12
col OWNER for a20
column counted format 99G999
column mb format 9G999G999D99
compute sum of mb on owner report
compute sum of counted on owner report

break on owner skip 1 on tablespace_name on report

select con_id,owner, tablespace_name, segment_type, sum(bytes)/1024/1024 MB,dbms_xplan.format_size(sum(bytes)) bytesh ,count(*) counted
from cdb_segments
where owner not in ( 'DBSNMP', 'DIP', 'MGMT_VIEW', 'ORACLE_OCM', 'OUTLN', 'SYS', 'SYSMAN', 'SYSTEM', 'TSMSYS', 'WMSYS' )
group by  con_id,owner, tablespace_name, segment_type
order by con_id,owner, tablespace_name, segment_type;

Monday, 10 March 2025

ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0



idle sqlplus> startup nomount pfile='/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initvihcdbd8x.ora';
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0





 sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 10 05:36:54 2025

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected.
idle sqlplus> startup ;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0





[oracle@rac02 dbs]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 10 05:43:36 2025

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01089: immediate shutdown or close in progress - no operations are permitted


**************************************************************************************************************************************************************

Before this  Cannot login to the Database after Changing $ORACLE_HOME Directory Permission ( ORA-01012 ) (Doc ID 2497191.1)


Try below !!!!!!!!!!!!!!!!


https://anuj-singh.blogspot.com/2013/12/how-to-use-prelim-option-in-sqlplus.html


 echo $ORACLE_SID
vihcdbd8x

[oracle@ibrac02 dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.2.0/dbhome_1

[oracle@rac02 dbs]$






[oracle@rac02 dbs]$ sqlplus -prelim

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 10 05:48:28 2025

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: / as sysdba

idle sqlplus> shutdown abort ;
ORACLE instance shut down.


idle sqlplus>


pwd
/u01/app/oracle/product/12.2.0/dbhome_1



idle sqlplus> startup nomount pfile='initvihcdbd8x.ora'
ORACLE instance started.

Total System Global Area 2.2750E+10 bytes
Fixed Size                 19421832 bytes
Variable Size            3355444600 bytes
Database Buffers         1.9327E+10 bytes
Redo Buffers               47685632 bytes
idle sqlplus>

Friday, 7 March 2025

Oracle copy command !!!!

Oracle copy command !!!! ==================================================================



SQL> alter session set container=PDB1 ;

Session altered.



SQL> create table dba_table1 as select * from dba_tables where 1=1 ;

Table created.



SET ARRAYSIZE 1000 COPYCOMMIT 2

copy from system/sys@//192.168.1.120:1521/pdb1.localdomain insert dba_table1 using select * from dba_tables ;

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
   2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2185 rows inserted into DBA_TABLE1.
   2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.




SQL> select count(*) from dba_table1 ;

  COUNT(*)
----------
      4369




SQL>
SET ARRAYSIZE 1000 COPYCOMMIT 2

copy from system/sys@//192.168.1.120:1521/pdb1.localdomain insert dba_table1 using select * from dba_tables ;
Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
   2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2185 rows inserted into DBA_TABLE1.
   2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.

SQL> select count(*) from dba_table1 ;

  COUNT(*)
----------
      6554

SQL>


copy from system/sys@//192.168.1.120:1521/pdb1.localdomain append dba_table1 using select * from dba_tables ;


copy from system/sys@//192.168.1.120:1521/pdb1.localdomain append dba_table1 using select * from dba_tables ;SQL>

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
   2185 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2185 rows inserted into DBA_TABLE1.
   2185 rows committed into DBA_TABLE1 at DEFAULT HOST connection.

SQL> select count(*) from dba_table1 ;

  COUNT(*)
----------
      8739


===


SQL> COPY FROM system/sys@//192.168.1.120:1521/pdb1.localdomain TO system/sys@//192.168.1.120:1521/pdb1.localdomain  APPEND DBA_TABLE1 using select * from dba_tables ;

Array fetch/bind size is 1000. (arraysize is 1000)
Will commit after every 2 array binds. (copycommit is 2)
Maximum long size is 80. (long is 80)
Table DBA_TABLE1 created.

   2186 rows selected from system@//192.168.1.120:1521/pdb1.localdomain.
   2186 rows inserted into DBA_TABLE1.
   2186 rows committed into DBA_TABLE1 at system@//192.168.1.120:1521/pdb1.localdomain.

SQL>

Tuesday, 25 February 2025

Find Oracle Objects ...

Find Oracle Objects

============================================

set pagesize 200 linesize 200
set term on feed on
--set term off feed off
col object_name         format a30
col object_type         format a30
col owner               format a15
col created             format a21
col last_ddl_time       format a21
col status              format a10


select distinct * from (select 
'D' con_id
,object_name
	,object_type
	, owner
	, status
	, to_char(created,'DD/MM/yyyy hh24:mi:ss') created
	, to_char(last_ddl_time,'DD/MM/yyyy hh24:mi:ss') last_ddl_time
from dba_objects
where object_name like upper('%&&uobject%')
union all
select 
to_char(con_id)
,object_name
	,object_type
	, owner
	, status
	, to_char(created,'DD/MM/yyyy hh24:mi:ss') created
	, to_char(last_ddl_time,'DD/MM/yyyy hh24:mi:ss') last_ddl_time
from cdb_objects
where object_name like upper('%&&uobject%')
union all
select 'v' con_id,name object_name, type object_type, 'SYS' owner, 'FIXED' status, null created, null last_ddl_time
from v$fixed_table
where name like upper('%&&uobject%')
order by object_name
)
/
undefine uobject

Sunday, 23 February 2025

How to export PDB tables with out password with sys user ?

 How to export PDB tables with out password with sys user ?
========================================================================================



export ORACLE_PDB_SID=ANUJGP
expdp \'/ as sysdba\' schemas=ANUJ NOLOGFILE=y ESTIMATE_ONLY=y include=TABLE:"IN('JOB_LIST_143')"




Export: Release 19.0.0.0.0 - Production on Sun Feb 23 15:17:50 2025
Version 19.16.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" schemas=ANUJ NOLOGFILE=y ESTIMATE_ONLY=y include=TABLE:IN('JOB_LIST_143')
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.  estimated "ANUJ"."JOB_LIST_143"                      192 KB
Total estimation using BLOCKS method: 192 KB
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Feb 23 15:18:09 2025 elapsed 0 00:00:18


******************************************

export ORACLE_PDB_SID=ANUJGP
expdp \'/ as sysdba\' schemas=ANUJ directory=ORACLE_BASE  dumpfile=JOB_LIST1.dmp logfile=JOB_LIST1.log include=TABLE:"IN('LIST_143')" compression=all ESTIMATE=STATISTICS metrics=Y JOB_NAME=JOB_LISTEXP



 expdp \'/ as sysdba\' schemas=ANUJ directory=ORACLE_BASE  dumpfile=JOB_LIST1.dmp logfile=JOB_LIST1.log include=TABLE:"IN('LIST_143')" compression=all ESTIMATE=STATISTICS metrics=Y JOB_NAME=JOB_LISTEXP

Export: Release 19.0.0.0.0 - Production on Sun Feb 23 15:34:49 2025
Version 19.16.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."JOB_LISTEXP":  "/******** AS SYSDBA" schemas=ANUJ directory=ORACLE_BASE dumpfile=JOB_LIST1.dmp logfile=JOB_LIST1.log include=TABLE:IN('LIST_143') compression=all ESTIMATE=STATISTICS metrics=Y JOB_NAME=JOB_LISTEXP
W-1 Startup took 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
W-1 .  estimated "ANUJ"."LIST_143"                    5.481 KB
W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
W-1      Completed 2 INDEX_STATISTICS objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
W-1      Completed 1 TABLE_STATISTICS objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
W-1      Completed 1 PROCACT_INSTANCE objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/TABLE
W-1      Completed 1 TABLE objects in 0 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
W-1      Completed 1 INDEX objects in 1 seconds
W-1 Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
W-1      Completed 1 CONSTRAINT objects in 1 seconds
W-1 . . exported "ANUJ"."LIST_143"                    4.820 KB       1 rows in 0 seconds using direct_path
W-1      Completed 1 SCHEMA_EXPORT/TABLE/TABLE_DATA objects in 0 seconds
W-1 Master table "SYS"."JOB_LISTEXP" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.JOB_LISTEXP is:
  /u01/app/oracle/JOB_LIST1.dmp
Job "SYS"."JOB_LISTEXP" successfully completed at Sun Feb 23 15:36:15 2025 elapsed 0 00:01:25


=======

nohup expdp "'/ as sysdba'" DUMPFILE=DIR:XXX_%U.dmp SCHEMAS=anuj LOGFILE=DIR:anuj.log cluster=N parallel=4 status=10 metrics=Y ESTIMATE=STATISTICS CONSISTENT=Y &

==============

How to export and import Unified Audit Trail from > Oracle 18c

How to export and import Unified Audit Trail from  > Oracle 18c
==================================================================

Via old method

exp \"/ as sysdba\" file=expaudtable.dmp log=expaudtable.log tables=sys.aud$

https://anuj-singh.blogspot.com/2016/11/oracle-datapump-dir-grant-info.html

expdp \"/ as sysdba\" full=y directory=ORACLE_BASE  logfile=unified_audit_trail.dmp.log dumpfile=unified_audit_trail.dmp INCLUDE=AUDIT_TRAILS

Export: Release 19.0.0.0.0 - Production on Sun Feb 23 14:03:48 2025
Version 19.16.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production




Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA" full=y directory=ORACLE_BASE logfile=unified_audit_trail.dmp.log dumpfile=unified_audit_trail.dmp INCLUDE=AUDIT_TRAILS

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW"               6.078 KB      36 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5177"          204.5 MB  664247 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5986"          106.6 MB  350671 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5831"          84.63 MB  275667 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5347"          40.27 MB  124343 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5513"          30.60 MB   95274 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P181"           41.19 MB   31632 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P6152"          26.15 MB   85764 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5665"          24.40 MB   77046 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3069"          3.388 MB    7075 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P457"           8.178 MB    3558 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3256"          1.510 MB    3023 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P4655"          1.249 MB    2205 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P5002"          1.159 MB    3171 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3966"          1.178 MB    2198 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P4151"          813.7 KB    1985 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P4325"          714.2 KB    1261 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P4836"          507.6 KB     869 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3796"          483.1 KB     854 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P2745"          646.2 KB     434 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P4485"          293.0 KB     462 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3630"          218.0 KB     352 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P3454"          115.6 KB     149 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P2499"          109.2 KB     105 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P2912"          84.62 KB      70 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P1987"          67.64 KB      42 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P1335"          65.55 KB      32 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P2227"          59.61 KB      19 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P1130"          51.52 KB       2 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$"                   7.242 KB       1 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.585 KB      18 rows
. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0"         0 KB       0 rows
. . exported "SYS"."AUD$"                                    0 KB       0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$"                     0 KB       0 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"               5.953 KB       2 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT"                      0 KB       0 rows
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /u01/app/oracle/unified_audit_trail.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Sun Feb 23 14:07:13 2025 elapsed 0 00:03:18



 ls -lthr /u01/app/oracle/unified_audit_trail.dmp
-rw-r----- 1 oracle asmadmin 581M Feb 23 14:07 /u01/app/oracle/unified_audit_trail.dmp





===================

with datapump api 



declare
l_datapump_handle NUMBER; -- Data Pump job handle
l_datapump_dir VARCHAR2(20) := 'ORACLE_BASE'; -- Data Pump Directory
l_status varchar2(200); -- Data Pump Status
begin
l_datapump_handle := dbms_datapump.open(operation => 'EXPORT', -- operation = EXPORT, IMPORT, SQL_FILE
job_mode =>'FULL', -- job_mode = FULL, SCHEMA, TABLE, TABLESPACE, TRANSPORTABLE
job_name => 'AUD FULL EXPORT JOB RUN 002', -- job_name = NULL (default) or: job name (max 30 chars)
version => 'latest'); -- version = COMPATIBLE (default), LATEST (dbversion), a value (11.0.0 or 12)
dbms_datapump.add_file(handle => l_datapump_handle,filename => 'exp_FULL_AUD_%U.dmp',directory => l_datapump_dir);
dbms_datapump.add_file(handle => l_datapump_handle,filename => 'exp_FULL_AUD.log' ,directory => l_datapump_dir ,filetype => DBMS_DATAPUMP.ku$_file_type_log_file);
dbms_datapump.metadata_filter(handle => l_datapump_handle, name => 'EXCLUDE_PATH_EXPR', value => 'IN(''AUDIT_TRAILS'')');
dbms_datapump.set_parameter(l_datapump_handle,'CLIENT_COMMAND','Full Consistent Data Pump Export of AUD Schema HR with PARALLEL 8');
dbms_datapump.set_parameter(l_datapump_handle,'FLASHBACK_TIME','SYSTIMESTAMP');
dbms_datapump.set_parallel(l_datapump_handle,8); -----<<< parallel 
dbms_datapump.start_job(handle => l_datapump_handle);
dbms_datapump.wait_for_job(handle => l_datapump_handle,
job_state => l_status );
dbms_output.put_line( l_status );
end;
/



-rw-r--r--   1 oracle asmadmin         0 Feb 23 14:14 exp_FULL_AUD.log
-rw-r-----   1 oracle asmadmin     16384 Feb 23 14:15 exp_FULL_AUD_07.dmp
-rw-r-----   1 oracle asmadmin     36864 Feb 23 14:15 exp_FULL_AUD_03.dmp
-rw-r-----   1 oracle asmadmin     57344 Feb 23 14:16 exp_FULL_AUD_02.dmp
-rw-r-----   1 oracle asmadmin     20480 Feb 23 14:16 exp_FULL_AUD_04.dmp
-rw-r-----   1 oracle asmadmin     28672 Feb 23 14:16 exp_FULL_AUD_01.dmp
-rw-r-----   1 oracle asmadmin     24576 Feb 23 14:16 exp_FULL_AUD_06.dmp
-rw-r-----   1 oracle asmadmin     16384 Feb 23 14:16 exp_FULL_AUD_05.dmp

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Tuesday, 6 May 2025

Oracle Sql_id Stale statistics

Tuesday, 25 March 2025

segment type on tablespace

Monday, 10 March 2025

ORA-01012: not logged on Process ID: 0 Session ID: 0 Serial number: 0

Friday, 7 March 2025

Oracle copy command !!!!

Tuesday, 25 February 2025

Find Oracle Objects ...

Sunday, 23 February 2025

How to export PDB tables with out password with sys user ?

How to export and import Unified Audit Trail from > Oracle 18c

Oracle DBA

anuj blog Archive