| |
Before a live feedback data update in question to see the feeling is because the table space is full due to (erroneous written essay in the previous blog), so the remote server for viewing. Individuals usually through Oracle client Entreprise Manager Console view, but found only the installation of Oracle server on the server and can not normally log in to view the page.
So checked online to view the Oracle table space usage query, query view through PLSQL, where the record about the other attached to a few common Oracle and SDE command.
See table space usage (use to 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 by modifying the table space from growth to (since the growth rate to be bigger, set by the 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 the 'D: ORACLE PRODUCT 10.2.0 ORADATA ORCL BUSINESS.DBF' growth since the file is set to 100M
Attachment:
Maintaining a common Oracle SQL (through PLSQL test)
1, 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, 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, view the name and size of the rollback segment
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. Check the control file
select name from v $ controlfile;
5, view the log file
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, see the version of the database
Select version FROM Product_component_version
Where SUBSTR (PRODUCT, 1,6) = 'Oracle';
8, creation date, and view the archive database
Select Created, Log_Mode, Log_Mode From V $ Database;
9, the mounting platform to judge how remote Oracle database
select * from v $ version;
10, the view parameter information 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 the transaction has not submitted
select * from v $ locked_object;
select * from v $ transaction;
12, rollback 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, to capture the long run 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, the view parameter information 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 the object for which the process 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 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, resource-intensive process (top session)
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 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 ('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. Check the lock (lock) case
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 ',' UL ',' 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, according to sid check which computer is the link
select osuser, machine, username, sid, serial # from v $ session where sid = '128';
19, according to the investigation corresponding sql sid
select SID, SQL_TEXT from v $ open_cursor where SID = '128';
20. See wait (wait) case
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 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 See object classification number
select OBJECT_TYPE, COUNT (*) from all_objects GROUP BY OBJECT_TYPE;
25, the relevant information about the connection
1) Access to which users connect
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) under the circumstances v.sid see the corresponding connection resource usage, etc.
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) According to sid view corresponding connector running sql
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 lookup 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, which queries the database instance is running
select inst_name from v $ active_instances;
28, before the oracle 10 Tiao get the most resource intensive sql statement
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 by CMD)
1, View sde connection status quickly see there are several connections (without entering the sql * plus)
sdemon -o status
2, delete a user is currently connected, you need to provide the S-ID, password and sde
sdemon -o kill -t 1731 -p sde
3. Disconnect all users connected
sdemon -o kill -t all -s 127.0.0.1 -i 5151 -p sde
4, to see the difference between the number of instances and the name sde
sdemon -o info -I instances
5. Check sde instance occupied table, the table behind the id, sde library table_registry
sdemon -o info -I locks
6, querying the operating system type, operating system environment variables, system drive letter, sde installation path and other information
sdemon -o info -I vars
7, SDE service open, stop, pause
sdemon -o start -p sde
sdemon -o shutdown -p sde
sdemon -o pause -p sde |
|
|
|