Scripts:查询数据库中参数文件的信息（在重新建库或者克隆数据库时经常使用）dba_cr_init.sql

-- +----------------------------------------------------------------------------+
-- |                          Jeffrey M. Hunter                                 |
-- |                      [e-mail]                             |
-- |                         www.idevelopment.info                              |
-- |----------------------------------------------------------------------------|
-- |      Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.       |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : dba_cr_init.sql                                                 |
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : This script reads the database instance parameters and creates  |
-- |            an example init.ora file. This is often used when cloning a     |
-- |            database and need a fresh text init.ora file for the new        |
-- |            database.                                                       |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET ECHO        OFF
SET FEEDBACK    OFF
SET HEADING     OFF
SET LINESIZE    32767
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF


CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES


COLUMN oracle_sid   NEW_VALUE xoracle_sid  NOPRINT FORMAT a1


SELECT  value  oracle_sid
FROM    v$parameter
WHERE   name = 'instance_name'
/


spool init&xoracle_sid..ora.sample


SELECT 
    '# +-------------------------------------------------------------------+' || chr(10) ||
    '# | FILE          : init' || i.value || '.ora' || LPAD('|', 43-length(i.value), ' ') || chr(10) || 
    '# | CREATION DATE : ' || 
      to_char(sysdate, 'DD-MON-YYYY') || 
      '                                       |' || chr(10) || 
    '# | DATABASE NAME : ' || d.value || LPAD('|', 51-length(d.value), ' ') || chr(10) ||
    '# | INSTANCE NAME : ' || i.value || LPAD('|', 51-length(i.value), ' ') || chr(10) ||
    '# | SERVER NAME   : ' || s.value || LPAD('|', 51-length(s.value), ' ') || chr(10) ||
    '# | GLOBAL NAME   : ' || g.global_name|| LPAD('|', 51-length(g.global_name), ' ') || chr(10) ||
    '# +-------------------------------------------------------------------+'
FROM
    v$parameter d
  , v$parameter i
  , v$parameter s
  , global_name g
WHERE
      d.name = 'db_name'
  AND i.name = 'instance_name'
  AND s.name = 'service_names';




select 
    '# +---------------------+' || chr(10) ||
    '# | DATABASE PARAMETERS |' || chr(10) ||
    '# +---------------------+'
from dual;


SELECT
  DECODE(isdefault, 'TRUE', '# ') ||
  DECODE(isdefault, 'TRUE', RPAD(name,43), RPAD(name,45)) ||
  ' = ' ||
  value
FROM v$parameter
ORDER BY name;


spool off


SET FEEDBACK    6
SET HEADING     ON