对Oracle DBA初学者有用的常见命令集锦

三牛哥 2021-3-15 1587

为了帮助初学者快速掌握Oracle DBA工作中的各种实际使用的命令,我把一些我工作中使用的命令汇集在这里,希望对初学者有所帮助。

  1. 这段代码是检查表空间大小的,是我日常工作中经常使用的。
    
    -- check the tablespace size
    COL "Tablespace" FOR a22
    COL "Used MB" FOR 99,999,999
    COL "Free MB" FOR 99,999,999
    COL "Total MB" FOR 99,999,999
    SET PAGES 50000
    SELECT df.tablespace_name "Tablespace",
        totalusedspace "Used MB",
        (df.totalspace - tu.totalusedspace) "Free MB",
        df.totalspace "Total MB",
        ROUND (100 * ( (df.totalspace - tu.totalusedspace) / df.totalspace))
           "Pct. Free"
    FROM (  SELECT tablespace_name, ROUND (SUM (bytes) / 1048576) TotalSpace
             FROM dba_data_files
         GROUP BY tablespace_name) df,
        (  SELECT ROUND (SUM (bytes) / (1024 * 1024)) totalusedspace,
                  tablespace_name
             FROM dba_segments
         GROUP BY tablespace_name) tu
    WHERE df.tablespace_name = tu.tablespace_name
    ORDER BY 1;
最新回复 (5)
  • 三牛哥 2021-3-15
    引用 2

    这段代码是检查底层ASM磁盘组diskgroup的使用情况。

    --- check the ASM disk usage
    SET lines 1000 PAGES 50000
    SELECT group_number,name,type
          , round(DECODE(type, 'HIGH', (total_mb/3), 'NORMAL', (total_mb/2), total_mb), 0) total_MB
          , round(DECODE(type, 'HIGH', (free_mb/3), 'NORMAL', (free_mb/2), free_mb), 0) free_MB
          , USABLE_FILE_MB
          , round(DECODE(type, 'HIGH', (total_mb - free_mb)/3, 'NORMAL', (total_mb - free_mb)/2, (total_mb - free_mb)), 0) used_mb
          , round((((total_mb - free_mb))/(total_mb)) * 100, 0) Percent_Used
    FROM v$asm_diskgroup ORDER BY name;
    
  • 三牛哥 2021-3-15
    引用 3

    Data Pump导出导入的例子:

    --- check the directory
    col directory_path for a60
    col DIRECTORY_NAME for a20
    set lines 1000 pagesize 1000
    SELECT directory_name, directory_path FROM dba_directories ORDER BY 1;
    
    -- create the directory
    CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/dbbackup1/dump_dir';
    GRANT READ ON DIRECTORY DUMP_DIR TO system;
    GRANT WRITE ON DIRECTORY DUMP_DIR TO system;
    
    -- export the table 
    expdp user/password directory=DPUMP tables=WT.OBPS dumpfile=WT.OBPS.dmp logfile=WT.OBPS.log parallel=16
    
    expdp user/password directory=DPUMP tables=WT.JOBS_ASSIGNMENTS query="WT.JOBS_ASSIGNMENTS:WHERE rownum\<=1000" dumpfile=WT.JOBS_ASSIGNMENTS.dmp logfile=WT.JOBS_ASSIGNMENTS.log parallel=16
    
    expdp user/password directory=DPUMP tables=WT.JOBS_ELEMENTS content=METADATA_ONLY dumpfile=WT.JOBS_ELEMENTS.dmp logfile=WT.JOBS_ELEMENTS.log
    
    -- import the table 
    impdp username/password@ogcdb-dt-ad-grd.ula.comcast.net:1555/SVC_NWTST_NA directory=dpump dumpfile=WT.NCPDB_NCPS.dmp logfile=impdp.WT.NCPDB_NCPS.log TABLE_EXISTS_ACTION=REPLACE parallel=32
    
    -- import and rename the table name, schema name, tablespace name
    impdp user/password directory=dpump remap_schema=WT:FW tables=WT.HONGT remap_table=HONGT:HONGTXZ remap_tablespace=WT_DATA:USERS dumpfile=WT.HONGT.dmp logfile=impdp.WT.HONGT.log TABLE_EXISTS_ACTION=SKIP parallel=2
    
  • 三牛哥 2021-3-15
    引用 4

    创建用户的基本命令:

    -- create the user
    CREATE USER cnocauto IDENTIFIED BY XgDK3eqP DEFAULT TABLESPACE WT_DATA;
    
    -- allow the user to connect to the database
    GRANT CREATE SESSION TO cnocauto;
    
    -- grant one role to this user
    GRANT WTREADONLY TO cnocauto;
    
    -- test the user can connect to the database or not
    CONN cnocauto/XgDK3eqP
    
  • 三牛哥 2021-3-15
    引用 5

    如何产生AWR report

    -- generate the AWR report
    SQL> conn /as sysdba
    Connected.
    -- generate the local report
    SQL> @?/rdbms/admin/awrrpt 
    
    -- generate the global/RAC report
    SQL> @?/rdbms/admin/awrgrpt 
    
    -- compare two AWR reports
    SQL> @?/rdbms/admin/awrddrpt.sql
    
    
  • 宋元良 2021-3-16
    引用 6
    三牛哥 Data Pump导出导入的例子: ```sql --- check the directory col directory_path for a60 col DIRECTORY_NAME ...
    expdp前需要注意:
    1. The directory exists at the OS level.
    2. Oracle software owner has read/write privileges on the OS level directory.
    3. The user doing export/import has been granted rights on directory created at oracle level
返回
发新帖