oracle数据库常用操作命令

数据库常用操作命令

• 安装数据库时注意修改字符集

1.impdp/expdp导入导出用户

1. 创建数据目录

1.1创建目录

create directory dmp as 'd:\dbbak\dumpfile\dmp';

1.2授权

grant read,write on directory dmp to user;

1.3查看创建目录

 select * from DBA_DIRECTORIES ;

2导出数据

2.1 导出所有用户

expdp system/oracle@orcl directory = DMP dumpfile='EXDPALL.DMP' log =expdp_log.log full=y;

2.2 导出指定用户

expdp system/oracle@orcl directory = DMP dumpfile='EXDPALL.DMP' log =expdp_log.log schemas=(user1,user2);

3.数据泵导入用户

3.1 导入所有用户

impdp system/oracle@orcl directory = DMP dumpfile='EXDPALL.DMP' log =impdp_log.log full=y;

3.2 导入指定用户

impdp system/oracle@orcl directory = DMP dumpfile='EXDPALL.DMP' log =impdp_log.log schemas=(user1,user2);

3.3 impdp导入卡住查看日志文件

show pareameter dump
...\trace\alert.log

4. 创建表空间

create tablespace tablespace_name datafile='ts.dbf' size 4g auoextend on next 1g;

4.1增加表空间

alter tablespace JTSTS add DATAFILE 'JTSTS16.dbf' size 4g  ;

4.2 查看数据文件

Select * FROM DBA_DATA_FILES;

4.3 查看表空间使用情况

select *
  from (Select a.tablespace_name,
               to_char(a.bytes / 1024 / 1024, '99,999.999') total_bytes,
               to_char(b.bytes / 1024 / 1024, '99,999.999') free_bytes,
               to_char(a.bytes / 1024 / 1024 - b.bytes / 1024 / 1024,
                       '99,999.999') use_bytes,
               to_char((1 - b.bytes / a.bytes) * 100, '99.99') || '%' use
          from (select tablespace_name, sum(bytes) bytes
                  from dba_data_files
                 group by tablespace_name) a,
               (select tablespace_name, sum(bytes) bytes
                  from dba_free_space
                 group by tablespace_name) b
         where a.tablespace_name = b.tablespace_name
        union all
        select c.tablespace_name,
               to_char(c.bytes / 1024 / 1024, '99,999.999') total_bytes,
               to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999.999') free_bytes,
               to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes,
               to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
          from (select tablespace_name, sum(bytes) bytes
                  from dba_temp_files
                 group by tablespace_name) c,
               (select tablespace_name, sum(bytes_cached) bytes_used
                  from v$temp_extent_pool
                 group by tablespace_name) d
         where c.tablespace_name = d.tablespace_name)
 order by tablespace_name ;

4.4 删除表空间和数据文件

drop tablespace test_data including contents and datafiles;

5.创建用户

5.1 创建用户并指定表空间

create user test identified by test default tablespace tablespace_name;

5.2用户授权

grant connect,resources,dba to user;

6 imp/exp导入导出用户

1.导出用户

exp system/oracle@orcl  file='d:\dback\dbback.dmp' owner=(test) log=exp.log;

TABLES ：指定表名

ROWS ：导出指定行

rows=n 只导出表结构

owner=(test1,test2) 导出指定用户

full=y ignore=y 整库导出

2.导入用户

imp system/oracle@orcl file='d:\dback\dbback.dmp' fromuser=test touser = test log=imp.log;

3.exp默认无法导出空表

Oacle 11g中有个新特性，当表无数据时，不分配segment，以节省空间，当我们用exp导出空表时，无法导出。解决方法：

1. 查询所有空表并分配segment:

   select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or max_extents is null;
   

2. 设置系统参数，让后续新表自动分配segment，查询系统参数，该参数值默认是TRUE，当改为FALSE时，无论是空表还是非空表，都分配segment.

   查询语句：

   show parameter deferred_segment_creation;
   

   将这个参数修改为FALSE，修改语句为：

   alter system set deferred_segment_creation=false;
   

   再次查询确认参数是否修改成功：

   show parameter deferred_segment_creation;
   

7.查询会话

1. 查询死锁

select * from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

2.杀会话

select 'alter system kill session '''||sid||','||serial#||''';' from v$session t1, v$locked_object t2 where t1.sid = t2.SESSION_ID;

8.查看数据库字符集

select * from v$nls_parameters t where t.PARAMETER='NLS_CHARACTERSET';
select userenv('language') from dual;

默认字符集中文占3个字节

ZHS16GBK中文占2个字节

9.同义词授权

grant insert any table to test;
grant update any table to test;
grant delete any table to test;