達夢dimp備份恢復資料庫remap以及檢視錶大小等

济南小老虎發表於2024-04-30
資料庫REM

達夢dimp備份恢復資料庫remap以及檢視錶大小等

now=`date +%Y%m%d`
mkdir -p /dm_published_${now}
rm -rf /dm_published_${now}/*
cd /opt/dmdbms/bin/
time ./dexp myprod2206dm0721/'"Passwdxxxx?!"'@127.0.0.1 file=/dm_published_${now}/myprod2206dm0721.dump log=/dm_published_${now}/${now}.log


建立:
create tablespace myprodtenant01 datafile '/opt/dmdbms/data/DAMENG/myprodtenant01.dbf' size 1024 ;
create user myprodtenant01  identified by Passwdxxxx default tablespace myprodtenant01 ;
grant dba,resource to myprodtenant01 ;

恢復:
cd /opt/dmdbms/bin/
./dimp myprodtenant01/Passwdxxxx@127.0.0.1 file=/myprod2206dm0721.dump log=/2024042301.log remap_schema=myprod2206dm0721:myprodtenant01

刪除多餘的資料檔案

如果想刪除多餘的表空間方法為:

select TABLESPACE_NAME  from dba_data_files;
drop user somename cascade ;
drop tablespace somename ;

檢視錶大小

判斷表大小相關:
select t.owner TABLE_SCHEMA,TABLE_NAME, num_rows TABLE_ROWS,
s.BYTES /1024/1024 TABLE_VOLUME   -- 以M為單位
from dba_tables  t
left join dba_segments s  on t.table_name=s.segment_name
where t.owner = 'YourUserName' 
and t.table_name = s.segment_name
and t.owner = s.owner
order by TABLE_VOLUME DESC;

第二種方法:


表資訊: 
create
        table table_count
        (
                owner         varchar(100),
                table_name    varchar(100),
                used_space_mb varchar(100)
        );


declare
begin
        for rec in
        (
                select owner, table_name from all_tables where owner='myprod2206DM0721' order by 1, 2
        )
        loop
                insert into table_count
                select
                        rec.owner     ,
                        rec.table_name,
                        table_used_pages(rec.owner, rec.table_name)*(page()/1024)/1024
                from
                        dual;
                COMMIT;
        end loop;
end;

--3、查詢
select
        t.owner              ,
        t.table_name         ,
        t.used_space_mb                      as MB,
        CAST(t.used_space_mb as number)/1024 as GB
from
        table_count t
where
        table_name like 'GSI_PRO%'
order by
        GB desc

select * from table_count where used_space_mb is not null  order by TO_NUMBER(used_space_mb) desc 



--檢視字符集，0[GB18030]，1[UTF-8]，2[EUC-KR]
select sf_get_unicode_flag();
--檢視頁大小，執行下面任一語句，執行結果單位為位元組，例如8192，表示8K
select page();
SELECT SF_GET_PAGE_SIZE();
select para_name,para_value from v$dm_ini where para_name ='GLOBAL_PAGE_SIZE';

相關文章