Oracle Basic Syntax

First, Oracle's startup and shutdown
1, in a stand-alone environment
To start or shut down an Oracle system, you must first switch to an Oracle user, as follows
Su-oracle
A, start Oracle system
Oracle>svrmgrl
Svrmgr>connect Internal
Svrmgr>startup
Svrmgr>quit
B. Shutting down Oracle Systems
Oracle>svrmgrl
Svrmgr>connect Internal
Svrmgr>shutdown
Svrmgr>quit
To start the oracle9i database command:
$ sqlplus/nolog
Sql*plus:release 9.2.0.1.0-production on Fri Oct 31 13:53:53 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Sql> Connect/as SYSDBA
Connected to a idle instance.
Sql> Startup^c
Sql> Startup
ORACLE instance started.
2, in the dual-machine environment
To start or shut down the Oracle system, you must first switch to the root user, as follows
Su-root
A, start Oracle system
Hareg-y Oracle
B. Shutting down Oracle Systems
Hareg-n Oracle
What kinds of startup methods do Oracle databases have?
Description
There are several ways to start this:
1, Startup Nomount
Non-installation startup, this way to launch executable: Rebuild control file, rebuild database
Read the Init.ora file, start the instance, that is, start the SGA and background processes, this startup requires only Init.ora files.
2, startup Mount DBName
Install startup, this way to start under executable:
Database log archiving,
Database Media Recovery,
Bring the data file online or offline,
Reposition the data file and redo the log file.
Execute "nomount" and then open the control file to confirm the location of the data file and the online log file.
However, the data file and log files are not checked for verification at this time.
3, Startup Open dbname
Execute "nomount" first, then "Mount", and then open all the database files, including the redo log file,
In this way, the data in the database can be accessed.
4, startup, equal to the following three orders
Startup Nomount
ALTER DATABASE Mount
ALTER DATABASE Open
5, startup restrict
Constraint start
This way the database can be started, but only users with certain privileges are allowed to access
When an unprivileged user accesses, the following prompts appear:
ERROR:
Ora-01035:oracle only allow users with restricted session permissions to use
6, Startup force
Forced start mode
When you cannot close the database, you can use startup force to complete the shutdown of the database
Close the database before performing the normal start database command
7, startup pfile= parameter filename
Startup method with initialization parameter file
Read the parameter file first, then start the database by setting in the parameter file
Example: Startup Pfile=e:oracleadminoradbpfileinit.ora
8, startup EXCLUSIVE
How to use data dictionary effectively by users
Oracle's data dictionary is one of the important components of the database, which is produced with the database and changes with the database.
Some tables and views are reflected under the SYS user. The data dictionary name is an uppercase English character.
The data dictionary contains the user information, the user's permission information, all the data object information, the table constraint condition, the statistical Analysis database view and so on.
We can't modify the information in the data dictionary by hand.
Most of the time, the average Oracle user doesn't know how to use it effectively.
Dictionary The name and interpretation of all data dictionary tables, it has a synonym dict
Dict_column All data Dictionary list field names and explanations
If we want to query the index-related data dictionary, we can use the following SQL statement:
Sql>select * from dictionary where InStr (comments, ' index ') >0;
If we want to know the detailed meaning of each field name in the User_indexes table, you can use the following SQL statement:
Sql>select column_name,comments from Dict_columns where table_name= ' user_indexes ';
And so on, you can easily know the detailed name and interpretation of the data dictionary without looking at Oracle's other documentation.
The following categories list the query usage methods for some Oracle users ' common data dictionaries.
1, users
View the default table space for the current user
Sql>select username,default_tablespace from User_users;
View the role of the current user
Sql>select * from User_role_privs;
View system and table-level permissions for the current user
Sql>select * from User_sys_privs;
Sql>select * from User_tab_privs;
2, table
View all tables under the user
Sql>select * from User_tables;
View a table with a name that contains log characters
Sql>select object_name,object_id from User_objects
where InStr (object_name, ' LOG ') >0;
View when a table was created
Sql>select object_name,created from User_objects where Object_name=upper (' &table_name ');
View the size of a table
Sql>select sum (bytes)/(1024*1024) as "size (M)" from user_segments
where Segment_name=upper (' &table_name ');
View the tables placed in Oracle's memory area
Sql>select Table_name,cache from User_tables where InStr (Cache, ' Y ') >0;
3, Index
View the number and category of indexes
Sql>select Index_name,index_type,table_name from User_indexes ORDER by TABLE_NAME;
To view the fields indexed by an index
Sql>select * from User_ind_columns where Index_name=upper (' &index_name ');
Viewing the size of an index
Sql>select sum (bytes)/(1024*1024) as "size (M)" from user_segments
where Segment_name=upper (' &index_name ');
4, serial number
View serial number, Last_number is the current value
Sql>select * from User_sequences;
5, view
View the name of the view
Sql>select view_name from User_views;
View a SELECT statement that creates a view
Sql>set view_name,text_length from User_views;
Sql>set Long 2000; Note: You can set the size of the set long based on the text_length value of the view
Sql>select text from User_views where View_name=upper (' &view_name ');
6. Synonyms
View the name of a synonym
Sql>select * from user_synonyms;
7. Constraint conditions
View constraints on a table
Sql>select constraint_name, Constraint_type,search_condition, R_constraint_name
From user_constraints WHERE table_name = UPPER (' &table_name ');
Sql>select C.constraint_name,c.constraint_type,cc.column_name
From User_constraints c,user_cons_columns cc
where C.owner = Upper (' &table_owner ') and c.table_name = Upper (' &table_name ')
and C.owner = Cc.owner and c.constraint_name = Cc.constraint_name
Order BY Cc.position;
8, storage functions and procedures
View the status of functions and procedures
Sql>select object_name,status from user_objects where object_type= ' FUNCTION ';
Sql>select object_name,status from user_objects where object_type= ' PROCEDURE ';
View source code for functions and procedures
Sql>select text from All_source where Owner=user and Name=upper (' &plsql_name ');
Third, view the SQL of the database
1, view the table space name and size
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;
2, view the table space physical file name and size
Select Tablespace_name, file_id, file_name,
Round (bytes/(1024*1024), 0) Total_space
From Dba_data_files
Order BY Tablespace_name;
3. View rollback segment name and size
Select Segment_name, Tablespace_name, R.status,
(initial_extent/1024) Initialextent, (next_extent/1024) nextextent,
Max_extents, V.curext curextent
From Dba_rollback_segs R, V$rollstat V
Where r.segment_id = V.USN (+)
Order BY Segment_name;
4, view the control file
Select name from V$controlfile;
5. View Log file
Select member from V$logfile;
6, view the use of table space
Select sum (bytes)/(1024*1024) as free_s