oracle删除一些对象的测试

conn jyc/jyc
-- 创建作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'my_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_OUTPUT.PUT_LINE("Job executed"); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
    enabled         => TRUE
  );
END;
/

-- 创建存储过程
CREATE OR REPLACE PROCEDURE my_procedure AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Procedure executed');
END;
/


-- 创建包
CREATE OR REPLACE PACKAGE my_package AS
  TYPE my_type IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
  FUNCTION my_function RETURN NUMBER;
END my_package;
/

-- 创建包体
CREATE OR REPLACE PACKAGE BODY my_package AS
  -- 包体实现
  my_function FUNCTION RETURN NUMBER IS
  BEGIN
    RETURN 42; -- 返回一个示例值
  END my_function;
END my_package;
/

-- 创建视图
CREATE OR REPLACE VIEW my_view AS
SELECT * FROM dual;


[oracle@lnkf ~]$ expdp jyc/jyc directory=oradmp dumpfile=jyc2.dmp logfile=j.log schemas=jyc content=metadata_only

Export: Release 11.2.0.4.0 - Production on Tue Jan 16 08:57:47 2024

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JYC"."SYS_EXPORT_SCHEMA_01":  jyc/******** directory=oradmp dumpfile=jyc2.dmp logfile=j.log schemas=jyc content=metadata_only 
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Master table "JYC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_SCHEMA_01 is:
  /oracle/dmp/jyc2.dmp
Job "JYC"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Jan 16 08:58:11 2024 elapsed 0 00:00:22



SQL> show user;
USER is "JYC"



col object_name for a30
set wrap off
set line 160
select owner,object_name,OBJECT_TYPE, LAST_DDL_TIME,CREATED from dba_objects where owner not in('SYS','SYSTEM','OWBSYS_AUDIT','OWBSYS','APEX_PUBLIC_USER','APEX_030200','FLOWS_FILES','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','MDDATA','OLAPSYS','ORDDATA','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','ORDSYS','XDB','ANONYMOUS','CTXSYS','EXFSYS','WMSYS','APPQOSSYS','DBSNMP','ORACLE_OCM','DIP','OUTLN','MGMT_VIEW','XS$NULL','SYSMAN') and LAST_DDL_TIME>=to_date('2024-01-12','yyyy-mm-dd');

OWNER                          OBJECT_NAME                    OBJECT_TYPE         LAST_DDL_TIME       CREATED
------------------------------ ------------------------------ ------------------- ------------------- -------------------
JYC                            MY_PROCEDURE                   PROCEDURE           2024-01-16 09:21:37 2024-01-16 09:21:37
JYC                            MY_PACKAGE                     PACKAGE             2024-01-16 09:21:37 2024-01-16 09:21:37
JYC                            MY_PACKAGE                     PACKAGE BODY        2024-01-16 09:21:38 2024-01-16 09:21:38
JYC                            MY_VIEW                        VIEW                2024-01-16 09:21:37 2024-01-16 09:21:37
JYC                            MY_JOB                         JOB                 2024-01-16 09:21:41 2024-01-16 09:21:38
执行删除操作：
drop PROCEDURE jyc.MY_PROCEDURE;
EXECUTE DBMS_SCHEDULER.STOP_JOB('JYC.MY_JOB');
EXECUTE DBMS_SCHEDULER.DROP_JOB('JYC.MY_JOB');
drop PACKAGE jyc.MY_PACKAGE;
drop PACKAGE BODY jyc.MY_PACKAGE;
drop view jyc.MY_VIEW;

导入：
[oracle@lnkf ~]$ impdp jyc/jyc directory=oradmp dumpfile=jyc2.dmp logfile=j2.log schemas=jyc content=metadata_only

Import: Release 11.2.0.4.0 - Production on Tue Jan 16 09:21:35 2024

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "JYC"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "JYC"."SYS_IMPORT_SCHEMA_01":  jyc/******** directory=oradmp dumpfile=jyc2.dmp logfile=j2.log schemas=jyc content=metadata_only 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JYC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "JYC"."T2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."T" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."J2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."J1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"JYC"."V_T" already exists
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082: Object type PACKAGE_BODY:"JYC"."MY_PACKAGE" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Job "JYC"."SYS_IMPORT_SCHEMA_01" completed with 8 error(s) at Tue Jan 16 09:21:38 2024 elapsed 0 00:00:02


关于删除其它用户非DBMS_SCHEDULER的JOB处理：
conn jyc/jyc测试创建job:
declare 
job number;   
begin
sys.dbms_job.submit(job,'MY_PROCEDURE;',to_date('08-05-2024 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),'TRUNC(sysdate) + 1 +1 / (24)');
end;
/
commit;
select * from dba_jobs; 
使用sys用户删除：
SQL> select job, priv_user, what from dba_jobs;

       JOB PRIV_USER                      WHAT
---------- ------------------------------ ----------------------------------------------------------------------------------------------------------------------
       254 JYC                            MY_PROCEDURE;

SQL> exec sys.dbms_ijob.remove(254);
删除job脚本：
begin
  for v in(select job from user_jobs)
loop
dbms_job.remove(v.job);
  end loop;
  commit;
end;
/
commit;