Oracle Basic Knowledge

First, the database login

Sqlplus Please enter username:  Enter password:assysdba

Second, the user

Create user

Create User  by password;

Create a user, and specify a table space

Create User  by default tablespace user_data [temporary  tablespace user_temp];  where  [] the content is optional; the user is logged on, and any database objects that are created belong to the user_temp and user_data table spaces, so you don't have to specify a tablespace for each object you create.

Delete User

Drop User user_name Cascade;

User Rights

Grant  to username;     Authorized revokefrom  username;                  Right of withdrawal
more information about users, permissions, and roles can be found on the following pages:http://blog.csdn.net/junmail/article/details/4381287

Modify User Password

Select  from Dba_users;          View User Alter User  by manager;          Alter User  by New Password Connect system /Manager                            can now operate with this password link Alternatively, if the password is complex and contains special characters, use double quotation marks to 

Query All Users

Select *  from All_users;

Three, table space

Create a temporary table space

Create Temporary ' D:\oracle\oradata\Oracle9i\user_temp.dbf '  on Next 50m maxsize 20480m extent management Local];
where [] The content is optional

Create a data table space

Create ' D:\oracle\oradata\Oracle9i\user_data.dbf '  on next 50m maxsize 20480m extent management Local];
where [] The content is optional

Delete Table space

DROP  and datafiles;

View Tablespace (two ways)

DESC Dba_tables; SELECT  from Dba_tablespaces;

Iv. table

Create a table

Create Table  Number (Tenchar(ten)); Where: ID and name are field names; number and char are field types 

Create a table and specify a table space

Create Table  Number (Tenchar(

Inserting data

Insert  into Values ();

Delete a table

Drop table tablename;

V. Status of the database

Query (two ways)

Select status from V$instance; Select

Check Database status

PS grep Oracle View database process

Start and close parameter descriptions

[Close Database]

There are four parameters, meaning the following:

Normal needs to wait for all users to disconnect before performing a shutdown database task, so sometimes it looks like the command is not running, and new connections are not allowed after the command is executed.

Immediate the user is disconnected after waiting for the user to complete the current statement, and does not allow the new user to connect.

Transactional waits for the user to disconnect after completing the current transaction, and does not allow the new user to connect.

Abort does not do any waiting, execution forcibly disconnects and shuts down the database directly

The first three methods do not lose user data, the fourth is used only in the case of unavoidable circumstances.

[Start Database]

Database startup using the startup command, there are three scenarios:

(1) Without parameters, start the DB instance and open the database so that the user can use the database, in most cases, use this method;

(2) with Nomount parameter, only start the database instance, but do not open the database, when you want to create a new database to use, or when you need such a time to use;

(3) with the mount parameter, when the database renaming is used. This time the database is open and ready to use.

Vi. Log Status

Select *  from V$logfile    can view the log's group, status, type, member;Select* from v$log        can view the log of group, thread#, sequence#, Bytes, members, ARC, status, first_change#, First_time;

The values for the common status are:

Current log, which is the log that Oracle is using for writing;

Active activity but not the current log, which is used by Oracle, but contains content that is not checkpoint, that is, if the database shuts down abnormally, the data file needs to be recovered using this set of logs;

Inactive Inactive, the contents of its record have been saved to the data file by checkpoint.

Another archived field indicates whether the log has been archived and can be queried to archive the log by querying V$archived_log.

Alter 　　　　system switch logfile; Toggles the current log alterlogpresent   ; Alter Log　　　　All; Archive all logs
Switching logs in auto-archive mode can cause log archives, and log archives also require switching logs, so the actual effect of the above command may be the same

Seven, monitoring

Lsnrctl start monitor lsnrctl status  View monitor Lsnrctl stop   stop listening

Eight, query

Table Query

Select Count　　　　　　　　(* from TableName;　　　　　　  Find the number of bars for all records in the TableName table Selectcount from TableName;  TableName The number of records in the Coln column in the table, including the duplicate selectcount(distinctfrom　　TableName; tablename The number of records in the Coln column in the table, excluding duplicate Select Max　　　　　　　 from TableName; Find the number of records in the TableName table

Table Space Query

Select  from　　　　　　　　　　　　　　　　　　dba_tablespaces;  See what table spaces are available Select  from　　where tablespace_name='yourtablespace'; See what tables are in the table space note yourtablespace to capitalize

Data file Query

Select　　　　　　　　　　file_name, online_status from    dba_data_files; View the status of a data file Select tablespace_name,file_id, Bytes,file_name from Dba_data_ files;  View the data file name, size, and path

View service Name

Select　　 from Global_name; Service Name

View Port Numbers

"Configration and migration tools--net manager--Local--service naming" see

IX, version number

Inquire

 select  *   v$version; 

The first is the large version of the logo that we most often refer to, which represents the major release version of the database, such as 10g whether it is R1 or R2, the first digit of its version number is always 10; there is a significant difference in functionality between different version numbers.   Second represents the database maintenance release number, which is what we often call R1 or R2. The existing new feature version numbers are: 8.1 (more special), 9.1, 9.2, 10.1, 10.2, and 11.1, 11.2. Starting from 9i each large version has 2 release versions, generally R1 is always less stable (at least 11g before), by introducing a lot of features in the R1, and according to the actual use of users to revise the bug, to R2 release when the new features introduced in R1 has matured, Of course, some features are introduced in R2 in accordance with Oracle's style, and some features may even be disruptive. It is also worth mentioning that the R1 is very unstable the iron law seems to be broken in the 11g, the 11gr1 of the use of the high rate.   Third represents the release version number of Oracle Application Server (ORACLEAS); this bit is always 0 for Oracle database software.   Fourth bit represents the release number of a component. The component referred to here is that we use DBCA to create a database that is selected for installation component, such as Oracle OLAP, Label Security, and so on, depending on the set of component patch sets or interim that are played on the database. Release temporary version, different components may have different component version numbers in the same database. In practice, the release version of the component is generally used to refer to the database software or a database of the patch set patch set, Patch set patches are generally big (1-2gb not uncommon). After patch set to the database software we need to upgrade the component version to the existing database, in layman's case, upgrade the data dictionary to the database, so that the component version in the database is generally consistent, If I upgrade a 10.2.0.1 database to 10.2.0.4, then the component version in this database will also be 10.2.0.4 (not to rule out that you made a special patch for a component after the version of this component is unique, of course this is rare, not recommended). We can understand the version of the component in the database by querying the Dba_server_registry view.   Fifth stands for the platform-related release number. In general, this digit is affected by another patch (not a patch set). After 10g Oracle introduced an update to the patch set released on the basis of a particular patch set (Psu-patch set update), spoken in Chinese is very awkward; with the release of the PSU, Oracle can be centrally fixed for bugs that occur on a particular patch set version, such as 10.2.0.4, a very extensive version of 10g. If a bug is found on the 10.2.0.4, you can merge the fix into the PSU. In general, the PSU will be released annually in 1, 4, 7, October and the most recent PSU release was in April in 2011, containing the 8th PSU of 10.2.0.4, also known as 10.2.0.4.8. Note Oracle will only make a PSU patch for several final releases on a large version, and the current release of the PSU on 10g is 10.2.0.4 and 10.2.0.5, while for 10.2.0.3 no longer has PSU support. The PSU information for the database (DB, not database software) can be queried by querying the Registry$history table. Reference:Http://wenku.baidu.com/view/319a52305a8102d276a22ffa