主页 / dba / 问题 / 252321
Accepted
Darrell
Asked: 2019-11-01 07:39:20 +0800 CST

将 SQL 查询输出导出到调度程序作业中的文件

  • 772

我需要将数据导出到调度程序作业中的 csv 文件。以下是命令：

SPOOL "\\AServerName\Output\RMS_DataFileSizes.csv" REPLACE;
SELECT /*csv*/
  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS "CurrentDTM",
  df.tablespace_name AS "Tablespace Name",
  SUBSTR(df.file_name,INSTR(df.file_name,'RMS_'),20) AS "File Name",
  Round(df.bytes/1073741824,2) AS "Size (GB)",
  decode(e.used_bytes,NULL,0,Round(e.used_bytes/1073741824,2)) AS "Used (GB)",
  decode(f.free_bytes,NULL,0,Round(f.free_bytes/1073741824,2)) AS "Free (GB)",
  decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,1)) AS "% Used"
FROM DBA_DATA_FILES DF,
     (SELECT file_id,sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
     (SELECT sum(bytes) free_bytes,file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
  AND df.file_id  = f.file_id (+)
  AND df.tablespace_name = 'RMS_DATA'
ORDER BY df.tablespace_name,df.file_name;
SPOOL OFF;

当我将“作业类型”设为“PL/SQL 块”时，它会失败并且出现以下错误：

"ORA-06550: line 1, column 764:
PLS-00103: Encountered the symbol "\\AServerName\Output\RMS_DataFileSizes.csv" when expecting one of the following:

   := . ( @ % ;
ORA-06550: line 17, column 7:
PLS-00103: Encountered the symbol "OFF" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "OFF" to continue.

我究竟做错了什么？

oracle oracle-12c

2 个回答

  1. 稍微不同的方法怎么样？

    将该代码放入 .SQL 文件中，我们称之为p.sql：

    spool p.txt

SELECT /*csv*/
  TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS "CurrentDTM",
  df.tablespace_name AS "Tablespace Name",
  SUBSTR(df.file_name,INSTR(df.file_name,'RMS_'),20) AS "File Name",
  Round(df.bytes/1073741824,2) AS "Size (GB)",
  decode(e.used_bytes,NULL,0,Round(e.used_bytes/1073741824,2)) AS "Used (GB)",
  decode(f.free_bytes,NULL,0,Round(f.free_bytes/1073741824,2)) AS "Free (GB)",
  decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,1)) AS "% Used"
FROM DBA_DATA_FILES DF,
     (SELECT file_id,sum(bytes) used_bytes FROM dba_extents GROUP by file_id) E,
     (SELECT sum(bytes) free_bytes,file_id FROM dba_free_space GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
  AND df.file_id  = f.file_id (+)
  AND df.tablespace_name = 'RMS_DATA'
ORDER BY df.tablespace_name,df.file_name;

spool off
exit

    我在 MS Windows 上，所以 - 创建一个 .BAT 文件；让我们称之为p.bat：

    sqlplus sys/pwd as sysdba @p.sql

    最后，使用您的操作系统调度程序（我的 Windows 上的任务管理器）并安排p.bat尽可能多地运行，例如

    • 让它在每天 22:00 运行
    • action = 启动一个程序（导航到p.bat）
    • 可选（我建议你使用它），设置“开始”目录

    应该管用; 至少，对我有用。

    • 0
  2. Best Answer

    这不是你这样做的方式。PLSQL_BLOCK打字作业不接受此类命令。使用SQL_SCRIPT类型作业。例子：

    begin
  dbms_credential.create_credential(
    credential_name => 'oracle_user',
    username        => 'oracle',
    password        => 'Oracle123'
  );
end;
/

begin
  dbms_scheduler.create_job(
    job_name        => 'my_script_job',
    job_type        => 'sql_script',
    job_action      => 'conn bp/bp
set markup csv on
spool "/home/oracle/output.txt"
select tablespace_name, file_id, bytes/1024/1024 as size_mb from dba_data_files;
spool off',
    credential_name => 'oracle_user',
    enabled         => true,
    auto_drop       => false
  );
end;
/

$ cat /home/oracle/output.txt
SQL> select tablespace_name, file_id, bytes/1024/1024 as size_mb from dba_data_files;

"TABLESPACE_NAME","FILE_ID","SIZE_MB"
"SYSTEM",1,700
"SYSAUX",2,870
"UNDOTBS1",3,295
"USERS",4,112.5
"TEST",5,100

    请记住，SET MARKUP CSV ON是 12.2 的功能。它不适用于 12.1。 /*csv*/是 SQL Developer 的一项功能，它也不起作用。

    • 0

相关问题