Common Oracle and SDE maintenance commands-view tablespaces

Common Oracle and SDE maintenance commands-view tablespaces

Previously, I reported a data update problem on the site. I thought it was caused by the full table space (the error was written in the previous blog article). Therefore, I remotely checked the server. Generally, I use the Entreprise Manager Console of the Oracle client to view the server. However, I found that only the Oracle server is installed on the server and cannot log on to the server.

Therefore, I checked the query statement on the Internet to view the Oracle tablespace usage, and used PLSQL to query and view the table space usage. Here I will record it, and I will also attach several common Oracle and SDE commands.

View table space usage (solve this problem)
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;

To solve this problem, you can change the tablespace to auto-increment (the auto-increment is larger, which is set by storing local files)

Alter database datafile 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ BUSINESS. dbf'autoextend on next 100 M MAXSIZE UNLIMITED

The preceding statement sets the automatic growth of the file D: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ BUSINESS. dbf' to 100 MB.

Appendix:

Oracle maintains common SQL statements (tested in PLSQL)
1. view the table space name and size.
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 name and size of the tablespace physical file.
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. view log files
Select member from v $ logfile;
6. view database objects
Select owner, object_type, status, count (*) count # from all_objects group by owner, object_type, status;
7. view the database version
Select version FROM Product_component_version
Where SUBSTR (PRODUCT, 1, 6) = 'oracle ';
8. view the database creation date and archiving method
Select Created, Log_Mode, Log_Mode From V $ Database;
9. How to remotely determine the Oracle Database Installation Platform
Select * from v $ version;
10. view the parameter information of the 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 uncommitted transactions
Select * from v $ locked_object;
Select * from v $ transaction;
12. View rollback segments
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. Capture SQL statements that have been running for a long time
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 the parameter information of the 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 the object is used
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, s. 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. resource-consuming processes (top sessions)
Select s. schemaname schema_name, decode (sign (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,
S. 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 ('38') 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 status
Select/* + RULE */ls. osuser OS _user_name, ls. username user_name, decode (ls. type, 'rw ', 'row wait enqueue lock', 'Tm', 'dml enqueue lock', 'tx ', 'transaction enqueue lock', 'U ', 'User supplied lock') lock_type, o. object_name object, decode (ls. lmode, 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. Check the link of the Computer Based on the sid.
Select osuser, machine, username, sid, serial # from v $ session where sid = '20140901 ';
19. query the corresponding SQL statement based on the sid
Select SID, SQL _TEXT from v $ open_cursor where SID = '000000 ';
20. view the waiting status
SELECT v $ waitstat. class, v $ waitstat. 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 sga status
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_cils, 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. Information about connection
1) check which user connections are available
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, s. 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) view the resource usage of the corresponding connection according to v. sid.
Select n. name, v. value, n. class, n. statistic #
From v $ statname n, v $ sesstat v
Where v. sid = 71 and v. statistic # = n. statistic #
Order by n. class, n. statistic #
3) view the SQL statement that the connection is running based on 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_cils, 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. query the degree of table space fragmentation

Select a. tablespace_name,
Trunc (sqrt (max (blocks)/sum (blocks) x (100/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 database instances are running
Select inst_name from v $ active_instances;
28. Obtain the first 10 most resource-consuming SQL statements of oracle
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;

Common sde SQL statements (run directly through CMD)

1. view the sde connection status and quickly view several connections (you do not need to enter SQL * plus)

Sdemon-o status

2, delete a user of the current connection, 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. Check the number and name of sde instances.

Sdemon-o info-I instances

5. view the table occupied by the sde instance. The id behind the table is table_registry In the sde library.

Sdemon-o info-I locks

6. query information about the operating system type, operating system environment variables, system drive letters, and sde installation path.

Sdemon-o info-I vars

7. Enable, stop, and suspend the SDE Service

Sdemon-o start-p sde

Sdemon-o shutdown-p sde

Sdemon-o pause-p sde