在做 oracle 升级或者迁移的时候，例如从 oracle 10g 升级到 11g,从 11g 升级到 19c,亦或者是刚入职刚换项目接触一个数据库的时候，需要查看数据库中的一些信息以便对它有一个更加深入的了解，所以就需要通过一些 sql 语句来了解他，如下整理了一些相关的常用 sql 分享给有需要的朋友，可在公众号后台回复【sql大全四】获取。
作者：jiekexu
来源 | jiekexu dba 之路（id: jiekexu_it）
转载请联系授权 | (微信 id：jiekexu_dba)
sql 大全二 https://www.modb.pro/db/45337
sql 大全四 https://www.modb.pro/doc/103483
oracle dba 日常维护 sql 脚本大全（收藏版） https://www.modb.pro/db/44364
本文 sql 均是在运维工作中总结整理而成的，部分 sql 来源于互联网，但现在已经不知道具体是来源哪个网站，如有侵权，可联系我及时删除，谢谢！
1、查看业务用户相关信息
set line 240
col profile for a20
set pages 999
col username for a25
col account_status for a18
select username,account_status,created,profile,default_tablespace from dba_users where account_status='open' and default_tablespace not in ('system','users','ogg_tbs') order by created asc;
username account_status created profile default_tablespace
------------------------- ------------------ --------- -------------------- ------------------------------
test_py open 25-nov-20 default test_py_data
dba_bak open 08-jun-22 default dba_bak_data
select username,account_status,created,profile,default_tablespace from dba_users
where account_status='open'
and default_tablespace!='users' order by created asc;
2、用户权限收集
drop table scott.t_tmp_user_jieke;
create table scott.t_tmp_user_jieke(id number, username varchar2(50), exec_sql varchar2(4000),create_type varchar2(20) );
drop sequence scott.s_t_tmp_user_jieke;
create sequence scott.s_t_tmp_user_jieke;
begin
for cur in (select d.username,
d.default_tablespace,
d.account_status,
'create user ' || d.username || ' identified by ' ||
d.username || ' default tablespace ' ||
d.default_tablespace || ' temporary tablespace ' ||
d.temporary_tablespace || ';' create_user,
replace(to_char(dbms_metadata.get_ddl('user',d.username)),chr(10),'') create_user1
from dba_users d
--where d.username not in ('anonymous','apex_030200','apex_public_user','appqossys','bi','ctxsys','dbsnmp','dip','dmsys','dvsys','exfsys','flows_files','hr','ix','lbacsys','mddata','mdsys','mgmt_view','oe','olapsys','oracle_ocm','orddata','ordplugins','ordsys','outln','owbsys','owbsys_audit','pm','remote_scheduler_agent','scott','sh','si_informatn_schema','si_informtn_schema','spatial_csw_admin_usr','spatial_wfs_admin_usr','sys','sysman','system','tsmsys','wk_test','wkproxy','wksys','wmsys','xdb','xs$null')) loop
where d.username in ('prod_cc','prod_cb','prod_cf','prod_cu')) loop
--create user
insert into scott.t_tmp_user_jieke
(id, username, exec_sql, create_type)
values
(scott.s_t_tmp_user_jieke.nextval, cur.username, cur.create_user, 'user');
---system privilege
insert into scott.t_tmp_user_jieke
(id, username, exec_sql, create_type)
select scott.s_t_tmp_user_jieke.nextval,
cur.username,
case
when d.admin_option = 'yes' then
'grant ' || d.privilege || ' to ' || d.grantee ||
' with grant option ;'
else
'grant ' || d.privilege || ' to ' || d.grantee || ';'
end priv,
'dba_sys_privs'
from dba_sys_privs d
where d.grantee = cur.username;
---role privilege
insert into scott.t_tmp_user_jieke
(id, username, exec_sql, create_type)
select scott.s_t_tmp_user_jieke.nextval,
cur.username,
case
when d.admin_option = 'yes' then
'grant ' || d.granted_role || ' to ' || d.grantee ||
' with grant option;'
else
'grant ' || d.granted_role || ' to ' || d.grantee || ';'
end priv,
'dba_role_privs'
from dba_role_privs d
where d.grantee = cur.username;
---objects privilege
insert into scott.t_tmp_user_jieke
(id, username, exec_sql, create_type)
select scott.s_t_tmp_user_jieke.nextval,
cur.username,
case
when d.grantable = 'yes' then
'grant ' || d.privilege || ' on ' || d.owner || '.' ||
d.table_name || ' to ' || d.grantee ||
' with grant option ;'
else
'grant ' || d.privilege || ' on ' || d.owner || '.' ||
d.table_name || ' to ' || d.grantee || ';'
end priv,
'dba_tab_privs'
from dba_tab_privs d
where d.grantee = cur.username;
---column privilege
insert into scott.t_tmp_user_jieke
(id, username, exec_sql, create_type)
select scott.s_t_tmp_user_jieke.nextval,
cur.username,
case
when d.grantable = 'yes' then
'grant ' || d.privilege || ' (' || d.column_name || ') on ' ||
d.owner || '.' || d.table_name || ' to ' || d.grantee ||
' with grant option ;'
else
'grant ' || d.privilege || ' (' || d.column_name || ') on ' ||
d.owner || '.' || d.table_name || ' to ' || d.grantee || ';'
end priv,
'dba_col_privs'
from dba_col_privs d
where d.grantee = cur.username ;
end loop;
commit;
end;
/
--select * from scott.t_tmp_user_jieke;
select * from scott.t_tmp_user_jieke where username in ('prod_cc','prod_cb','prod_cf','prod_cu');
可以直接运行exec_sql列来创建用户并赋予相应的权限。另外，可以创建如下的视图：
create or replace view vw_user_privs_prod_cc as
select d.grantee,
case
when d.admin_option = 'yes' then
'grant ' || d.privilege || ' to ' || d.grantee ||
' with grant option ;'
else
'grant ' || d.privilege || ' to ' || d.grantee || ';'
end priv,
'system_grant' type,
'dba_sys_privs' from_view
from dba_sys_privs d
union all
select d.grantee,
case
when d.admin_option = 'yes' then
'grant ' || d.granted_role || ' to ' || d.grantee ||
' with grant option;'
else
'grant ' || d.granted_role || ' to ' || d.grantee || ';'
end priv,
'system_grant' type,
'dba_sys_privs' from_view
from dba_role_privs d
union all
select d.grantee,
case
when d.grantable = 'yes' then
'grant ' || d.privilege || ' on ' || d.owner || '.' ||
d.table_name || ' to ' || d.grantee || ' with grant option ;'
else
'grant ' || d.privilege || ' on ' || d.owner || '.' ||
d.table_name || ' to ' || d.grantee || ';'
end priv,
'system_grant' type,
'dba_sys_privs' from_view
from dba_tab_privs d
union all
select d.grantee,
case
when d.grantable = 'yes' then
'grant ' || d.privilege || ' (' || d.column_name || ') on ' ||
d.owner || '.' || d.table_name || ' to ' || d.grantee ||
' with grant option ;'
else
'grant ' || d.privilege || ' (' || d.column_name || ') on ' ||
d.owner || '.' || d.table_name || ' to ' || d.grantee || ';'
end priv,
'col_grant' type,
'dba_col_privs' from_view
from dba_col_privs d;
这样就可以直接查询某个用户的权限了：
set line 9999
select * from vw_user_privs_prod_cc d where d.grantee = 'prod_cc';
通过系统包dbms_metadata.get_ddl也可以获取用户的权限信息，如下所示：
set long 9999 line 999 pages 999
select dbms_metadata.get_ddl('user', 'prod_cc') ddl_sql from dual
union all
select dbms_metadata.get_granted_ddl('object_grant', 'prod_cc') from dual
union all
select dbms_metadata.get_granted_ddl('role_grant', 'prod_cc') from dual
union all
select dbms_metadata.get_granted_ddl('system_grant', 'prod_cc') from dual;
3、检查时区
select dbtimezone from dual;
dbtime
------
00:00
sql> !date
fri apr 15 16:30:50 cst 2022
4、检查字符集
col parameter for a30
col value for a30
select * from nls_database_parameters where parameter like '%characterset%';
parameter value
------------------------------ ------------------------------
nls_characterset al32utf8
nls_nchar_characterset al16utf16
select userenv('language') from dual;
userenv('language')
----------------------------------------------------
american_america.al32utf8
5、查看补丁信息
col opatch for a30
col comments for a99
select 'opatch',comments from dba_registry_history;
opatc comments
------ ---------------------------------------------------------------------------------------------------
opatch patchset 11.2.0.2.0
opatch patchset 11.2.0.2.0
$oracle_home/opatch/opatch lspatches
29141201;ocw patch set update : 11.2.0.4.190416 (29141201)
29141056;database patch set update : 11.2.0.4.190416 (29141056)
6、检查数据库组件的安装情况
set pages 345 line 456
col comp_name for a40
select comp_id,comp_name,version,status from dba_registry;
select comp_id, status from dba_registry;
7、检查是否使用索引压缩（keycompression）
select index_name,table_name from dba_indexes where compression='enable';
select owner,table_name from dba_tables where owner not in ('sys','system','goldengate','sysman','exfsys','ctxsys','wmsys','apex_030200','dbsnmp','ogg') and iot_type is not null;
8、检查是否存在同名数据文件
select substr(file_name,-6,2) from dba_data_files
where tablespace_name='cc_data' order by 1;
select file_name from dba_data_files
where tablespace_name='cc_data' order by 1;
9、检查永久表空间
select t.tablespace_name tablesapce_name,count(f.file_id),sum(f.bytes/1024/1024/1024) gb
from dba_tablespaces t,dba_data_files f
where t.tablespace_name=f.tablespace_name
and t.contents='permanent'
and t.tablespace_name in ('cc_data','cc_index','cb_data','prod_cf_tbs','prod_cu_data','prod_co_data','cc_gp_data')
group by t.tablespace_name order by 2;
10、检查表空间是否加密
select tablespace_name,encrypted from dba_tablespaces;
select * from dba_encrypted_columns;
11、检查源端compatible参数
show parameter compatible
name type value
------------------------------------ ----------- ------------------------------
compatible string 11.2.0.4.0 --必须大于10.2
如何确定数据库的兼容性级别?
col value for a20
col description for a80
select * from database_compatible_level;
col value clear
col description clear
value description
-------------------- -------------------------------------------------
11.2.0.4.0 database will be completely compatible with this
software version
12、检查业务用户视图
select owner,view_name from dba_views
where owner in (select username from dba_users
where account_status='open' and default_tablespace not in ('users','system'));
13、检查无效对象
select * from dba_invalid_objects;
select count(*) from dba_invalid_objects;
create table scott.tmp_invalid_objects45 as select * from dba_invalid_objects;
--重新编译无效对象
@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlrp.sql
select owner,object_name,object_type,created,last_ddl_time,timestamp from dba_invalid_objects where owner in ('prod_cc','prod_cb','prod_cf','prod_cu');
--如果有用户自定义对象，可使用 dbms_metdata.get_ddl 查看定义语句
set long 9999 pagesize 9999
select dbms_metadata.get_ddl('function','splitstr','prod_cb') from dual;
14、统计源端需要迁移的表空间和数据文件大小
select distinct(tablespace_name) from dba_data_files d
where d.tablespace_name not in ('system','sysaux','undotbs1','undotbs2','undotbs3','undotbs4');
select d.file_id,
d.tablespace_name,
(select (sum(nb.bytes / 1024 / 1024))
from dba_data_files nb
where nb.tablespace_name = d.tablespace_name) ts_size_m,
(d.user_bytes / 1024 / 1024) file_use_size_m
from dba_data_files d
where d.tablespace_name not in
('system', 'sysaux', 'undotbs1', 'undotbs2', 'undotbs3', 'undotbs4')
order by file_id;
15、统计用户表总大小
select d.owner,(sum(bytes)/1024/1024) sizes_m from dba_segments d
where d.owner in ('prod_cc','prod_cb','prod_cf','prod_cu','prod_co')
and not exists (select 1 from dba_recyclebin b
where b.object_name=d.segment_name and d.owner=b.owner)
group by d.owner order by sum(bytes) desc;
select d.owner,(sum(bytes)/1024/1024/1024) sizes_g from dba_segments d
where d.owner in ('cc_sz','cc_cb','cc_op','prod_cu','prod_co')
and not exists (select 1 from dba_recyclebin b
where b.object_name=d.segment_name and d.owner=b.owner)
group by d.owner order by sum(bytes) desc;
owner sizes_g
------------------------------ ----------
cc_sz 1392.85962
cc_cb 105.377991
cc_op 7.37866211
prod_co .458251953
prod_cu .004638672
select (sum(bytes)/1024/1024/1024) sizes_g from dba_segments;
sizes_g
----------
1590.34473
16、统计用户对象的个数和类型
对象总数
select d.owner,count(1) from dba_objects d
where d.owner in ('prod_cc','prod_cb','prod_cf','prod_cu','prod_co')
and d.owner not in ('public')
and not exists (select 1 from dba_recyclebin b where b.object_name=d.object_name and d.owner = b.owner)
group by d.owner order by count(1) desc;
--查找使用自建函数的 sql
select distinct sql_id, sql_text, module
from v$sql,
(select object_name
from dba_objects o
where owner = 'prod_cc'
and object_type in ('function', 'package'))
where (instr(upper(sql_text), object_name) > 0)
and plsql_exec_time > 0
and regexp_like(upper(sql_fulltext), '^[select]')
and parsing_schema_name = 'prod_cc';
对象类型汇总
select d.owner,d.object_type,count(1) from dba_objects d
where d.owner in ('prod_cc','prod_cb','prod_cf','prod_cu','prod_co')
and d.owner not in ('public')
and not exists (select 1 from dba_recyclebin b
where b.object_name=d.object_name and d.owner = b.owner)
group by d.owner,d.object_type
order by count(1) desc;
owner object_type count(1)
------------------------------ ------------------- ----------
prod_cc index 7352
prod_cb index 7125
prod_cf index 4566
prod_cc sequence 1151
prod_cc table 1144
prod_cb sequence 1115
prod_cb table 1106
prod_cf sequence 676
prod_cf table 668
prod_cc lob 126
prod_cb lob 118
prod_cf lob 55
prod_cc function 18
prod_cb function 17
prod_cu index 15
prod_cc procedure 3
prod_cu table 3
prod_cb procedure 2
prod_cu sequence 2
prod_cc trigger 1
prod_cc type 1
prod_cf function 1
22 rows selected.
检查业务用户自建对象
select owner,object_type,object_name from dba_objects d where d.owner in ('prod_cc','prod_cb','prod_cf','prod_cu','prod_co') and object_type not in ('index','sequence','lob','table') order by 2,1;
17、检查无效索引
select owner,index_name,status from dba_indexes
where status='unusable' order by 1,2;
select i.owner,i.index_name,p.partition_name,p.status from dba_ind_partitions p,dba_indexes i
where p.index_name=i.index_name and p.status='unusable' order by 1,2,3;
select i.owner,i.index_name,s.subpartition_name,s.status from dba_ind_subpartitions s,dba_indexes i
where s.index_name=i.index_name and s.status='unusable' order by 1,2,3;
18、确认系统用户是否包含业务对象
--检查sys和system的重复对象，返回如下行则正常。
set line 345
col object_name for a40
select owner,object_name,object_type from dba_objects
where (object_name,object_type)
in (select object_name,object_type from dba_objects where owner='sys')
and owner='system';
owner object_name object_type
------------------------------ ---------------------------------------- -------------------
system aq$_schedules table
system aq$_schedules_primary index
system dbms_repcat_auth package body
system dbms_repcat_auth package
select owner,segment_name,segment_type,tablespace_name from dba_segments
where tablespace_name in('system','sysaux')
and owner in ('prod_cc','prod_cb','prod_cf','prod_cu','prod_co');
19、确认操作系统字节序
select platform_id,platform_name,endian_format
from v$transportable_platform
where platform_name in ('linux x86 64-bit','aix-based systems (64-bit)');
platform_id platform_name endian_format
----------- ------------------------------ --------------
6 aix-based systems (64-bit) big
13 linux x86 64-bit little
20、检查表空间是否具有自包含特性
select a.tablespace_name,round(total/1024/1024/1024) "total g",
round(free/1024/1024/1024) "free g",round((total-free)/total,4)*100 "used%"
from (select tablespace_name,sum(bytes) free from dba_free_space
group by tablespace_name ) a,
(select tablespace_name,sum(bytes) total from dba_data_files
group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by 4;
exec sys.dbms_tts.transport_set_check('cc_data,ccbscf_index,t4_cbmc_data,prod_cf_tbs,prod_cu_data,prod_co_data',true);
select * from sys.transport_set_violations;
21、检查是否存在用户使用 tstz 字段
select c.owner||'.'||c.table_name ||'('||c.column_name ||') -' || c.data_type || '' col
from dba_tab_cols c,dba_objects o
where c.data_type like '%with time zone'
and c.owner=o.owner and c.table_name=o.object_name
and o.object_type='table'
order by col;
22、检查兼容的高级队列（compatible advanced queues）
select owner,queue_table,recipients,compatible
from dba_queue_tables
where recipients='multiple'
and compatible like '%8.0%';
23、检查基于xmlschema的xmltype对象
select distinct owner from dba_xml_schemas;
select distinct p.tablespace_name
from dba_tablespaces p,dba_xml_tables x,dba_users u,all_tables t
where t.table_name=x.table_name and t.tablespace_name=p.tablespace_name
and x.owner=u.username;
24、检查 spatial 空间组件对象
select owner,index_name from dba_indexes where ityp_name='spatial_index';
select owner,table_name,column_name from dba_tab_columns
where data_type='sdo_geometry' and owner!='mdsys'
order by 1,2,3;
25、检查索引组织表、外部表、临时表、物化视图
select owner,table_name from dba_tables
where iot_type is not null and owner in ('prod_cc','prod_cb','prod_cf','prod_cu','prod_co');
select owner,table_name from dba_external_tables
where owner in ('prod_cc','prod_cb','prod_cf','prod_cu','prod_co');
select owner,table_name from dba_tables
where temporary='y' and owner in ('prod_cc','prod_cb','prod_cf','prod_cu','prod_co');
select owner,count(*) from dba_mviews group by owner;
26、检查 opaque types 类型字段
select distinct owner,data_type from dba_tab_columns
where owner in ('prod_cc','prod_cb','prod_cf','prod_cu','prod_co');
27、收集数据字典统计信息
查询最近的统计信息收集
set linesize 200
select max(end_time) latest, operation from dba_optstat_operations
where operation in ('gather_dictionary_stats', 'gather_fixed_objects_stats')
group by operation;
收集统计信息命令如下
execute dbms_stats.gather_dictionary_stats;
execute dbms_stats.gather_fixed_objects_stats;
收集几个聚簇索引的统计信息
--bug 25286819 : cluster index stats not gathered when stale table or dictionary stats are gather
exec dbms_stats.gather_schema_stats('sys');
exec dbms_stats.gather_index_stats('sys','i_obj#');
exec dbms_stats.gather_index_stats('sys','i_file#_block#');
exec dbms_stats.gather_index_stats('sys','i_ts#');
exec dbms_stats.gather_index_stats('sys','i_user#');
exec dbms_stats.gather_index_stats('sys','i_toid_version#');
exec dbms_stats.gather_index_stats('sys','i_mlog#');
exec dbms_stats.gather_index_stats('sys','i_rg#');
28、查看数据字典状态
该 dbupgdiag.sql 脚本在升级之前或之后收集有关数据库状态的诊断信息。从 my oracle support 556610.1 下载脚本，并以数据库 sys 用户身份运行脚本。该脚本在名为 db_upg_diag_sid_timestamp.log 的日志文件中以可读格式生成诊断信息 ，其中 sid 是数据库的 oracle 系统标识符， timestamp 是生成文件的时间。
sql> @dbupgdiag.sql
enter location for spooled output as parameter 1:
enter value for 1: /tmp
more db_upg_diag_jiekedb_21_apr_2022_1102.log
29、确认是否有物化视图刷新
select o.name from sys.obj$ o,sys.user$ u,sys.sum$ s where o.type#=42 and bitand(s.mflags,8)=8;
30、查看数据类型
select distinct(data_type) from all_tab_columns where owner='prod_cc';
select distinct(data_type) from user_tab_columns;
data_type
--------------------------------------------------------------------------------
timestamp(6)
nvarchar2
number
char
clob
date
blob
varchar2
31、确认数据文件不需要介质恢复，且不处于备份（backup）模式
select * from v$recover_file;
select * from v$backup where status !='not active';
32、处理分布式事务
select * from dba_2pc_pending;
如果上面有返回行，执行下面内容。
select local_tran_id from dba_2pc_pending;
execute dbms_transaction.purge_lost_db_entry('');
commit;
33、清理回收站信息
select count(*) from dba_recyclebin;
select owner,object_name,object_type,created,last_ddl_time
from dba_objects where created >=to_date('2023-05-20 15:38:58','yyyy-mm-dd hh24:mi:ss')
and owner!='sys' and object_type='table';
purge dba_recyclebin;
34、检查表空间和数据文件的状态
select tablespace_name,status from dba_tablespaces;
select status,online_status,count(*) from dba_data_files
group by status,online_status;
35、获取创建 dblink 的脚本
col db_link for a15
col username for a15
col host for a45
select * from dba_db_links;
select 'create '||decode(u.name,'public','public ')||'database link '||chr(10)
||decode(u.name,'public',null, 'sys','',u.name||'.')|| l.name||chr(10)
||'connect to ' || l.userid || ' identified by "'||l.password||'" using
'''||l.host||''''
||chr(10)||';' text
from sys.link$ l, sys.user$ u
where l.owner# = u.user#;
36、获取创建表空间语句
创建所有表空间语句，以用于从生产端导入所有权限及用户，相关脚本如下：
set heading off feedback off trimspool on linesize 500
spool create_tablespace.sql
prompt /* ===================== */
prompt /* create user tablespaces */
prompt /* ===================== */
select 'create tablespace ' || tablespace_name ||
' datafile ' ||''' data/jredb/datafile/'||tablespace_name||'.dbf'''||' size 10m
autoextend on;'
from dba_tablespaces
where tablespace_name not in ('system','sysaux','users')
and contents = 'permanent';
prompt /* ===================== */
prompt /* create user temporary tablespaces */
prompt /* ===================== */
select 'create temporary tablespace ' || tablespace_name ||
' tempfile ' ||''' data/jredb/datafile/'||tablespace_name||'.dbf'''||' size 10m
autoextend on;'
from dba_tablespaces
where tablespace_name not in ('temp')
and contents = 'temporary';
spool off
37、查询表空间使用率
set pagesize 1000 linesize 180
tti 'tablespace usage status'
col "total(gb)" for 99,999,999.999
col "usage(gb)" for 99,999,999.999
col "free(gb)" for 99,999,999.999
col "extensible(gb)" for 99,999,999.999
col "max_size(gb)" for 99,999,999.999
col "free pct %" for 999.99
col "used pct of max %" for 999.99
col "no_axf_num" for 9999
col "axf_num" for 999
select d.tablespace_name "tbs_name"
,d.contents "type"
,nvl(a.bytes /1024/1024/1024,0) "total(gb)"
,nvl(a.bytes - nvl(f.bytes,0),0)/1024/1024/1024 "usage(gb)"
,nvl(f.bytes,0)/1024/1024/1024 "free(gb)"
,nvl((a.bytes - nvl(f.bytes,0))/a.bytes * 100,0) "free pct %"
,nvl(a.artacak,0)/1024/1024/1024 "extensible(gb)"
,nvl(a.max_bytes,0)/1024/1024/1024 "max_size(gb)"
,nvl((a.bytes - nvl(f.bytes,0))/ (a.bytes nvl(a.artacak,0)) * 100,0) "used pct of max %"
,a.no_axf_num
,a.axf_num
from sys.dba_tablespaces d,
(select tablespace_name
,sum(bytes) bytes
,sum(decode(autoextensible,'yes',maxbytes - bytes,0 )) artacak
,count(decode(autoextensible,'no',0)) no_axf_num
,count(decode(autoextensible,'yes',0)) axf_num
,sum(decode(maxbytes, 0, bytes, maxbytes)) max_bytes
from dba_data_files
group by tablespace_name
) a,
(select tablespace_name
,sum(bytes) bytes
from dba_free_space
group by tablespace_name
) f
where d.tablespace_name = a.tablespace_name( )
and d.tablespace_name = f.tablespace_name( )
and not (d.extent_management like 'local'and d.contents like 'temporary')
union all
select d.tablespace_name "tbs_name",d.contents "type",nvl(a.bytes /1024/1024/1024,0) "total(gb)",nvl(t.bytes,0)/1024/1024/1024 "usage(gb)",nvl(a.bytes - nvl(t.bytes,0),0)/1024/1024/1024 "free(gb)",nvl(t.bytes/a.bytes * 100,0) "free pct %",nvl(a.artacak,0)/1024/1024/1024 "extensible(gb)",nvl(a.max_bytes,0)/1024/1024/1024 "max_size(gb)",nvl(t.bytes/(a.bytes nvl(a.artacak,0)) * 100,0) "used pct of max %",a.no_axf_num,a.axf_num
from sys.dba_tablespaces d,
(select tablespace_name
,sum(bytes) bytes
,sum(decode(autoextensible,'yes',maxbytes - bytes,0 )) artacak
,count(decode(autoextensible,'no',0)) no_axf_num
,count(decode(autoextensible,'yes',0)) axf_num
,sum(decode(maxbytes, 0, bytes, maxbytes)) max_bytes
from dba_temp_files
group by tablespace_name
) a,
(select tablespace_name
, sum(bytes_used) bytes
from v$temp_extent_pool
group by tablespace_name
) t
where d.tablespace_name = a.tablespace_name( )
and d.tablespace_name = t.tablespace_name( )
and d.extent_management like 'local'
and d.contents like 'temporary%'
order by 3 desc;
38、比对新旧环境中的 profile 是否一致
select distinct(t.pro) from
(select s.profile pro,l.profile pro2
from dba_profiles@dblink s,dba_profiles l
where s.profile = l.profile( )
) t
where t.pro2 is null order by t.pro;
--目标环境需要创建连接源环境的 dblink
39、查看 administer database trigger 权限
检查拥有 administer database trigger 权限的用户。如果用户创建了数据库级别的触发器，则必须要拥有 administer database trigger 权限。
select owner,trigger_name from dba_triggers where base_object_type='database' and owner not in (select grantee from dba_sys_privs where privilege='administer database trigger');
owner trigger_name
------------------------------ ------------------------------
system logon_ip_control
sysman mgmt_startup
grant administer database trigger to owner;
40、无效对象警告和 dba 注册表错误
在开始升级之前，oracle 强烈建议您运行升级前信息工具 ( preupgrd.jar)。
升级前信息工具识别无效的 sys 和 system 对象，以及其他无效对象。用于 utlrp.sql 重新编译无效对象。如果您在升级之前未能执行此操作，则很难确定系统中的哪些对象在开始升级之前是无效的，以及哪些对象由于升级而变得无效。
$oracle_home/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar file text
升级前执行
sql>@/u01/app/oracle/cfgtoollogs/jiekedb/preupgrade/preupgrade_fixups.sql
升级后执行
sql>@/u01/app/oracle/cfgtoollogs/jiekedb/preupgrade/postupgrade_fixups.sql
欢迎关注我的公众号【jiekexu dba之路】，第一时间一起学习新知识！
————————————————————————————
公众号：jiekexu dba之路
csdn ：https://blog.csdn.net/jiekexu
墨天轮：https://www.modb.pro/u/4347
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
最后修改时间：2023-05-19 09:35:30
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。