Oracle database Daily Maintenance commands

first, start, close the database

1, start the various modes of the instance

1 start the instance, load and open the database

This is the most common database operation, meaning that an instance has been started and the database has been loaded and opened. This mode allows any valid user to connect to the database and perform typical data access operations. The startup instance then reads the initialization parameters from the default server parameter file location and then loads and opens the database by using the startup command:

Sql> STARTUP

2 Start the instance, do not mount the database

Starting an instance instead of loading the database typically uses the pattern only during the entire database creation process:

Sql> STARTUP Nomount

3 start the instance and load the database

You can start the instance and mount the database, but do not open the database. This mode allows specific maintenance operations to be performed, such as having to mount the database without opening the database in the following tasks.

* Renaming data files

* Add cancel or rename redo log file

* Allow and disable redo log archiving options

* Perform full data for recovery operations

Sql> STARTUP MOUNT

4 restricting access to the database during the startup process

You can start the instance in restricted mode and load and open the database so that only administrators, not ordinary database users, can take advantage of the database. Use this database startup mode when you need to complete one of the following tasks:

* Perform import or export operations on database data

* Perform database mount operations with Sql*loader

* Temporarily block user data for general use

* During a migration process and during the upgrade operation

Sql> STARTUP RESTRICT

5) Force instance to start-use with caution

In some special environments, you may experience problems when you start a database instance. Generally do not force the database to start unless the following conditions exist:

* When the current instance cannot be closed with the SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE, SHUTDOWN transactional command.

* When the instance is started, some problems are encountered.

Sql> STARTUP FORCE

6 Start the instance, load the database, and start the complete media recovery process

If you know that a media recovery process is required, you can start the instance and mount the database for it, and start the recovery process automatically by using the startup command with the Recover option.

STARTUP OPEN RECOVER

2, change the availability of the database

1 loading the database for the instance

sql> ALTER DATABASE MOUNT;

2 Open a closed database

sql> ALTER DATABASE OPEN;

3 Open the database in read-only mode

sql> ALTER DATABASE OPEN READ only;

Open the database in read-write mode

sql> ALTER DATABASE OPEN READ WRITE;

3. Close the database

1 shut down the database in normal mode

New connections are not allowed, waiting for the session to end, waiting for the transaction to end, making a checkpoint, and closing the data file. No instance recovery is required at startup.

Sql> SHUTDOWN NORMAL

2) Close the database in immediate mode

Does not allow new connections, does not wait for the session to end, does not wait for the transaction to end, makes a checkpoint, and closes the data file. Transactions that do not end are automatically rollback. No instance recovery is required at startup.

Sql> SHUTDOWN IMMEDIATE

3 Shut down the database in transaction mode

Do not allow new connections, do not wait for the session to end, wait for the transaction to end, make a checkpoint, and close the data file. No instance recovery is required at startup.

sql> SHUTDOWN Transactional

4 Shutdown the database in terminate mode

Does not allow new connections, does not wait for the session to end, does not wait for the transaction to end, does not checkpoint, and does not close the data file. Automatic instance recovery at startup.

Sql> SHUTDOWN ABORT

Second, user login

1. Start Sql*plus in a way that doesn't connect to the database

# Sqlplus/nolog

2. Log in with the SYSDBA role in the command prompt environment and start the Sql*plus

1)

# Sqlplus/nolog

Sql> Connect/as SYSDBA

2)

# Sqlplus/as SYSDBA

3, log in as a user, and start Sql*plus

1)

# Sqlplus Jsam/jsam123

2)

# Sqlplus JSAM/JSAM123@ORCL

3)

# Sqlplus/nolog

Sql> Conn jsam/jsam123

4)

# Sqlplus/nolog

Sql> Conn JSAM/JSAM123@ORCL

Wait a minute...

third, security management

1, User management

1 Create the user account name is Jsam, the password is jsam123, uses the database authentication method. The default table space is users, temporary tablespace is temp, where the use of table space users is limited to 500K, which means that you can use up to 500K of table space:

Sql> create user Jsam identified by jsam123

Default Tablespace users

Temporary Tablespace Temp

Quota 500k on users;

