oracle\u67e5\u8be2\u8868\u7a7a\u95f4\u8bed\u53e5\uff1a<\/p>\n
set linesize 200\r\nset pagesize 100\r\ncol totalmb for 999999999 heading 'TOTAL(MB)'\r\ncol freemb for 9999999999 heading 'FREE(MB)'\r\ncol usedl for 99999 heading 'USE(%)'\r\ncol tsname for a40\r\ncol segment_space_management for a10\r\ncol block_size for 99999\r\ncol status for a10\r\nselect nvl(b.tablespace_name, nvl(a.tablespace_name, 'UNKNOWN')) tsname,\r\n c.segment_space_management,\r\n c.block_size,\r\n c.status,\r\n round(totalmb, 0) totalmb,\r\n round(totalmb - nvl(freemb, 0)) usedmb,\r\n round(nvl(freemb, 0), 0) freemb,\r\n round(((totalmb - nvl(freemb, 0)) \/ totalmb) * 100, 0) usedl\r\n from (select sum(bytes) \/ 1024 \/ 1024 freemb, tablespace_name\r\n from dba_free_space\r\n group by tablespace_name) a,\r\n (select sum(bytes) \/ 1024 \/ 1024 totalmb, tablespace_name\r\n from dba_data_files\r\n group by tablespace_name) b,\r\n (select segment_space_management,\r\n block_size,\r\n status,\r\n tablespace_name,\r\n CONTENTS\r\n from dba_tablespaces) c\r\n where a.tablespace_name(+) = b.tablespace_name\r\n and c.tablespace_name = b.tablespace_name\r\n order by usedl desc;<\/code><\/pre>\n\u67e5\u8be2\u3001resize\u8868\u7a7a\u95f4\u6587\u4ef6\uff1a<\/p>\n
select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;\r\nalter database datafile '\/opt\/oracle\/oradata\/LIUSDB\/data01.dbf' resize 8192M;<\/code><\/pre>\n\u6269\u5c55\u8868\u7a7a\u95f4\uff08\u4e24\u79cd\u65b9\u6cd5\uff09\uff1a<\/p>\n
alter tablespace \u8868\u7a7a\u95f4 add datafile '\u8868\u7a7a\u95f4\u8def\u5f84' size 10240M; \/\/ \u589e\u52a0\u8868\u7a7a\u95f4\u6587\u4ef6\r\nalter database datafile '\/opt\/oracle\/oradata\/data0.dbf' resize 4096M; \/\/ \u62d3\u5c55\u539f\u6709\u7684\u8868\u7a7a\u95f4\u6587\u4ef6\uff0c\u53ea\u8981\u4e0d\u5927\u4e8e31G\uff08\u5355\u4e2a\u8868\u7a7a\u95f4\u6587\u4ef6\uff09\uff0c\u62d3\u5c55\u4e0d\u4f24\u5bb3\u6570\u636e\u3002<\/code><\/pre>\n\u5efa\u8868\u8bed\u53e5\u67e5\u8be2\uff1a<\/p>\n
set long 99999\r\nset linesize 500\r\nset pagesize 900\r\nspool \/home\/oracle\/create_tb.sql\r\nselect dbms_metadata.get_ddl('TABLE','TB_LIUS','USERLIUS') from dual;\r\nspool off;\r\n\r\n\u8fd9\u6837\uff0c\u5efa\u8868\u8bed\u53e5\u4f1a\u5728\/home\/oracle\/create_tb.sql\u4e2d\uff0c\u65b9\u4fbf\u67e5\u770b\u3002<\/code><\/pre>\n\u521b\u5efa\u67e5\u8be2\u7528\u6237\uff1a<\/p>\n
create user query_user identified by my_password default tablespace query_user;\r\n\r\ngrant execute on SYS.DBMS_EXPORT_EXTENSION to QUERY_USER;\r\ngrant execute on SYS.SYS_PLSQL_DA3FBA35_644_1 to QUERY_USER with grant option;\r\ngrant connect to QUERY_USER;\r\ngrant resource to QUERY_USER;\r\ngrant create procedure to QUERY_USER;\r\ngrant create sequence to QUERY_USER;\r\ngrant create table to QUERY_USER;\r\ngrant create view to QUERY_USER;\r\ngrant select any dictionary to QUERY_USER;\r\ngrant select any table to QUERY_USER;\r\ngrant unlimited tablespace to QUERY_USER;<\/code><\/pre>\n\u67e5\u770bprofile\u7684\u540d\u79f0:<\/p>\n
select profile from dba_users where username='TB_LIUS';\r\n\u9ed8\u8ba4\u662fDEFAULT, \u5927\u5c0f\u5199\u654f\u611f\uff1b<\/code><\/pre>\n\u7ec4\u7ec7\u51fa\u6539\u53d8profile\u8fc7\u671f\u65f6\u95f4\u7684\u8bed\u53e5\uff1a<\/p>\n
select 'alter profile ' || profile || ' limit ' || resource_name || ' ' ||decode(resource_name, 'PASSWORD_LIFE_TIME','UNLIMITED','PASSWORD_REUSE_TIME','UNLIMITED','PASSWORD_REUSE_MAX','UNLIMITED',\r\n'PASSWORD_VERIFY_FUNCTION','null','PASSWORD_LOCK_TIME','UNLIMITED','PASSWORD_GRACE_TIME', 'UNLIMITED') ||' ;' sql from dba_profiles a where a.profile='DEFAULT' AND a.resource_name like 'PASS%';<\/code><\/pre>\n\u91cd\u65b0\u4fee\u6539\u5bc6\u7801\uff1a<\/p>\n
alter user USER_LIUS identified by 123456;<\/code><\/pre>\n <\/p>\n
\u8f6c\u8f7d\u8bf7\u6ce8\u660e\uff1aliutianfeng.com<\/a> » Oracle\u5e38\u7528\u8bed\u53e5<\/a><\/p>","protected":false},"excerpt":{"rendered":"oracle\u67e5\u8be2\u8868\u7a7a\u95f4\u8bed\u53e5\uff1a set linesize 200 set pagesize 100 col to […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.liutianfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/887"}],"collection":[{"href":"https:\/\/www.liutianfeng.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.liutianfeng.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.liutianfeng.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.liutianfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=887"}],"version-history":[{"count":1,"href":"https:\/\/www.liutianfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/887\/revisions"}],"predecessor-version":[{"id":888,"href":"https:\/\/www.liutianfeng.com\/index.php?rest_route=\/wp\/v2\/posts\/887\/revisions\/888"}],"wp:attachment":[{"href":"https:\/\/www.liutianfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=887"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.liutianfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=887"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.liutianfeng.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=887"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}