Shulou(Shulou.com)06/01 Report--

Common statements for oracle12c database management:

Select name from vault database; # View the database name:

Desc v$database

Select instance_name from instance; # Database instance name

The above three sql statements can only query the instance of the current connection and the database under the instance.

-- most of the DBA/ALL/USER/V$/GV$/SESSION/INDEX starts with views

-- DBA_TABLES means all relational tables owned or accessible by DBA.

-- ALL_TABLES means all relational tables owned or accessible by a user.

-- USER_TABLES means all the relational tables owned by a user.

When a user is a database DBA, DBA_TABLES is equivalent to ALL_TABLES.

-- DBA_TABLES > = ALL_TABLES > = USER_TABLES

-- it should be noted that the case is sensitive in the ORACLE database, and the data in these three tables are all uppercase by default, so paying attention to lowercase data when querying may make the data unsearchable.

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 viewname LIKE'Variety% views;-- views for an instance

SELECT FROM dba_views WHERE viewname 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

-- the vast majority of the names that begin with the Vendor GV$ table are aliases for the GV$ table.

SELECT FROM dba_synonyms WHERE synonym_name LIKE 'Venture%'

SELECT FROM dba_synonyms WHERE synonym_name LIKE 'GV$%'

-- X$ does not have a corresponding Xpassport $

SELECT * FROM dba_synonyms WHERE synonym_name LIKE 'Xerox%'

The more commonly used views at the beginning of DBA are

Select from dba_users;-database user information

Select from dba_roles;-role information

Select from dba_segments;-Table segment information

Select from dba_extents;-datazone information

Select from dba_objects;-Database object information

Select from dba_lobs;-lob data information

Select from dba_tablespaces;-database tablespace information

Select from dba_data_files;-data file setting information

Select from dba_temp_files;-temporary data file information

Select from dba_rollback_segs;-rollback segment information

Select from dba_ts_quotas;-user tablespace quota information

Select from dba_free_space;-database free space information

Select from dba_profiles;-Database user resource limit information

Select from dba_sys_privs;-user's system permission information

Select from dba_tab_privs;-the object permission information that the user has

Select from dba_col_privs;-the column object permission information that the user has

Select from dba_role_privs;-the role information that the user has

Select from dba_audit_trail;-Audit trail record information

Select from dba_stmt_audit_opts;-Audit settings information

Select from dba_audit_object;-object audit result information

Select from dba_audit_session;-session audit result information

Select from dba_indexes;-Index information for user mode

The more commonly used views at the beginning of ALL are

Select from all_users;-Information of all users of the database

Select from all_objects;-Information of 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;-Information for all database object indexes

Select from all_tab_comments;-query all users' tables, views, etc.

Select from all_col_comments;-query the column names and comments of all users' tables.

Select from all_tab_columns;-query all users' table column names and other information (detailed but no comments)

The more commonly used views at the beginning of ALL are

Select from user_objects;-user object information

Select from user_source;-all the resource object information of the database user

Select from user_segments;-the table segment information of the user

Select from user_tables;-the table object information of the user

Select from user_tab_columns;-the user's tabular information

Select from user_constraints;-user's object constraint information

Select from user_sys_privs;-the system permission information of the current user

Select from user_tab_privs;-object permission information of the current user

Select from user_col_privs;-the table permission information of the current user

Select from user_col_comments;-query the column names and comments of this user's table

Select from user_role_privs;-the role permission information of the current user

Select from user_indexes;-the index information of the user

Select from user_ind_columns;-the table column information corresponding to the user's index

Select from user_cons_columns;-the table column information corresponding to the user's constraints

Select from user_clusters;-all the 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 on hash clusters

The more common aliases that begin with V$ are

Select from vault database;-- Database information

Select from vault datafile;-data file information

Select from vault control file;-- Control file information

Select from vault log file;-- redo log information

Select from instance;-- Database instance information

Select from vault log;-- log group information

Select from vault loghist;-- Log history information

Select from vascal;-- Database SGA information

Select from vault parameter;-- initialize parameter information

Select from vault process;-- database server process information

Select from vault bgprocess;-database background process information

Select from controls the information recorded in each part of the file.

Select from vthread;-- Thread information

Select from data headers;-- the information recorded in the header of the data file

Select from vault archived log;-- Archive log information

Select from vault archivedestinations;-- setting information for archived logs

Select from vault logmnrcontents;-- DML DDL result information of archived log analysis