Each user should be assigned a default table space. If you do not specify a table space when you create a user, the system tablespace is used as the default table space. The system table space contains a data dictionary and is often used by Oracle. Placing multiple user objects in the same table space can result in degraded database system performance due to disk competition.

If you do not limit the user's use of the table space limit, you can add quota 500k on users.

After you create a user, you will be authorized at the same time, usually authorize users to connect to the database, CREATE TABLE/sequence/process/package/function/View permissions:

Sql> Grant Connect,resource,create Viewto Jsam;

2) Modify the password

sql> alter user Jsam identified by "123456";

3) Delete User

* Delete User Jsam

sql> drop user Jsam;

* If you delete a user and also delete the database objects (such as tables, indexes, clusters, views, and so on) that the user owns, you can use the DROP statement with Cascade

sql> drop user Jsam cascade;

4 Lock and unlock user account

* Lock user account

sql> ALTER user Jsam account lock;

* Unlock user account

sql> alter user Jsam account unlock;

5) View Current user identity

Sql> Show User

6) Query User information

* View all account information, user ID, and creation time that the current user can access:

Sql> select * from All_users;

* View the current database all user account information, configuration files and status:

Sql> Select Username, profile, Account_statusfrom dba_users;

* Query a user's table space usage limit (if no limit is specified, return no rows selected):

Sql> SELECT * from Dba_ts_quotas where username = ' Jsam ';

7) View user status

Sql> select Username,account_status from Dba_users;

8 User Profile

* View All Configurations

Sql> select * from Dba_profiles;

* View Default profile parameters

Sql> SELECT * from Dba_profiles whereprofile= ' DEFAULT ';

* View Password lifetime configuration

Sql> SELECT * from Dba_profiles whereprofile= ' DEFAULT ' and resource_name= ' password_life_time ';

* View the number of consecutive logon failures that caused a user to be locked out

Sql> SELECT * from Dba_profiles whereprofile= ' DEFAULT ' and resource_name= ' failed_login_attempts ';

* Set Password is valid for "indefinite"

sql> Alter profile default Limitpassword_life_time unlimited;

* Modify continuous login failure Lock number limit

sql> Alter profile default Limitfailed_login_attempts unlimited;

sql> Alter profile default limitfailed_login_attempts 20;

2. Grant and REVOKE system privileges

1 Grant users multiple system permissions

Sql> Grant Create session,create table,createview,create any index,create sequence,create type to appdeveloper1;

2 If you want a user to administer a specific permission, you can specify the WITH GRANT option when granting system permissions.

The user has the following operational capabilities:

* You can grant or revoke the system permissions to any other user in the database;

* You can use the WITH ADMIN option option when granting system permissions to other users.

Sql> grant CREATE table to tableadmin with adminoption;

3 give the user all system privileges (except Selectany dictionary)

Sql> Grant all privileges to admin;

4 Give users DBA authority

Sql> Grant DBA to admin;

5 Revoke the Create type and create sequence system permissions granted to the user Appdeveloper1

Sql> revoke create type, create sequence fromappdeveloper1;

3. Grant and Revoke object permissions

1 Grant all object permissions to the table

Sql> grant all privileges on BookInfo tobooksystemdeveloper;

2) Granting the specified object permissions

Sql> Grant Insert,update,delete,select onauthorinfo to Booksystemdeveloper;

3 If the object is granted permission to use withgrant option, then the authorized person will be able to grant access to the object to other users.

Sql> grant all privileges in BookInfo tobooksystemdeveloper with GRANT option;

4 Revoke the UPDATE, delete object permissions granted to the user:

Sql> revoke update,delete on Authorinfo frombooksystemdeveloper;

4. View permission Information

1 Query the user's own system permissions

Sql> Select privilege, admin_option fromdba_sys_privs where grantee = ' jsam ';

View system permissions for the current user

Sql> select * from User_sys_privs;

2 Query user-owned object permissions

Sql> Select Owner | | '.' || Table_nameobject_name, Privilege, grantable

From Dba_tab_privs

Where grantee = ' Jsam '

ORDER BY owner, TABLE_NAME, privilege;

View object permissions for the current user

