oracle 常用操作

——建表空间

create tablespace 表空间名称 datafile '文件存放位置' size 数据增长大小 autoextend on;

create tablespace tbs_ly_data datafile '/home/oracle/oradata/lykjdb/tbs_ly_data01.dbf' size 20m autoextend on;

——增加表空间

alter tablespace 表空间名称 add datafile '文件存放位置' size 数据增长大小 autoextend on;

alter tablespace tbs_ly_data add datafile '/home/oracle/oradata/lykjdb/tbs_ly_data02.dbf' size 20m autoextend on;

——添加用户

create user 用户名 identified by 密码 default tablespace 表空间名;

create user LIANYIDEV2 identified by LIANYIDEV2 default tablespace TBS_LY_DATA;

——赋权限

grant 权限 to 用户名;

grant dba,resource,connect,unlimited tablespace ,select_catalog_role to LIANYIDEV2;

——修改密码

alter user 用户名 identified by 密码;

alter user username identified by password;

——解除所有限制

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED; 
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 
ALTER PROFILE DEFAULT LIMIT PASSWORD_LOCK_TIME UNLIMITED; 
ALTER PROFILE DEFAULT LIMIT PASSWORD_GRACE_TIME UNLIMITED;

——导出用户数据库文件

expdp 用户名/密码 directory=文件位置变量 dumpfile=导出文件名 schemas=用户空间 compression=all

expdp LIANYIDEV2/LIANYIDEV2 directory=DIR_DP4
dumpfile=LIANYIDEV2_20180702.dmp schemas=LIANYIDEV2 compression=all

——导出整库

expdp 用户名/密码 directory=文件位置变量 dumpfile=导出文件名 full=y compression=all

expdp LIANYIDEV2/LIANYIDEV2 directory=DIR_DP4 dumpfile=LIANYIDEV2_20180702.dmp full=y compression=all

——导入数据库

impdp 用户名/密码 DIRECTORY=文件位置变量 DUMPFILE=导入文件名 remap_schema=导入文件中的用户名:导入用户 remap_tablespace=导入文件中的表空间:现表空间

impdp LIANYIDEV2/LIANYIDEV2 DIRECTORY=DATA_PUMP_DIR DUMPFILE=LIANYIDEV2_20180702.dmp
remap_schema=LIANYIDEV2:LIANYIDEV2 remap_tablespace=ARMSPACE:TBS_LY_DATA

——远程导库

创建连接

CREATE public database link dblnk_205_moe1
CONNECT TO umon IDENTIFIED BY umon
USING '(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.100.205)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = moe1)
    )
  )';

——删除用户下所有对象

drop user 用户名 cascade;

——去除密码过期

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

——查询 日志位置

select * from v$diag_info;

——查询用户表空间

select username,default_tablespace from dba_users  where username='DEMO_ETMS';

——查询文件变量

select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

——查询所有表名：

select t.table_name from user_tables t;

——查询所有字段名：

select t.column_name from user_col_comments t;

——查询指定表的所有字段名：

select t.column_name from user_col_comments t where t.table_name = 'BIZ_DICT_XB';

——查询指定表的所有字段名和字段说明：

select t.column_name, t.column_name from user_col_comments t where t.table_name = 'BIZ_DICT_XB';

——查询所有表的表名和表说明：

select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name;

——查询模糊表名的表名和表说明：

select t.table_name from user_tables t where t.table_name like 'BIZ_DICT%';
select t.table_name, f.comments
  from user_tables t
 inner join user_tab_comments f
    on t.table_name = f.table_name
 where t.table_name like 'BIZ_DICT%';

——查询表的数据条数、表名、中文表名

select a.num_rows, a.TABLE_NAME, b.COMMENTS
from user_tables a, user_tab_comments b
WHERE a.TABLE_NAME = b.TABLE_NAME
order by TABLE_NAME;

——查询死锁

select b.* from v$locked_object a,v$session b,dba_objects c
where a.SESSION_ID=b.sid and a.object_id=c.OBJECT_ID
and c.object_name='FACT_REPORTCHANGELOG';
alter system kill session '683,8159' immediate;  sid,serial#

——查询哪些进程耗费资源以及拼接杀进程语句

SELECT        x.SQL_TEXT,
      'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# || ''';','kill -9 '||SPID,
       S.SID,
       S.SERIAL#,
       S.USERNAME, 
       S.MACHINE,
       S.SQL_HASH_VALUE,
       S.LAST_CALL_ET,S.LAST_CALL_ET/60,
       CLIENT_INFO,
       S.STATUS
  FROM V$SESSION S, V$PROCESS P, V$SQLTEXT x
 WHERE (P.ADDR(+) = S.PADDR)
   AND S.USERNAME IS NOT NULL
   and x.HASH_VALUE= S.SQL_HASH_VALUE
   --AND SPID='5275666'
   AND S.STATUS = 'ACTIVE' 
   --and s.USERNAME = 'GCMSDW'
  -- AND S.MACHINE LIKE '%TL%'
 ORDER BY LAST_CALL_ET DESC, S.USERNAME ASC,x.HASH_VALUE,x.PIECE;

——删除所有表

select 'DROP TABLE '||OBJECT_NAME||' PURGE;' from user_objects where object_type='TABLE'