oracle sqlplus 常用命令实例
SQL info; Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 using D:\oracleinstantclient_11_2\oci.dll (OCI version 11.1) Connected as SYS SQL select * from v$log; /////此视图包含需要归档的重做日志文件的信息 GROUP# THRE
SQL> info;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
using D:\oracleinstantclient_11_2\oci.dll (OCI version 11.1)
Connected as SYS
SQL> select * from v$log; /////此视图包含需要归档的重做日志文件的信息
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 25 52428800 512 1 NO INACTIVE 1649914 2012-10-22 1666515 2012-10-22
2 1 26 52428800 512 1 NO CURRENT 1666515 2012-10-22 281474976710
3 1 24 52428800 512 1 NO INACTIVE 1611653 2012-10-21 1649914 2012-10-22
SQL> select * from V$BACKUP; ////此视图显示所有联机数据文件的备份状态。
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- -----------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0
6 NOT ACTIVE 0
6 rows selected
SQL> select * from V$BACKUP_ASYNC_IO; ///此视图显示控制文件中的备份集信息。在成功地完成备份集时，插入一个备份集记录。
SID SERIAL USE_COUNT RMAN_STATUS_RECID RMAN_STATUS_STAMP DEVICE_TYPE TYPE STATUS FILENAME SET_COUNT SET_STAMP BUFFER_SIZE BUFFER_COUNT TOTAL_BYTES OPEN_TIME
CLOSE_TIME ELAPSED_TIME MAXOPENFILES BYTES EFFECTIVE_BYTES_PER_SECOND IO_COUNT READY SHORT_WAITS SHORT_WAIT_TIME_TOTAL SHORT_WAIT_TIME_MAX LONG_WAITS LONG_WAIT_TIME_TOTAL LONG_WAIT_TIME_MAX
---------- ---------- ---------- ----------------- ----------------- ----------------- --------- ----------- -------------------------------------------------------------------------------- ---------- ---------- ----------- ------------ ----------- -----------
----------- ------------ ------------ ---------- -------------------------- ---------- ---------- ----------- --------------------- ------------------- ---------- -------------------- ------------------
SQL> select * from V$BACKUP_CORRUPTION; ///此视图显示来自控制文件的数据文件备份中出错的相关信息。注意，在控制文件和归
////档日志备份中是不容许出错的。
RECID STAMP SET_STAMP SET_COUNT PIECE# FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# MARKED_CORRUPT CORRUPTION_TYPE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------ -------------- ---------------
SQL> select * from V$BACKUP_DATAFILE; ////此视图显示来自控制文件的备份数据文件和备份控制文件。
RECID STAMP SET_STAMP SET_COUNT FILE# CREATION_CHANGE# CREATION_TIME RESETLOGS_CHANGE# RESETLOGS_TIME INCREMENTAL_LEVEL INCREMENTAL_CHANGE# CHECKPOINT_CHANGE# CHECKPOINT_TIME ABSOLUTE_FUZZY_CHANGE# MARKED_CORRUPT MEDIA_CORRUPT LOGICALLY_CORRUPT
DATAFILE_BLOCKS BLOCKS BLOCK_SIZE OLDEST_OFFLINE_RANGE COMPLETION_TIME CONTROLFILE_TYPE USED_CHANGE_TRACKING BLOCKS_READ USED_OPTIMIZATION FOREIGN_DBID PLUGGED_READONLY PLUGIN_CHANGE# PLUGIN_RESETLOGS_CHANGE# PLUGIN_RESETLOGS_TIME div_SIZE UNDO_OPTIMIZED
BLOCKS_SKIPPED_IN_CELL
---------- ---------- ---------- ---------- ---------- ---------------- ------------- ----------------- -------------- ----------------- ------------------- ------------------ --------------- ---------------------- -------------- ------------- -----------------
--------------- ---------- ---------- -------------------- --------------- ---------------- -------------------- ----------- ----------------- ------------ ---------------- -------------- ------------------------ --------------------- ------------ --------------
----------------------
SQL>
SQL> select * from V$BACKUP_DEVICE; (注释：这个视图显示支持设备的设备的有关信息。如果某种设备类型不支持指定的设备，则返
回该设备的一个带设备类型和空设备的行。如果某种设备类型支持指定的设备，则为该类型
的每个可用设备返回一行。这个视图不返回特殊的设备类型DISK，因为它总是可用的。)
DEVICE_TYPE DEVICE_NAME
----------------- --------------------------------------------------------------------------------
SBT_TAPE
SQL>
SQL> select * from V$INSTANCE; ////这个视图显示当前实例的状态。
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_TIME STATUS PARALLEL THREAD# ARCHIVER LOG_SWITCH_WAIT LOGINS SHUTDOWN_PENDING DATABASE_STATUS INSTANCE_ROLE ACTIVE_STATE
BLOCKED
--------------- ---------------- ---------------------------------------------------------------- ----------------- ------------ ------------ -------- ---------- -------- --------------- ---------- ---------------- ----------------- ------------------ ------------
-------
1 orcl zhangshibo 11.2.0.1.0 2012-10-17 1 OPEN NO 1 STOPPED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL
NO
SQL>
SQL> select * from V$LOCK; ////这个视图列出Oracle 服务器当前拥有的锁以及未完成的锁或栓锁请求。
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000C6C891A8 00000000C6C89200 209 XR 4 0 1 0 443625 0
00000000C6C89278 00000000C6C892D0 209 RD 1 0 1 0 443625 0
00000000C6C89348 00000000C6C893A0 209 CF 0 0 2 0 443625 0
00000000C6C894E8 00000000C6C89540 209 RS 25 1 2 0 443621 0
00000000C6C895B8 00000000C6C89610 99 AE 100 0 4 0 3425 0
00000000C6C89688 00000000C6C896E0 193 RT 1 0 6 0 443621 0
00000000C6C89758 00000000C6C897B0 161 PW 1 0 3 0 443301 0
00000000C6C89828 00000000C6C89880 225 TS 3 1 3 0 443304 0
00000000C6C898F8 00000000C6C89950 1 AE 100 0 4 0 443303 0
00000000C6C899C8 00000000C6C89A20 161 MR 1 0 4 0 443304 0
00000000C6C89A98 00000000C6C89AF0 161 MR 2 0 4 0 443304 0
00000000C6C89B68 00000000C6C89BC0 161 MR 3 0 4 0 443304 0
00000000C6C89C38 00000000C6C89C90 161 MR 4 0 4 0 443304 0
00000000C6C89D08 00000000C6C89D60 161 MR 5 0 4 0 443304 0
00000000C6C89DD8 00000000C6C89E30 161 MR 6 0 4 0 443304 0
00000000C6C89EC0 00000000C6C89F18 161 MR 201 0 4 0 443304 0
00000000C6C89F90 00000000C6C89FE8 114 AE 100 0 4 0 3507 0
00000000C6C8A060 00000000C6C8A0B8 130 AE 100 0 4 0 408 0
00000000C6C8A130 00000000C6C8A188 146 AE 100 0 4 0 3409 0
00000000C6C8A200 00000000C6C8A258 162 AE 100 0 4 0 3367 0
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
00000000C6C8A2D0 00000000C6C8A328 178 AE 100 0 4 0 1067 0
21 rows selected
SQL>
SQL> select * from V$OPTION; ////这个视图列出与 Oracle 一道安装的选项。
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Partitioning TRUE
Objects TRUE
Real Application Clusters FALSE
Advanced replication TRUE
Bit-mapped indexes TRUE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery TRUE
Parallel execution TRUE
Parallel load TRUE
Point-in-time tablespace recovery TRUE
Fine-grained access control TRUE
Proxy authentication/authorization TRUE
Change Data Capture TRUE
Plan Stability TRUE
Online Index Build TRUE
Coalesce Index TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Managed Standby TRUE
Materialized view rewrite TRUE
Materialized view warehouse refresh TRUE
Database resource manager TRUE
Spatial TRUE
Automatic Storage Management FALSE
Export transportable tablespaces TRUE
Transparent Application Failover TRUE
Fast-Start Fault Recovery TRUE
Sample Scan TRUE
Duplexed backups TRUE
Java TRUE
OLAP Window Functions TRUE
Block Media Recovery TRUE
Fine-grained Auditing TRUE
Application Role TRUE
Enterprise User Security TRUE
Oracle Data Guard TRUE
Oracle Label Security FALSE
OLAP TRUE
Basic Compression TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Join index TRUE
Trial Recovery TRUE
Data Mining TRUE
Online Redefinition TRUE
Streams Capture TRUE
File Mapping TRUE
Block Change Tracking TRUE
Flashback Table TRUE
Flashback Database TRUE
Transparent Data Encryption TRUE
Backup Encryption TRUE
Unused Block Compression TRUE
Oracle Database Vault FALSE
Result Cache TRUE
SQL Plan Management TRUE
SecureFiles Encryption TRUE
Real Application Testing TRUE
Flashback Data Archive TRUE
DICOM TRUE
Active Data Guard TRUE
Server Flash Cache TRUE
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Advanced Compression TRUE
XStream TRUE
Deferred Segment Creation TRUE
65 rows selected
SQL>
SQL> select * from V$ROLLNAME; ///这个视图列出所有联机回退段的名称。它只有在数据库打开时访问。
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1_3780397527$
2 _SYSSMU2_2232571081$
3 _SYSSMU3_2097677531$
4 _SYSSMU4_1152005954$
5 _SYSSMU5_1527469038$
6 _SYSSMU6_2443381498$
7 _SYSSMU7_3286610060$
8 _SYSSMU8_2012382730$
9 _SYSSMU9_1424341975$
10 _SYSSMU10_3550978943$
11 rows selected
SQL>
SQL> select * from V$RSRC_CONSUMER_GROUP_CPU_MTH; ////这个视图显示资源使用者组的所有可用资源分配方法。
NAME
----------------------------------------
ROUND-ROBIN
RUN-TO-COMPLETION
SQL>
SQL> select * from V$RSRC_PLAN; ////这个视图显示所有当前活动资源计划的名称。
ID NAME IS_TOP_PLAN CPU_MANAGED
---------- -------------------------------- ----------- -----------
SQL>
SQL> select * from V$SESSION_CONNECT_INFO; ///这个视图显示当前会话的网络连接的有关信息。
SID SERIAL# AUTHENTICATION_TYPE OSUSER NETWORK_SERVICE_BANNER CLIENT_CHARSET CLIENT_CONNECTION CLIENT_OCI_LIBRARY CLIENT_VERSION
CLIENT_DRIVER CLIENT_LOBATTR CLIENT_REGID
---------- ---------- -------------------------- ------------------------------ -------------------------------------------------------------------------------- ---------------------------------------- ----------------- --------------------------- ----------------------------------------
------------- ----------------------- ------------
99 2486 INTERNAL 马明 TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
99 2486 INTERNAL 马明 Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
99 2486 INTERNAL 马明 Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0. UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
114 2040 DATABASE lxm TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production US7ASCII Homogeneous Home-based 11.2.0.2.0
Client Temp Lob Rfc On 0
114 2040 DATABASE lxm Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro US7ASCII Homogeneous Home-based 11.2.0.2.0
Client Temp Lob Rfc On 0
114 2040 DATABASE lxm Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0. US7ASCII Homogeneous Home-based 11.2.0.2.0
Client Temp Lob Rfc On 0
130 163 DATABASE Administrator TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
130 163 DATABASE Administrator Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
130 163 DATABASE Administrator Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0. UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
146 77 INTERNAL 马明 TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
146 77 INTERNAL 马明 Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
146 77 INTERNAL 马明 Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0. UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
162 89 INTERNAL 马明 TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
162 89 INTERNAL 马明 Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
162 89 INTERNAL 马明 Oracle Advanced Security: crypto-checksumming service for Linux: Version 11.2.0. UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
178 65 DATABASE Administrator TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
Client Temp Lob Rfc On 0
178 65 DATABASE Administrator Oracle Advanced Security: encryption service for Linux: Version 11.2.0.1.0 - Pro UTF8 Heterogeneous Full Instant Client 11.2.0.2.0
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
Video Face Swap
使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！
熱門文章
熱工具
記事本++7.3.1
好用且免費的程式碼編輯器
SublimeText3漢化版
中文版，非常好用
禪工作室 13.0.1
強大的PHP整合開發環境
Dreamweaver CS6
視覺化網頁開發工具
SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)
oracle游標關閉怎麼解決
Apr 11, 2025 pm 10:18 PM
解決 Oracle 游標關閉問題的方法包括：使用 CLOSE 語句顯式關閉游標。在 FOR UPDATE 子句中聲明游標，使其在作用域結束後自動關閉。在 USING 子句中聲明游標，使其在關聯的 PL/SQL 變量關閉時自動關閉。使用異常處理確保在任何異常情況下關閉游標。使用連接池自動關閉游標。禁用自動提交，延遲游標關閉。
oracle怎麼循環創建游標
Apr 12, 2025 am 06:18 AM
Oracle 中，FOR LOOP 循環可動態創建游標， 步驟為：1. 定義游標類型；2. 創建循環；3. 動態創建游標；4. 執行游標；5. 關閉游標。示例：可循環創建游標，顯示前 10 名員工姓名和工資。
oracle視圖怎麼導出
Apr 12, 2025 am 06:15 AM
可以通過 EXP 實用程序導出 Oracle 視圖：登錄 Oracle 數據庫。啟動 EXP 實用程序，指定視圖名稱和導出目錄。輸入導出參數，包括目標模式、文件格式和表空間。開始導出。使用 impdp 實用程序驗證導出。
oracle日誌寫滿怎麼辦
Apr 12, 2025 am 06:09 AM
Oracle 日誌文件寫滿時，可採用以下解決方案：1）清理舊日誌文件；2）增加日誌文件大小；3）增加日誌文件組；4）設置自動日誌管理；5）重新初始化數據庫。在實施任何解決方案前，建議備份數據庫以防數據丟失。
甲骨文在商業世界中的作用
Apr 23, 2025 am 12:01 AM
Oracle不僅是數據庫公司，還是雲計算和ERP系統的領導者。 1.Oracle提供從數據庫到雲服務和ERP系統的全面解決方案。 2.OracleCloud挑戰AWS和Azure，提供IaaS、PaaS和SaaS服務。 3.Oracle的ERP系統如E-BusinessSuite和FusionApplications幫助企業優化運營。
HDFS配置CentOS需要哪些步驟
Apr 14, 2025 pm 06:42 PM
在CentOS系統上搭建Hadoop分佈式文件系統（HDFS）需要多個步驟，本文提供一個簡要的配置指南。一、前期準備安裝JDK:在所有節點上安裝JavaDevelopmentKit(JDK)，版本需與Hadoop兼容。可從Oracle官網下載安裝包。環境變量配置:編輯/etc/profile文件，設置Java和Hadoop的環境變量，使系統能夠找到JDK和Hadoop的安裝路徑。二、安全配置：SSH免密登錄生成SSH密鑰:在每個節點上使用ssh-keygen命令
oracle數據庫怎麼停止
Apr 12, 2025 am 06:12 AM
要停止 Oracle 數據庫，請執行以下步驟：1. 連接到數據庫；2. 優雅關機數據庫（shutdown immediate）；3. 完全關機數據庫（shutdown abort）。
centos上weblogic的數據庫連接如何配置
Apr 14, 2025 pm 02:06 PM
在CentOS系統上配置WebLogic數據庫連接，需要完成以下步驟：JDK安裝與環境配置:確保服務器已安裝與WebLogic版本兼容的JDK(例如，WebLogic14.1.1通常需要JDK8)。正確設置JAVA_HOME、CLASSPATH和PATH環境變量。 WebLogic安裝與解壓:從Oracle官方網站下載適用於CentOS系統的WebLogic安裝包，並將其解壓到指定目錄。 WebLogic用戶與目錄創建:創建一個專用的WebLogic用戶賬戶，並設置安全密碼
