oracle常用汇总2

--创建表空间 CREATE TABLESPACE test_tablespace LOGGING DATAFILE 'E:\ORACLETABLESPACE\TEST_TABLESPACE.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL;

--创建临时表空间 CREATE TEMPORARY TABLESPACE test_temptablespace TEMPFILE 'E:\ORACLETABLESPACE\TESTTEMP_TABLESPACE.DBF' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M EXTENT MANAGEMENT LOCAL;

-- 创建用户 create user viva1   identified by viva1   default tablespace TEST_TABLESPACE   temporary tablespace TEST_TEMPTABLESPACE   profile DEFAULT;   --给用户授权 GRANT 　　CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE, 　　ALTER ANY TABLE, ALTER ANY PROCEDURE, 　　DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE, 　　SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE 　　TO viva1;

--查看用户 SELECT * FROM DBA_USERS; SELECT * FROM ALL_USERS; SELECT * FROM USER_USERS;

--查看用户权限 SELECT * FROM DBA_SYS_PRIVS; SELECT * FROM USER_SYS_PRIVS;

--查看用户对应权限 SELECT * FROM DBA_TAB_PRIVS; SELECT * FROM ALL_TAB_PRIVS; SELECT * FROM USER_TAB_PRIVS;

--查看所有角色 SELECT * FROM DBA_ROLES;

--查看用户或角色所拥有的角色 SELECT * FROM DBA_ROLE_PRIVS; SELECT * FROM USER_ROLE_PRIVS;

--遇到no privileges on tablespace 'tablespace ' alter user userquota 10M[unlimited] on tablespace;

--查看所有表空间的使用情况 select b.file_id 文件ID号, b.tablespace_name 表空间名, b.bytes/1024/1024||'M'字节数, (b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M' 已使用, sum(nvl(a.bytes,0))/1024/1024||'M' 剩余空间, 100 - sum(nvl(a.bytes,0))/(b.bytes)*100 占用百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_id,b.bytes order by b.file_id;

--查看用户默认的表空间 select username,default_tablespace from dba_users;

--查看要扩展的表空间使用的数据文件路径与名字 select * from dba_data_files where tablespace_name like 'USERS%';

--增加表空间数据文件 ALTER TABLESPACE test_tablespace ADD DATAFILE 'E:\ORACLETABLESPACE\TEST_TABLESPACE2.DBF' SIZE 500M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M;

--增加表空间数据文件的大小 ALTER DATABASE DATAFILE 'E:\ORACLETABLESPACE\TEST_TABLESPACE.DBF' RESIZE 256M;

--或者使用file_id ALTER DATABASE DATAFILE 10 RESIZE 50000M;

--改变表空间某个数据文件的最大大小 ALTER DATABASE DATAFILE 'E:\ORACLETABLESPACE\TEST_TABLESPACE.DBF' AUTOEXTEND ON MAXSIZE 4096M;

--查找死锁 select * from dba_jobs_running --首先找到该进行的session_id：  select xidusn, object_id, session_id, locked_mode from v$locked_object; --131 --然后找到该进程的serial# select username,sid,serial# from v$session where sid=131; --372 --最后杀掉该进程： alter system kill session '131,372';

 --拓展子分区

ALTER TABLE  vav_stb_sec ADD      PARTITION "P_DEFAULT" VALUES LESS THAN (MAXVALUE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MDMS_TS_ZJSM_SEC" NOCOMPRESS

ALTER TABLE  vav_stb_sec ADD      PARTITION "MDMS_ZJSM_SEC_P_20121102" VALUES LESS THAN (TO_DATE(' 2012-11-03 02:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MDMS_TS_ZJSM_SEC" NOCOMPRESS 

ALTER TABLE  vav_stb_sec ADD      PARTITION "MDMS_ZJSM_SEC_P_20121103" VALUES LESS THAN (TO_DATE(' 2012-11-04 02:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MDMS_TS_ZJSM_SEC" NOCOMPRESS 

ALTER TABLE  vav_stb_sec ADD      PARTITION "MDMS_ZJSM_SEC_P_20121104" VALUES LESS THAN (TO_DATE(' 2012-11-05 02:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "MDMS_TS_ZJSM_SEC" NOCOMPRESS 

删除表空间及数据文件
DROP TABLESPACE "MDMS_TS_HNHL_SOURCE_201212" INCLUDING CONTENTS AND DATAFILES

 

MERGE INTO test1 p1
using (SELECT ROWNUM + (select to_number(nvl(max(cid), 4700)) from test1) AS CAREAID,
              CREGION_NAME
         FROM (SELECT CREGION_NAME
                 FROM VAV_VIEW_STB_FULL_INFO_R
                GROUP BY CREGION_NAME)) p2
on (p1.cname = p2.cregion_name)
when not matched then
  insert values (p2.careaid, p2.cregion_name)