总结一些Oracle数据库的操作

--查询表空间占用情况
select a.a1 表空间名称,
       c.c2 类型,
       c.c3 区管理,
       b.b2 / 1024 / 1024 表空间大小M,
       (b.b2 - a.a2) / 1024 / 1024 已使用M,
       substr((b.b2 - a.a2) / b.b2 * 100, 1, 5) 利用率
  from (select tablespace_name a1, sum(nvl(bytes, 0)) a2
          from dba_free_space
         group by tablespace_name) a,
       (select tablespace_name b1, sum(bytes) b2
          from dba_data_files
         group by tablespace_name) b,
       (select tablespace_name c1, contents c2, extent_management c3
          from dba_tablespaces) c
 where a.a1 = b.b1
   and c.c1 = b.b1;
   
SELECT T.TABLESPACE_NAME,
       D.FILE_NAME,
       D.AUTOEXTENSIBLE,
       D.BYTES/1024/1024/1024,
       D.MAXBYTES/1024/1024/1024,
       D.STATUS
  FROM DBA_TABLESPACES T, DBA_DATA_FILES D
 WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
 ORDER BY TABLESPACE_NAME, FILE_NAME;
--数据表空间扩展
alter tablespace VBM_DATA01 add datafile 'E:\APP\LC\PRODUCT\11.2.0\DBHOME_1\DATABASE\GUIHOUSPACE9201.DBF' size 200M autoextend on next 100M maxsize 3000M;

select round(sum(BYTES)/1024/1024/1024,2)||'G' from user_segments where segment_name like 'ALM_RES_PMT_9999';

select round(sum(BYTES)/1024/1024/1024,2)||'G' from user_segments where segment_name like 'ALM_RES_PMT_RATE_9999';

select round(sum(BYTES)/1024/1024/1024,2)||'G' from user_segments where segment_name like 'MAS_PORT_9999_alm';

select * from dba_data_files;
select * from dba_temp_files;
--临时表空间扩展
alter tablespace VBM_DATA01 add datafile '/data/orcl/oradata/VBM_DATA010.DBF' size 200M autoextend on next 100M maxsize 10000M;

--172.36.1.40:1521/testdb
select * from user_users

--锁表
select l.SESSION_ID,o.OWNER,o.OBJECT_NAME from v$locked_object l, dba_objects o where l.OBJECT_ID  = o.OBJECT_ID;
select t1.USERNAME,t1.SID,t1.SERIAL#,t1.LOGON_TIME,t1.MACHINE from  v$locked_object t, v$session t1 where t.SESSION_ID = t1.SID order by t1.LOGON_TIME;

select spid, osuser, s.program
     from v$session s,v$process p
     where s.paddr=p.addr and s.sid=371 
--杀死进程
alter system kill session '15,53453';
alter system kill session '5,34903';
alter system kill session '147,145';
alter system kill session '587,57007';
--查询正在执行的sql
SELECT b.sid oracleID,
       b.username 登录Oracle用户名,
       b.serial#,
       spid 操作系统ID,
       paddr,
       sql_text 正在执行的SQL,
       b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
   AND b.sql_hash_value = c.hash_value
--sql执行进度
SELECT SE.SID,
       OPNAME, ---简要说明
       -- TARGET,---操作对象
       -- TARGET_DESC,--目标对象说明
       START_TIME, ---开始操作时间
       LAST_UPDATE_TIME,
       TIME_REMAINING, ---预计完成剩余秒
       TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK, --SOFAR 迄今为止完成的工作量，TOTALWORK 总工作量
       ELAPSED_SECONDS ELAPSED, ---从操作开始总花费时间(秒)
       ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
       SQL_TEXT
  FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
 WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
   AND SL.SID = SE.SID
   AND SOFAR != TOTALWORK
 ORDER BY START_TIME;
  
--查询表名及相关字段
select  A.TABLE_NAME as "表名",
        C.COMMENTS       AS "表说明",
        A.COLUMN_ID      AS "字段序号",
        A.COLUMN_NAME    AS "字段名",
        B.COMMENTS       AS "字段说明",
        A.DATA_TYPE      AS "字段数据类型",
        A.DATA_LENGTH    AS "数据长度",
        A.DATA_PRECISION AS "整数位",
        A.DATA_SCALE     AS "小数位"
 from USER_TAB_COLUMNS A left join user_col_comments B
 on A.TABLE_NAME = B.table_name and A.COLUMN_NAME = B.column_name 
 left join USER_TAB_COMMENTS C 
 on A.TABLE_NAME = C.table_name
 order by A.TABLE_NAME, A.COLUMN_ID

--查看表占用的空间
select t.segment_name,
       t.segment_type,
       sum(t.bytes / 1024 / 1024) "占用空间(M)",
       OWNER
       from dba_segments t