Sql> select * from User_tab_privs;

3 Query the permissions that the current session can use

Sql> select * from Session_privs;

5. Role Management

A role is a set of system permissions and object permissions that have a name.

1 connect,resource two roles to users

Sql> Grant Connect, resource to Jsam;

2 View the list of roles enabled by the current session

Sql> select * from Session_roles;

3 View the current user's role list

Sql> select * from User_role_privs;

4 View all roles in the database

Sql> select * from Dba_roles;

5 View the roles that a user has

Sql> Select Granted_role, admin_option

From Dba_role_privs

Where grantee = ' jsam ';

6 View the system permissions that the role connect has

Sql> Select role, Privilege, admin_option

From Role_sys_privs

where role = ' CONNECT ';

7 View the object permissions that the role has

Sql> Select Owner | | '.' || Table_nameobject_name, Privilege, grantable

From Role_tab_privs

where role = ' CONNECT '

ORDER BY owner, TABLE_NAME, privilege;

iv. Transaction Processing

1. Transaction Submission

1) Turn on automatic submission

Sql> set autocommit on;

2) Turn off automatic submission

Sql> set autocommit off;

3) Display Submit command

Sql> commit;

2. Transaction rollback

1 Save the storage point

sql> savepoint Storage Point Name

2 rolling back to a storage point

sql> rollback to Storage point Name

3 rolling back the entire transaction

sql> rollback

v. Object Management

1, display the structure of a table

sql> DESC table_name;

Sql> describe table_name;

2. View objects and Status

To view the current user's functions and stored procedures and status:

Sql> Select Object_name,status from User_objectswhere object_type= ' FUNCTION ';

Sql> Select Object_name,status from User_objectswhere object_type= ' PROCEDURE ';

Object_type can be:

SEQUENCE

PROCEDURE

Lob

PACKAGE

PACKAGE body

TRIGGER

INDEX

TABLE

VIEW

FUNCTION

JAVA CLASS

JAVA SOURCE

TYPE

...

3, view the source code of the object

Can be used to view stored procedures, functions, packages, and other source code

Sql> Select text from All_source where Owner=userand name=upper (' &plsql_name ');

4, view the current user all the table

Sql> select * from User_tables;

Sql> Select table_name from User_tables;

Tabs is synonymous with user_tables, so you can use tabs more succinctly.

5. Compiling objects

sql> alter function function_name compile;

Sql> alter procedure procedure_name compile;

Sql> alter TRIGGER trigger_name compile;

...

6. Lock Object

1 Current all Locked object information

Sql> select * from V$locked_object;

2 query out the locked object, lock mode and the user

Sql> Select B.owner, B.object_name, A.session_id,a.locked_mode

From V$locked_object A, dba_objects b

where b.object_id = a.object_id;

3 session information of the locked object

Sql> Select B.username, B.sid, B.serial#,logon_time

From V$locked_object A, v$session b

where a.session_id = B.sid

Order BY B.logon_time;

Some locks may not be released for a long time, and may need to be forcibly closed:

Syntax: Alter system kill session ' sid,serial# ';

Sql> alter system kill session ' 104,1894 ';

4 query occurrence lock corresponding statement

A

Sql> Select Sql_text

From V$sql

where Hash_value in

(Select Sql_hash_value

From V$session

where Sid in (selectsession_id from V$locked_object));

b

Sql> SELECT DISTINCT Sql_text

From V$sql

where Hash_value in

(Select Sql_hash_value

From V$session

where Sid in (selectsession_id from V$locked_object));

C

Sql> Select Sql_text,count (*)

From V$sql

where Hash_value in

(Select Sql_hash_value

From V$session

where Sid in (selectsession_id from V$locked_object)) group by Sql_text;

Six, table space

1. View the name and default storage parameters of all the table spaces in a database

Sql> Select Tablespace_name "Tablespace",

Initial_extent "Initial_ext",

Next_extent "Next_ext",

Min_extents "Min_ext",

Max_extents "Max_ext",

Pct_increase

From Dba_tablespaces;

2. Create a table space

1 CREATE table Space MySpace, size 100m

sql> Create tablespace myspace datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf ' size 100m;

