View Oracle Systems

Preface:Oracle after the installation is complete, you can open the Services window by executing the Control Panel | Administrative Tools | Services command, where you can view Oracle service information.
ORACLEDBCONSOLEORCL The OEM console service process.
Oraclejobscheduler<sid> the service process of the timer. Where <SID> is the instance name that was configured for the DB instance when it was created.
Oracle<oracle_home_name>tnslistener the listener's service process. Where <ORACLE_HOME_NAME> represents the home directory of ORACLE.
Oracleservice<sid> the service process for the Oracle DB instance.

If you want to understand the user information in Oracle, you can query the data dictionary dba_users,
For example, in Oracle's development tools Sql*plus, use the system user to log in to the database and then query the data dictionary using the SQL language

Oracle Enterprise Manager (OEM): Provides a web-based interface for tools that can be used to manage a single Oracle database. Because the OEM employs a Web-based application, its access to the database also uses the HTTP/HTTPS protocol, even if the Oracle database system is accessed with a 3-tier structure. After the successful installation of Oracle, the OEM is installed.

Https://localhost:1158/em

It should be mentioned that there are many data dictionaries that begin with "Dba_".
For example, there are dba_tables, in fact, this is a list of all the data in the database,
For example, using Dba_tables will list all the data tables in the database (n-more),
But you can use User_tables or all_tables instead of dba_tables, and of course there are some that don't correspond to it,
This will only list the data tables that are allowed to be viewed by the current logged-on user.
For the "Dba_" is basically the corresponding "User_" and "All_",
where "User_" corresponds to the object that records the current logged-in user,
and "All_" is to record the current logged on user's object information and authorized access to the object information,
and "Dba_" is all the object information that contains the DB instance!!!

Oracle queries All tables under the user:

Select *  fromAll_tab_comments--querying all users ' tables, views, etc.Select *  fromUser_tab_comments--query This user's table, view, etc.Select *  fromAll_col_comments--Query the column names and comments for all users ' tables.Select *  fromUser_col_comments--Query the column names and comments for this user's tableSelect *  fromAll_tab_columns--query for information such as column names for all users ' tables (verbose but no notes).Select *  fromUser_tab_columns--query for information such as column names for this user's table (verbose but no notes).

--General use 1:

Select  from User_tab_comments t

--General use 2:

Select R1, R2, R3, R5  from (Select  a.table_name R1, A.column_name R2, a.comments R3          from  user_col_comments a),       (Select from user_tab_comments t) where = R1

Oracle system table query: 　　

1. Users:

　Select  from　　dba_users;   alteruser by

2. Table Space:

 　　　 Select *  fromDba_data_files; Select *  fromDba_tablespaces;//Table SpaceSelectTablespace_name,sum(bytes),sum(Blocks) fromDba_free_spaceGroup  byTablespace_name;//Free table SpaceSelect *  fromDba_data_fileswhereTablespace_name='RBS';//data file for table spaceSelect *  fromdba_segmentswhereTablespace_name='Indexs';

3. Database objects:

　Select *  from dba_objects; "CLUSTER, DATABASE LINK,FUNCTION,INDEX,    LIBRARY, package, package BODY,PROCEDURE, SEQUENCE,    synonym,TABLE,TRIGGER, TYPE, UNDEFINED,VIEW. 】       

4. Table:
　　　

Select *  fromDba_tables;analyze my_tableCompute Statistics; -dba_tables after 6 columnsSelectExtent_id,bytes fromdba_extentswhereSegment_name='CUSTOMERS'  andSegment_type='TABLE'Order  byextent_id;//The table uses the extent information. Segment_type='ROLLBACK'to view the spatial allocation Information column information for a rollback segment:Select distincttable_name fromUser_tab_columnswherecolumn_name='so_type_id';

5. Index:　

Select *  fromdba_indexes;//index, including primary key indexSelect *  fromDba_ind_columns;//Indexed ColumnsSelectI.index_name, i.uniqueness, C.column_name fromuser_indexes I, user_ind_columns cwhereI.index_name=C.index_name andI.table_name= 'ACC_NBR';/ /Join use

6. Sequence:

Select *  from

7. View:

Select *  from dba_views; Select *  from all_views; text

8. Cluster:
SELECT * from Dba_clusters;

9. Snapshot:
SELECT * from Dba_snapshots;
The corresponding tablespace should exist for the snapshot and partition.

