Organize your own commands that are common in Oracle

Organize your own commands that are common in Oracle:

ORACLE11G has not supported Isqlplus anymore. Built-in Oracle SQL developer

1-----User Multiple Login error, Locked:

ALTER USER username account UNLOCK;

2-----Modify User password:

alter user system IDENTIFID by manager;

3-----Create/delete users:

Create user Lisi identified by Lisi;

Drop user Lisi cascade;

4.1-----Grant and Reclaim system permissions:

Grant create session to Lisi; login link to database;

Grant unlimited tablespace to Lisi; The table space can be established, but the table cannot be built;

Grant CREATE table to Lisi; After the above two authorizations are successful, grant the list permission to build the tables;

Revoke create session to Lisi;

Revoke create unlimited tablepspace to Lisi;

Revoke CREATE table to Lisi;

###### Grant create session to public;

###### select* from User_sys_privs; querying system permissions owned by the current user

4.2------Grant and Reclaim object permissions:

Grant Select on Test1 to Lisi; grant permissions to query another user table, Grant Delete,update,insert permission to no longer repeat;

Grant all on test1 to Lisi; Granted permissions on table test1;

Grant update (name) on Test1 to Lisi; object permission control to column; insert no longer repeat;

###### query, delete cannot control to the column;

5------permissions, roles, tablespace queries:

SELECT * from System_privilege_map order by name; query all system permissions in Oracle, typically dba;208

SELECT * from dba_roles; query all roles for Oracle: 55

Select distinct privilege from Dba_tab_privs; query Oracle all object permissions; 17

Select Tablespace_name from Dba_tablespaces; Querying the table space of the database;

SELECT * from Dba_role_privs where grantee= ' user name '; query for a user's role note user names are case sensitive

SELECT * from Dba_sys_privs where grantee=dba '; Query the system permissions owned by a role;

or select * from Dba_sys_privs where grantee= ' DBA '; Query the system permissions owned by a role;

6------Run the edit, export the SQL script file:

start e:\oracle\aa.sal; run SQL script;

Edit the SQL script e:\oracle\aa.sql;

Spool E:\oracle\aa.sql; Create a record file, start recording, and then enter the command:

Spool off; record keeping;

7------Use profile to manage user passwords:

Account lockout:

To create a profile:

Create profile Lock_account limit failed_login_attempts 3 Password_lock_time 2, set, three login failed, limited logon 2 days;

Modify Profile:alter user Lisi profile Lock_account;

To unlock an account:

ALTER USER username account UNLOCK;

8------The Establishment savepoint and rollback of the thing processing;

SavePoint AA; Establishment of a conservation point;

Rollback to AA; Roll back to save Point AA;

###### #注意; The rollback condition is that the thing is not committed (commit), if the thing commits, rollback, the hint savepoint does not exist;

9------Paging

9.1-----Based on ROWID page:

SELECT * from Test1 where rowid in

(SELECT ID from

(Select RowNum Rn,rid from

(select rowID rid,cid from Test1 ORDER BY cid DESC)

where ronnum<1000)

where rn>9980)

ORDER BY CID Desc;

9.2-----Analysis Function Paging:

SELECT * FROM

(select T.*,row_number () over (order BY CID DESC) RK from test)

where rk<1000 and rk>9980;

9.3-----Use rownum paging:

SELECT * FROM

(Select A.*,rownum rn from

(select Ename,job from EMP) a where rownum<=10)

where rn>=5;

#######: My own method: SELECT * FROM (select RowNum rn,ename,job from EMP) where rn>5 and rn<=10;

Logical backup and Recovery of-------database:

10.1----Non-interactive export:

1. Export Scenario:

$exp Scott/[email protected] Owner=scott file=e:\oracle_workspace\scott.dmp; export its own scheme;

$exp System/[email protected] owner= (System,scott) file=e:\oracle_workspace\system.dmp;

Note: If you want to export other scenarios, you need DBA authority or have exp_full_database permissions, such as system to export any scheme;

2. Export the database:

$exp Userid=system/[email protected] full=y inctype=complete file=e:\oracle_workspace\oracle.dmp;

Note: Exporting a database means exporting the objects and data in all databases using export, requiring the user to have DBA authority or exp_full_database permissions;

####### #注释: Interactive here does not do a detailed introduction:

In fact, the interactive export is the first input: $exp scott/[email protected], and then prompted to choose the cache, export type, file and so on a series,,,

3. Export the table:

$exp Scott/tiger tables=emp,dept file=e:\oracle_workspace\scott_emp_dept.dmp grants=y

Description: Export two tables emp,dept from Scott users to a file e:\oracle_workspace\scott_emp_dept.dmp

$exp Scott/tiger tables=emp query=/"where job=/' salesman/' and sal/<1600/" File=e:\oracle_workspace\scott_emp_ Salesman.dmp

Description: Add the query Condition job= ' salesman ' and sal<1600 to export EMP in EXP

10.2----Non-interactive import:

1. Import the table:

$imp Userid=scott/[email protected] tables= (EMP) file=e:\oracle_workspace\emp.dmp; import your own tables;

$imp Userid=system/[email protected] tables= (EMP) file=e:\oracle_workspace\scott_emp.dmp; Import tables to other users;

$imp Userid=scott/[email protected] tables= (EMP) file=e:\oracle_workspace\empstrcture.dmp rows=n; The structure of the imported table;