where t.segment_type = 'TABLE' --and t.segment_name like '%MAS_PORT%'
group by OWNER, t.segment_name, t.segment_type
order by sum(t.bytes / 1024 / 1024) desc;

--查看正在等待的会话
select *
  from v$active_session_history h
 where sample_time > trunc(sysdate)
   and session_state = 'WAITING'
   and exists (select *
          from v$sql s
         where upper(s.sql_text) like '%insert%'
           and s.sql_id = h.sql_id)
 order by sample_time desc;

--新增表空间
create tablespace VBM_DATA01 datafile '/oradata/ALM/vbm_data02.dbf' size 200M autoextend on next 100M maxsize 30000M;
alter tablespace VBM_DATA01 add datafile '/oradata/ALM/vbm_data02.dbf' size 200M autoextend on next 100M maxsize 30000M;
alter database datafile '/oradata/ALM/vbm_data01.dbf' resize 50000M
--删除用户，及级联关系也删除掉
drop user vbm_rapm_ts_alm cascade;
--删除表空间，及对应的表空间文件也删除掉
drop tablespace GUIZHOUSPACE92 including contents and datafiles cascade constraint;
--删除表空间文件
alter tablespace GUIZHOUSPACE92 drop datafile 'E:\APP\LC\PRODUCT\11.2.0\DBHOME_1\DATABASE\GUIHOUSPACE92.DBF';
--查询默认表空间
select * from user_users;
--默认表空间
alter user vbm_rapm_yh_alm default tablespace VBM_DATA01;
--12c
alter user c##vbm_rapm_ts_alm default tablespace VBM_DATA01;

select * from dba_directories
--新建用户 11g
create user vbm_rapm_yh_alm identified by 1;
--12c
create user c##vbm_rapm_ts_alm identified by vbmrisk;

--授权用户 11g
grant connect, resource to vbm_rapm_yh_alm;
--12c
grant connect, resource to c##vbm_rapm_ts_alm;
-- 11g
grant dba  to vbm_rapm_yh_alm;
-- 12c
grant dba  to c##vbm_rapm_ts_alm;

--查询字符集
select userenv('language') from dual;

--数据泵导入：
impdp vbm_mas/1@172.36.1.40:1521/testdb remap_schema=vbm_mas:vbm_mas REMAP_TABLESPACE=VBM_DATA01:VBM_DATA01 directory=DUMP_DIR dumpfile=vbm_mas_20200408.dmp logfile=vbm_mas_20200408.log table_exists_action=REPLACE transform=segment_attributes:n;

--数据泵导出：
expdp vbm_rapm_ts_alm/vbmrisk@10.5.102.145:1521/alm dumpfile=vbm_rapm_0917.dmp directory=DATA_PUMP_DIR logfile=vbm_rapm_0917.log version=11.2
--tables=VBM_RAPM_GZ20200317.Mas_Curve_Data
--version=11.2   --指定版本

impdp vbm_rapm_ts_alm/vbmrisk@orcl remap_schema=vbm_rapm_ts_alm:vbm_rapm_ts_alm REMAP_TABLESPACE=VBM_DATA01:VBM_DATA01 directory=GUIZHOU92_DATA dumpfile=vbm_rapm_0917.dmp logfile=vbm_rapm_0917.log table_exists_action=REPLACE transform=segment_attributes:n

select file_name,tablespace_name,bytes from dba_data_files;

imp vbm_rapm_ts_alm/1@192.168.1.51:1521/orcl

impdp  vbmprp/1 transform=segment_attributes:n dumpfile=rptprp.dmp directory=GUIZHOU92_DATA remap_schema=rptprp:vbmprp;

select * from dba_directories
--查看数据库版本
select * from v$version

--第一步：启用行迁移
alter table ROMP_SYS_MENU enable row movement;
--第二步：闪回表到120分钟前
flashback table romp_sys_function to timestamp systimestamp -interval '30' minute; 

/*重启数据库监听*/
--（1） 以oracle身份登录数据库，命令：su -oracle

--（2） 进入Sqlplus控制台，命令：sqlplus /nolog

--（3） 以系统管理员登录，命令：connect / as sysdba
   
--（4） 启动数据库，命令：startup

--（5） 如果是关闭数据库，命令：shutdown immediate

--（6） 退出sqlplus控制台，命令：exit

--（7） 进入监听器控制台，命令：lsnrctl

--（8） 启动监听器，命令：start

--（9） 退出监听器控制台，命令：exit

/*重启数据库实例*/

--（1） 切换需要启动的数据库实例：export ORACLE_SID=C1

--（2） 进入Sqlplus控制台，命令：sqlplus /nolog

--（3） 以系统管理员登录，命令：connect / as sysdba

--（4） 如果是关闭数据库，命令：shutdown abort

--（5） 启动数据库，命令：startup

--（6） 退出sqlplus控制台，命令：exit