資源描述：
《oracle創(chuàng)建表空間用戶及授權(quán)查看權(quán)限》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在工程資料-天天文庫。
1、實(shí)用標(biāo)準(zhǔn)文案Oracle創(chuàng)建表空間、創(chuàng)建用戶以及授權(quán)、查看權(quán)限一、創(chuàng)建臨時(shí)表空間#sqlplus/assysdba;sql>CREATETEMPORARYTABLESPACEtest_tempTEMPFILE'/opt/oradata/test_temp.dbf'SIZE32MAUTOEXTENDONNEXT32MMAXSIZE2048MEXTENTMANAGEMENTLOCAL;二、創(chuàng)建用戶表空間sql>CREATETABLESPACEtest_dataLOGGINGDATAFILE'/opt/oradata/test_data.dbf'SIZE32MAUTOEXTEN
2、DONNEXT32MMAXSIZE2048MEXTENTMANAGEMENTLOCAL;sql>selecttablespace_name,block_size,extent_management,segment_space_management精彩文檔實(shí)用標(biāo)準(zhǔn)文案fromdba_tablespaceswheretablespce_namelike'USER%';三、還原表空間sql>createundotablespaceuser01_undodatafile'/u01/oracle/app/oracle/user01_undo.dbf'size20M;sql>sele
3、cttablespace_name,block_size,extent_management,segment_space_managementfromdba_tablespacesfromtablespace_namelike'user01_%';sql>selecttablespace_name,status,contentsfromdba_tablespacesfromtablespace_namelike'user01_%';四、創(chuàng)建用戶并制定表空間sql>CREATEUSERusernameIDENTIFIEDBYpasswordDEFAULTTABLESPACE
4、TEST_DATATEMPORARYTABLESPACETEST_TEMP;sql>GRANTroleTOusername;精彩文檔實(shí)用標(biāo)準(zhǔn)文案五、查看所有用戶sql>SELECT*FROMDBA_USERS;sql>SELECT*FROMALL_USERS;sql>SELECT*FROMUSER_USERS;六、查看用戶對(duì)象或角色權(quán)限sql>SELECT*FROMDBA_TAB_PRIVS;sql>SELECT*FROMALL_TAB_PRIVS;sql>SELECT*FROMUSER_TAB_PRIVS;七、查看所有角色sql>SELECT*FROMDBA_ROLES
5、;八、查看用戶或角色所擁有的角色sql>SELECT*FROMDBA_ROLE_PRIVS;sql>SELECT*FROMUSER_ROLE_PRIVS;九、表空間脫機(jī)sql>selecttablespace_name,status,contentsfromdba_tablespaceswheretablespce_namelike'user_%';sql>colnamefora55精彩文檔實(shí)用標(biāo)準(zhǔn)文案sql>selectfile#,name,statusfromv$datafilewherefile#>=8;設(shè)置脫機(jī)狀態(tài)sql>altertablespaceuser01
6、offline;sql>selecttablespace_name,status,contentsfromdba_tablespaceswheretablespce_namelike'user01_%';sql>selectfile#,name,statusfromv$datafilewherefile#>=8;設(shè)置聯(lián)機(jī)狀態(tài)sql>altertablespaceuser01online;十一、只讀表空間設(shè)置只讀表空間sql>altertablespaceuser01readonly;sql>selecttablespce_name,status,contentsfromd
7、ba_tablespaceswheretablespace_namelike'user_%';可讀可寫精彩文檔實(shí)用標(biāo)準(zhǔn)文案sql>altertablespaceuser01readwrite;sql>selecttablespace_name,status,contentsfromdba_tablespacewheretablespace_namelike'user%';十二、改變表空間的存儲(chǔ)設(shè)置sql>altertablespaceuser01minimumextent100K;sql>altertablespaceuser
