Oracle System View SQL statement collation

--The vast majority of dba/all/user/v_$/gv_$/session/index begins with a view--dba_tables means all the relational tables that the DBA has or can access. --All_tables All relational tables that a user has or can access. -User_tables means all the relational tables owned by a user. Dba_tables and All_tables are equivalent when a user itself is a DBA for the database. --Dba_tables >= all_tables >= user_tables--It is important to note that casing is sensitive in the Oracle database, and that the data in this three tables is in uppercase by default. Therefore, in the query when the attention of the lower case data may cause data can not be found. SELECT * from Dba_views WHERE view_name like ' dba% '; SELECT * from Dba_views WHERE view_name like ' all% '; SELECT * from Dba_views WHERE view_name like ' user% '; SELECT * from Dba_views WHERE view_name like ' v_$% '; --View for an instance select * from Dba_views WHERE view_name like ' gv_$% '; --Global view, for multiple instance environments select * from Dba_views WHERE view_name like ' session% '; SELECT * from Dba_views WHERE view_name like ' index% '; SELECT count (1) from Dba_tables; SELECT count (1) from All_tables; The majority of select COUNT (1) from user_tables;--v$/gv$ begins with the alias of the v_$/gv_$ table, select * from dba_synonyms WHERE synonym_name like ' v$% '; SELECT * from dba_synonyms WHERE synonym_name like ' gv$% ';--x$ no corresponding X_$select * from dba_synonyms WHERE synonym_name like ' x$% ';--the more common view of DBA openings is a select * from Dba_users; --Database user Information select * from Dba_roles; --Role Information select * from Dba_segments; --Table segment Information select * from Dba_extents; --Data area information select * from Dba_objects; --Database object information select * from Dba_lobs; --lob Data Information select * from Dba_tablespaces; --Database table space information select * from Dba_data_files; --Data file setup Information select * from Dba_temp_files; --Temporary data file information select * from Dba_rollback_segs; --Rollback Segment Information select * from Dba_ts_quotas; --User table space quota information select * from Dba_free_space; --Database free space information select * from Dba_profiles; --Database User resource restriction information select * from Dba_sys_privs; --User's system permission information select * from Dba_tab_privs; --The user has the object permission information select * from Dba_col_privs; --The user has the Column object permission information select * from Dba_role_privs; --User's role information select * from Dba_audit_trail; --Audit Trail record information select * from Dba_stmt_audit_opts; --Audit Setup Information select * from Dba_audit_object; --Object Audit result Information select * from Dba_audit_session; --Session Audit result Information select * from Dba_indexes; --User mode index information--the view with the more commonly used all starts with a select * from All_users; --Information for all users of the database select * from All_objects; --Information about all objects in the database SeleCT * from all_def_audit_opts; --All default audit setup Information select * from All_tables; --All Table object information select * from All_indexes; --All Database object index information select * from All_tab_comments; --Query All users ' tables, views, etc. select * from All_col_comments; --Query the column names and comments for all users ' tables. select * from All_tab_columns; --Query the column names of all users ' tables (verbose but no notes)--the view with the more commonly used all starts with select * from User_objects; --User Object information select * from User_source; --All resource object information for database user select * from user_segments; --User's table segment information select * from User_tables; --User's Table object information select * from User_tab_columns; --User's table column information select * from User_constraints; --User's object constraint information select * FROM User_sys_privs; --Current user's system permissions information select * from User_tab_privs; --Object permission information for the current user select * from User_col_privs; --Current user's Table column permission information select * from User_col_comments; --Query the column names and comments for this user's table select * from User_role_privs; --Current user's role permission information select * from User_indexes; --User's index information select * from User_ind_columns; --The table column information corresponding to the user's index select * from User_cons_columns; --The user's constraint corresponds to the table column information select * from User_clusters; --all cluster information of the user select * from User_clu_columns; --The content information contained in the user's cluster select * from User_cluster_hash_expRessions; --Hash cluster information--the more commonly used aliases at the beginning of v$ have select * from V$database; --Database Information select * from V$datafile; --Data file information select * from V$controlfile; --Control file information select * from V$logfile; --Redo Log Information select * from V$instance; --database instance information select * from V$log; --Log Group information select * from V$loghist; --Log historical information select * from V$SGA; --Database SGA Information select * from V$parameter; --Initialization of parameter information select * from V$process; --Database server process information select * from V$bgprocess; --Database background process information SELECT * from V$controlfile_record_section; --control of the information contained in the document select * from V$thread; --Thread Information select * from V$datafile_header; --Information in the header of the data file select * from V$archived_log; --Archive log information select * from V$archive_dest; --Archive Log setup Information select * from V$logmnr_contents; --The DML DDL result information for the archive log analysis select * from V$logmnr_dictionary; --Dictionary file information for log analysis select * from V$logmnr_logs; -Log list information for log analysis select * from V$tablespace; --Table space information select * from V$tempfile; --Temporary file information select * from V$filestat; --I/O statistics for data files select * from V$undostat; --undo Data Information select * from V$rollname; --Online rollback segment Information select * from V$session; --Session Information SELECT * FROM V$transactIon --Transaction information select * from V$rollstat; --Rollback Segment Statistics SELECT * from V$pwfile_users; --Privileged User Information select * from V$sqlarea; --The resources and related information accessed by the currently queried SQL statement select * from V$sql; --basic information about the same as V$sqlarea select * from V$sysstat; ---Database System state information--a more common view of session opening is a select * from Session_roles; --Session role Information SELECT * from Session_privs; --The access information of the session--a view with a more common index beginning is a select * from Index_stats; --Index settings and storage information--pseudo-table, refer to Oracle Dual detailed: Http://blog.csdn.net/ozhouhui/article/details/7935196select * from dual; --System Pseudo list information select sysdate from dual; --sysdate can be treated as a function whose result is the current date and time, and sysdate can be used wherever an Oracle function can be used. It can also be thought of as a hidden column or pseudo-column for each table. Select current_date from dual; --The system date in the time zone of the report session. Note: You can set your own time zone to distinguish it from the time zone of the database. Select Systimestamp from dual; --Report the system date in timestamp data type format. --System permissions--GRANTEE the user name that accepts the permission--owner of the owner object--Grantor the user who gives permission select * from Dba_sys_privs WHERE GRANTEE = ' sys '; SELECT * FROM Dba_sys_privs WHERE grantee = ' CONNECT '; SELECT * FROM dba_sys_privs where grantee = ' RESOURCE ';--Role permissions--see which roles a user has select * from Dba_role_privs where grantee= ' SY S ';--See which users a role has been given select * from Dba_role_privs WHERE granted_role = ' DBA ';--Object permissions select * from dba_tab_privs;--grant certain roles to a user gr ANT connect,resource to ' USER '; GRANT dba to ' USER '; --When you grant a DBA role to a normal user, you need to reconnect to take effect revoke DBA to ' user ';--directly grant certain permissions to a user grant CREATE VIEW to ' user ';-- See if a system user has SYSDBA or sysoper permissions-the difference between the Oracle:dba,sysdba,sysoper: http://blog.chinaunix.net/ Uid-22457844-id-3045741.htmlselect * from v$pwfile_users;--lock, unlock user select * from dba_users WHERE username = ' SCOTT '; ALTER USER SCOTT account LOCK; --Lock user alter users SCOTT account UNLOCK; --Unlock user commit;--oracle10g Modify user password: http://blog.163.com/benbenfafa_88/blog/static/64930162200972594612972/--users Default Password Check in Oracle 11g:http://www.dbform.com/html/2009/673.htmlselect Password from Dba_users WHERE Userna me = ' Scott '; Alter user SCOTT identified by New_password; --Change user password--service_names:http://docs.oracle.com/database/121/refrn/ Guid-ac956707-d568-4f8a-bf2e-99ba41e0a64f.htm#refrn10194select * from Global_name; --View Oracle's global database nameSELECT * from V$database; --View database name show parameter db_name;--DB instance name corresponds to sid--sid:http://docs.oracle.com/database/121/ladbi/glossary.htm# ladbi8021--Linux in the case of configuring Oracle environment variables can use the Echo $ORACLE _sid, if you can not use PS-EF |grep Oracle to query, the result of XXXX is the corresponding SID.        --Oracle 2548 1 0 Aug17? 00:00:00 ora_pmon_xxxx--in a Windows environment, Oracle is managed as a background service, so look at the name: "ORACLESERVICEORCL" in the services, management tools, Control Panel, Then ORCL is SID; SELECT * from V$instance; --View DB instance name show parameter Instance_name;select instance from v$thread;--show parameter is Oracle command, not standard SQL statement-- Can be executed in sqlplus or PL/SQL Dev's command Window-show parameter aaaa, equivalent to select * from V$parameter WHERE name like '%aaaa% '; SELECT * from V$parameter WHERE name is like '%name% '; --equivalent to show parameter Name;select * from V$parameter where name is like '%db_domain% ';  --Query database domain name Select username from all_users where username like '%scott% ';d ROP user SCOTT cascade;commit;--ERROR at line 1:-- Ora-01940:cannot drop a user that is currently connectedselect ' ALTER SYSTEM KILL SESSION ' | | '|| sid| | ', ' | |  serial#| | "' | | '; ' as KILLER from v$session where username= ' SCOTT ';--killer--ALTER SYSTEM KILL session ' 363,35 ';--ALTER  SYSTEM KILL SESSION ' 364,51 '; Commit;select * from dba_roles where role like '%connect% ';d rop role Connect;commit;select * From dba_tablespaces where tablespace_name like ' EXAMPLE ';d rop tablespace EXAMPLE including contents and Datafiles Cascad e constraints;--including contents delete the contents of the tablespace, if the table space is deleted before the table space has content, without this parameter, the table space is not deleted, so it is customary to add this parameter. --including datafiles delete the data files in the tablespace. --cascade constraints also deletes foreign key references for tables in tablespace. --How to create DBLink and views--http://docs.oracle.com/database/121/sqlrf/statements_5006.htm#i2061505--If you need to create a global DBLink, You need to first determine the user has permission to create Dblink: SELECT * from User_sys_privs where privilege like Upper ('%database link% ');--if not, you need to use the SYSDBA role to User empowerment: Grant create public database link to dbusername;--If you create a global dblink, you must use the SYSTM or SYS user to add public before database. Create/* Public */Database link Dblink1connect to dbusername identified by dbpasswordusing ' (DESCRIPTION = (ADdress_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521))) (Connect_data = (service_name = ORCL))) ';--Create Dbl After ink, you can create a view directly on the Dblink created or replace view CPTP as (select Sjdh from [email protected]); Drop View cptp;--Lock table Query Sqlselect object_name, machine, S.sid, S.serial#from gv$locked_object l, dba_objects O, gv$session s WHERE l.object_id = O.object_idand l.session_id = s.sid;--unlock table alter system kill session ' Sid, Serial# ';--back up a table create tab Le new_table as SELECT * from old_table;--see if the database is in the cluster of the RAC Environment show parameter Cluster_database;select * from V$parameter whe Re name = ' Cluster_database ';--column operation--adding and modifying columns does not need to add a keyword column--delete a single column, be sure to add column, delete multiple columns when the column keyword cannot be added--add a row of ALTER TABLE Emp4 Add test varchar2 (10);--Modify a row of ALTER TABLE Emp4 Modify Test varchar2 (20);--Delete a column ALTER TABLE emp4 drop column test;--add Multi-row ALTER TABLE Emp4 Add (test varchar2, test2 number);--Modify multiple columns of ALTER TABLE EMP4 Modify (test varchar2), Test2 varchar2 (2 0);--delete multiple rows of ALTER TABLE Emp4 drop (TEST,TEST2);--Windows under Start database net start ORACLESERVICEORCL--ORCL is the DB instance name you installed, net start Oracleoradb11g_home1tnslistener--non-essential-- Linux under SYSDBA User login, and then start the database sqlplus/as sysdbastartup--sqlplus Login Method Sqlplus/as SYSDBA--Oracle SYS administrator authenticated with operating system access Sqlplus /nologconn/as SYSDBA--Oracle SYS administrator logged in Sqlplus sys/[email protected] as SYSDBA--logged in with SYS user must use as Sysdbasqlplus/nolog--Not in CMD or teminal to expose the password to the login mode conn Sys/password as Sysdbasqlplus--do not reveal the password of the way login enter User-name:sysenter Password:password as SYSDBA--with SYS user must add as SYSDBA clause sqlplus scott/[email protected]-- Non-admin user login desc v$database; --Query the table structure of the V$database database--Execute SQL script in Sqlplus, the following two ways can start file_name@file_name--determine whether the table exists, if present, delete declare num number;      Begin select COUNT (1) into NUM from all_tables where table_name = ' EMP ' and owner= ' SCOTT ';      If Num=1 then execute immediate ' drop table EMP ';        End if;end;/--CREATE TABLE EMP (EMPNO number (4) Not NULL, ename VARCHAR2 (Ten), JOB VARCHAR2 (9), MGR Number (4), HireDate DATE, SAL number (7, 2), COMM number (7, 2), DEPTNO number (2)); You can load the stored procedure above To the front of each create table. --oracle determine if the sequence exists, if present, delete declare v_num number;    BEGIN----Multiple deletions, each time V_num is set to 0 v_num: = 0; ----Determine if the sequence Seq_name_1 exists (case-sensitive) select COUNT (0) into V_num from user_sequences where sequence_name = ' Seq_business_proc    Ess_index_id ';    ----If present delete immediately if V_num > 0 Then execute immediate ' DROP SEQUENCE seq_business_process_index_id '; End If; end;--Set Sqlplus mode display total number of rows show pagesize; --View current pagesizeset pagesize 300;--settings sqlplus mode display line width show linesize; --View the current Linesizeset linesize 300;--Modify the installation directory Glogin.sql file to ensure that the previous settings are permanently active set pagesize 300;set linesize 300;--Delete Table Object Select ' drop table ' | | Segment_name from dba_segments where owner= ' Vpmuser ' and segment_type= ' table ';--Create Table Object select ' Create TABLE ' | | Segment_name | | ' AS SELECT * from ' | | segment_name | | ' @DBLINK ' from dba_segments where owner= ' Vpmuser ' and segment_type= ' table ';--check if the table is fully imported select Segment_name from[email protected] where owner= ' vpmuser ' and segment_type= ' TABLE ' and (segment_name not like ' bin$% ' and segment_name Not as '%201% ') Minusselect segment_name from dba_segments where owner= ' Vpmuser ' and segment_type= ' TABLE ' and Segment_na Me not like ' bin$% '--Query user All table statement 1select t.table_name,t.comments from user_tab_comments t--query user All table statements 2:select R1, R2, R3, R 5from (select A.table_name R1, A.column_name R2, a.comments R3 from User_col_comments a), (select T.table_n Ame R4, t.comments R5 from User_tab_comments t) where R4 = r1--All indexes of the lookup table (including index name, type, constituent column): Select T.*,i.index_type from User_in D_columns t,user_indexes i where t.index_name = i.index_name and T.table_name = i.table_name and t.table_name = table to query-find Primary key (including name, constituent column) of the table: select cu.* from User_cons_columns cu, user_constraints au where cu.constraint_name = Au.constraint_name A nd au.constraint_type = ' P ' and au.table_name = table to query-the uniqueness constraint (including name, constituent column) of the lookup table: Select column_name from User_cons_columns cu, User_constraints au where CU. constraint_name = au.constraint_name and Au.constraint_type = ' U ' and au.table_name = table to query-lookup table's foreign key (including name, table name of reference table and corresponding key name, under Polygons are divided into multi-step queries): SELECT * from user_constraints c where c.constraint_type = ' R ' and c.table_name = table to query--the column name of the query foreign KEY constraint: SELECT * Fro M user_cons_columns cl where cl.constraint_name = FOREIGN Key Name-the column name of the key that queries the reference table: SELECT * from User_cons_columns CL where cl.constraint _name = key Name of the foreign key reference table--all columns of the query table and their properties select T.*,c.comments from User_tab_columns t,user_col_comments c where t.table_name = C.tab Le_name and t.column_name = c.column_name and t.table_name = table to query-backup table Data CREATE TABLE EMP as SELECT * FROM scott.emp--  Restore table data INSERT INTO EMP SELECT * FROM Scott.emp--view already executed SQL These are present in the shared pool, the user name needs to be capitalized and must have DBA authority SELECT * from V$sqlarea t where T.parsing_schema_name in (' User name ') Order by t.last_active_time Desc--oracle11g Character Set change (here to Al32utf8) Sqlplus Sys as s ysdba--executes the following command, it is possible to cause data chaos in the database, so before the operation, the database backup operation shutdown Immediate; STARTUP MOUNT; ALTER SESSION SET sql_trace=true; ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET job_queue_processes=0; ALTER SYSTEM SET aq_tm_processes=0; ALTER DATABASE OPEN; ALTER DATABASE Character Set Internal_use Al32utf8; ALTER SESSION SET sql_trace=false;shutdown immediate;startup;--View nls_lang information: SELECT parameter, value from V$nls_ Parameters WHERE parameter like '%characterset '; UPDATE Staffset modify_time = to_date (' 2016/04/22 00:01:00 ', ' yyyy/mm/dd hh24:mi:ss ') WHERE modify_time < To_date (' 2016/04/22 00:01:00 ', ' yyyy/mm/dd hh24:mi:ss '); UPDATE Staffset modify_time = To_timestamp (' 19-03-2008 02:36:00.360000 ', ' dd-mm-yyyy hh24:mi:ss.ff ') WHERE staff_id = ' 01 ‘;

　　

Performance Analysis sql:

Select    "Sql_fulltext",    "Last_active_time",    "Cpu_time",    "Elapsed_time",    "executions",    ROUND (elapsed_time/1000000/executions,3) as Persqltimefrom    v$sqlwhere    executions!=0and rownum<500

　　

Oracle System View SQL statement collation