Oracle维护之日常伪DBA操作

爱被打了一巴掌 2022-04-08 08:42 237阅读 0赞

> 好记性不如烂笔头，本人日常DBA的SQL如下，整理了常用的，创建用户之类的就直接不写了，因为他不算日常的SQL，偶尔用一下，直接通过笔记调出来

-- 查询当前用户所有表
    select t.* from user_tables t 
    -- impdp导出
    imp xxx/xxx@xxx file=xx_DATA.DMP grants=N tables=tablename1,tablename2
    
    exp xxx/xxx file=xxx.dmp  grants=N owner=xxx
    
    -- listagg within group 拼接结果，比如把所有表用逗号拼接起来
    select LISTAGG('xx:'||partition_name,',') within group(order by partition_name asc) from user_tab_partitions where table_name='table_name'
    
    -- 查询表所属表空间
    select TABLE_NAME,TABLESPACE_NAME,partitioned from dba_tables where table_name = 'tablename'
    
    -- 查询表空间和表空间剩余量
    SELECT a.tablespace_name "表空间名",
           total / (1024 * 1024 * 1024) "表空间大小(G)",
           free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
           (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
           round((total - free) / total, 4) * 100 "使用率 %"
      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;
    
    -- 查询分区
    select * from user_sys_privs where 
    --privilege like upper('%DATABASE LINK%') AND
     USERNAME='username';
     
    -- 赋予创建DBLink权限 
    grant create public database link to username;
    
    -- 查询directory
    SELECT * FROM DBA_DIRECTORIES
    
    -- 管理员身份sys导出
    expdp \'sys/password@ORCL AS SYSDBA\' grants=n TABLES=username.table_name dumpfile=filename.DMP DIRECTORY=EXPDP;
    -- 管理员身份sys导出分区表
    expdp \'sys/password@ORCL AS SYSDBA\' grants=n TABLES=username.table_name:分区名 dumpfile=文件名.DMP DIRECTORY=EXPDP;
    
    -- impdp导入
    impdp username/password@ORCL dumpfile=filename.DMP directory=EXPDP remap_schema=原用户名:当前要导入的用户名
    
    -- 后台开并行expdp导出，将会导出多个DP开头的文件
    nohup expdp \'sys/password@ORCL AS SYSDBA\' grants=FALSE schemas=username  parallel=40 dumpfile=DP%u.DMP cluster=N logfile=DP.log directory=EXPDP &
    
    -- 创建directory 
    create directory EXPDP as '/home/xxx';
    -- 给directory赋予权限
    grant read,write on directory EXPDP to username;
    
    --rman归档 判断log_mode为ARCHIVELOG
    select * from v$database;
    
    --展示undo参数
    show parameter undo 
    
    -- 显示归档文件日志及相关的信息
    select * from v$recovery_file_dest;
    select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
    select * from v$flash_recovery_area_usage;
    select * from v$version;
    
    select group#,sequence#,bytes/1024/1024,members,status from v$log;
    
    -- 删除N天以前的归档
    DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
    delete obsolete;
    select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;
    delete archivelog until time 'sysdate-5';
    
    -- 定时删除归档日志脚本，此脚本需要写成 xxx.sh 文件
    $ORACLE_HOME/bin/rman log=delete_archivelog.log <<EOF       
    rman target sys/password
    run{
            crosscheck archivelog all;    
            DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
    }
    
    -- 查询表空间的数据文件
    Select * FROM DBA_DATA_FILES where tablespace_name = 'tablespacename';
    
    -- 给表空间增加数据文件
    ALTER TABLESPACE DATA_ODS ADD DATAFILE '/oradata/xxx.dbf' SIZE 1G AUTOEXTEND ON NEXT 8M MAXSIZE 34359721984;
    
    -- 直接修改原有数据文件大小，修改成32G，单个数据文件最大只能32G
    alter database datafile '/oradata/xxx.dbf' RESIZE 34359721984; 
    
    -- 查询某个用户表空间的数据文件 
    Select * FROM DBA_DATA_FILES where tablespace_name = 'username';
    
    -- 查询临时表空间数据文件
    select * from dba_temp_files d; 
          
    -- 给现有表空间增加数据文件
    ALTER TABLESPACE DATA_ODS ADD DATAFILE
    '/oradata/xxx.dbf' SIZE 34359721984;
    
    -- 给临时表空间增加数据文件
    ALTER DATABASE TEMPFILE '/home/xxx/temp03.dbf' RESIZE 34359721984;
    
    -- 查询所有表空间
    select * from v$tablespace;
    
    -- 查询表空间下面所有的用户
    select distinct s.owner from dba_segments s where s.tablespace_name ='tablespacename';