Oracle and SDE maintain common commands-view table spaces, etc.

Before the site feedback a data update problem, to see the feeling is because the table space is full (the error was written in the previous blog essay), so remote to the server to view. The individual is usually viewed through the entreprise Manager console of the Oracle client, but discovers that only the Oracle server is installed on the servers and does not perform the Web login properly.

So I went to the Internet to check the query statements for Oracle tablespace usage, to query through Plsql, to record here, plus several common Oracle and SDE commands.

View table Space Usage (resolve this issue)
Select sum (bytes)/(1024*1024) as Free_space,tablespace_name
From Dba_free_space
Group BY Tablespace_name;
SELECT a.tablespace_name,a.bytes total,b.bytes used, c.bytes free,
(b.bytes*100)/a.bytes "% used", (c.bytes*100)/a.bytes "% free"
From SYS. Sm$ts_avail A,sys. Sm$ts_used B,sys. Sm$ts_free C
WHERE A.tablespace_name=b.tablespace_name and A.tablespace_name=c.tablespace_name;

Solve this problem by modifying the table space for self-growth (a bit larger from the growth, set by local storage file)

ALTER DATABASE datafile ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\BUSINESS. DBF ' autoextend on NEXT 100M MAXSIZE UNLIMITED

The above statement is put ' D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\BUSINESS. DBF ' This file has a self-growth setting of 100M

Report:

Oracle maintains common SQL (tested in plsql)
1. View the name and size of the table space
Select T.tablespace_name, round (SUM (bytes/(1024*1024)), 0) ts_size
From Dba_tablespaces T, Dba_data_files D
where t.tablespace_name = D.tablespace_name
Group BY T.tablespace_name;
2. View the table space physical file name and size
Select Tablespace_name, file_id, File_name,round (bytes/(1024*1024), 0) Total_space
From Dba_data_files
Order BY Tablespace_name;
3. Check the rollback segment name and size
Select Segment_name, Tablespace_name, R.status,
(initial_extent/1024) Initialextent, (next_extent/1024) nextextent,
Max_extents, V.curext curextent
From Dba_rollback_segs R, V$rollstat V
Where r.segment_id = V.USN (+)
Order BY Segment_name;
4. View Control files
Select name from V$controlfile;
5. Viewing log files
Select member from V$logfile;
6. View Database Library objects
Select owner, object_type, Status, COUNT (*) count# from All_objects Group by owner, object_type, status;
7. View the version of the database
Select version from Product_component_version
Where SUBSTR (product,1,6) = ' Oracle ';
8. View the date the database was created and how it was archived
Select Created, Log_mode, Log_mode from V$database;
9, how to remotely judge the installation platform of Oracle database
SELECT * from V$version;
10. View parameter information of data table
SELECT Partition_name, High_value, High_value_length, Tablespace_name,
Pct_free, pct_used, Ini_trans, Max_trans, Initial_extent,
Next_extent, Min_extent, Max_extent, Pct_increase, Freelists,
Freelist_groups, LOGGING, Buffer_pool, num_rows, blocks,
Empty_blocks, Avg_space, chain_cnt, Avg_row_len, Sample_size,
Last_analyzed
From Dba_tab_partitions
--where table_name =: tname and Table_owner =: Towner
ORDER by Partition_position
11. View transactions that have not yet been submitted
SELECT * from V$locked_object;
SELECT * from V$transaction;
12. Rollback Segment View
Select RowNum, sys.dba_rollback_segs.segment_name name, v$rollstat.extents
Extents, V$rollstat.rssize size_in_bytes, V$rollstat.xacts xacts,
V$rollstat.gets gets, V$rollstat.waits waits, V$rollstat.writes writes,
Sys.dba_rollback_segs.status status from V$rollstat, Sys.dba_rollback_segs,
V$rollname where v$rollname.name (+) = Sys.dba_rollback_segs.segment_name and
V$ROLLSTAT.USN (+) = V$rollname.usn ORDER by rownum
13, capturing long-running SQL
Select Username,sid,opname,
Round (sofar*100/totalwork,0) | | '% ' as progress, time_remaining,sql_text
From V$session_longops, V$sql
where sql_address = Address
and Sql_hash_value = Hash_value
14. View parameter information of data table
SELECT Partition_name, High_value, High_value_length, Tablespace_name,
Pct_free, pct_used, Ini_trans, Max_trans, Initial_extent,
Next_extent, Min_extent, Max_extent, Pct_increase, Freelists,
Freelist_groups, LOGGING, Buffer_pool, num_rows, blocks,
Empty_blocks, Avg_space, chain_cnt, Avg_row_len, Sample_size,
Last_analyzed
From Dba_tab_partitions
--where table_name =: tname and Table_owner =: Towner
ORDER by Partition_position
15. Find out which processes are used for object
Select p.spid,s.sid,s.serial# serial_num,s.username user_name,a.type object_type,s.osuser Os_user_name,a.owner,
A.object Object_name,decode (sign (48-command), 1,to_char (command), ' Action Code # ' | | to_char (command)) action,
P.program oracle_process,s.terminal Terminal,s.program program,s.status session_status
From V$session S, v$access A, v$process p
where s.paddr = p.addr and S.type = ' USER ' and a.sid = S.sid and a.object= ' subscriber_attr '
Order by S.username, S.osuser
16. The process of consuming resources (top session)
Select S.schemaname schema_name, decode (48-command), 1,
To_char (command), ' Action Code # ' | | To_char (command)) action, status
Session_status, S.osuser os_user_name, S.sid, P.spid, s.serial# Serial_num,
NVL (S.username, ' [Oracle process] ') user_name, s.terminal terminal,
S.program program, St.value Criteria_value from V$sesstat St, v$session S, v$process p
where St.sid = S.sid and st.statistic# = to_number (' + ') and (' All ' = ' all '
or s.status = ' all ') and p.addr = s.paddr ORDER BY st.value Desc, p.spid ASC, s.username ASC, S.osuser ASC
17. View Lock Condition
Select/*+ RULE */Ls.osuser Os_user_name, Ls.username user_name,decode (ls.type, ' RW ', ' Row wait enqueue lock ', ' TM ', ' DM ' L enqueue lock ', ' TX ', ' Transaction enqueue lock ', ' UL ', ' User supplied lock ') Lock_type,o.object_name object, decode (LS.L mode, 1, NULL, 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' Share ', 5, ' Share Row Exclusive ', 6, ' Exclusive ', null)
Lock_mode, O.owner, Ls.sid, ls.serial# serial_num, LS.ID1, Ls.id2
From Sys.dba_objects o, (select S.osuser, S.username, L.type, L.lmode, S.sid, s.serial#, L.id1, L.id2 from V$session s,
V$lock l where s.sid = l.sid) ls where o.object_id = ls.id1

18, according to SID Check which computer is the link
Select osuser,machine,username,sid,serial# from v$session where sid= ' 128 ';
19, according to SID check the corresponding SQL
Select Sid,sql_text from V$open_cursor where sid= ' 128 ';
20. Look for wait (wait) conditions
SELECT V$waitstat.class, V$waitstat.count count, SUM (v$sysstat.value) sum_value
From V$waitstat, V$sysstat WHERE v$sysstat.name in (' db block gets ',
' Consistent gets ') group by V$waitstat.class, V$waitstat.count
21. View the SGA situation
SELECT NAME, BYTES from SYS. V_$sgastat ORDER by NAME ASC
22. View Catched Object
SELECT owner, name, Db_link, namespace, type, SHARABLE_MEM, loads, executions, locks, pins, kept from V$db_object_cache
23. View V$sqlarea
SELECT Sql_text, Sharable_mem, Persistent_mem, Runtime_mem, sorts,
Version_count, Loaded_versions, open_versions, users_opening, executions,
Users_executing, LOADS, First_load_time, Invalidations, Parse_calls, Disk_reads,
Buffer_gets, rows_processed from V$sqlarea
24. View the number of object categories
Select Object_type,count (*) from all_objects GROUP by object_type;

25. Related Information about connection
1) See which users are connected
Select S.osuser os_user_name, decode (sign (48-command), 1, to_char (command),
' Action Code # ' | | To_char (command)) action, P.program oracle_process,
Status Session_status, S.terminal terminal, S.program program,
S.username user_name, s.fixed_table_sequence activity_meter, ' query,
0 memory, 0 max_memory, 0 cpu_usage, S.sid, s.serial# serial_num
From V$session S, v$process p where s.paddr=p.addr and s.type = ' USER '
Order by S.username, S.osuser
2) According to the V.SID to view the resource usage of the corresponding connection
Select n.name,v.value,n.class,n.statistic#
From V$statname N,v$sesstat V
where v.sid = v.statistic# = n.statistic#
Order by N.class, n.statistic#
3) View the SQL that the corresponding connection is running according to the SID
Select Command_type,sql_text,sharable_mem,persistent_mem,runtime_mem,sorts,version_count,loaded_versions,open_ Versions,users_opening,
Executions,users_executing,loads,first_load_time,invalidations,parse_calls,disk_reads,buffer_gets,rows_ Processed,sysdate Start_time,sysdate Finish_time,
Address sql_address, ' N ' status
From V$sqlarea
where address = (select sql_address from v$session where sid = 71)
26. The degree of fragmentation of the query table space

