Oracle/ms SQL system table

Oracle system table Query

Oracle queries all tables under the user

SELECT * FROM All_tab_comments--Query All users ' tables, views, etc.
SELECT * FROM User_tab_comments--Query the user's table, view, etc.
SELECT * FROM All_col_comments--queries the column names and comments for all users ' tables.
SELECT * FROM User_col_comments--Query the column names and comments for this user's table
SELECT * FROM All_tab_columns--queries for information such as column names for all users ' tables (verbose but no notes).
SELECT * FROM User_tab_columns-query for information such as column names for this user's table (verbose but no notes).

--General use 1:
Select t.table_name,t.comments 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 T.table_name R4, t.comments R5 from User_tab_comments t)
where R4 = R1

Oracle system table Query

1. Users:
Select username from dba_users;
Order
Alter user SPGroup identified by Spgtest;
2. Table Space:
SELECT * from Dba_data_files;
SELECT * FROM dba_tablespaces;//table space

Select Tablespace_name,sum (bytes), SUM (blocks)
From Dba_free_space GROUP by tablespace_name;//free table space

SELECT * FROM Dba_data_files
where tablespace_name= ' RBS ';//data file corresponding to table space

SELECT * FROM Dba_segments
where tablespace_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 * from Dba_tables;
Analyze my_table Compute statistics;->dba_tables after 6 columns
Select Extent_id,bytes from Dba_extents
where Segment_name= ' CUSTOMERS ' and segment_type= ' TABLE '
The ORDER by extent_id;//table uses the extent information. Segment_type= ' ROLLBACK ' to view spatial allocation information for rollback segments
Column information:
SELECT DISTINCT table_name
From User_tab_columns
where column_name= ' so_type_id ';
5. Index:
SELECT * from dba_indexes;//index, including primary key index
SELECT * FROM dba_ind_columns;//index column
Select I.index_name,i.uniqueness,c.column_name
From User_indexes i,user_ind_columns C
where I.index_name=c.index_name
and i.table_name = ' ACC_NBR ';//join use
6. Sequence:
SELECT * from Dba_sequences;
7. View:
SELECT * from Dba_views;
SELECT * from All_views;
Text can be used to query the script generated by the view
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.

Oracle/ms SQL system table