10. Synonyms:
SELECT * FROM dba_synonyms
where table_owner= ' SPGroup ';
If owner is public,then the synonyms are a public synonym.
If owner is one of the users,then the synonyms is a private synonym.
11. Database Chain:
SELECT * from Dba_db_links;
Build a database chain under Spbase
Create DATABASE link Dbl_spnew
Connect to Spnew identified by Spnew using ' JHHX ';
insert INTO [email protected]_spnew
SELECT * from ACC_NBR where nxx_nbr= ' 237 ' and line_nbr= ' 8888 ';
12. Trigger:
SELECT * from Dba_trigers;
Stored procedures, functions are looked up from dba_objects.
Its text: Select text from User_source where name= ' book_sp_example ';
Error setting: SELECT * from User_errors;
Oracle always places software such as stored procedures, functions, and so on in the system table space.
13. Constraints:
(1) The constraint is associated with the table and can be created, modified, and deleted in the CREATE TABLE or ALTER TABLE table_name add/drop/modify.
Constraints can be temporarily suppressed, such as:
ALTER TABLE Book_example
Disable constraint book_example_1;
ALTER TABLE Book_example
Enable constraint book_example_1;
(2) Primary keys and foreign keys are called table constraints, and constraints such as NOT NULL and unique are called column constraints. The primary and foreign keys are typically placed under the field list as separate naming constraints, and column constraints can be placed on the same row as the column definition, which is more readable.
(3) Column constraints can be seen from the table definition, namely describe; table constraints are primary keys and foreign keys, which can be traced from Dba_constraints and Dba_cons_columns.
SELECT * FROM User_constraints
where table_name= ' book_example ';
Select Owner,constraint_name,table_name
From User_constraints
where constraint_type= ' R '
ORDER BY TABLE_NAME;
(4) Definition constraints can be nameless (the system automatically generates constraint names) and self-defined constraint names (especially primary keys, foreign keys)
such as: CREATE TABLE Book_example
(identifier number not NULL);
CREATE TABLE Book_example
(identifier number constranit book_example_1 not NULL);

14. Rollback segment:
All the information required to recover the transaction is maintained in the rollback segment before all modifications are saved to the disk, and must be sized appropriately in the transactions that occurred in the database (DML statements can be rolled back, and DDL such as create,drop,truncate cannot be rolled back).
Number of rollback segments = Concurrent Transaction/4, but not more than 50; make each rollback segment large enough to handle a complete transaction;
Create rollback segment R05
Tablespace RBS;
Create rollback segment RBS_CVT
Tablespace RBS
Storage (initial 1M next 500k);
Make rollback segment Online
Alter rollback segment R04 online;
Use Dba_extents,v$rollback_segs to monitor the size and dynamic growth of the rollback segment.
Interval information for rollback segments
SELECT * FROM Dba_extents
where segment_type= ' ROLLBACK ' and segment_name= ' RB1 ';
Segment information for the rollback segment, where bytes shows the number of bytes for the current rollback segment
SELECT * FROM Dba_segments
where segment_type= ' ROLLBACK ' and segment_name= ' RB1 ';
Assign a regression segment to a thing
Set TRANSACTION use rollback segment RBS_CVT
You can use rollback segment retraction for bytes.
Alter rollback segment RBS_CVT shrink;
Select Bytes,extents,max_extents from Dba_segments
where segment_type= ' ROLLBACK ' and segment_name= ' rbs_cvt ';
Current status information for the rollback segment:
SELECT * FROM Dba_rollback_segs
where segment_name= ' RB1 ';
The rollback segment belongs to the instance instance_num than the multi-rollback segment state status
Check the optimized value optimal
Select N.name,s.optsize
From V$rollname N,v$rollstat s
where N.usn=s.usn;
Rolling back data in a segment
Set TRANSACTION use rollback segment rb1;/* rollback segment Name */
Select N.name,s.writes
From V$rollname N,v$rollstat s
where N.usn=s.usn;
When the transaction is complete, the $rollstat is queried again, and the writes (the number of Rollback segment Entry bytes) difference is compared to determine the size of the transaction.
Querying transactions in a rollback segment
Column RR heading ' RB Segment ' format A18
Column US heading ' Username ' format A15
Column OS heading ' OS User ' format A10
Column te heading ' Terminal ' format A10
Select R.name Rr,nvl (s.username, ' no transaction ') Us,s.osuser os,s.terminal te
From V$lock l,v$session S,v$rollname R
where L.SID=S.SID (+)
and Trunc (l.id1/65536) =r.usn
and l.type= ' TX '
and l.lmode=6
Order BY R.name;
15. Homework
Querying job Information
Select Job,broken,next_date,interval,what from User_jobs;
Select Job,broken,next_date,interval,what from Dba_jobs;
Querying a running job
SELECT * from Dba_jobs_running;
Use the package exec Dbms_job.submit (: V_num, ' A; ', sysdate, ' sysdate + (10/(24*60*60)) ') to join the job. Interval 10 seconds
EXEC dbms_job.submit (: V_num, ' A; ', sysdate, ' sysdate + (11/(24*60)) ') Join the job. Interval 11 minutes Use package exec Dbms_job.remove (21) to remove job 21st.

View Oracle Systems