2 CREATE table Space MySpace, size of 100m, when the space is insufficient to automatically expand by 128K, the maximum is 200m

sql> Create tablespace myspace datafile '/home/oracle/oracle/oradata/ora10/myspace01.dbf ' size 100m autoextend on next128k maxsize 200m;

3, increase the table space

1 increase the table space by increasing the size of the table space data file

sql> ALTER DATABASE datafile '/HOME/ORACLE/ORACLE/ORADATA/ORA10/MYSPACE01.DBF ' resize 120m;

2 Increase the table space by adding data files

Sql> Alter tablespace myspace add datafile '/home/oracle/oracle/oradata/ora10/myspace02.dbf ' size 50M;

4, modify the table space

1) Open Data file automatic expansion

sql> ALTER DATABASE datafile '/HOME/ORACLE/ORACLE/ORADATA/ORA10/MYSPACE01.DBF ' autoextend on;

2 Close the data file for automatic expansion

sql> ALTER DATABASE datafile '/HOME/ORACLE/ORACLE/ORADATA/ORA10/MYSPACE01.DBF ' autoextend off;

5, delete the table space

sql> Drop tablespace MySpace;

6, view the user's default table space

1 View the default table space for the current user

Sql> selectusername,default_tablespace,temporary_tablespace from User_users;

2 View the default table space for a user

Sql> Select Username,default_tablespace from Dba_userswhere lower (username) = ' Jsam ';

View all users and default table space relationships

sql> Select Username,default_tablespace fromdba_users;

3 View the table space of a table

Sql> Select Tablespace_name from All_tables wheretable_name= ' company ';

Sql> Select Tablespace_name from User_tableswhere table_name= ' company ';

7, view the table space physical file name and size

1 View all data files and related tablespaces in a database

Sql> select file_name, blocks, Tablespace_namefrom dba_data_files;

Sql> select file_name, blocks, tablespace_name,autoextensible from Dba_data_files;

2 View the name and size of the table space

Sql> Select T.tablespace_name, round (sum (Bytes/(1024 * 1024)), 0 ts_size

From Dba_tablespaces T, Dba_data_files D

where t.tablespace_name = D.tablespace_name

Group BY T.tablespace_name;

3 View the name and size of the physical file in the table space

Sql> Select Tablespace_name,

FILE_ID,

file_name,

Round (Bytes/(1024 * 1024), 0) Total_space

From Dba_data_files

Order BY Tablespace_name;

4 View the name and size of the physical file in the table space (commonly used)

Format: file_name tablespace total (m) USED (m)%used

Statement:

Sql> Select B.file_name,

B.tablespace_name,

b.bytes/1024/1024 "Total (M)",

(B.bytes-sum (NVL (a.bytes,0)))/1024/1024 "USED (M)",

substr (B.bytes-sum (NVL (a.bytes, 0))/(b.bytes) * 1, 5) "%used"

From Dba_free_space A, dba_data_files b

where a.file_id (+) = b.file_id

Group by B.tablespace_name, B.file_name, b.bytes

Order BY B.tablespace_name;

5 Query the free table space

sql> select sum (bytes)/(1024 * 1024) Asfree_space, Tablespace_name

From Dba_free_space

Group BY Tablespace_name;

6 Query the free table space

Sql> Select Tablespace_name,

COUNT (*) as extends,

Round (sum (bytes)/1024/1024, 2) as "Free (MB)",

SUM (blocks) as blocks

From Dba_free_space

Group BY Tablespace_name;

Seven, view the parameters

1. View initialization parameters

1 View all initialization parameters

Sql> show Parameters;

2 If you only want to query a specific parameter value, then use the command: Show parameters [parameter name].

For example, query control file information:

Sql> Show Parameters Control_files;

2. Query Character Set

1) Querying the current database character set

Sql> Select Userenv (' LANGUAGE ') from dual;

2 View server-side character Set configuration

Sql> select * from V$nls_parameters;

Sql> select * from Nls_database_parameters;

The second statement has a query result that is more than the first statement with a database version of the parameter nls_rdbms_version.

3) client Character set

Sql> select * from Nls_instance_parameters;

