Oracle database creates users, tablespaces, authorization
1. First, you can log in with user scott to log in to oracle as sysdba
conn scott/tiger as sysdba;
2. Create user eg:cyz
create user cyz identified by "123456";
3. If you want to modify the user password:
alter user cyz identified by cyz21；
4.User unlock
alter user cyz account unlock;
5. Query or delete the tablespace of the current user/system
select username,default_tablespace from user_users;//Query the current table space
drop tablespace USERS including contents and datafiles cascade constraint;/Delete tablespace
select username,default_tablespace from dba_users;//Query the system tablespace
6. Create a tablespace
create tablespace db_space datafile 'E:\db_space\db_data.dbf' size 200M;
7. Assign tablespace to users
alter user cyz default tablespace db_space ;
8. Determine whether the user exists
select username from all_users where username='cyz';
9. Authorization (single authorization)
grant create session to cyz; Login permissions
grant create tableto cyz; Table creation permissions
grant create viewto cyz; Create view permissions
grant create connectto cyz；
grant resource to cyz；
grant all to public;All permissions are given to all users10. Entire user authorization
grant connect ,resource,dba,exp_full_database,imp_full_database to cyz;
Detailed introduction:
The permissions that the connect role has are: alter session, create cluster, create database
link，create sequence，create session，create stnonym，create table，create
View; the permissions owned by the resource role are: create cluster, create indextype, create
operator，create procedure，creawte sequence， create table，create
trigger, create type; all management permissions owned by dba exp_full_database is mainly the export of user oracle data
imp_full_database is mainly used for importing oracle data
11. Revoke permissions
revoke connect from cyz;
12. Query the permissions that the user has
select * from session_privs;
13. Delete users and related objects
drop user cyz cascade;
14. Operations related to tables, fields, and types.
create table test
(
id number not null,
name varchar2(20)
)
alter table test rename to test1; Modify the table name
alter table test rename column nameto name1; Modify the list name of the table
alter table test modify(name1 number(20));Modify field type
alter table test add statusvarchar2(50);Add field status
alter table test drop column status;Delete field status15. Delete all tables under the user
select ‘drop table ‘||table_name||’;’ from cat where table_type='TABLE' order by TABLE_NAME;
16. Data export and import
exp Username/password@Database instance owner=Username file=File storage path
exp cyz/123456@127.0.0.1:1521/orcl owner=cyz file=F:\cyzdb.dmp
Import
imp username/password@database fromuser=username touser=usernamefile=d:\ignore=y
imp cyz/123456@127.0.0.1:1521/orcl fromuser=cyz touser=data file=F:\test.dmp ignore=y
Basic syntax and examples:
1. exp: There are three main ways (complete, user, table) 1. Complete: EXP SYSTEM/MANAGER BUFFER=64000
FILE=C:\ FULL=Y If you want to perform a complete export, you must have special permissions 2. User mode: EXP SONIC/SONIC
BUFFER=64000 FILE=C:\ OWNER=SONIC All objects of the user SONIC are output to the file.
3. Table mode: EXP SONIC/SONIC BUFFER=64000 FILE=C:\ OWNER=SONIC
TABLES=(SONIC) In this way, the table SONIC of the user SONIC will be exported
17. Determine whether the following table exists for the user. If it exists, it will be deleted. If it does not exist, it will be created.
declare
num number;
Begin
select count(1) into num from all_tables where table_name='DS_TABLE' and owner='data';
if num=1
then
execute immediate 'drop table DS_TABLE';
else
execute immediate 'create table XXXX(id number not null,name varchar(100))';
end if;
end;
18. Extended content:
18.1 Query oracle version
select version from product_component_version where substr(product, 1, 6) = 'Oracle';
18.2 Query tablespace size
select sum(bytes) / (1024 * 1024) as free_space, tablespace_name from dba_free_space group by tablespace_name;
18.3 Query the table space size, free size, and use size.
select a.tablespace_name tablespace,//Query table space
total/(1024*1024) allsize,//The total size of the query is Mb
free/(1024*1024) freesize,//The size of free space
(total-free)/(1024*1024) usedsize,//Size used
total/(1024*1024*1024) Gsize,//Query the total size, unit is G
round((total-free)/total,4)*100 usedlv //Usage rate
from (select tablespace_name,sum(bytes)free
from dba_free_space group by tablespace_name) a,
(select tablespace_name,sum(bytes) total
from dba_data_files group by tablespace_name)b
where a.tablespace_name=b.tablespace_name;
18.4 Modify the connection pool
//Query the number of connections to the connection pool
show parameter processes;
//Modify the connection pool
alter system set processes=1000 scope=spfile;
//Query the connection session
show parameter sessions;
//Modify the connection session size
alter system set sessions=1000 scope=spfile;
19. Turn on and close the database
19.1 Close the database (SHUTDOWN IMMEDIATE)
（1) Open cmd and enter sqlplus/nolog, press Enter;
(2) Enter conn/as sysdba (highest permission)
(3）shutdown immediate；
19.2. Start the database
（1) Open cmd and enter sqlplus/nolog, press Enter;
(2) Enter conn/ as sysdba
（3）startup
20. Query whether oracle user is connected
select sid,serial#,username from v$session where username=upper('username');
/*
Delete the current connected user
*/
-- 1. First switch to SYSTEM user
-- 2. Query the corresponding sid and serial# of each process of the user.
SELECT sid,serial#,username FROM v$session WHERE username = UPPER('TEST1');
-- 3. According to the above query results, kill all processes occupied by the user.
--Note: sid and serial# are one by one.
ALTER SYSTEM KILL SESSION 'xxx, xxx';
-- 4. Delete the user
DROP USER test1 CASCADE;
21. Query the job process and kill the process query the job sequence number
select * from user_jobs;
Check the process
exec DBMS_JOB.BROKEN(JOB,true);
Start the process
exec DBMS_JOB.RUN(JOB)
=The problem of entering the highest permissions of sysdba user to operate the user lock and modify the user password====
cmd>sqlplus, enter the oracle console
Username: sqlplus/as sysdba, password: empty (just enter)
#View user list
SQL>select username from dba_users;
#Modify a user's password
SQL>alter user username identified by new password;
#oracle user unlock
SQL>alter user user name account unlock;