Commonly used Oracle commands

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
A, Oracle startup and shutdown
1, In the stand-alone environment
To start or shut down the ORACLE system must first switch to the ORACLE user, as follows
su - oracle

a, Start the ORACLE system

>sqlplus / as sysdba

b, Close the ORACLE system
>sqlplus / as sysdba
2, View the current user default tablespace
            SQL>selectusername,default_tablespace from user_users;

        View the current user role
        SQL>select * from user_role_privs;

        View the current user system privileges and privilege level
        SQL>select * from user_sys_privs;
        SQL>select * from user_tab_privs;

    3, Table

            Table view all users
            SQL>select * fromuser_tables;

            View name contains a log character table
            SQL>selectobject_name,object_id from user_objects

            Look at table creation time
            SQL>selectobject_name,created from user_objects whereobject_name=upper('&table_name');

            Look at the size of the table
            SQL>selectsum(bytes)/(1024*1024) as "size(M)" from user_segments

            View on the ORACLE memory area table
            SQL>selecttable_name,cache from user_tables where instr(cache,'Y')>0;

    4, Indexes

            Check the index number and category
            SQL>selectindex_name,index_type,table_name from user_indexes order by table_name;

            The index is the index field
            SQL>select * fromuser_ind_columns where index_name=upper('&index_name');

            Check the index size
            SQL>selectsum(bytes)/(1024*1024) as "size(M)" from user_segments

    5, Serial number

            Check the serial number, last_number is the current value
            SQL>select * fromuser_sequences;

6, View

            The name of the view
            SQL>select view_namefrom user_views;

            To view the select statement to create view
            SQL>setview_name,text_length from user_views;
            SQL>set long 2000;               Note: you can set the set long size according to the view of text_length
            SQL>select text fromuser_views where view_name=upper('&view_name');

    7, Synonym

            To view the name of synonyms
            SQL>select * fromuser_synonyms;

8, Constraint conditions

            A table view constraints
            SQL>selectconstraint_name, constraint_type,search_condition, r_constraint_name
                from user_constraintswhere table_name = upper('&table_name');

            from user_constraintsc,user_cons_columns cc
            where c.owner =upper('&table_owner') and c.table_name = upper('&table_name')
            and c.owner = cc.owner andc.constraint_name = cc.constraint_name
            order by cc.position;

9, Storage function and process

            See the function and status of the process.
            SQL>selectobject_name,status from user_objects where object_type='FUNCTION';
            SQL>selectobject_name,status from user_objects where object_type='PROCEDURE';

            See the function and process of the source code
            SQL>select text fromall_source where owner=user and name=upper('&plsql_name');
10, Check the date
select * from t where to_char(createdate,'yyyy-mm-dd')='20131125';

Three, check the database SQL
1, And the size of the table space

    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, To view the name table space and physical file size

    select tablespace_name, file_id, file_name,
    round(bytes/(1024*1024),0) total_space
    from dba_data_files
    order by tablespace_name;

3, To view the name rollback segment 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, Check the control file

    select name from v$controlfile;

5, To view the log file

    select member from v$logfile;

6, View usage table space

    select sum(bytes)/(1024*1024) as free_space,tablespace_name
    from dba_free_space
    group by tablespace_name;

    (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES"% FREE"

7, View the database object

    select owner, object_type, status, count(*) count# fromall_objects group by owner, object_type, status;

8, View the database version

    Select version FROM Product_component_version
    Where SUBSTR(PRODUCT,1,6)='Oracle';

9, View the database creation date and archiving methods

    Select Created, Log_Mode, Log_Mode From V$Database;
Four, the ORACLE user connection management

With the system administrator, view the current database has several user connection:

SQL> select username,sid,serial# from v$session;

If you want to stop a connection

SQL> alter system kill session 'sid,serial#';

If this command is not, find it UNIX process

SQL> select pro.spid from v$session ses,v$process pro where ses.sid=21 andses.paddr=pro.addr;

Description: 21 is a SID number of connections

And then use the kill command to kill the process.

Five, the use of SQL*PLUS
a, Near to the SQL*Plus
$Sqlplus user name / password

Exit SQL*Plus

b, Get help information in sqlplus
All SQL commands and SQL*Plus command
List a specific command information
SQL>The help command name

c, List structure display command DESCRIBE
SQL>The DESC table name

d, SQL*Plus in the edit command
Display command SQL buffer

Modify the SQL command
First of all to be corrected for changes to the current row
Modify the content using the CHANGE command
SQL>c/The old / New
To confirm the correct

Use the INPUT command to add one or more rows in the SQL buffer
SQL>The input content

e, Calling external system editor
SQL>The edit file name
You can use the DEFINE command to set the system variable EDITOR to change the type of text editor, defined as a line in the login.sql file

f, Run the command file

The commonly used SQL*Plus statement
a, Create table, delete, modify
Create table command format is as follows:
The create table table name (column list),

As the base table to add new column.:
ALTER TABLE ADD (column list)
Example: add a column of Age for the test table, used to store age
    sql>alter table test
        add (Age number(3)),

Modify the column definition command following the base table:
The ALTER TABLE table name
MODIFY (column data type)
Example: the test table in the Count column width is lengthened to 10 characters
    sql>alter atble test
        modify (County char(10)),

b, A delete table format as follows:
The DORP TABLE table name,
Example: Table delete will also delete data in the table and the table definition
sql>drop table test

c, Table space creation, deletion
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Marvin at December 17, 2013 - 1:11 PM