Oracle creates new users and databases under Linux

1.su-oracle


2. Log in to the SYS user
Sqlplus ' sys/or#k017$ddb001 as Sysdba '


3. Create temporary table spaces:


--Queries the absolute path of the temporary table space file. If necessary, you can write the absolute path by query. Generally with ${oracle_home} on it
Select name from V$tempfile;
Create temporary tablespace mpg_temp98 tempfile ' ${oracle_home}\oradata\mpg_temp98.bdf ' size 100m reuse autoextend on NEX T 20m MaxSize Unlimited;


4. Create a table space:


--Query the absolute path of the user table space file:
Select name from V$datafile;
Create tablespace mpg_data98 datafile ' ${oracle_home}\oradata\mpg_data98.dbf ' size 100M reuse autoextend on next 40M maxsi Ze Unlimited default storage (initial 128k next 128k minextents 2 Maxextents Unlimited);




5. Create user and password, specify the temporary table space and tablespace created above


Create user Mpg_admin98 identified by mpg_admin98 default tablespace mpg_data98 temporary tablespace;


6. Grant permissions to the user to DBA authority * *
Grant Create session, create any table, create no view, create any index,
Create any procedure,alter any table, alter any procedure,drop any table,
Drop any view, drop no index, drop any procedure,select any table, create any trigger,create table,
Inserts any table, the update any table, the delete any table, the unlimited tablespace,connect,resource,dba to Mpg_admin98;




7. Exit
Exit
8. Import Database
Imp mpg_admin98/mpg_admin98@mpgdb01 fromuser=mpg_admin98 touser=mpg_admin98 file=/data/orcl090201.dmp ignore=y;


6. System Rights Recycling: System permissions can only be reclaimed by DBA users
Command:sql> Revoke Connect, resource,dba from Mpg_admin98;


1. Delete a user
Drop user mpg_admin98 cascade;




/* Delete temporary table space * *




--View temporary table space files
Select name from V$tempfile;
--View the relationship between the user and the table space
Select Username,temporary_tablespace from Dba_users;
--If a user's default temp table space is notifydb_temp, change is recommended
Alter user xxx temporary tablespace tempdefault;
---set Tempdefault as the default temp table space
ALTER DATABASE default temporary tablespace Tempdefault;
--Remove tablespace notifydb_temp and its containing data objects and data files
Drop tablespace mpg_temp13 including contents and datafiles;




Alter user mpg_admin13 temporary tablespace mpg_temp13;
Drop user mpg_admin13 cascade;


Select sid,serial# from v$session where username= ' mpg_admin13 ';
--Modify User table space
Alter user mpg_admin13 default Tablespace mpg_data13;--Specify tablespace at creation time


--Delete temporary table space (make sure it is not used before deleting)
Drop tablespace mpg13_temp including CONTENTS and datafiles;
--Remove Tablespace
Drop tablespace mpg13_db including CONTENTS and datafiles;



Authorization for CONNECT,RESOURCE,DBA in--oracle
CONNECT,RESOURCE,DBA is the three built-in roles for Oracle systems, and the role permissions are as follows:
Connect: Users who have connect permissions can only log on to Oracle and cannot create entities and cannot create database structures.
RESOURCE: Users with RESOURCE permissions can create entities only and cannot create database structures.
DBA: With full privileges, the system has the highest privileges, and only DBAs can create the database structure.
In general, for ordinary users, grant Connect, resource permissions. For the administrator user, Grant Connect,resource, dba authority.


--The following permissions are required for stored procedure debugging
GRANT Debug any procedure, debug connect sessions to Scott