oracle 相关sql杂

仅做记录,就自己看看而已

//合并分区ALTER TABLE EMS_STORAGE_FILE  MERGE PARTITIONS EMS_STORAGE_FILE20160207, EMS_STORAGE_FILE20160208 INTO PARTITION EMS_STORAGE_FILE20160208 UPDATE INDEXES;//杀sessionselect sid，serial#,PADDR,username from v$session where username='VISS35';alter system kill session '447,103';select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status='KILLED' and b.username='VISS35'//表空间select name, status from v$datafile where status='OFFLINE';alter database datafile  '/xxx/xxx/xxx.dbf' online; alter database datafile 547 online;alter tablespace my01 online;alter tablespace test_mult_ws_20 offline; DROP TABLESPACE test_mult_ws_20 INCLUDING CONTENTS AND DATAFILES;SELECT segment_name, segment_type, sum(bytes/1024/1024/1024)||'G' ,sum(blocks),sum(extents),count(*) FROM user_segments  WHERE segment_name='EMS_STORAGE_FILE' group by segment_name, segment_type;SELECT segment_name, segment_type, (bytes/1024/1024)||'M' ,blocks,extents FROM user_segments WHERE segment_name='EMS_STORAGE_FILE' ;//mountnid target=sys/oracle dbname=orcl setname=yes（dbid修改）   //rman备份恢复111// list incarnation;List of Database IncarnationsDB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time------- ------- -------- ---------------- --- ---------- ----------1       1       ORCL     1443165364       PARENT  1          24-AUG-132       2       ORCL     1443165364       PARENT  925702     24-JUN-16... 14      14      ORCL     1443165364       PARENT  1786506    28-JUN-1615      15      ORCL     1443165364       CURRENT 1896462    28-JUN-16reset database to incarnation 14;restore database until scn 1896461;recover  database until scn 1896461;alter database add logfile thread 1 group 1('/home/oracle/oracleDB/oradata/orcl/redo01.log') size 100m;alter database add logfile thread 1 group 2('/home/oracle/oracleDB/oradata/orcl/redo02.log') size 100m;alter database add logfile thread 1 group 3('/home/oracle/oracleDB/oradata/orcl/redo03.log') size 100m;// 将Oracle数据库设置为归档模式1)sql>shutdown normal/immediate;2)sql>startup mount;3)sql>alter database archivelog;4)sql>alter database open;5)archive log list;select current_scn from v$database;create or replace directory  NAMExx as 'PATH';GRANT CONNECT,RESOURCE TO xxx;GRANT CREATE VIEW TO xxx;GRANT CREATE TABLE TO xxx;GRANT CREATE SEQUENCE TO xxx;GRANT CREATE TABLESPACE TO xxx;GRANT DROP TABLESPACE TO xxx;GRANT ALTER TABLESPACE TO xxx;GRANT EXECUTE ON DBMS_PIPE TO xxx;GRANT CREATE ANY DIRECTORY to xxx;GRANT DROP ANY DIRECTORY to xxx;expdp viss35/viss35  directory=DIRECTORY tables=xx:xx,xx:xx access_method=external_table  dumpfile=tttt2.dmp    nologfile=y  ;impdp viss35/viss35  directory=DIRECTORY tables=xx:xx,xx:xx  dumpfile=tttt2.dmp   access_method=external_table table_exists_action=replace   nologfile=y  ;SELECT s.sid, s.username, s.osuser, p.spid, s.program, s.module, s.event, s.status  FROM v$session s, v$process p WHERE s.paddr   = p.addr AND p.spid IN ( '20903');  --活动信息  SELECT sql_text sql FROM v$process a, v$session b, v$sqlarea c WHERE a.addr = b.paddr AND b.sql_hash_value = c.hash_value;