"Reprint" Oracle system table grooming + Common SQL statement Collection

--Most of the dba/all/user/v_$/gv_$/session/index starts 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
Note that the case is sensitive in the Oracle database, and the data in the three tables is in uppercase by default, so it is possible that the data cannot be traced when the query is made with the lowercase data.

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;
SELECT count (1) from User_tables;

--Most of the 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 view that compares the common DBA beginning has
select * from Dba_users;--Database user information
Select * from Dba_roles;--Role Information
SELECT * FROM Dba_seg ments; --Table Segment Information
Select * from Dba_extents;--Data area information
Select * from Dba_objects;--Database object information
Select * from dba_lobs;--lob data information Br>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_r Ollback_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_pro Files --Database User resource restriction information
Select * from Dba_sys_privs;--User's system permissions information
Select * from Dba_tab_privs;--the object permission information that the user has
select * FROM Dba_col_privs; --The user has the Column object permission information
Select * from Dba_role_privs;--Role information that the user has
select * from Dba_audit_trail;--Audit trail 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 DB a_indexes; --index information for user mode

--The view with the more common start of all has
select * from All_users;--Information for all users of the database
Select * from All_objects;--Information on all objects in the database
Select * FROM All_def_audit_opts; --All default audit settings 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 more common view at the beginning of all is
SELECT * from User_objects; --User Object information
SELECT * from User_source; --All resource object information for the 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; --system permission information for the current user
SELECT * from User_tab_privs; --Object permission information for the current user
SELECT * from User_col_privs; --table column permission information for the current user
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; --table column information corresponding to the user's index
SELECT * from User_cons_columns; --The table column information for the user's constraints
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; --Information of the hash cluster

--the more commonly used v$ begins with aliases that 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 history 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 in each part of the document
SELECT * from V$thread; --Thread information
SELECT * from V$datafile_header; --Information recorded in the header of the data file
SELECT * from V$archived_log; --Archive log information
SELECT * from V$archive_dest; --Setup information for archived logs
SELECT * from V$logmnr_contents; --DML DDL result information for archived 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

--the more commonly used view at the beginning of a session is
SELECT * from Session_roles; --Role information for the session
SELECT * from Session_privs; --Permission information for the session

--the more commonly used views at the beginning of the index are
SELECT * from Index_stats; --Indexing settings and storage information

--pseudo-table, refer to Oracle Dual detailed: http://blog.csdn.net/ozhouhui/article/details/7935196
SELECT * 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 user name to accept this permission
-Owner of 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 what roles a user has
SELECT * from Dba_role_privs where grantee= ' SYS ';
--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
GRANT 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 ';
--Grant certain permissions directly to a user
GRANT CREATE VIEW to ' USER ';

--See if a system user has SYSDBA or sysoper permissions
--the difference between Oracle:dba,sysdba,sysoper three: http://blog.chinaunix.net/uid-22457844-id-3045741.html
SELECT * from V$pwfile_users;

--Lock, unlock users
SELECT * from dba_users WHERE username = ' SCOTT ';
ALTER USER SCOTT account LOCK; --Lock user
ALTER USER SCOTT account UNLOCK; --Unlock User
COMMIT;

--oracle10g Modify user password: http://blog.163.com/benbenfafa_88/blog/static/64930162200972594612972/
--User Default Password Check in Oracle 11g:http://www.dbform.com/html/2009/673.html
SELECT password from dba_users WHERE username = ' SCOTT ';
Alter user SCOTT identified by New_password; --Modify User password

--service_names:http://docs.oracle.com/database/121/refrn/guid-ac956707-d568-4f8a-bf2e-99ba41e0a64f.htm#refrn10194
SELECT * from Global_name; --View Oracle's global database name
SELECT * from V$database; --View database name show parameter db_name;

--The database instance name corresponds to Sid
--sid:http://docs.oracle.com/database/121/ladbi/glossary.htm#ladbi8021
--In the case of configuring Oracle environment variables under Linux, you can use the Echo $ORACLE _sid, and if you can not query using PS-EF |grep Oracle, the result of XXXX is the corresponding SID.
--Oracle 2548 1 0 Aug17? 00:00:00 ora_pmon_xxxx
--In the Windows environment, Oracle is managed as a backend service, so look at the name: "ORACLESERVICEORCL" in the service, management tools, Control Panel, then ORCL is SID;
SELECT * from V$instance; --View the database instance name show parameter instance_name;
Select instance from V$thread;

--Show parameter is an Oracle command, not a 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% '; --Querying the database domain name

Select username from all_users where username like '%scott% ';
Drop user SCOTT cascade;
Commit

--ERROR at line 1:
--Ora-01940:cannot drop a user that is currently connected

Select ' 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% ';
Drop role CONNECT;
Commit

SELECT * from dba_tablespaces where tablespace_name like ' EXAMPLE ';
Drop tablespace EXAMPLE including contents and datafiles cascade constraints;
---including contents delete the contents of the table space, if the table space before the delete table space has content, without this parameter, the table space is not deleted, so the habitual 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 empower the user:
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 DBLINK1
Connect to Dbusername identified by Dbpassword
Using ' (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521))) (Connect_data = (service_ NAME = ORCL)) ';

--After creating the Dblink, you can create the view directly on the Dblink
Create or Replace View CPTP as (select SJDH from [email protected]); Drop View CPTP;

