Oracle Sqlplus显示不足问题

原创

wx62e28ac222a20 2022-08-04 09:41:43 ©著作权

文章标签 oracle safari 视频处理 ios sql 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者wx62e28ac222a20的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.在执行sqlplus / as sysdba中，往往很多时候，需要
set pagesize 200
set linesize 200
等等，能不能在执行sqlplus的时候，就把这些参数设置好？
答案是可以的

[root@node02]# su - oracle
[oracle@node02]$ cd /u01/app/oracle/product/19.3.0/db/sqlplus/admin
[oracle@node02:/u01/app/oracle/product/19.3.0/db/sqlplus/admin]$ cat glogin.sql
--OracleBegin
define _editor=vi
set serveroutput on size 1000000
set long 200
set timing on
set linesize 500
set pagesize 9999
set trimspool on
col Name format a80
set termout off
col global_name new_value gname
define gname=idle
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0,length(global_name), dot-1) ) global_name from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname _DATE> '
ALTER SESSION SET nls_date_format = 'HH24:MI:SS';
set termout on

col TABLESPACE_NAME for a20
select tbs_used_info.tablespace_name,
       tbs_used_info.alloc_mb,
       tbs_used_info.used_mb,
       tbs_used_info.max_mb,
       tbs_used_info.free_of_max_mb,
       tbs_used_info.used_of_max || '%' used_of_max_pct
  from (select a.tablespace_name,
               round(a.bytes_alloc / 1024 / 1024) alloc_mb,
               round((a.bytes_alloc - nvl(b.bytes_free,
                                          0)) / 1024 / 1024) used_mb,
               round((a.bytes_alloc - nvl(b.bytes_free,
                                          0)) * 100 / a.maxbytes) used_of_max,
               round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free,
                                                       0)) / 1048576) free_of_max_mb,
               round(a.maxbytes / 1048576) max_mb
          from (select f.tablespace_name,
                       sum(f.bytes) bytes_alloc,
                       sum(decode(f.autoextensible,
                                  'YES',
                                  f.maxbytes,
                                  'NO',
                                  f.bytes)) maxbytes
                  from dba_data_files f
                 group by tablespace_name) a,
               (select f.tablespace_name,
                       sum(f.bytes) bytes_free
                  from dba_free_space f
                 group by tablespace_name) b
         where a.tablespace_name = b.tablespace_name(+)) tbs_used_info
 order by tbs_used_info.used_of_max desc;

col status for a10
col input_type for a20
col INPUT_BYTES_DISPLAY for a10
col OUTPUT_BYTES_DISPLAY for a10
col TIME_TAKEN_DISPLAY for a10

select input_type,
       status,
       to_char(start_time,
               'yyyy-mm-dd hh24:mi:ss'),
       to_char(end_time,
               'yyyy-mm-dd hh24:mi:ss'),
       input_bytes_display,
       output_bytes_display,
       time_taken_display,
       COMPRESSION_RATIO
  from v$rman_backup_job_details
 where start_time > date '2021-07-01'
 order by 3 desc;
 --OracleEnd