Oracle Operating Notes

1, query Oracle version, database SID select * from V$version; Select name from V$database;

2. Query the features supported by the Oracle database SELECT * FROM V$option;

3. Get the database character set SELECT * from Nls_database_parameters; SELECT * from V$nls_parameters;

4, query table space information SELECT * from Dba_data_files;

5. View the maximum number of sessions SELECT * from V$parameter WHERE NAME is like ' proc% '; Select Sessions_highwater,sessions_current, Sessions_max from V$license;

6, distinguish a user from which machine landing Oracle's SELECT machines, terminal from v$session;

7, query the user in the use of the system select * from the v$session where username like '%gxx% ';

8. Query the IP address of the computer accessing the Oracle Service Select Sys_context (' Userenv ', ' ip_address ') from dual;

9. View data table Footprint Select Tablespace_name,sum (bytes)/1024/1024/1024 GB from Dba_data_files Group by Tablespace_name Union All select Tablespace_name,sum (bytes)/1024/1024/1024 GB from Dba_temp_files GROUP by tablespace_name Order by GB;

10. Table Partition occupancy select   a.tablespace_name,          ROUND (A.total_ Size) "Total_size (MB)",          ROUND (a.total_size)-ROUND (B.free_size, 3) "Used_size (MB)",          ROUND (b.free_size, 3) "Free_size (MB)",           ROUND (b.free_size/total_size * 100, 2) | | '% ' free_rate   from   (  select   tablespace_name, SUM (bytes)/1024/1024 total_size &NB sp;             FROM   Dba_data_files           GROUP by   tablespace_name) A,           (  select   tablespace_name, SUM (bytes)/1024/1024 free_size  &nbs p;            from   Dba_free_space &Nbsp;         GROUP by   tablespace_name) b  WHERE    A.tablespace_name = B.tablespace_name (+);

11. Query table Space occupancy SELECT b.file_id file ID number, B.tablespace_name table space name, b.bytes bytes, (b.bytes-sum (NVL)) has been used, SUM (a.bytes,0 (A). bytes,0)) remaining space, SUM (NVL (a.bytes,0))/(b.bytes) *100 remaining percent from Dba_free_space a,dba_data_files B WHERE a.file_id=b.file_ ID GROUP by B.tablespace_name,b.file_id,b.bytes ORDER by b.file_id

12, query table space SELECT * from dba_tablespaces;

13. View System Locked System time SELECT * from V$locked_object;

14. Check the undo Segment status Select Usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from V$rollstat ORDER by Rssize;

15. Query System Lock Table SELECT S.sid session_id, S.username, DECODE (lmode, 0, ' None ', 1, ' Null ', 2, ' Row-s (SS) ', 3, ' Row-x (SX) ', 4, ' Share ', 5, ' S/row-x (SSX) ', 6, ' Exclusive ', To_char (Lmode)) Mode_held, DECODE (REQUEST, 0, ' None ', 1, ' Null ', 2, ' Row-s (S S) ', 3, ' Row-x (SX) ', 4, ' Share ', 5, ' S/row-x (SSX) ', 6, ' Exclusive ', To_char (REQUEST)) mode_requested, o.owner| | '. ' | | o.object_name| | ' (' | | o.object_type| | ') ', S.type Lock_type, L.id1 lock_id1, L.id2 lock_id2 from V$lock L, SYS. Dba_objects O, v$session S WHERE l.sid = s.sid and l.id1 = o.object_id;

16, if unlock ALTER SYSTEM KILL SESSION ' c3mlsc,1001# ';

17. Get Database link SELECT * from dba_db_links;

18. Query field select * FROM All_tables where table_name like '%sys% ' and owner = ' GXX01 ' select * from All_tab_columns where tabl E_name= ' sys_dept '

19, modify the table's primary key ALTER TABLE AAA DROP CONSTRAINT Aaa_key;

ALTER TABLE AAA ADD CONSTRAINT aaa_key PRIMARY KEY (A1,B1);

Oracle Action Note