Oracle Database Common Commands

--querying all users in the current systemSelect *  fromall_users--Check all the tables below the current user:Select *  fromtabwhereTname not  like '%$%'--View all tables under the current user (more info)Select *  fromUser_tables--Check all the tables in the databaseSelect *  fromAll_tables--Create a user (the current user for this operation must have DBA authority)Create UserUser name identified byPassword--CREATE TABLE SpaceCreatetablespace ts_mytestloggingdatafile'f:\oracle_xe_datafile\mytest.dbf'size 32mautoextend on Next10m MaxSize Unlimited--Delete Table SpaceDropTablespace Hoteldata including contents anddatafiles;/*three types of users sys;//system administrator, with the highest privileges system;//local administrator, sub-high privilege scott;//ordinary users, password default to Tiger, the default is not unlocked new user is not authorized all for ordinary users*/--Delete User (cascade)Drop UserTestUserCascade--Grant this user permission (the current user doing this must have DBA authority)GrantConnect, Resource toUser name--The connect role is the typical right to give the end user the most basic  --Modify Session    AlterSession--Establish a session    CreateSession--Build a cluster    CreateCluster--Establish a database link    Create DatabaseLink--set up a sequence    Createsequence--Create synonyms    Createsynonym--Build a View    Create View--resource roles are granted to developers  --Build a cluster    CreateCluster--establish a stored procedure    Create procedure  --set up a sequence    Createsequence--Build Table    Create Table  --Creating triggers    Create Trigger  --Build Type    Createtype--Modify user password formatAlter UserUser name identified byPassword--lock/Unlock Oracle usersAlter UserUser name account unlockAlter UserUser Name Account lock--to change the structure of a table:Alter TableTable name Modify (nameChar(Ten))--querying all user default tablespaceSelectUsername, Default_tablespace fromdba_users--See which tables and tablespaces are used by one userSelectT.table_name, T.tablespace_name fromDba_all_tables TwhereT.owner='COLLEGE'--querying the current user connectionSelect Count(*) fromv$session--View Current user permissionsSelect *  fromSession_privs;--Add primary KeyAlter TableTestAdd constraintPk_testPrimary Key(userid);--Delete primary KeyAlter TableTable nameDrop constraintPRIMARY Key Name--View all users of the current databaseSelect *  fromdba_users--Add foreign KeyAlter TableTable 2Add constraintFOREIGN Key NameForeign Key(Fields in table 2 that require a foreign key)ReferencesTable 1--Delete foreign keyAlter TableTest2Drop constraintFk_test2--querying data at a point in time (which can be used to recover data, but cannot be queried if the truncate of the information in the table is deleted)Select *  fromT_person as  of timestampTo_date ('2017-04-21 21:30:00','YYYY-MM-DD Hh24:mi:ss');

Oracle Database Common Commands