Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle SDE and maintain common commands - Display space     - Multi-core CPU, multi-threading and parallel computation (Linux)

- Installation and use the telnet command under CentOS 6.x (Linux)

- Linux Getting Started tutorial: How to backup Linux systems (Linux)

- C ++ thread creates transmission parameters are changed (Programming)

- Linux batch copy data script (Linux)

- CentOS 6.5_x64 install Oracle 11g R2 (Database)

- Linux, Chrome browser font settings beautification (Linux)

- Lua non-blocking write log (Programming)

- Linux file system data file deletion problem space is not freed (Database)

- Root of AVL Tree- achieve balanced search trees AVL tree (Programming)

- Java factory pattern (Factory mode) (Programming)

- PuTTY Xming achieve the X11 forwarding ssh (Server)

- Git / Github use notes (Linux)

- Linux /var/spool/ insufficient clientmqueue space solutions (Linux)

- Java String type time compare the size (Programming)

- Go build the locale under Windows (Linux)

- Quota for Vsftpd do use disk quotas (Server)

- Upgrade installation manual CentOS6.5 GCC4.8.2 (Linux)

- Linux Tutorial: Open multiple tabs in the GNOME terminal in Ubuntu 15.04 (Linux)

- 15 things to do after installing Ubuntu 15.04 Desktop (Linux)

 
         
  Oracle SDE and maintain common commands - Display space
     
  Add Date : 2018-11-21      
         
         
         
  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
     
         
         
         
  More:      
 
- Use UDEV SCSI Rules configured ASM on Oracle Linux 5 and 6 (Database)
- Linux installation notes under GAMIT (Linux)
- Linux how to handle file names that contain spaces and special characters (Linux)
- Ubuntu 14.04 Install WordPress on Nginx (Server)
- Novice Advanced Ubuntu7.10 configured IP address (Linux)
- Ubuntu 14.04 VirtualBox can not start solution (Linux)
- Thinking in Java study notes - initialization and cleanup (Programming)
- Ubuntu 10.10 install Oracle 10g Installation Guide (Database)
- C ++ Learning Notes: references (Programming)
- The execution order of Oracle WHERE condition is not from right to left (Database)
- Ubuntu 14.04 Solution login interface infinite loop (Linux)
- Use OpenSSL to generate a certificate detailed process (Linux)
- Linux - use chroot command (Linux)
- Jigsaw project will solve the problem of Java JAR hell Mody (Programming)
- MySQL query performance comparison of a single truth (Database)
- How to set the default Fedora from the command line (Linux)
- Linux use chattr and lsattr commands to manage file and directory attributes (Linux)
- Three strategies to teach you to release the device memory (Linux)
- RHEL7.0 environment Linux kernel upgrade (Linux)
- Use Ambari rapid deployment Hadoop big data environment (Server)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.