Oracle's common system table queries

(Transferred from: http://blog.csdn.net/marshalchen/article/details/6552103)

SELECT * from User_tab_cols where column_name=upper (' xxxxx '); query which tables have a column

Select status from User_objects where Object_name=upper (' f_get_multicountry_from_code ') query procedure or function compilation completed

Alter PROCEDURE ' | | object_name| | ' compile; Compilation procedure or function

The data dictionary dict always belongs to the Oracle user sys.
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.

system table: 1.2.1 Dba_ start

Dba_users Database user Information
Dba_segments Table Segment Information
Dba_extents Data Area Information
Dba_objects Database object Information
Dba_tablespaces database table Space information
Dba_data_files Data File setup Information
Dba_temp_files Temporary data file information
Dba_rollback_segs Rollback Segment Information
Dba_ts_quotas User table Space quota information
Dba_free_space Database Free Space information
Dba_profiles Database User Resource limit information
Dba_sys_privs User's system permissions information
Dba_tab_privs the object permission information that the user has
DBA_COL_PRIVS user has Column object permission information
Dba_role_privs The role information that the user has
Dba_audit_trail Audit trail Record information
Dba_stmt_audit_opts Audit Setup Information
Dba_audit_object Object Audit Results information
Dba_audit_session Session Audit Results Information
Dba_indexes index information for user mode
1.2.2 User_ Opening
User_objects User Object Information
User_source all resource object information for a database user
User_segments User's Table segment information
User_tables User's Table object information
User_tab_columns User's Table column information
User_constraints object constraint information for a user
User_sys_privs System permissions information for the current user
User_tab_privs Object permission information for the current user
User_col_privs table column permission information for the current user
User_role_privs role permission information for the current user
User_indexes User's index information
Table column information for the User_ind_columns user's index
Table column information for the USER_CONS_COLUMNS user's constraints
User_clusters all cluster information for the user
User_clu_columns The content information contained in the user's cluster
User_cluster_hash_expressions Hash Cluster information
1.2.3 v$ Opening
V$database Database Information
V$datafile Data File Information
V$controlfile Control File Information
V$logfile Redo Log Information
V$instance DB Instance Information
V$log Log Group Information
V$loghist Log History information
V$SGA Database SGA Information
V$parameter Initialization of parameter information
V$process Database Server process information
V$bgprocess Database Background Process information
V$controlfile_record_section the information of each part of the control file
V$thread Thread Information
V$datafile_header information in the header of the data file
V$archived_log Archive Log Information
V$archive_dest setting information for archived logs
v$logmnr_contents DML DDL result information for archived log analysis
V$logmnr_dictionary dictionary file information for log analysis
V$logmnr_logs log list information for log analysis
V$tablespace Table Space Information
V$tempfile Temporary file information
I/O statistics for v$filestat data files
V$undostat Undo Data Information
V$rollname Online rollback segment Information
V$session Session Information
V$transaction Transaction information
V$rollstat Rollback Segment Statistics
V$pwfile_users Privileged User Information
V$sqlarea Resources and related information that have been accessed by SQL statements that are currently queried
V$sql basic information about the same as V$sqlarea
V$sysstat Database System State information
1.2.4 All_ Opening
All_users information for all users of the database
All_objects information about all objects in the database
All_def_audit_opts All default Audit setup Information
All_tables all Table Object information
All_indexes information for all database object indexes

1.2.5 Session_ Opening
Role information for Session_roles sessions
Permissions information for Session_privs sessions
1.2.6 Index_ Opening
Index_stats setting and storing information for indexes
1.2.7 Pseudo-table
Dual system pseudo-list information
Software Environment:
1. Operating system: Windows Server
2. Database: Oracle 8i R2 (8.1.6) for NT Enterprise Edition
3. Installation path: c:/oracle
Meaning explanation:
Dual is a physically existing table in Oracle that can be read by any user, often in a select without a target table.
How to use:
View current Connected Users
Sql> Select User from dual;

USER
--
SYSTEM


View current date, time
Sql> select Sysdate from dual;

Sysdate
--
1 August-March-01


Sql> Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

To_char (sysdate, ' YY
---
2001-03-18 22:37:56


Use as a calculator
Sql> select 1+2 from dual;

1+2
--
3


Viewing sequence values
sql> Create sequence AAA increment by 1 start with 1;
Sql> select Aaa.nextval from dual;

Sql> select Aaa.nextval from dual;

Nextval
--
1


Sql> select Aaa.currval from dual;

Currval
--