[Switch to]oracle common directives

1, display the current user name Select User from dual; Show user

2, display of course users have what table select * from tab;

3, display when all users of the table select * from User_tables;

4, display when a user can access the table select * from All_tables;

5. Display the user as Scott's table select * from Dba_tables where owner= ' Scott ';

6. Display the status of all user names and Accounts select Username,account_status from Dba_users;

7. Display all user information select * from Dba_users;

8. Unlock the Scott account (locking) alter user Scott accounts unlock (lock);

9, the current user's default table space Select Default_tablespace from Dba_users WHERE username= (select User from dual);

10, display the current database user SELECT username,account_status,default_tablespace,temporary_tablespace from Dba_users;

11. Create a new user edpthw identified by "123456" default tablespace users temporary tablespace temp quota UNL imited on users quota 10m on MYTBS2;

12, to the user authorized grant Connect,resource to EDPTHW;

13, respectively authorized Grant create any table to ' Hmtong ' grant create table to ' Hmtong ' grant DROP any table to ' Hmtong ' grant select any table To "Hmtong"

14. Object Authorization GRANT SELECT on "Hmtong". " A "to" EDPTHW "

15. Cancellation Authorization REVOKE UNLIMITED tablespace from "Hmtong"

16, the cancellation of the object authorization REVOKE Select on "Hmtong". " A "from" EDPTHW "

17. User Password Change

Alter user EDPTHW identified by EDPTHW;

18, authorized SYSDBA to the designated user grant SYSDBA to Hmtong;

19, cancel the specified user's SYSDBA rights revoke SYSDBA from Hmtong;

20, modify the authorization of the system of the properties of an alter system set remote_login_passwordfile=exclusive;

Can SYSDBA login, can authorize the alter system set remote_login_passwordfile=shared Scope=spfile; Only SYSDBA login, cannot authorize alter system set remote_login_passwordfile=none; Cancel

21, authorized to connect to the specified user GRANT "CONNECT" to "Hmtong"; GRANT UNLIMITED Tablespace,connect,resource to Hmtong;

22, query pwfile the user information stored in select * from V$pwfile_users;

I. Several startup modes of Oracle Database

1, Startup Nomount

Non-installation boot, this way startup executable: Rebuild the control file, rebuild the database, read the Init.ora file, start the instance, that is, start the SGA and background process, this boot only need Init.ora files.

2. Startup Mount (dbname)

Installation starts, this way starts under executable: Database log archive, database media recovery, bring data files online or offline, relocate data files, redo log files.

Execute "Nomount", and then open the control file to confirm the location of the data file and the online log file, but the data and log files are not checked for verification at this time.

3. Startup open (dbname)

You can access the data in the database by executing "nomount", then executing "mount", and then opening all the database files, including the redo log file.

4, startup equals the following three commands

Startup Nomount

ALTER DATABASE Mount

ALTER DATABASE Open

5, startup restrict

Constrained mode to start the database, but only a privileged user is allowed to access it, and the following prompt appears when a non-privileged user accesses it:

Error:ora-01035:oracle only allow users with RESTRICTED SESSION permissions to use

6. Startup force

Force start mode, when the database cannot be closed, you can use startup force to complete the database shutdown, first shut down the database, and then execute the normal start database command

7. Startup pfile= parameter file name

Start with the initialization parameter file, read the parameter file, and start the database according to the settings in the parameter file

Example: Startup Pfile=e:\oracle\admin\oradb\pfile\init.ora

8, startup EXCLUSIVE

Exclusive and shared boot

Ii. three ways of shutting down Oracle

1, Shutdown Normal mode to close the database

2, Shutdown immediate

Immediately closes the database, executes the shutdown immediate in SVRMGRL, the database does not shut down immediately, but shuts down (terminates the session, releases the session resource) after Oracle performs some cleanup work, when using

Shutdown you cannot close the database, shutdown immediate can complete the operation of the database shutdown.

3, Shutdown abort