Select from vault logmnr dictionary;-- dictionary file information for log analysis

Select from vault logmnrlog logs;-- Log list information for log analysis

Select from vested tablespace;-- tablespace information

Select from vault temp file;-- temporary file information

Select from vault filestat;-- the statistics of the data file.

Select from vault undostats;-- Undo data information

Select from vault rollname;-- online rollback segment information

Select from session;-- session information

Select from vested transaction;-- transaction information

Select from vault rollstats;-- rollback segment statistics

Select from vested pwfileusers;-- privileged user information

Select from vsqlarea;-- the resources and related information accessed by the currently queried sql statements

Select from vSecretsql;-- basically the same relevant information as v$sqlarea

Select * from vstatsysstats;-- Database system status information

The more commonly used views at the beginning of SESSION are

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 INDEX are

Select * from index_stats;-setting and storing information for the index

-- pseudo table. See dual in oracle for more information: http://blog.csdn.net/ozhouhui/article/details/7935196

Select * from dual;-system pseudo-list information

Select sysdate from dual;-think of Sysdate as a function whose result is the current date and time, and you can use Sysdate anywhere you can use the Oracle function. You can also think of it as a hidden or pseudo column of 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 database time zone.

Select SYSTIMESTAMP from dual;-reports the system date in the TIMESTAMP data type format.

-- system permissions

-- the user name that GRANTEE accepts this permission

-- owner of the OWNER object

-- users with permissions granted by GRANTOR

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 are assigned to a role

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 the dba role to an ordinary user, you have to reconnect to take effect

REVOKE dba to 'USER'

-- directly grant certain permissions to a user

GRANT CREATE VIEW TO 'USER'

-- check to see if a system user has SYSDBA or SYSOPER permissions

-- the difference among oracle:DBA,SYSDBA,SYSOPER:

Select * from V$PWFILE_USERS

-- locking and unlocking users

SELECT * FROM dba_users WHERE username = 'SCOTT'

ALTER USER SCOTT account LOCK;-Lock the user

ALTER USER SCOTT account UNLOCK;-unlock the user

COMMIT

SELECT password FROM dba_users WHERE username = 'SCOTT'

Alter user SCOTT identified by new_password;-change the user's password

-- SERVICE_NAMES:

SELECT FROM global_name;-View the global database name of oracle

SELECT FROM vault database;-- View the database name show parameter db_name

-- Database instance name corresponds to SID

-- SID:

-- echo $ORACLE_SID can be used when the oracle environment variable is configured under linux. If not, you can use ps-ef | grep oracle to query. The xxxx in the result 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 if you look at "Control Panel-> Administrative tools-> Service name:" OracleServiceORCL ", ORCL is sid.

SELECT * FROM instance;-- View the database instance name show parameter instance_name

Select instance from v$thread

-- show parameter is a command of oracle, not a standard SQL statement

-- can be executed in the command window of sqlplus or pl/sql dev

-- show parameter aaaa; is equivalent to SELECT FROM v$parameter WHERE name like'% aaaa%'

SELECT FROM v$parameter WHERE name like'% name%';-equivalent to show parameter name

Select * from v$parameter where name like'% db_domain%';-- query 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 035'

-- 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 deletes the contents of the tablespace. If there are contents in the tablespace before deleting the tablespace without adding this parameter, the tablespace cannot be deleted, so it is customary to add this parameter.

-- including datafiles deletes the data file in the tablespace.

-- cascade constraints also deletes the foreign key reference of the table in tablespace.

-- if you need to create a global DBLink, you need to make sure that the user has permission to create the 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 and precede the database with public.

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 a dblink, you can create views directly on the dblink

Create or replace view cptp as (select SJDH from dbusername.cptp@dblink1); 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 the table

Alter system kill session 'sid, serial#'

-- back up a table

Create table new_table as select * from old_table

-- check whether the database is in the cluster of the rac environment

Show parameter cluster_database

Select * from v$parameter where name = 'cluster_database'

-- column operation

-- adding and modifying columns does not require the keyword COLUMN

If you delete a single column, you must add COLUMN. When you delete multiple columns, you cannot add the COLUMN keyword.

-- 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 (10), test2 number)

-- modify multiple columns

Alter table emp4 modify (test varchar2 (20), test2 varchar2 (20))

-- delete multiple columns

Alter table emp4 drop (test,test2)

-- start the database as an administrator under Windows

Net start oracleserviceorcl-- the following orcl is the name of the database instance you installed