--Lock Table query sql
SELECT object_name, Machine, S.sid, s.serial#
From Gv$locked_object L, dba_objects O, gv$session s
WHERE l.object_id = o.object_id
and l.session_id = S.sid;

--Unlock Table
Alter system kill session ' Sid, Serial# ';

--Back up a table
CREATE TABLE new_table as SELECT * from Old_table;

--See if the database is in a cluster in the RAC environment
Show parameter cluster_database;
SELECT * from v$parameter where name = ' Cluster_database ';

--Column operations
--Adding and modifying columns does not need to add a keyword column
--To delete a single column, be sure to add column, delete multiple columns when the column keyword cannot be added

--Add a column
ALTER TABLE Emp4 Add test varchar2 (10);
--Modify a column
ALTER TABLE Emp4 Modify Test varchar2 (20);
--Delete a column
ALTER TABLE emp4 drop column test;
--Add multiple columns
ALTER TABLE Emp4 Add (Test varchar2 (Ten), test2 number);
--Modify multiple columns
ALTER TABLE Emp4 Modify (test varchar2, Test2 varchar2 (20));
--Delete multiple columns
ALTER TABLE Emp4 drop (TEST,TEST2);

--Start the database as an administrator under Windows
net start ORACLESERVICEORCL--ORCL is the name of the DB instance you installed later
net start Oracleoradb11g_home1tnslistener--Non-essential

--Linux under SYSDBA User login, and then start the database
Sqlplus/as SYSDBA
Startup

--Sqlplus Landing mode
Sqlplus/as SYSDBA--Oracle SYS administrator logged on with operating system Authority authentication

Sqlplus/nolog
Conn/as SYSDBA--Oracle SYS administrator logged on with operating system Authority authentication

Sqlplus Sys/[email protected] as SYSDBA--Login with SYS user must use as SYSDBA

Sqlplus/nolog--does not expose password login mode in cmd or teminal
Conn Sys/password as Sysdba

Sqlplus--Login without revealing the password
Enter User-name:sys
Enter Password:password as Sysdba--the AS SYSDBA clause must be added to the login with the SYS user

Sqlplus Scott/[email Protected]--Non-admin user Login

Desc v$database; --Querying the table structure of the V$database database

--Execute SQL script in Sqlplus, the following two ways can be
START file_name
@file_name

--Determine if the table exists and delete if it exists
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 a table
CREATE TABLE EMP
(EMPNO number (4) Not NULL,
Ename VARCHAR2 (10),
JOB VARCHAR2 (9),
MGR Number (4),
HireDate DATE,
SAL Number (7, 2),
COMM Number (7, 2),
DEPTNO number (2));
You can load the above stored procedures in front of each create table.

--oracle to determine if a sequence exists, delete it if it exists

Declare
V_num number;

BEGIN
V_num is set to 0 each time----multiple deletions
V_num: = 0;
----Determine if sequence seq_name_1 exist (case-sensitive)
Select COUNT (0) into V_num from user_sequences where sequence_name = ' seq_business_process_index_id ';
----Delete immediately if present
If V_num > 0 Then
Execute immediate ' DROP SEQUENCE seq_business_process_index_id ';
End If;
END;

--set Sqlplus mode to show total rows
Show pagesize; --View the current pagesize
Set pagesize 300;

--Set Sqlplus mode to display line width
Show Linesize; --View the current linesize
Set 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 whether 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 like '%201% ')
Minus
Select Segment_name from dba_segments where owner= ' Vpmuser ' and segment_type= ' TABLE ' and segment_name ' don't like ' bin$% '

--Query the statement of all tables of the user 1
Select t.table_name,t.comments from User_tab_comments t

--Query Statement 2 for all tables of the user:
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

--Find all indexes of the table (including index name, type, constituent column):
Select T.*,i.index_type from User_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = I.tabl E_name and t.table_name = table to query
--lookup table's primary key (including name, constituent column):
Select cu.* from User_cons_columns cu, user_constraints au where cu.constraint_name = Au.constraint_name and Au.constraint _type = ' P ' and au.table_name = table to query

--Lookup Table uniqueness constraints (including name, constituent columns):
Select column_name from User_cons_columns cu, user_constraints au where cu.constraint_name = Au.constraint_name and Au.con Straint_type = ' U ' and au.table_name = table to query

--Find the foreign key of the table (including the name, the table name of the reference table and the corresponding key name, which is divided into multi-step query):
SELECT * from user_constraints c where c.constraint_type = ' R ' and c.table_name = table to query

--Query the column name of the FOREIGN KEY constraint:
SELECT * from User_cons_columns cl where cl.constraint_name = FOREIGN key Name

--Query the column name of the key referencing the table:
SELECT * from User_cons_columns cl where cl.constraint_name = foreign key reference table key name

--Querying all columns of a table and their properties
Select t.*,c.comments from User_tab_columns t,user_col_comments c where t.table_name = C.table_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 the SQL that has been executed 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 changes (change here to Al32utf8)
Sqlplus SYS as SYSDBA

--Execute the following command, it is possible to cause data chaos in the database, so before the operation, to make 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 Staff
SET 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 Staff
SET modify_time = To_timestamp (' 19-03-2008 02:36:00.360000 ', ' dd-mm-yyyy hh24:mi:ss.ff ')
WHERE staff_id = ' 01 ';

Thanks for Aloys Wind.

Original address: https://www.cnblogs.com/jiangxinnju/p/5840420.html

Reprint Oracle system table grooming + Common SQL statement collection