Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Common query statements in oracle

2025-04-05 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

To kill a conversation, you must memorize it all the time:

Select 'alter system kill session''| | sid | |','| | serial# | |''immediate;' from gv$session where username =' username' and status = 'ACTIVE'

Kill the specified wait event:

Select 'alter system kill session''| | sid | |','| | serial# | |''immediate;' from v$session where event =' latch: row cache objects'

Select 'alter system kill session''| | sid | |','| | serial# | |''immediate;' from gv$session where event =' latch: row cache objects' and inst_id = 2

Check and kill the specified sql:

Select 'alter system kill session''| | sid | |','| | serial# | |''immediate;' from gv$session where sql_id =' dnwqt3wq75993'

Alter system kill session '568, 12353' immediate

View the tablespace:

SELECT total.tablespace_name

Round (total.MB, 2) AS Total_MB

Round (total.MB-free.MB, 2) AS Used_MB

Round ((1-free.MB / total.MB) * 100,2)

| |'%'AS Used_Pct |

FROM (SELECT tablespace_name

Sum (bytes) / 1024 / 1024 AS MB

FROM dba_free_space

GROUP BY tablespace_name) free

(SELECT tablespace_name

Sum (bytes) / 1024 / 1024 AS MB

FROM dba_data_files

GROUP BY tablespace_name) total

WHERE free.tablespace_name = total.tablespace_name

3. Collect statistics:

BEGIN

DBMS_STATS.GATHER_TABLE_STATS (OWNNAME = > 'username', TABNAME = >' TRUE, DEGREE = > 128, CASCADE = > TRUE, FORCE = > TRUE)

END

5.dataguard class:

Alter database recover managed standby database using current logfile disconnect

Alter database recover managed standby database disconnect from session

Alter database recover managed standby database cancel

Configuration:

Alter system set LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST valid_for= (all_logfiles,all_roles) db_unique_name=pri'

Alter system set LOG_ARCHIVE_DEST_2='SERVICE=std LGWR ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) compression=enable DB_UNIQUE_NAME=std'

Alter system set log_archive_config='dg_config= (pri,std)'

Prepare the database to view logs that are not applied:

Select l.SEQUENCELING where l.NAME is not null and applied l.THREADONAME from gv$archived_log l.APPLIED SEQUENCEE where l.NAME is not null and applied = 'NO' order by l.SEQUENCE# desc

Select from v$archive_gap

Select from v$managed_standby where process = 'MRP0'

Select * from v$dataguard_stats

Orapwd file=orapsid password=password#123 entries=10 force=y ignorecase=y

Rman target sys/password@primarydb auxiliary sys/password@standbydb

Run {

Allocate channel ch2 type disk

Allocate channel ch3 type disk

Allocate channel ch4 type disk

Allocate channel ch5 type disk

Allocate auxiliary channel ch6 type disk

Allocate auxiliary channel ch7 type disk

Allocate auxiliary channel ch7 type disk

Allocate auxiliary channel ch8 type disk

Duplicate target database for standby from active database

Release channel ch2

Release channel ch3

Release channel ch4

Release channel ch5

Release channel ch6

Release channel ch7

Release channel ch7

Release channel ch8

}

6. View the lob size:

Select s.TABLENAME where d.segment_name d.segmentationname where d.segment_name. TABLESPACENAME from user_segments (TABLESPACENAME d.BYTES/1024/1024/1024)

Group by d.segment_name,s.TABLE_NAME,s.TABLESPACE_NAME order by 4 desc

7. Look for spid according to sid, and perform kill-9 spid at the operating system level:

Select a.spidrec b.sidrecoveryb.serialauthorauthorb.username from v$process arewlegsession b where a.addr=b.paddr and b.statuswriting KILLED`

Select b.spidrecovera.osuserjour b.program from v$session a where a.paddr=b.addr and a.sid=4901 vested process b

8.select * from nls_database_parameters

Export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

9. It is constantly updated in the future.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report