首页 > itarticle > oracle日常运维

oracle日常运维

admin 11月 12, 2020 0

创建数据库

使用DB Config Assistant创建数据库后执行

sqlplus sys/[email protected] as sysdba

CREATE TABLESPACE myspace DATAFILE 'E:myspacemyspace01.DBF'  SIZE 15G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

create user myuser identified by mypasswd default tablespace myspace temporary tablespace temp quota unlimited on myspace;
grant create session to myuser;
grant Resource to myuser;
grant connect to myuser;
grant DBA to myuser;

导入导出数据库

使用exp和imp导入导出


exp system/[email protected] file=d:dumptest.dmp full=y;

# 导入数据
imp username/[email protected] full=y file=D:/dump/test.dmp ignore=y;

使用expdb和impdb导入导出

sqlplus system/[email protected] as sysdba
# 查看目录
select * from dba_directories;
# 如果没有合适的目录，则创建一个
create directory dump_dir as 'D:dump';
# 导出scheme
expdp system/[email protected] DIRECTORY=dump_dir schemas=orguser dumpfile=dump.dmp 
# 导入scheme
impdp system/[email protected] directory=dump_dir dumpfile=dump.dmp  schemas=orguser remap_schema=orguser:newuser remap_tablespace=orgtable:newtable table_exists_action=replace logfile=impdp.log;

查看进程执行的sql

SELECT sql_text FROM v$sqltext a WHERE (a.hash_value, a.address) IN
(SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value ),
  DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
  FROM v$session b
  WHERE b.paddr =
    (SELECT addr FROM v$process c WHERE c.spid='$pid'
      )
      ) ORDER BY piece ASC;

查看资源消耗最多的sql

select b.username username,a.disk_reads reads,
    a.executions exec,a.disk_reads/decode(a.executions,0,1,a.executions) rds_exec_ratio,
        a.sql_text Statement
        from  v$sqlarea a,dba_users b
        where a.parsing_user_id=b.user_id
         and a.disk_reads > 100000
         order by a.disk_reads desc;

查看磁盘消耗最多的sql

select disk_reads,sql_text
from (select sql_text,disk_reads,
   dense_rank() over
        (order by disk_reads desc) disk_reads_rank
           from v$sql)
           where disk_reads_rank <=5;

参考
 Oracle exp/expdp imp/impdp导入导出数据