Common SQL (i)

Blogging is a good thing to keep a record of some commonly used SQL.

Querying all user information in Oracle

SELECT * from Dba_users;

Querying users and Passwords only

Select Username,password from Dba_users;

Querying Current User Information

SELECT * from Dba_ustats;

Querying view text that users can access

SELECT * from Dba_varrays;

Querying text for all views in the database

SELECT * FROM Dba_views;select distinct tablespace_name from SYS. Dba_free_space where tablespace_name like '%rb% '

;

Create a sequence

CREATE SEQUENCE sequence name--keyword

Create sequence table_s

[INCREMENT by N]--Growth, default is 1, can be negative

[START with N]--from where to add, that is, the first value, the default 1

[{Minvalue/maxvalue n| Nomaxvalue}]--min/MAX/no maximum (default)

[{cycle| Nocycle}]--whether the loop, if not loop, will go wrong after the maximum value [{CACHE n|                   NOCACHE}]; --memory buffer, default is 20, can improve system performance

Create sequence Seq_tb_user increment by 1 start with 1 minvalue 1 maxvalue 999999 nocycle cache 10;

View the default tablespace for the current user

　　

Select Username,default_tablespace from User_users

View the role of the current user

SELECT * FROM User_role_privs

View system and table-level permissions for the current user

SELECT * FROM User_sys_privsselect * from User_tab_privs

View all the tables under the user

SELECT * FROM User_tables

Display user information (owning table space)

Select Default_tablespace,temporary_tablespace from Dba_users

Displays the permissions that the current session has

SELECT * FROM Session_privs

Displays the system permissions that the specified user has

SELECT * FROM Dba_sys_privs

Show Privileged Users

SELECT * FROM V$pwfile_users

View a table with a name that contains a log character

Select object_name,object_id from User_objects where InStr (object_name, ' log ') >0

To view the creation time of a table

Select object_name,created from user_objects where object_name= ' Zw_yingyez '

View the size of a table

Select sum (bytes)/(1024*1024) tablesize from User_segmentswhere segment_name= ' Zw_yingyez '

View tables placed in Oracle's memory area

Select Table_name,cache from User_tables where InStr (Cache, ' Y ') >0

View index number and category

Select Index_name,index_type,table_name from User_indexes ORDER BY table_name

View the fields indexed by the index

SELECT * from User_ind_columns where table_name= ' cb_chaobiaosj201004 '

To view the size of an index

Select sum (bytes)/(1024*1024) as indexsize from User_segmentswhere segment_name=upper (' As_menuinfo ')

Viewing View information

SELECT * FROM User_views

View the name of a synonym

SELECT * FROM user_synonyms

View the status of functions and procedures

Select Object_name,status from user_objects where object_type= ' FUNCTION ' select Object_name,status from User_objects where object_type= ' PROCEDURE '

View source code for functions and procedures

Select text from All_source where Owner=user and Name= ' sf_split_string '

View table fields

Select cname from col where tname= ' zw_yingyez ' select column_name from user_tab_columns where table_name= ' Zw_yingyez '

To view the Oracle version command:

SELECT * FROM V$version

This article is from the "write-free" blog, please be sure to keep this source http://7156680.blog.51cto.com/7146680/1790822

Common SQL (i)