Oracle's Common SQL statements

--1, querying system tablespace information

SELECT * from SYS. Dba_tablespaces;

--2, query table space where the path information

SELECT * from Dba_data_files;

--3, querying user-owned table spaces

Select U.username as user name, u.default_tablespace as table space from Dba_users u;

--4, creating table spaces

Create Tablespace GUEST

Logging

DataFile ' D:\oracle\oradata\ORCL\GUEST.dbf '

Size 100m

Autoextend on

Next 50m MaxSize Unlimited

Extent management Local;

--5, creating a user and specifying a tablespace

Create user GUEST identified by Neusoft

Default Tablespace GUEST

Temporary tablespace TEMP;

--6, granting permissions to users

Grant Connect,resource to EPM;

--7, querying Oracle version information 11.2.0.1.0

SELECT * FROM Product_component_version

--8, creating an Oracle directory This directory is not an OS directory

Create or replace directory IMPDP as ' D:\IMPDP ';

--9, assigning directory operation permissions to system users

Grant Read, write on the directory IMPDP to system;

--10, querying all directories

SELECT * from Dba_directories;

--11, deleting the specified directory

Drop directory IMPDP;

--12, querying the database after importing table data

SELECT * from Epsa.sa_user us where us. Real_name like '% Du Yu% ';

--13, deleting users

Drop user EPSA cascade;

Drop user EPM Cascade;

Drop user flow cascade;

Drop user guest cascade;

--14, delete table spaces, and what they belong to

Drop tablespace EPSA including contents;

Drop tablespace epm including contents;

Drop tablespace flow including contents;

Drop tablespace guest including contents;

--15, if the oralce prompt cannot delete the current connected user, first query the user's process number and then force the kill process

Select username,sid,serial# from V$session v where v.username= ' FLOW ';

--16, kill user process

Alter system kill session ' 18,42957 ';

--17, deleting users

Drop user flow cascade;

--18, Data Pump Import command

IMPDP System/[email protected] schemas=epsa,epm,flow,guest directory=impdir dumpfile=20141024.dump

--19, Data Pump Export command

EXPDP System/[email protected] schemas=epsa,epm,flow,guest directory=impdir dumpfile=20141024.dump logfile=20141024. Log version=11.1.0.7.0

--20, change the Oracle login password expires, oracle default password is valid for 180 days

SELECT * from Dba_profiles p where p.resource_name= ' password_life_time ';

Alter profile default limit Password_life_time unlimited;

SELECT * FROM Sysdba.dba_table_privs

This article is from the "Oralce Learning path" blog, make sure to keep this source http://dushuai.blog.51cto.com/9461011/1575599

Oracle's Common SQL statements