Oralce
Imp help=y; View commands used;
Local export of the remote database DMP file locally;
1: Set an instance name in Tnsnames.ora
Orcl=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.20.110) (PORT = 1521))
(connect_data) = (SERVER = dedicated)
(service_name = ORCL)
2: Under Doc
Data Pump Export
EXPDP Scott/[email protected] Schemas=scott dumpfile=expdp.dmp directory=dpdata1;
Data Pump Import
Sql>sqlplus/as Sysdba;
Create directory PRD as ' E:/JZJK ';
Grant Read,write on directory PRD to PRDJZJK;
IMPDP Scott/[email protected] Schemas=scott dumpfile=expdp.dmp DIRECTORY=PRD;
Prototype export
EXP User name/password @orcl owner= user file= file name. dmp
Exp Prdjzjk/[email protected] owner=prdjzjk file= ' e:/prdjzjk/prdjzjk.dmp '
logfile= ' E:/prdjzjk/prdjzjk.log ';
Exp Unable to export empty table solution
1>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>
Set serveroutput on;
Declare
Cursor foremptytable
Is
Select Tname from tab t where
Not EXISTS (select 1 from dba_segments s where t.tname = S.segment_name)
and T.tabtype = ' TABLE ';
Tname VARCHAR2 (50);
TSQL VARCHAR2 (2000);
Begin
Open foremptytable;
Loop
Fetch foremptytable into tname;
Exit when Foremptytable%notfound;
Begin
tsql:= ' ALTER TABLE ' | | tname| | ' allocate extent ';
Dbms_output.put_line (TSQL);
EXECUTE IMMEDIATE ' | | tsql| | ";
exception
When others then null;
End
End Loop;
Close foremptytable;
Commit
End
2>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>
Setting the Deferred_segment_creation parameter
Show Parameter Deferred_segment_creation
Sql> alter system set DEFERRED_SEGMENT_CREATION=FALSE;
Modify before creating a table ....
EXP Export
Exp Prdjzjk/[email protected] owner=prdjzjk file= ' e:/prdjzjk/prd21.dmp ';
IMP Import
Imp prdjzjk/[email protected] file= ' e:/prdjzjk/prd21.dmp ' full=y;
CREATE TABLE Space
sql> Create tablespace jzjk_space datafile ' F:\app\Administrator\oradata\orcl\JZJK. DBF ' size 100M autoextend on NEXT 20M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
Delete Table space
DROP tablespace tablespace_name including CONTENTS and datafiles;
Table space that the query table belongs to
SELECT * from User_tables;
Modify table-owned table space
Sql:alter table Spaceone.tablename move tablespace spacetwo;
Explanation: The above statement is to move the TableName table from Spaceone to Spacetwo.
Remark One:
The current user must have operation permissions on Spacetwo, Spaceone.
Remark Two:
In fact, if you have permissions on two tablespaces, you can
Create Spacetwo.tablename as SELECT * from Spaceone.tablename;
Querying table Spaces
. View all table space sizes
sql> Select Tablespace_name,sum (bytes)/1024/1024 from Dba_data_files Group by Tablespace_name; (data files)
2. The size of the table space that has been idle
sql> Select Tablespace_name,sum (bytes)/1024/1024 from Dba_free_space Group by Tablespace_name; (free table space)
3. So the use of space can be calculated like this
Select A.tablespace_name,total,free,total-free used from
(select Tablespace_name,sum (bytes)/1024/1024 Total from Dba_data_files
Group by Tablespace_name) A,
(select Tablespace_name,sum (bytes)/1024/1024 free from Dba_free_space
Group BY Tablespace_name) b
where A.tablespace_name=b.tablespace_name;
4. The following statement looks at the size of all segment.
Select segment_name,sum (bytes)/1024/1024 from User_extents Group by segment_name
Query which tables have no space allocated
SELECT * from tab t
Where NOT EXISTS
(SELECT * from Dba_segments b where b.segment_name=t.tname)
and t.tabtype= ' TABLE ';
"", "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "In" "" "" "" ""
Oracle Permissions
Permissions allow users to access objects or execute programs that belong to other users, and the Oracle system provides three permissions: Object-level, system-level, role-level. These permissions can be granted to a user, to a special user, to public, or to a role, and if a privilege is granted to a special user "public" (the user public is predefined by Oracle and each user has the permissions that the user has), it means that the permission is granted to all users of that database.
For administrative permissions, a role is a tool, and permissions can be granted to a role, and a role can be granted to another role or user. The user can inherit permissions through the role, except for the role service, which has no other purpose than administrative permissions. Permissions can be granted, or they can be revoked in the same way.
Permissions classification
1, System permissions: The system specifies the user permission to use the database. (System permissions are for users).
2, Entity permissions: Some kind of permissions users access to other users ' tables or views. (For a table or view).
System Rights Management
System Permissions Classification:
DBA: Has full privileges, is the highest system privilege, and only the DBA can create the database structure.
RESOURCE: A user with RESOURCE permission can only create entities and cannot create a database structure.
Connect: A user with connect permission can only log on to Oracle, not create an entity, and cannot create a database structure.
For normal users: Grant Connect, resource permissions.
For DBA administration users: Grant Connect,resource, dba authority.
System Permission Authorization command:
System permissions can only be granted by the DBA User: sys, system (only two users at the beginning)
Authorization Command:sql> Grant Connect, resource, DBA to username 1 [, user name 2] ...;
Note: An ordinary user can have the same user rights as the system through authorization, but never the same permissions as the SYS user, and the permissions of the system user can also be reclaimed.
Cases:
Sql> Connect System/manager
sql> Create User User50 identified by USER50;
Sql> Grant Connect, resource to User50;
Query where the user has permissions:
Sql> select * from Dba_role_privs;
Sql> select * from Dba_sys_privs;
Sql> select * from Role_sys_privs;
Find out what system permissions you have
Sql> select * from Session_privs;
Delete User
Sql> drop user username cascade; Plus cascade the user with everything they created.
System Permission Pass:
Add the WITH ADMIN option option, then the resulting permissions can be passed.
sql> Grant Connect, Resorce to user50 with admin option; You can pass the permissions you have received.
System Rights Reclamation: System permissions can only be reclaimed by DBA users
sql> Revoke Connect, resource from User50;
Description
1) If you use the WITH ADMIN option to grant system permissions to a user, canceling the user's system permissions does not cascade the same permissions for all users who are granted the same permissions by the user.
2) There is no cascade of system permissions, that is, a grants B permission, b grants C permissions, if a withdraws the permissions of B, the permissions of C are not affected, and the system permissions can be reclaimed across users, that is, a can directly reclaim the rights of C users.
Entity Rights Management
Entity Permissions Classification
Select, UPDATE, INSERT, ALTER, INDEX, DELETE, all//all include all permissions
Execute//execute Stored procedure permissions
User01:
Sql> Grant SELECT, UPDATE, insert on product to User02;
Sql> grant all on product to User02;
User02:
Sql> select * from User01.product;
At this time user02 check user_tables, not including user01.product this table, but if check all_tables can be found, because he can access.
To grant the operation permissions of the table to all users:
Sql> grant all on product to public; Public means all users, and the all permission here does not include drop.
Entity Permission data Dictionary
Sql> Select owner, table_name from All_tables; Tables that users can query
Sql> Select table_name from User_tables; User-Created Tables
Sql> Select Grantor, TABLE_SCHEMA, table_name, privilege from All_tab_privs; Tables (authorized) to which access is granted
Sql> Select grantee, owner, TABLE_NAME, privilege from User_tab_privs; Grant permission to the table (granted permission)
DBA users can manipulate any base table for all users (without authorization, including deletion):
DBA User:
sql> Create Table Stud02.product (
ID Number (10),
Name VARCHAR2 (20));
sql> drop table stud02.emp;
Sql> CREATE TABLE Stud02.employee
As
SELECT * from Scott.emp;
Entity permission Pass (with GRANT OPTION):
User01:
Sql> Grant SELECT, update on product to USER02 with GRANT option; User02 is granted permission and can be passed.
Entity Permission Reclamation:
User01:
Sql>revoke Select, update on product from User02; The passed permissions are all lost.
Description
1) If you cancel the object permissions for a user, the same permissions are also canceled for users who grant permissions with GRANT option, which means that they are cascaded when the authorization is canceled.
Oracle Role Management
A role is a set of permissions that assigns a role to a user who has all the permissions in that role. System-predefined roles are some of the most commonly used roles that the system automatically creates after the database is installed. The following is a brief introduction to these predetermined roles. The permissions that the role contains can be queried using the following statement:
Sql>select * from Role_sys_privs where role= ' role name ';
CONNECT, RESOURCE, DBA: These pre-defined roles are primarily for backwards compatibility. It is mainly used for database management. Oracle recommends that users design their own database management and security rights planning instead of simply using these predefined roles. In future releases, these roles may not be used as predefined roles.
Delete_catalog_role, Execute_catalog_role, Select_catalog_role: These roles are primarily used to access data dictionary views and packages.
Exp_full_database, Imp_full_database: These two roles are used for the data Import Export tool.
Aq_user_role, AQ_ADMINISTRATOR_ROLE:AQ:Advanced Query. These two roles are used for Oracle advanced query functionality.
Snmpagent: For Oracle Enterprise Manager and Intelligent Agent
Recovery_catalog_owner: Used to create a user who owns the recovery library. For information on recovering a library, refer to the Oracle documentation oracle9i user-managed Backup and Recovery Guide
Hs_admin_role:a DBA using Oracle ' s heterogeneous services feature needs this ROLE to access appropriate tables in the data Dictionary.
Administrative roles
Build a role
sql>create role Role1;
Delegate to role
Sql>grant Create any table,create procedure to role1;
Grant a role to a user
Sql>grant Role1 to User1;
To view the permissions that a role contains
Sql>select * from Role_sys_privs;
Create a password with a role (password must be provided when a role with password is in effect)
Sql>create role Role1 identified by Password1;
Modify role: password is required
Sql>alter role Role1 not identified;
Sql>alter role Role1 identified by Password1;
Set the role that the current user is going to take effect
(Note: What is the concept of role effectiveness?) Assuming that user A has b1,b2,b3 three roles, then if B1 does not take effect, the permissions contained in the B1 are not owned by a, only the role is valid, the role's permissions are applied to the user, the maximum number of valid roles is set by the parameter Max_enabled_roles, and after the user logs on, Oracle assigns all permissions that are directly assigned to the user and permissions in the user's default role to the user. ）
Sql>set role Role1; Make Role1 Effective
Sql>set role Role,role2; Make Role1,role2 effective
Sql>set role Role1 identified by Password1; Use Role1 with password to take effect
Sql>set role All; All roles that use this user are in effect
Sql>set role None; Set all role invalidation
Sql>set role all except role1; All other roles for the user except Role1 are in effect.
Sql>select * from Session_roles; View the roles in effect for the current user.
Modify the specified user, set their default role
Sql>alter user User1 default role role1;
Sql>alter user User1 default role all except Role1;
See Oracle Reference documentation
9. Deleting a role
Sql>drop role Role1;
After the role is deleted, the user who owns the role will no longer have the role, and the corresponding permissions will not be available.
Description
1) cannot use with GRANT option to grant object permissions to a role
2) You can use the with ADMIN OPTION to grant system permissions to the role, not cascade when canceling
The data dictionary tables related to permission security are:
All_tab_privs
All_tab_privs_made
All_tab_privs_recd
Dba_sys_privs
Dba_roles
Dba_role_privs
Role_role_privs
Role_sys_privs
Role_tab_privs
Session_privs
Session_roles
User_sys_privs
User_tab_priv
Oracle's list of System and object permissions
Alter ANY cluster permission to modify any cluster
Alter any index to modify permissions on arbitrary indexes
Alter any role to modify permissions for arbitrary roles
Alter ANY SEQUENCE permission to modify any sequence
Alter any snapshot permissions to modify any snapshot
Alter any TABLE permission to modify any of the tables
Alter ANY trigger permissions to modify arbitrary triggers
Alter cluster Modify the permissions of the owning cluster
ALTER DATABASE permissions to modify databases
Alter procedure modify the owning stored procedure permissions
Alter profile permissions to modify resource restriction summary tables
Alter resource cost set permissions for episode resource overhead
Alter ROLLBACK segment Modify permissions for rollback segments
Alter sequence modify the owning sequence permission
Alter session Modify permissions for a database session
Alter SYTEM permissions to modify database server settings
ALTER TABLE permission to modify the owning table
Alter TABLESPACE permissions to modify table spaces
Alter user to modify permissions for users
Analyze using the Analyze command to parse arbitrary tables, indexes, and clusters in the database
Audit any set auditing options for any database object
Audit system Operation Audit allowed
Backup any table permissions to back up any tables
Become user permissions to switch users state
Commit any table permission to submit tables
Create any cluster permissions for creating clusters for any user
Create any index permissions for creating indexes for any user
Create any procedure permissions for creating stored procedures for any user
Create any sequence permissions for creating a sequence for any user
Create any snapshot permissions for creating snapshots for any user
Create any synonym permissions for creating synonymous names for any user
Create any table permissions for creating tables for any user
Create any trigger permissions for creating triggers for any user
Create any view permissions for creating views for any user
Create cluster permissions for creating clusters for users
Create database link creates permissions for the user
Create PROCEDURE permissions for creating stored procedures for users
Create profile permissions for creating resource restriction summary tables
Create public database link permissions for creating a common db link
Create public synonym permissions for creating a common synonym name
Create role permissions for creating roles
Create rollback segment Creating a rollback segment permission
Create session permissions for creating sessions
Create sequence permissions for creating a sequence for a user
Create snapshot permissions for creating a snapshot for a user
Create synonym permissions for creating synonymous names for users
CREATE TABLE permissions for creating tables for users
Create Tablespace permissions for creating table spaces
Create user permissions for creating users
Create View permissions for creating views for users
Delete any table to remove permissions for any row of tables
Delete any view permission to remove any views row
Delete Snapshot permission to delete a row in a snapshot
Delete Table permissions for the user to delete the row
Delete View permissions for the user to remove the views row
Drop any cluster permission to delete any cluster
Drop any index delete permissions for any indexes
Drop any procedure permission to delete any stored procedure
Drop any role remove permissions for any roles
Drop any sequence permission to delete any sequence
Drop any snapshot permission to delete any snapshot
Drop any synonym permission to delete any synonymous name
Drop any table permission to delete any tables
Drop any trigger permission to delete any trigger
Drop any view permission to delete any views
Drop profile permission to delete resource Limit summary
Drop public cluster permissions to delete common clusters
Drop Public Database link permission to delete a common data link
Drop public synonym permission to remove a common synonym name
Drop rollback segment permissions to delete rollback segments
Drop tablespace permissions to delete tablespaces
Drop user permissions to delete users
Execute any procedure permission to execute any stored procedure
Execute function Execute stored function permissions
Execute Package permissions to execute a storage pack
Execute procedure permissions to execute user stored procedures
Force any transaction manage output permissions for uncommitted transactions
Force transaction to manage output permissions for uncommitted user transactions
Grant any privilege permissions granted to any system privilege
Grant any role grants permissions to any roles
Index Table Indexed permissions
Insert any table permission for inserting rows into any table
Insert snapshot permissions for inserting rows into a snapshot
Insert table permissions for inserting rows into a user table
Insert View permission to insert rows into a user view
Lock any table locks permissions on any tables
Manager tablespace permissions to manage (backup availability) tablespaces
References permissions for table reference tables
Restricted session permissions to create a restricted database session
Select any sequence permission to use any sequence
Select any table permission to use any of the tables
Select snapshot permissions to use snapshots
Select sequence permissions to use user sequences
Select Table permissions for using user tables
Select View permissions for using views
Unlimited tablespace unrestricted access to tablespace size
Update any table to modify the permissions for rows in arbitrary tables
Update snapshot Modify permissions for a row in a snapshot
Update table permissions to modify rows in a user table
Update View permissions to modify rows in a view
Oracle Database Export table space query and Rights Management collation
