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脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
热门话题
oracle如何查表空间大小
Apr 11, 2025 pm 08:15 PM
要查询 Oracle 表空间大小，请遵循以下步骤：确定表空间名称，方法是运行查询：SELECT tablespace_name FROM dba_tablespaces;查询表空间大小，方法是运行查询：SELECT sum(bytes) AS total_size, sum(bytes_free) AS available_space, sum(bytes) - sum(bytes_free) AS used_space FROM dba_data_files WHERE tablespace_
oracle视图如何加密
Apr 11, 2025 pm 08:30 PM
Oracle 视图加密允许您加密视图中的数据，从而增强敏感信息安全性。步骤包括：1) 创建主加密密钥 (MEk)；2) 创建加密视图，指定要加密的视图和 MEk；3) 授权用户访问加密视图。加密视图工作原理：当用户查询加密视图时，Oracle 使用 MEk 解密数据，确保只有授权用户可以访问可读数据。
Oracle安装失败如何卸载
Apr 11, 2025 pm 08:24 PM
Oracle 安装失败的卸载方法：关闭 Oracle 服务，删除 Oracle 程序文件和注册表项，卸载 Oracle 环境变量，重新启动计算机。若卸载失败，可使用 Oracle 通用卸载工具手动卸载。
oracle如何查看实例名
Apr 11, 2025 pm 08:18 PM
在 Oracle 中查看实例名的方法有三种：命令行中使用 "sqlplus" 和 "select instance_name from v$instance;" 命令。在 SQL*Plus 中使用 "show instance_name;" 命令。通过操作系统的任务管理器、Oracle Enterprise Manager 或检查环境变量 (Linux 上的 ORACLE_SID)。
oracle awr报告怎么看
Apr 11, 2025 pm 09:45 PM
AWR 报告是显示数据库性能和活动快照的报告，解读步骤包括：识别活动快照的日期和时间。查看活动、资源消耗的概览。分析会话活动，找出会话类型、资源消耗和等待事件。查找潜在性能瓶颈，如缓慢的 SQL 语句、资源争用和 I/O 问题。查看等待事件，识别并解决它们以提高性能。分析闩锁和内存使用模式，以识别导致性能问题的内存问题。
oracle动态sql怎么创建
Apr 12, 2025 am 06:06 AM
可以通过使用 Oracle 的动态 SQL 来根据运行时输入创建和执行 SQL 语句。步骤包括：准备一个空字符串变量来存储动态生成的 SQL 语句。使用 EXECUTE IMMEDIATE 或 PREPARE 语句编译和执行动态 SQL 语句。使用 bind 变量传递用户输入或其他动态值给动态 SQL。使用 EXECUTE IMMEDIATE 或 EXECUTE 执行动态 SQL 语句。
oracle怎么使用触发器
Apr 11, 2025 pm 11:57 PM
Oracle 中的触发器是用于在特定事件(插入、更新或删除)触发后自动执行操作的存储过程。它们用于各种场景，包括数据验证、审核和数据维护。创建触发器时，需要指定触发器名称、关联表、触发事件和触发时间。有两种类型的触发器：BEFORE 触发器在操作之前触发，而 AFTER 触发器在操作之后触发。例如，BEFORE INSERT 触发器可确保插入行的年龄列不为负。
oracle表名怎么改
Apr 11, 2025 pm 11:54 PM
重命名 Oracle 表名的两种方法：使用 SQL 语句：ALTER TABLE <旧表名> RENAME TO <新表名>;使用 PL/SQL 语句：EXECUTE IMMEDIATE 'ALTER TABLE ' || :old_table_name || ' RENAME TO ' || :new_table_name;
