{"id":5294,"date":"2022-06-10T10:28:16","date_gmt":"2022-06-10T02:28:16","guid":{"rendered":"https:\/\/ixyzero.com\/blog\/?p=5294"},"modified":"2022-06-10T10:40:21","modified_gmt":"2022-06-10T02:40:21","slug":"oracle%e6%95%b0%e6%8d%ae%e5%ba%93%e6%93%8d%e4%bd%9c%e5%ad%a6%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/5294.html","title":{"rendered":"Oracle\u6570\u636e\u5e93\u64cd\u4f5c\u5b66\u4e60"},"content":{"rendered":"\n<p>=Start=<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u7f18\u7531\uff1a<\/h4>\n\n\n\n<p>\u7b80\u5355\u8bb0\u5f55\u4e00\u4e0b\u8fd1\u671f\u5b66\u4e60\u7684Oracle\u6570\u636e\u5e93\u7684\u7b80\u5355\u64cd\u4f5c\u65b9\u6cd5\uff0c\u65b9\u4fbf\u4ee5\u540e\u6709\u9700\u8981\u7684\u65f6\u5019\u53c2\u8003\u3002<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u6b63\u6587\uff1a<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n\n\n\n<p>\u56e0\u4e3aOracle\u6570\u636e\u5e93\u548c\u6211\u4eec\u5e38\u89c1\u7684MySQL\u6570\u636e\u5e93\u76f8\u6bd4\u6709\u4e9b\u6982\u5ff5\u4e0d\u592a\u4e00\u6837\uff0c\u6982\u5ff5\u7684\u533a\u522b\u5bfc\u81f4\u5f88\u591a\u5185\u5bb9\u5728\u4e60\u60ef\u4e86MySQL\u7684\u90a3\u4e00\u5957\u4e4b\u540e\u4e0d\u592a\u5bb9\u6613\u7406\u89e3Oracle\u7684\u90a3\u5957\uff1b\u518d\u52a0\u4e0a\u73b0\u5728Oracle\u6570\u636e\u5e93\u5bf9\u4e8e\u6211\u6765\u8bf4\u89c1\u7684\u5c11\u3001\u7528\u7684\u4e5f\u5c11\uff0c\u6240\u4ee5\u53ea\u80fd\u662f\u7b49\u540e\u9762\u6709\u673a\u4f1a\u78b0\u5230\u4e86\u518d\u5b66\u4e60\u3001\u7406\u89e3\u548c\u8bb0\u5f55\u4e86\u3002<\/p>\n\n\n\n<p>\u53e6\u5916\u5c31\u662f\u63d0\u4e00\u53e5\uff0cOracle\u6570\u636e\u5e93\u7684\u5b98\u65b9\u6587\u6863\u771f\u7684\u662f\u5f88\u5168\u9762\uff08\u56fd\u5916\u5546\u4e1a\u4ea7\u54c1\u8fd9\u65b9\u9762\u505a\u7684\u5f88\u597d\uff09\uff0c\u901a\u8fc7\u9605\u8bfb\u6587\u6863\u80fd\u89e3\u51b3\u4f60\u7684\u7edd\u5927\u90e8\u5206\u95ee\u9898\uff1b\u5982\u679c\u4f60\u662f\u4ed8\u8d39\u7528\u6237\uff0c\u5b9e\u5728\u641e\u4e0d\u5b9a\u4e86\u76f4\u63a5\u627e\u6280\u672f\u652f\u6301\u5c31\u884c\u3002\u4e0d\u50cf\u5927\u90e8\u5206\u5f00\u6e90\u4ea7\u54c1\uff0c\u6587\u6863\u51e0\u4e4e\u6ca1\u6709\uff0c\u7f51\u4e0a\u7684\u6587\u7ae0\u4e71\u4e03\u516b\u7cdf\u7684\uff0c\u9700\u8981\u82b1\u5f88\u591a\u7684\u7cbe\u529b\u53bb\u7b5b\u9009\u3001\u9a8c\u8bc1\uff08\u8fd9\u90fd\u8fd8\u4e0d\u7b97\u4ee3\u7801\u7684bug\uff09\u3002<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>\u6570\u636e\u5e93\u8fde\u63a5\u65b9\u6cd5<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># sqlplus\u547d\u4ee4\u683c\u5f0f\n&gt;sqlplus {username | \/} &#91;as sysdba]\n\n# \u76f4\u63a5\u8f93\u5165 sqlplus \u547d\u4ee4\u540e\u9762\u4f1a\u8981\u6c42\u4ea4\u4e92\u5f0f\u7684\u8f93\u5165\u7528\u6237\u540d\u5bc6\u7801\nC:\\Users\\Administrator&gt;sqlplus\n\nSQL*Plus: Release 12.2.0.1.0 Production on \u661f\u671f\u5929 6\u6708 5 16:32:02 2022\n\nCopyright (c) 1982, 2016, Oracle.  All rights reserved.\n\n\u8bf7\u8f93\u5165\u7528\u6237\u540d:  atest\n\u8f93\u5165\u53e3\u4ee4:\n\u4e0a\u6b21\u6210\u529f\u767b\u5f55\u65f6\u95f4: \u661f\u671f\u5929 6\u6708  05 2022 16:31:01 +08:00\n\n\u8fde\u63a5\u5230:\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n\nSQL&gt;\nSQL&gt; exit\n\u4ece Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n\u65ad\u5f00\n\n# \u8f93\u5165 sqlplus \/ as sysdba \u547d\u4ee4\u8868\u660e\u4f7f\u7528\u64cd\u4f5c\u7cfb\u7edf\u8ba4\u8bc1\u65b9\u5f0f(operating system authentication)\n# \u6ce8\u610f\u548c\u4e0a\u9762\u76f8\u6bd4\uff0c\u8fd9\u6b21\u662f\u6ca1\u6709\u8981\u6c42\u8f93\u5165\u7528\u6237\u540d\u5bc6\u7801\u7684\nC:\\Users\\Administrator&gt;sqlplus \/ as sysdba\n\nSQL*Plus: Release 12.2.0.1.0 Production on \u661f\u671f\u5929 6\u6708 5 20:31:30 2022\n\nCopyright (c) 1982, 2016, Oracle.  All rights reserved.\n\n\n\u8fde\u63a5\u5230:\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n\nSQL&gt;\nSQL&gt; select ora_database_name from dual;\n\nORA_DATABASE_NAME\n--------------------------------------------------------------------------------\n\nTEST\n\nSQL&gt; select name from v$database;\n\nNAME\n------------------\nTEST\n\nSQL&gt;\nSQL&gt; select user from dual;\n\nUSER\n--------------------------------------------------------------------------------\n\nSYS\n\nSQL&gt; conn atest as sysdba\n\u8f93\u5165\u53e3\u4ee4:\n\u5df2\u8fde\u63a5\u3002\nSQL&gt;\nSQL&gt;<\/code><\/pre>\n\n\n\n<p><\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>\u6570\u636e\u5e93\u5e38\u89c1\u67e5\u8be2<\/li><\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code># \u67e5\u770b\u5f53\u524d\u4f7f\u7528\u7684\u6570\u636e\u5e93\u540d\u79f0\nselect ora_database_name from dual;\nselect * from global_name;\n\n# \u67e5\u770b\u5f53\u524d\u7684\u767b\u5f55\u7528\u6237\nselect user from dual;\n\n# \u67e5\u770b\u90fd\u6709\u54ea\u4e9b\u7528\u6237\nselect username from all_users order by username;\n\n# \u67e5\u770b\u90fd\u6709\u54ea\u4e9b\u6570\u636e\u5e93\nselect name from v$database;\n\nselect * from dba_users; -- \u8fd4\u56de\u7684\u5185\u5bb9\u8fc7\u591a\uff0c\u5bf9\u4e8e\u65b0\u624b\u4e0d\u53cb\u597d\n\n# \u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u4e0b\u90fd\u6709\u54ea\u4e9b\u8868\nselect tablespace_name from user_tablespaces;\n\nSELECT owner, table_name FROM dba_tables WHERE owner = 'owner_name';\nSELECT owner, table_name FROM dba_tables; --Viewing All Tables\n\nSELECT owner, table_name FROM all_tables; --Viewing Tables Accessible by Current User\n\nSELECT owner, table_name FROM user_tables; --Viewing Tables Owned by Current user\n\n# \u67e5\u770b\u7279\u5b9a\u8868\u7684\u5efa\u8868\u8bed\u53e5\nselect dbms_metadata.get_ddl('TABLE', 'table_name') from dual;\nselect dbms_metadata.get_ddl('TABLE', table_name) from user_tables;\ndescribe table_name;\n\n# \u67e5\u770bTABLESPACE\u7684\u4f7f\u7528\u7a7a\u95f4\u548c\u5269\u4f59\u7a7a\u95f4\nSELECT a.tablespace_name \"TABLESPACE_NAME\", \ntotal \"TOTAL\", \nfree \"FREE\", \n(total - free) \"USED\", \ntotal \/ (1024 * 1024 * 1024) \"TOTAL(G)\", \nfree \/ (1024 * 1024 * 1024) \"FREE(G)\", \n(total - free) \/ (1024 * 1024 * 1024) \"USED(G)\", \nround((total - free) \/ total, 4) * 100 \"USED %\" \nFROM (SELECT tablespace_name, SUM(bytes) free \nFROM dba_free_space \nGROUP BY tablespace_name) a, \n(SELECT tablespace_name, SUM(bytes) total \nFROM dba_data_files \nGROUP BY tablespace_name) b \nWHERE a.tablespace_name = b.tablespace_name;\n\n# \u67e5\u770b\u7279\u5b9aTABLESPACE\u7684\u8868\u6587\u4ef6\u6709\u54ea\u4e9b\nSELECT tablespace_name, \nfile_id, \nfile_name, \nround(bytes\/(1024*1024),0) total_space \nFROM dba_data_files \nwhere tablespace_name = 'USERS' \nORDER BY tablespace_name;<\/code><\/pre>\n\n\n\n<p><\/p>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n<p>Oracle\u6570\u636e\u5e93\u662f\u4ec0\u4e48\uff1f<br><a href=\"https:\/\/www.w3cschool.cn\/oraclejc\/oraclejc-dxgu2qqt.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.w3cschool.cn\/oraclejc\/oraclejc-dxgu2qqt.html<\/a><\/p>\n\n\n\n<p>Oracle \u6570\u636e\u5e93\u8fde\u63a5<br><a href=\"https:\/\/www.shulanxt.com\/database\/oracle\/oraclejc-connectdb\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.shulanxt.com\/database\/oracle\/oraclejc-connectdb<\/a><\/p>\n\n\n\n<p>3.8.3 Starting SQL*Plus and Connecting to the Database<br><a href=\"https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-DE8A79BD-FAE4-4364-98FF-D2BD992A06E7.htm\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-DE8A79BD-FAE4-4364-98FF-D2BD992A06E7.htm<\/a><\/p>\n\n\n\n<p>Oracle Database 2 Day DBA<br><a href=\"https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-6F9B427D-1298-4E42-A611-E7EA75FE018B.htm\">https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-6F9B427D-1298-4E42-A611-E7EA75FE018B.htm<\/a><\/p>\n\n\n\n<p>1.3 Common Oracle DBA Tasks<br><a href=\"https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-CC7F51A1-9F19-43CC-905B-C9E2006D7937.htm\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-CC7F51A1-9F19-43CC-905B-C9E2006D7937.htm<\/a><\/p>\n\n\n\n<p>Starting SQL*Plus and Connecting to the Database<br><a href=\"https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-DE8A79BD-FAE4-4364-98FF-D2BD992A06E7.htm#ADMQS0361\">https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-DE8A79BD-FAE4-4364-98FF-D2BD992A06E7.htm#ADMQS0361<\/a><\/p>\n\n\n\n<p>Example 1-3 Connecting to a Local Database User<br><a href=\"https:\/\/docs.oracle.com\/database\/121\/ADMIN\/dba.htm\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/docs.oracle.com\/database\/121\/ADMIN\/dba.htm<\/a><\/p>\n\n\n\n<p>How to Query Database Name in Oracle SQL Developer?<br><a href=\"https:\/\/stackoverflow.com\/questions\/8978047\/how-to-query-database-name-in-oracle-sql-developer\">https:\/\/stackoverflow.com\/questions\/8978047\/how-to-query-database-name-in-oracle-sql-developer<\/a><\/p>\n\n\n\n<p>PLSQL, SQL*PLUS get current username?<br><a href=\"https:\/\/stackoverflow.com\/questions\/23417522\/plsql-sqlplus-get-current-username\">https:\/\/stackoverflow.com\/questions\/23417522\/plsql-sqlplus-get-current-username<\/a><\/p>\n\n\n\n<p>How to display databases in Oracle 11g using SQL*Plus<br><a href=\"https:\/\/stackoverflow.com\/questions\/3004171\/how-to-display-databases-in-oracle-11g-using-sqlplus\">https:\/\/stackoverflow.com\/questions\/3004171\/how-to-display-databases-in-oracle-11g-using-sqlplus<\/a><\/p>\n\n\n\n<p>Query to display all tablespaces in a database and datafiles<br><a href=\"https:\/\/stackoverflow.com\/questions\/36209102\/query-to-display-all-tablespaces-in-a-database-and-datafiles\">https:\/\/stackoverflow.com\/questions\/36209102\/query-to-display-all-tablespaces-in-a-database-and-datafiles<\/a><\/p>\n\n\n\n<p>DBA_TABLESPACES describes all tablespaces in the database.<br><a href=\"https:\/\/docs.oracle.com\/database\/121\/REFRN\/GUID-B28A7D79-24E3-49B5-B948-7C2277CB1FB8.htm#REFRN23287\">https:\/\/docs.oracle.com\/database\/121\/REFRN\/GUID-B28A7D79-24E3-49B5-B948-7C2277CB1FB8.htm#REFRN23287<\/a><\/p>\n\n\n\n<p>DBA_DATA_FILES describes database files.<br><a href=\"https:\/\/docs.oracle.com\/database\/121\/REFRN\/GUID-0FA17297-73ED-4B5D-B511-103993C003D3.htm#REFRN23049\">https:\/\/docs.oracle.com\/database\/121\/REFRN\/GUID-0FA17297-73ED-4B5D-B511-103993C003D3.htm#REFRN23049<\/a><\/p>\n\n\n\n<p>How to List All Tables in Oracle<br><a href=\"https:\/\/chartio.com\/resources\/tutorials\/how-to-list-all-tables-in-oracle\/\">https:\/\/chartio.com\/resources\/tutorials\/how-to-list-all-tables-in-oracle\/<\/a><\/p>\n\n\n\n<p>Get list of all tables in Oracle?<br><a href=\"https:\/\/stackoverflow.com\/questions\/205736\/get-list-of-all-tables-in-oracle\">https:\/\/stackoverflow.com\/questions\/205736\/get-list-of-all-tables-in-oracle<\/a><\/p>\n\n\n\n<p>Find out free space on tablespace<br><a href=\"https:\/\/stackoverflow.com\/questions\/7672126\/find-out-free-space-on-tablespace\">https:\/\/stackoverflow.com\/questions\/7672126\/find-out-free-space-on-tablespace<\/a><\/p>\n\n\n\n<p>How to see list of databases in Oracle?<br><a href=\"https:\/\/dba.stackexchange.com\/questions\/27725\/how-to-see-list-of-databases-in-oracle\">https:\/\/dba.stackexchange.com\/questions\/27725\/how-to-see-list-of-databases-in-oracle<\/a><\/p>\n\n\n\n<p>Difference between a user and a schema in Oracle?<br><a href=\"https:\/\/stackoverflow.com\/questions\/880230\/difference-between-a-user-and-a-schema-in-oracle\">https:\/\/stackoverflow.com\/questions\/880230\/difference-between-a-user-and-a-schema-in-oracle<\/a><\/p>\n\n\n\n<p>SYSDBA and SYSOPER System Privileges<br><a href=\"https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-2033E766-8FE6-4FBA-97E0-2607B083FA2C.htm#ADMQS12004\">https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-2033E766-8FE6-4FBA-97E0-2607B083FA2C.htm#ADMQS12004<\/a><\/p>\n\n\n\n<p>How to get Oracle create table statement in SQL*Plus<br><a href=\"https:\/\/stackoverflow.com\/questions\/937398\/how-to-get-oracle-create-table-statement-in-sqlplus\">https:\/\/stackoverflow.com\/questions\/937398\/how-to-get-oracle-create-table-statement-in-sqlplus<\/a><br><a href=\"https:\/\/stackoverflow.com\/questions\/26249892\/how-can-i-generate-or-get-a-ddl-script-on-an-existing-table-in-oracle-i-have\">https:\/\/stackoverflow.com\/questions\/26249892\/how-can-i-generate-or-get-a-ddl-script-on-an-existing-table-in-oracle-i-have<\/a><\/p>\n\n\n\n<p>Show the structure of the table in SQL<br><a href=\"https:\/\/stackoverflow.com\/questions\/7356338\/show-the-structure-of-the-table-in-sql\">https:\/\/stackoverflow.com\/questions\/7356338\/show-the-structure-of-the-table-in-sql<\/a><\/p>\n\n\n\n<p>Oracle\u6570\u636e\u5e93\u4e2d\u7684\u8d26\u6237\u7684\u6982\u5ff5\u8bf4\u660e 7.1 About User Accounts<br><a href=\"https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-434BA586-DDFC-4C37-B442-C9348C2853AE.htm#ADMQS071\">https:\/\/docs.oracle.com\/database\/121\/ADMQS\/GUID-434BA586-DDFC-4C37-B442-C9348C2853AE.htm#ADMQS071<\/a><\/p>\n\n\n\n<p>Difference between Oracle and MySQL<br><a href=\"https:\/\/www.geeksforgeeks.org\/difference-between-oracle-and-mysql\/\">https:\/\/www.geeksforgeeks.org\/difference-between-oracle-and-mysql\/<\/a><\/p>\n\n\n\n<p>mysql\u548coracle\u7684\u533a\u522b<br><a href=\"https:\/\/zhuanlan.zhihu.com\/p\/39651803\">https:\/\/zhuanlan.zhihu.com\/p\/39651803<\/a><\/p>\n\n\n\n<p>Oracle and MySQL Compared<br><a href=\"https:\/\/docs.oracle.com\/cd\/E12151_01\/doc.150\/e12155\/oracle_mysql_compared.htm#CHDIIBJH\">https:\/\/docs.oracle.com\/cd\/E12151_01\/doc.150\/e12155\/oracle_mysql_compared.htm#CHDIIBJH<\/a><\/p>\n\n\n\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u7b80\u5355\u8bb0\u5f55\u4e00\u4e0b\u8fd1\u671f\u5b66\u4e60\u7684Oracle\u6570\u636e\u5e93\u7684\u7b80\u5355\u64cd\u4f5c\u65b9\u6cd5\uff0c\u65b9\u4fbf\u4ee5\u540e\u6709\u9700\u8981\u7684\u65f6\u5019\u53c2\u8003\u3002 \u6b63 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,12],"tags":[1837,1834,1262,1835,1836],"class_list":["post-5294","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-tools","tag-dba","tag-oracle","tag-sql","tag-sqlplus","tag-sysdba"],"views":2099,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5294","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=5294"}],"version-history":[{"count":4,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5294\/revisions"}],"predecessor-version":[{"id":5301,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/5294\/revisions\/5301"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=5294"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=5294"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=5294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}