$imp Userid=scott/[email protected] tables= (EMP) file=e:\oracle_workspace\empdata.dmp ignore=y; import data;

2. Import the scheme:

$imp Userid=scott/tiger file=e;\oracle_workspace\scott.dmp; import its own scheme;

$imp Userid=system/manager file=e:\oracle_workspace\system_scott.dmp; To import other scenarios that generally require DBA authority

Limit

3 Import the database:

$imp Userid=system/manager full=y file=e:\oracle_workspace\oracle.dmp;

####### #注释: Interactive here does not do a detailed introduction:

One---------data dictionaries and dynamic performance views:

Select table_name from user_tables; Displays all tables owned by the current user;

Select table_name from all_tables; Displays all tables that the current user can access;

Select table_name from Dba_tables; Displays the database tables owned by all scenarios, generally requiring DBA and system permissions with select any tables to take effect;

--------set up, use table spaces:

Create tablespace data01 datafile ' e:\oracle_workspace\data01.dbf ' size 20m unform 128k;

Create a tablespace called DATA01, and establish a DATA01.DBF data file for the tablespace, with a size of 128K;

CREATE TABLE MyData (deptno number (4), Dname varchar2 () tablespace data01;

Use Tablespace to establish table mydata in table space;

Alter tablespace DATA01 add datafile ' e:\oracle_workspace\test.dbf ' size 20m;

Add data files;

ALTER DATABASE datafile ' E:\ORACLE_WORKSPACE\TEST.DBF ' resize 20m;

Increase the size of 2 data files;

ALTER DATABASE datafile ' E:\ORACLE_WORKSPACE\TEST.DBF ' autoextend on next 10m maxsize 500m;

Set the automatic growth of the file;

------Viewing errors:

Show error, use this command to display verbose errors when entering statements or command prompt errors;

-----Oracle View data files, control files, and log file commands

I. Viewing data files

sql> select name from V$datafile;

Two. View Control files

sql> select name from V$controlfile;

Three. View Log files

Sql> Select member from V$logfile;

Four-View all administrators:

Sql>SELECT * from V$pwfile_users;

------Query for free space first

select Tablespace_name,file_id,block_id,bytes,blocks from Dba_free_space;

------about the transfer of permissions (preferably in the test of your own database), below is the result of my test under orcle11g

I. System permissions

1). For example, SYSDBA grants Scott users the Create session with admin option, and then Scott grants WANGWU permissions to the Create session (without the Add with admin option)

The test results are as follows

1. Wangwu can still log on when SYSDBA revoke create session from Scott, but cannot grant permissions to other users (without the Add with admin option);

2). For example, SYSDBA grants Scott users the Create session with admin option, and then Scott grants WANGWU permissions to the Create session with ADMIN option

1. Wangwu can also grant this permission to other users when SYSDBA revoke create session from Scott.

Two. Object permissions

For example, Scott grants Lisi permissions select on EMP with GRANT option, Lisi grants xiaoming permissions select on EMP with GRANT option

1. When Scott withdraws the permissions revoke select on the EMP from lisi,xiaoming Access, and grant permissions are all retracted.

-----Viewing process information

1), query the background process information initiated from v$process

2), view started several DBWR processes

SELECT * from v$process where program like '%dbw% ';

See the number of DBWR processes: Show parameter the number of DB_WR,DBWR processes has a certain impact on the generation of dirty data.

To modify the number of DBWR processes:

Alter system set DB_WRITER_PROCESSES=3 scope=memory; --"Unable to modify specified initialization parameters" error

Alter system set DB_WRITER_PROCESSES=3 scope=spfile;--modified successfully and will take effect the next time you start

3), view started several arc processes

SELECT * from v$process where program like '%arc% ';

4) Check if there is an archive log:archive log list;

5) View the table space for specific information

SELECT * from Dba_data_files;

6) View table space

SELECT * from V$tablespace;

Automatic memory management in-----11g

I1, automatic memory management, that is amm,automatic memories Management. Allocating a total memory size for Oracle's use as a whole can be done without allocating the size of the SGA and PGA as much as 10g.

The i2, initialization parameter statistics_level is typical or all before amm can be started.

Show parameter statistics_level; --View AMM

Show parameter memory; --See how much memory the entire Oracle occupies

Alter system set memory_max_target=900m;--sets the maximum memory size can be reached

i3, the new initialization parameter Memory_target, defines the overall size of the memory, which is the total size of the SGA plus the PGA.

alter system set memory_target=500m; The value of--memory_target setting is either greater than memory_max_target

I4, the new initialization parameter Memory_max_target defines the maximum achievable value for memory_target.

Alter system set memory_max_target=900m Scope=spfile; --The next launch takes effect, Scope=spfile can't omit or error

i5, note: If you use AMM, the values for Sga_target and pga_aggregate_target should be set to 0.

Show parameter sga_target;

Show parameter page_aggregate_target;

alter system set sga_target=0m;

alter system set pag_aggregate_target=0;

Background process Mman in I6, 11g for automatic memory management.

------Open the switch to show the operation time and show the operation time at the bottom

Set timing on/off;

eg, sql> insert into tb_stu values (' 0001 ', ' Zhangsan ', 24);

1 row inserted

Executed in 0.015 seconds

20. Set the read-only transaction.

Sql> set transaction Read only;

This article is from the "7439523" blog, please be sure to keep this source http://7449523.blog.51cto.com/7439523/1601796

Organize your own commands that are common in Oracle