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
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
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
Wednesday, 12 February 2025
Oracle 23ai sqlplus ping command
tns file !!!!
cat /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
FREE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREE)
)
)
LISTENER_FREE =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
FREEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREEPDB1)
)
)
===
https://www.oracle.com/ie/a/otn/docs/database/oracle-net-23ai-new-features.pdf
on 23ai !!!!!
sqlplus -v
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
new sqlplus ping command
sqlplus -help
Saturday, 8 February 2025
DBMS_SERVICE for Single Instance Databases and status
http://anuj-singh.blogspot.com/2024/12/how-to-create-oracle-database-services.html
edit tnsnames.ora
cd $ORACLE_HOME/network/admin
anujv =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.85.129)(PORT = 1521)))
(CONNECT_DATA =
(SERVICE_NAME = anuj)
)
)
to Create service
BEGIN
DBMS_SERVICE.create_service(
service_name => 'ANUJ',
network_name => 'ANUJ'
);
END;
/
====
BEGIN
DBMS_SERVICE.start_service(
service_name => 'ANUJ'
);
END;
/
COLUMN name FORMAT A30
COLUMN network_name FORMAT A30
SELECT name,
network_name
FROM dba_services
ORDER BY 1;
COLUMN name FORMAT A30
COLUMN network_name FORMAT A30
SELECT name,
network_name
FROM v$active_services
ORDER BY 1;
check the status from both view ..
COLUMN name FORMAT A30
COLUMN network_name FORMAT A30
SELECT 'ACTIVE' name,
network_name
FROM v$active_services
where 1=1
and NETWORK_NAME is not null
union all
SELECT 'DBA***' name,
network_name
FROM dba_services
where 1=1
and NETWORK_NAME is not null
order by 2
;
=====
lsnrctl services |grep -i -A3 ANUJ
sqlplus vihaan1/vihaan1@192.168.85.129:1521/anujv
set linesize 300 pagesize 300
col USERNAME for a20
col MACHINE for a20
col for MACHINE for a20
col PROGRAM for a35
col SERVICE_NAME for a20
col OSUSER for a25
SELECT service_name, inst_id, count(*), username, machine, program, osuser
FROM gv$session
WHERE 1=1
-- and service_name NOT IN ('SYS$BACKGROUND','SYS$USERS')
--AND inst_id=1
GROUP BY service_name, inst_id, username, machine, program, osuser
ORDER BY service_name, inst_id, username, machine, program;
BEGIN DBMS_SERVICE.disconnect_session( service_name => 'ANUJ', disconnect_option => DBMS_SERVICE.immediate ); END; /
Subscribe to:
Posts (Atom)
