"ORACLE 10" tablespace, user, restore library, imp,exp, unlock

1 Login:
--Run->sqlplus-> js45st/[email protected]
2 Table Space:
--Create table space, specify data file (recommended under Oracle directory), initialize 100M self-increment 50M
sql> Create tablespace tbs_jsnet datafile ' d:\oracledb\JetsenNet6\TBS_JSNET.dbf ' size 200m reuse autoextend on NEX T 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
--Delete Table space
Sql> drop tablespace tbs_jsnet including contents and datafiles;
Syntax syntax: DROP tablespace tablespace_name [including CONTENTS [and Datafiles][cascade CONSTRAINTS]];
--Modify Table space
Alter tablespace tbs_jsnet ADD datafile ' T:\oradata\live\Temp02.DBF ' SIZE 800M;
Alter tablespace tbs_jsnet rename datafile ' filename ' to ' filename '
Reference: http://ss64.com/ora/tablespace_a.html
Http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm
3 Users:
--Create User
Create user mam42 identified by password default tablespace tbs_jsnet; --Specify the password and default table space
--Authorized users
Grant all privilege to MAM42;
Grant Dba,connect to MAM42;
Revoke create table from User1;
Gant Select on table1 to user1;-specify table
Grant all in Space1.table1 to user1;--table all table space (SELECT, Update,delete.)
GRANT Create User,drop user,alter USER, create any VIEW, DROP any view,exp_full_database,imp_full_database,dba,connect,r Esource,create SESSION to User name
--Delete User
Drop user MAM42 cascade;
--Modify User
ALTER USER mam42 DEFAULT role all;--assigns all roles
Alter user MAM42 identified by Password1; --Change Password
Alter user MAM42 account lock;
Alter user MAM42 account unlock;--Resolve table lock and User lock solution
Reference: http://ss64.com/ora/user_c.html
4 characters
--Create a role
Create role Role1;
--Authorization
Grant insert on Tbs_jsnet. TABLE2 to Role1; The information that will be inserted into the table
Grant Role1 to User1; To a user role
Alter user User1 default role1,role2; Modify user Default Roles
--Remove roles
Drop role Role1;

5 Data import and export:
An example of a user of a database that guides all the tables of a user to another database
1. Exp system/[email protected]Owner=username1 file=expfile.dmp
2. Imp ystem/[email protected]Fromuser=username1 touser=username2 ignore=y file=expfile.dmp
Data export:
1 full export of database test, user Name System Password Manager exported to D:\daochu.dmp
Exp system/[email protected]File=d:\daochu.dmp full=y
2 Exporting the system user in the database to the SYS user's table
Exp system/[email protected]File=d:\daochu.dmp owner= (System,sys)
3 Exporting Tables Inner_notify, Notify_staff_relat in the database
Exp aichannel/[email protected]file= d:\datanewsmgnt.dmp tables= (Inner_notify,notify_staff_relat)
4 Export the field filed1 in table table1 in the database with data beginning with "00"
Exp system/[email protected]File=d:\daochu.dmp tables= (table1) query= "where filed1 like ' 0% '"

The above is commonly used for the export, for compression, both with WinZip to the DMP file can be very good compression.
It can also be implemented by adding compress=y to the above command.
Import of data
1 Import the data from the D:\DAOCHU.DMP into the test database.
Imp system/[email protected]File=d:\daochu.dmp
Imp aichannel/[email protected]Full=y file=d:\datanewsmgnt.dmp Ignore=y
There may be a problem, because some tables already exist, and then it is an error, and the table is not imported.
Add Ignore=y to the back.
2 Import the table table1 in D:daochu.dmp
Imp system/[email protected]File=d:\daochu.dmp tables= (table1)


---------------------------------------
Other:
--Querying permissions for objects owned by a user
Select Table_name,privilege from Dba_tab_privs where grantee= ' Xujin ';
--Query the system permissions that a user has
SELECT * from Dba_sys_privs where grantee= ' Xujin '
--------
See if the table is locked
SELECT
A.sid, B.owner, object_name, object_type
From V$lock A, all_objects b
WHERE TYPE = ' TM '
and a.id1 = b.object_id;
So you can kill it.
SELECT sid,serial# from v$session WHERE sid = &sid;
Alter system kill session ' sid,serial# ';
-----------------------------------------------------
First, look at the main memory parameters of Oracle:
Select "NUM", "NAME", "TYPE", "VALUE"/1024 as "KB",
"ISDEFAULT", "isses_modifiable", "issys_modifiable",
"IsModified", "isadjusted", "DESCRIPTION", "Update_comment"
From V$parameter
WHERE NAME in (' Db_block_size ', ' db_cache_size ',
' Java_pool_size ', ' large_pool_size ', ' pga_aggregate_target ',
' Shared_pool_size ', ' sort_area_size ')
------------------------------------
-----Set the default number of logins for the system, which is used to enter the wrong password multiple times and lock the user's workaround
Alter profile DEFAULT limit failed_login_attempts 10;
Alter profile DEFAULT limit failed_login_attempts UNLIMITED;

"ORACLE 10" tablespace, user, restore library, imp,exp, unlock