Net start oracleoradb11g_home1tnslistener-not required

Log in as sysdba user under linux, and then start the database

Sqlplus / as sysdba

Startup

-- sqlplus login method

Sqlplus / as sysdba-login with oracle sys administrator authenticated by operating system authority

Sqlplus / nolog

Conn / as sysdba-login with oracle sys administrator authenticated by operating system authority

Sqlplus sys/password@orcl as sysdba-you must use as sysdba to log in as sys users

Sqlplus / nolog-login method that does not expose passwords in cmd or teminal

Conn sys/password as sysdba

Sqlplus-login without revealing password

Enter user-name:sys

Enter password:password as sysdba-if you log in as a sys user, you must add an as sysdba clause

Sqlplus scott/tiger@orcl-non-administrator user login

Desc vault database;-- query the table structure of the v$database database

Execute the sql script in sqlplus in either of the following two ways

START file_name

@ file_name

-- determine whether the table exists, and delete it if so

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 tables

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))

The above stored procedures can be loaded in front of each create table.

-- ORACLE determines whether the sequence exists, and deletes it if it exists

Declare

V_NUM number

BEGIN

-v_num is set to 0 each time when it is deleted multiple times

V_NUM: = 0

-determine whether the sequence seq_name_1 exists (case sensitive)

Select count (0) into V_NUM from user_sequences where sequence_name = 'SEQ_BUSINESS_PROCESS_INDEX_ID'

-delete immediately if it exists

If V_NUM > 0 then

Execute immediate 'DROP SEQUENCE SEQ_BUSINESS_PROCESS_INDEX_ID'

End if

END

-- set the sqlplus mode to display the total number of lines

Show pagesize;-View the current pagesize

Set pagesize 300

-- sets the display line width in sqlplus mode

Show linesize;-View the current linesize

Set linesize 300

-- modify the installation directory glogin.sql file to ensure that the previous settings take effect permanently

Set pagesize 300

Set linesize 300

-- delete table objects

Select 'drop table' | | segment_name from dba_segments where owner='VPMUSER' and segment_type='TABLE'

-- create table objects

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 dba_segments@aaa 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 not like 'BIN$%'

-statement 1 that queries all the tables of the user

Select t.table_name,t.comments from user_tab_comments t

-- statement 2 that queries all the 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

-- look up all indexes of the table (including index names, types, constituent columns):

Select t.Query i.indexroomtype from user_ind_columns tMagnetizi where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = the table to query

-- look up the primary key of the table (including the name, which forms the 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 be queried

-- the uniqueness constraints of the lookup table (including names, constituent columns):

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 be queried

-- look up the foreign key of the table (including the name, the table name of the referenced table and the corresponding key name. The following is a multi-step query):

Select * from user_constraints c where c.constraint_type ='R' and c.table_name = table to be queried

-- 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 of the reference table:

Select * from user_cons_columns cl where cl.constraint_name = the key name of the foreign key reference table

-- query all columns of the table and their properties

Select t. Tables from user_tab_columns c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = tables to be queried

-- backup table data

Create table emp as select * from scott.emp

-- restore table data

Insert into emp select * from scott.emp

-- check the sql that has been executed. These exist in the shared pool. The user name needs to be capitalized and must have the permission of DBA.

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 changed to AL32UTF8)

Sqlplus sys as sysdba

-- execute the following command, which may cause confusion of the data already in the database, so before performing the operation, you should backup the database.

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

-- check the NLS_LANG message:

SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE'% CHARACTERSET'

UPDATE STAFF

SET MODIFY_TIME = TO_DATE ('2016-04-22 00VO1VO1VOLING,' yyyy/MM/dd hh34:mi:ss')

WHERE MODIFY_TIME < TO_DATE ('2016-04-22 00VO1VO1VOLING' yyyy/MM/dd hh34:mi:ss')

UPDATE STAFF

SET MODIFY_TIME = TO_TIMESTAMP ('19-03-2008 002 dd-MM-yyyy hh34:mi:ss.ff')

WHERE STAFF_ID = '01'

Query spam:

SELECT t.object_name,t.type, t.original_name FROM user_recyclebin t

Clean up spam: purge table origenal_tableName

Purge index origenal_indexName

Delete all spam messages from the Recycle Bin:

PURGE recyclebin

Delete the method that Table does not enter Recycle:

Drop table tableName purge

View password policy:

Select * from dba_profiles where profile='default'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.