Select A.tablespace_name,
Trunc (sqrt (max (blocks)/sum (blocks)) * (100/sqrt (sqrt (count (blocks))), 2) FSFI
From Dba_free_space a,dba_tablespaces b
where A.tablespace_name=b.tablespace_name
and b.contents not in (' temporary ', ' UNDO ', ' Sysaux ')
GROUP BY A.tablespace_name
Order BY FSFI;

27. Query which DB instances are running
Select Inst_name from V$active_instances;
28. Get Oracle's Top 10 most resource-consuming SQL statements
SELECT * FROM
(SELECT parsing_user_id
Executions,
Sorts,
Command_type,
Disk_reads,
Sql_text
From V$sqlarea
ORDER by Disk_reads DESC)
WHERE rownum<11;

SDE Common SQL (run directly through CMD)

1, check the SDE connection status, Quick View there are several connections (do not enter Sql*plus)

Sdemon-o status

2, delete the current connection of a user, need to provide s-id, and SDE password

Sdemon-o kill-t 1731-p SDE

3. Disconnect all Users

Sdemon-o kill-t all-s 127.0.0.1-i 5151-p SDE

4. See the number and name of the SDE instance

Sdemon-o Info-i Instances

5. View the table used by the SDE instance, the ID behind the table, and the table_registry in the SDE library

Sdemon-o Info-i Locks

6, query the operating system type, operating system environment variables 、??? Holistic characters, SDE installation path, etc.

Sdemon-o info-i VARs

7. Open, stop, and pause the SDE service

Sdemon-o start-p SDE

Sdemon-o shutdown-p SDE

Sdemon-o pause-p SDE
Reproduced in:? http://www.itxuexiwang.com/a/shujukujishu/2016/0302/200.html?1457018475

Oracle and SDE maintain common commands-view table spaces, etc.