Directly shuts down the database, the session that is accessing the database is abruptly terminated, and if a large number of operations are executing in the database, it takes a long time to restart the database after shutdown abort is executed.

Third, detailed

The Oracle database provides several different ways to start and shut down the database, and this article details the differences between these startup and shutdown methods and their different capabilities.

First, start and close the Oracle database

For most Oracle DBAs, the most common way to start and shut down an Oracle database is in the command-line mode of server Manager. From Oracle 8i, all the functions of the Server Manager are

Concentrated in the sql*plus, that is, from 8i onwards for the database startup and shutdown can be done directly through Sql*plus, and no longer need server Manager, but the system in order to maintain backward compatibility, still retained

Server Manager tools. In addition, the graphical user tool (GUI) of Oracle Enterprise Manager can be used to complete the system startup and shutdown, the graphical user interface instance Manager is very simple, here no longer

Detailed. To start and shut down a database, you must log in as a user with Oracle Administrator privileges, typically logged in as a user with SYSDBA permissions. Generally we use internal users to start and close the database

(The internal user is actually a synonym for the sys user to sysdba the connection). The new version of Oracle database will phase out internal this internal user, so we'd better set the DBA user to have SYSDBA

Permissions.

Second, the database startup (startup)

Starting a database requires three steps:

1. Create an Oracle instance (non-installation phase)

2. Install the database by instance (Installation phase)

3. Open the database (open stage)

In the startup command, different options are available to control the different startup steps of the database.

1, STARTUP Nomount

The Nonount option simply creates an Oracle instance. Reads the Init.ora initialization parameter file, initiates a background process, initializes the system global Zone (SGA). The Init.ora file defines the configuration of the instance, including the size of the memory structure

and the number and type of background processes started. The instance name is set according to Oracle_sid and does not have to be the same as the open database name. When the instance is open, the system displays a list of the structure and size of the SGA memory as follows:

Sql> startup Nomount ORACLE routine has been started. Total System Global area 35431692 bytes Fixed Size 70924 bytes Variable Size 18505728 bytes Database buffers 16777216 byte s Redo buffers 77824 bytes

2. STARTUP MOUNT

The command creates an instance and installs the database, but does not open the database. The Oracle system reads the contents of the control file about the data file and the redo log file, but does not open the file. This open mode is often used in database maintenance

such as renaming the data file, changing the redo log, and opening the archive. In this open mode, in addition to the list of SGA systems can be seen, the system also gives the \ "database load complete \" prompt.

3. STARTUP

This command completes all three steps to create an instance, install an instance, and open a database. At this point the database makes the data files and redo log files online, and typically requests one or more rollback segments. In addition to the system can see the front

All the prompts in the face startup Mount mode will also give a "database already open \" prompt. At this point, the database system is in a normal working state and can accept user requests. If using startup Nomount

or the database Open command mode of startup mount, you must use the ALTER DATABASE command to perform the Open database operation. For example, if you open a database with startup Nomount, you

This means that the instance has been created, but the database is not installed and opened. This is required to run the following two commands for the database to start correctly.

ALTER DATABASE MOUNT; ALTER DATABASE OPEN;

If you start the database with startup mount, you can open the database by running the following command:

ALTER DATABASE OPEN.

4. Other Open mode

In addition to the three types of database open options described earlier, there are other options.

(1) STARTUP RESTRICT

In this way, the database will be opened successfully, but only some privileged users (users with DBA roles) can use the database. This approach is commonly used to maintain databases, such as data import/export operations

Other users are connected to the database operation data.

(2) STARTUP force

This command is actually a synthesis of forcibly shutting down the database (shutdown abort) and starting the database (startup) two commands. This command is only used when the database is closed and the database is not closed.

(3) ALTER DATABASE OPEN READ only;

This command opens the database as read-only after the instance is created and the database is installed. A product database that only provides query functionality can be opened in this way.

Transferred from: http://www.cnblogs.com/NaughtyBoy/p/3180947.html