get partition definition using function

获取所有RANGE分区的表分区的定义,脚本运行环境为Oracle 11.2.0.4,Oracle 10g去掉INTERVAL字段。

--创建函数
create or replace function long_to_varchar(p_table_owner    in all_tab_partitions.table_owner%type,
                                          p_table_name     in all_tab_partitions.table_name%type,
                                          p_partition_name in all_tab_partitions.partition_name%type)
  return varchar2 as
  l_high_value LONG;
begin
  select high_value
    into l_high_value
    from all_tab_partitions
   where table_owner = p_table_owner
     and table_name = p_table_name
     and partition_name = p_partition_name;
  return substr(l_high_value, 1, 4000);
end;
/

--查询并保存excel
select t.table_name,
       t.partition_name,
       t.tablespace_name,
       t.num_rows,
       t.blocks,
       t.interval,
       long_to_varchar(user, t.table_name, t.partition_name) partition_def
  from user_tab_partitions t ;

--删除函数
drop function long_to_varchar;

此条目发表在PLSQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s