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" NOCOMPRESSALTER 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 p1using (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)) p2on (p1.cname = p2.cregion_name)when not matched then  insert values (p2.careaid, p2.cregion_name)