Eight, Import export

Exp and IMP can be used either on the client or on the server side.

EXPDP and IMPDP can only be used on the Oracle server and cannot be used on the client side.

1. Export

1) fully export the database

Using the DBA user to completely export the database

sql> exp system/pass123@ora10 file=./jsam.dmp full=y;

2) Export User Jsam table

sql> exp jsam/jsam123@ora10 file=./jsam.dmp compress=nlog=jsam.log;

Compress=n: Indicates that the derived file does not need to be compressed in a block of data

Log=jsam.log: Specify log file

2, Import

1) Import Database

sql> imp jsam/jsam123@ora10 file=./jsam.dmp;

2 when exporting and importing are using different user names, you need to specify Fromuser, Touser

sql> imp jsam/jsam123@ora10 file=./jsam.dmpfromuser=jsam_test touser=jsam ignore=y;

Ignore=y: Indicates that the process of ignoring table creation is simply importing the data from the table into the table. For example, a table already exists, using this parameter to omit the creation of the table and import the data into the database.

3, Data pump Import and Export

1) Create file export directory

# mkdir/home/oracle/dbbackup/tempdump/

2 Database Create logical export directory and authorize to Jsam

sql> Create or replace directory Tempdump as '/home/oracle/dbbackup/tempdump/';

Sql> Grant Read,write on directory tempdump to Jsam;

3 Export Database (export file saved in/home/oracle/dbbackup/tempdump)

# EXPDP JSAM/JSAM123@ORA10 Dumpfile=jsam_dp.dmpdirectory=tempdump

4) Import Database

# IMPDP jsam/jsam123@ora10 dumpfile=jsam_dp.dmpdirectory=tempdump logfile=jsam_dp.log remap_schema= "Jsam": "New username" Remap_tablespace= "Users": "New Table space name"

Description

Remap_schema: This option is used to load all objects of the source scheme into the target scenario. When the source, target user name does not need to be used

Remap_tablespace: Imports all the objects from the source table space into the destination table space. When the source and destination table space names do not need to be used

If you need to import into a database on another server, create a user, tablespace, 1, 2, as appropriate, and then perform the import operation by placing the exported file in the corresponding directory (where the directory can be different from the export).

ix. user connection Management

1, with the system administrator login to view the current database has several user connections

Sql> select username,sid,serial# from V$session;

Sql> Select username,sid,serial# from V$session whereusername=upper (' Jsam ');

2. Close a connection

Syntax: Alter system kill session ' sid,serial# ';

Sql> alter system kill session ' 104,1894 ';

3, query Oracle support the maximum number of connections

Sql> Show parameter processes;

4, modify the maximum number of connections

1) Modify the maximum number of connections

Sql> alter system set PROCESSES=300 scope = SPFile;

2) Create Pfile

Sql> create Pfile from SPFile;

3) Restart the database

5, view the number of connections

1) Query the current number of connections

Sql> Select COUNT (*) from v$session;

2 Query the current number of concurrent connections

Sql> Select COUNT (*) from v$session wherestatus= ' ACTIVE ';

3) Query the number of connections between different users

Sql> Select COUNT (*) from v$session whereusername was not null;

Sql> Select Username, COUNT (username)

From V$session

Where username is not null

Group by username;

4 View the session established by the current database

sql> Select Sid, serial#, username, program, machine,status from V$session;

10. Other Orders

1, tnsping command

1 Verify name Resolution (Nameresolution, of course, Oracle's own network service name)

2 Check whether the remote listener is started (usage: tnsping <address> [<count>])

# tnsping ORA10

# tnsping 192.168.0.103

# tnsping 192.168.0.103 2

2. Execute a SQL script file

sql> start file_name

sql> @ file_name

3, rerun the last run of SQL statements

Sql>/

4. Do not exit Sql*plus, execute an operating system command in Sql*plus

* Switch to the operating system command prompt command: host or!

* Switch back to Sql*plus command: Exit

Example:

Sql> Host

[/home/oracle]pwd

/home/oracle

[/home/oracle]exit

Exit

Sql>

5. Environment variable

1 Display Current environment variables

Command: Show parameter name