Display Avg and Max Connections of All Nodes since Instance Startup
=======================
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------- ------------------- --------------- ------------------ -----------
processes 335 377 20000 20000
sessions 382 406 30048 30048
display all status of all sessions
=======================
INST_ID STATUS USERNAME COUNT(*)
---------- -------- ----------- ----------
1 ACTIVE FINDPT 32
1 ACTIVE SYS 1
1 ACTIVE FINDPTLOCAL 1
1 INACTIVE FINDPT 68
1 INACTIVE FINDPTLOCAL 32
1 INACTIVE TEST 3
1 INACTIVE ZHANGLIANG 2
1 INACTIVE SYS 1
1 INACTIVE HR 1
INST_ID SERVICE_NAME COUNT(*)
---------- ------------------------------ ----------
1 SYS$USERS 132
1 test 9
display the active sessions and sql in current node
=======================
SID_AND_SERIAL# USERNAME TERMINAL MACHINE PROGRAM SPID SQL_ID SQL_TEXT
--------------- ----------- --------------- ------------------------- ---------------------------------- ------ ------------- ------------------------------
7526,37131 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77782 8wv8vf3fku4c2 insert all
10336,42907 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 74857 8wv8vf3fku4c2 insert all
15967,7387 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77841 8wv8vf3fku4c2 insert all
18789,30357 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77754 8wv8vf3fku4c2 insert all
18795,1361 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77780 8wv8vf3fku4c2 insert all
25362,7264 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 71626 8wv8vf3fku4c2 insert all
10335,26431 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77829 8wv8vf3fku4c2 insert all
10345,27408 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77821 8wv8vf3fku4c2 insert all
21601,50555 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 71618 8wv8vf3fku4c2 insert all
27237,2980 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 71602 8wv8vf3fku4c2 insert all
4708,38880 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77817 8wv8vf3fku4c2 insert all
11274,29602 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77837 8wv8vf3fku4c2 insert all
13153,30234 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77835 8wv8vf3fku4c2 insert all
13164,837 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 74826 8wv8vf3fku4c2 insert all
15970,57387 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 74834 8wv8vf3fku4c2 insert all
16913,30939 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77764 8wv8vf3fku4c2 insert all
23490,39252 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77831 8wv8vf3fku4c2 insert all
SID_AND_SERIAL# USERNAME TERMINAL MACHINE PROGRAM SPID SQL_ID SQL_TEXT
--------------- ----------- --------------- ------------------------- ---------------------------------- ------ ------------- ------------------------------
27243,18703 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77799 8wv8vf3fku4c2 insert all
8,15517 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77803 8wv8vf3fku4c2 insert all
8459,13504 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77825 8wv8vf3fku4c2 insert all
17847,35009 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77946 8wv8vf3fku4c2 insert all
29115,17856 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77775 8wv8vf3fku4c2 insert all
8455,11711 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 74747 8wv8vf3fku4c2 insert all
12214,55522 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77944 8wv8vf3fku4c2 insert all
18792,28656 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77823 8wv8vf3fku4c2 insert all
20665,38941 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77948 8wv8vf3fku4c2 insert all
23482,15745 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77819 8wv8vf3fku4c2 insert all
5638,32364 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 74741 8wv8vf3fku4c2 insert all
7523,25898 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77797 8wv8vf3fku4c2 insert all
18790,40336 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77827 8wv8vf3fku4c2 insert all
26306,34094 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 74715 8wv8vf3fku4c2 insert all
6588,4385 FINDPT unknown hs-10-20-30-193 JDBC Thin Client 77833 8wv8vf3fku4c2 insert all
display the active sessions and events in current node
=======================
SQL_ID SPID EVENT P1 P2 P3 SECONDS LOGON_TIME KILL_SQL
------------- ------ -------------------------- ---------------------------------- ------- ---------- ------------------------------------------------------------
8wv8vf3fku4c2 77780 latch: undo global data 1611079360,470,8583 5 0720 17:18 alter system disconnect session '18795,1361' immediate;
8wv8vf3fku4c2 74834 latch: undo global data 1611079360,470,8666 5 0720 17:17 alter system disconnect session '15970,57387' immediate;
8wv8vf3fku4c2 77803 latch: undo global data 1611079360,470,8431 2 0720 17:18 alter system disconnect session '8,15517' immediate;
8wv8vf3fku4c2 74747 latch: undo global data 1611079360,470,8529 2 0720 17:17 alter system disconnect session '8455,11711' immediate;
8wv8vf3fku4c2 77835 latch: undo global data 1611079360,470,8420 2 0720 17:18 alter system disconnect session '13153,30234' immediate;
8wv8vf3fku4c2 77831 latch: undo global data 1611079360,470,8782 2 0720 17:18 alter system disconnect session '23490,39252' immediate;
8wv8vf3fku4c2 77799 latch: undo global data 1611079360,470,8792 2 0720 17:18 alter system disconnect session '27243,18703' immediate;
8wv8vf3fku4c2 77821 latch: undo global data 1611079360,470,8507 2 0720 17:18 alter system disconnect session '10345,27408' immediate;
8wv8vf3fku4c2 71602 latch: undo global data 1611079360,470,8662 2 0720 17:16 alter system disconnect session '27237,2980' immediate;
8wv8vf3fku4c2 77754 latch: undo global data 1611079360,470,8647 1 0720 17:18 alter system disconnect session '18789,30357' immediate;
8wv8vf3fku4c2 77827 latch: undo global data 1611079360,470,8534 1 0720 17:18 alter system disconnect session '18790,40336' immediate;
8wv8vf3fku4c2 77764 latch: undo global data 1611079360,470,8372 1 0720 17:18 alter system disconnect session '16913,30939' immediate;
8wv8vf3fku4c2 77775 latch: undo global data 1611079360,470,8787 1 0720 17:18 alter system disconnect session '29115,17856' immediate;
8wv8vf3fku4c2 77837 latch: undo global data 1611079360,470,8487 1 0720 17:18 alter system disconnect session '11274,29602' immediate;
8wv8vf3fku4c2 74857 latch: undo global data 1611079360,470,8616 1 0720 17:17 alter system disconnect session '10336,42907' immediate;
8wv8vf3fku4c2 77829 latch: undo global data 1611079360,470,8389 1 0720 17:18 alter system disconnect session '10335,26431' immediate;
8wv8vf3fku4c2 77782 latch: undo global data 1611079360,470,8318 1 0720 17:18 alter system disconnect session '7526,37131' immediate;
SQL_ID SPID EVENT P1 P2 P3 SECONDS LOGON_TIME KILL_SQL
------------- ------ -------------------------- ---------------------------------- ------- ---------- ------------------------------------------------------------
8wv8vf3fku4c2 77797 latch: undo global data 1611079360,470,8431 1 0720 17:18 alter system disconnect session '7523,25898' immediate;
8wv8vf3fku4c2 77833 latch: undo global data 1611079360,470,8483 1 0720 17:18 alter system disconnect session '6588,4385' immediate;
8wv8vf3fku4c2 74741 latch: undo global data 1611079360,470,8435 1 0720 17:17 alter system disconnect session '5638,32364' immediate;
8wv8vf3fku4c2 77841 latch: undo global data 1611079360,470,8627 1 0720 17:18 alter system disconnect session '15967,7387' immediate;
8wv8vf3fku4c2 71626 latch: undo global data 1611079360,470,8641 1 0720 17:16 alter system disconnect session '25362,7264' immediate;
8wv8vf3fku4c2 77819 latch: undo global data 1611079360,470,8645 1 0720 17:18 alter system disconnect session '23482,15745' immediate;
8wv8vf3fku4c2 71618 latch: undo global data 1611079360,470,8716 1 0720 17:16 alter system disconnect session '21601,50555' immediate;
8wv8vf3fku4c2 77948 latch: undo global data 1611079360,470,8537 1 0720 17:18 alter system disconnect session '20665,38941' immediate;
8wv8vf3fku4c2 77944 latch: undo global data 1611079360,470,0 0 0720 17:18 alter system disconnect session '12214,55522' immediate;
8wv8vf3fku4c2 77825 latch: undo global data 1611079360,470,8359 0 0720 17:18 alter system disconnect session '8459,13504' immediate;
8wv8vf3fku4c2 74715 latch: undo global data 1611079360,470,2147483647 0 0720 17:17 alter system disconnect session '26306,34094' immediate;
8wv8vf3fku4c2 77817 latch: undo global data 1611079360,470,8396 0 0720 17:18 alter system disconnect session '4708,38880' immediate;
8wv8vf3fku4c2 74826 latch: undo global data 1611079360,470,8673 0 0720 17:17 alter system disconnect session '13164,837' immediate;
8wv8vf3fku4c2 77823 latch: undo global data 1611079360,470,8607 0 0720 17:18 alter system disconnect session '18792,28656' immediate;
display the session status that not active in current node
=======================
空间
FCNT Means DATAFILE_CNT
Used% Means PCT_USED%
AUTO? Means AUTOEXTEND
MANAGE? Means EXTENT_MANAGEMENT
UNDO%*REAL_USED Means The Count undo segments of UNEXPIRED and ACTIVE
UNDO Segment waiting for
SMON to be idle and automatically shrink
Tablespace Used Information
======================
-------------------------------------------------------------------------------------------------------------------------------------------------
| TABLESPACE_NAME | Used% | Used | AUTO? | TOTAL_GB | USED_GB | FREE_GB | FCNT | STATUS | CONTENTS | MANAGE? | MAXSIZE(MB) |
-------------------------------------------------------------------------------------------------------------------------------------------------
| UNDOTBS1*REAL_USED | 99.8% | #################### | YES | 154 | 153.7 | .3 | 5 | ONLINE | UNDO | LOCAL | 157694|
| USERS | 62.9% | ############# | YES | 190 | 161 | 29 | 8 | ONLINE | PERMANENT | LOCAL | 262136|
| SYSTEM | 27.7% | ###### | YES | 16.6 | 8.9 | 7.8 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
| SYSAUX | 4.3% | # | YES | 137.9 | 5.9 | 132 | 5 | ONLINE | PERMANENT | LOCAL | 141308|
| TEST | 2.5% | # | YES | 1 | .8 | .2 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
| ODC_TPS | 0% | # | YES | .1 | 0 | .1 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
| RISKDATA_DATA | 0% | # | YES | 1 | 0 | 1 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
| SRC_DATA | 0% | # | YES | 1 | 0 | 1 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
| TEMP | 0% | # | YES | 31.2 | 0 | 31.2 | 2 | ONLINE | TEMPORARY | LOCAL | 64511|
| LTDTS_ORA | 0% | # | YES | 1 | 0 | 1 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
| TBS | 0% | # | YES | 1 | 0 | 1 | 1 | ONLINE | PERMANENT | LOCAL | 30720|
| XSTREAM_ADM_TBS | 0% | # | YES | 0 | 0 | 0 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
| ES_TA_DB_01 | .4% | # | YES | .2 | .1 | .1 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
| ES_TA_DB_02 | .3% | # | YES | .2 | .1 | .1 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
| ES_TA_DB_03 | .3% | # | YES | .2 | .1 | .1 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
| ES_TA_DB_04 | .3% | # | YES | .4 | .1 | .3 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
| ZHOU | .2% | # | YES | 1 | .1 | .9 | 1 | ONLINE | PERMANENT | LOCAL | 32767|
-------------------------------------------------------------------------------------------------------------------------------------------------
There is No RECOVERY_DEST in Use
======================
Non sys user objects stored in SYS tablespace
======================
------------------------------------------------
| OWNER | CNT | SIZE_MB |
------------------------------------------------
| HR | 89 | 10 |
| XJH | 24 | 2 |
| XSTRMADMIN | 8 | 1 |
| TEST | 1 | 0 |
------------------------------------------------
Top 20 Big Table Information in The Database
======================
----------------------------------------------------------------------------------------------------
| OWNER | SEGMENT_NAME | SIZE(MB) | PARTITION_NAME |
----------------------------------------------------------------------------------------------------
| FINDPT | ACCOUNT | 38640 | None |
| BENCHMARKSQL | BMSQL_STOCK | 34017 | None |
| BENCHMARKSQL | BMSQL_CUSTOMER | 18898 | None |
| SYSTEM | BIG_TABLE_FATHER | 5332 | None |
| FINDPT | CUSTOMER | 4928 | None |
| TEST | TCUSTINFO | 4652 | None |
| TA6_PUB11 | TBFUND_C5NAVTMP3 | 1475 | None |
| TEST | TEST_MODIFY_1000000000_TB1 | 778 | None |
| SYSTEM | BMSQL_STOCK | 680 | None |
| BENCHMARKSQL | BMSQL_ORDER_LINE.SYS_P18374 | 664 | SYS_P18374 |
| BENCHMARKSQL | BMSQL_ORDER_LINE.SYS_P18431 | 536 | SYS_P18431 |
| BENCHMARKSQL | BMSQL_ORDER_LINE.SYS_P18424 | 528 | SYS_P18424 |
| BENCHMARKSQL | BMSQL_ORDER_LINE.SYS_P18393 | 528 | SYS_P18393 |
| BENCHMARKSQL | BMSQL_ORDER_LINE.SYS_P18423 | 488 | SYS_P18423 |
| BENCHMARKSQL | BMSQL_ORDER_LINE.SYS_P18399 | 488 | SYS_P18399 |
| BENCHMARKSQL | BMSQL_ORDER_LINE.SYS_P18392 | 488 | SYS_P18392 |
| BENCHMARKSQL | BMSQL_ORDER_LINE.SYS_P18387 | 447 | SYS_P18387 |
| BENCHMARKSQL | BMSQL_ORDER_LINE.SYS_P18379 | 440 | SYS_P18379 |
| BENCHMARKSQL | BMSQL_ORDER_LINE.SYS_P18386 | 424 | SYS_P18386 |
| BENCHMARKSQL | BMSQL_ORDER_LINE.SYS_P18384 | 417 | SYS_P18384 |
----------------------------------------------------------------------------------------------------
Top 20 Big LOB Information in The Database (When Migrating Database,<purge dba_recyclebin;> can purge unnecessary LOB)
======================
-------------------------------------------------------------------------------------------------------------------
| OWNER | TABLE_NAME | COLUMN_NAME | SEGMENT_NAME | SIZE(MB) |
-------------------------------------------------------------------------------------------------------------------
| SYS | WRH$_SQL_PLAN | OTHER_XML | SYS_LOB0000011147C00038$$ | 224|
| SYS | OPATCH_XINV_TAB | XML_INVENTORY | SYS_LOB0000010461C00001$$ | 168|
| WYF | LARGE_CLOB | A | SYS_LOB0000174836C00001$$ | 104|
| SYS | REGISTRY$SQLPATCH_RU_INFO | PATCH_DIRECTORY | SYS_LOB0000022516C00008$$ | 104|
| SYS | SYS_IMPORT_FULL_01 | XML_CLOB | SYS_LOB0000080671C00111$$ | 72|
| SYS | WRI$_ADV_SQLT_PLANS | OTHER_XML | SYS_LOB0000008766C00040$$ | 56|
| SYS | WRH$_SQLTEXT | SQL_TEXT | SYS_LOB0000011139C00004$$ | 40|
| SYS | WRI$_EMX_FILES | DATA | SYS_LOB0000007422C00004$$ | 24|
| SYS | AW$AWXML | AWLOB | SYS_LOB0000066421C00004$$ | 24|
| SYS | WRI$_EMX_FILES | BINARY_DATA | SYS_LOB0000007422C00005$$ | 24|
-------------------------------------------------------------------------------------------------------------------
HWM Information Rely on Table Statistics
======================
--------------------------------------------------------------------------------------------------------
| OWNER | TABLE_NAME | SEGMENT_TYPE | SEGMENT_MB | WASTER% |
--------------------------------------------------------------------------------------------------------
| SYSTEM | BIG_TABLE_FATHER | TABLE | 5332 | 100% |
| TEST | TEST_MODIFY_1000000000_TB1 | TABLE | 778 | 100% |
| SYSTEM | LOGMNR_RESTART_CKPT$ | TABLE | 57 | 99.85% |
| SYSTEM | LOGMNR_LOG$ | TABLE | 2 | 90.2% |
--------------------------------------------------------------------------------------------------------
Show DataPump Directories Information
expdp \"/ as sysdba\" DIRECTORY=EXPDP_DATA DUMPFILE=scott.DMP logfile=scott.log schemas=scott
also see ./ora dir
======================
--------------------------------------------------------------------------------------------------------------
| OWNER | DIRECTORY_NAME | DIRECTORY_PATH |
--------------------------------------------------------------------------------------------------------------
| SYS | EXPDP_DATA | /data/oracle/dumpdata |
| SYS | ORACLE_BASE | /oracle/app |
| SYS | ORACLE_HOME | /oracle/app/product/19.3.0/db_1 |
| SYS | DATA_PUMP_DIR | /oracle/app/admin/test/dpdump/ |
--------------------------------------------------------------------------------------------------------------
最后修改时间：2023-11-14 09:45:54
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
