×
Create a new article
Write your page title here:
We currently have 3,189 articles on s23. Type your article name above or create one of the articles listed here!



    s23
    3,189Articles

    Oracle Notes[edit]

    This is so far a start of a little set of sysadmins beginners guide to Oracle SQL, more ot come... Most of this was just found around the web.

    query Basics[edit]

    Wild cards[edit]

    %

    Logic[edit]

    SELECT

    Matching[edit]

    WHERE

    LIKE

    AND NOT

    Output[edit]

    ORDER BY, eg "SELECT itemname,id FROM schema.table ORDER by item_name"

    Functions[edit]

    count(query), eg "count(SELECT item FROM schema.table WHERE user='test')" distinct query, eg "distinct transactions.transaction_id" in(query), eg "in(select id from schema.table)" in(list) eg "in('item1','item2',(item3')"

    sysdate()

    Global[edit]

    Start a DB instance[edit]

    you can start you DB instance (amusing the DB init files are already set up) more info here

    As the Oracle user
    #> plus
    :> startup
    

    or As the Oracle user

    #> sqlplus /NOLOG
    @> CONNECT / AS SYSDBA
    SYS@DB_INST> startrup
    


    Show current database[edit]

    > SELECT * FROM global_name;


    Show who I am:[edit]

    > SHOW USER;

    Change a user's password[edit]

    > ALTER USER user IDENTIFIED BY password;

    Unlock an account[edit]

    > ALTER USER user ACCOUNT UNLOCK;

    Formating[edit]

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12013.htm http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm

    =Reset a format[edit]

    > COLUMN column_name CLEAR

    Temp clear[edit]

    > COLUMN column_name OFF
    > COLUMN column_name ON

    Formating a column as text[edit]

    > col Col_name for a40
    > COLUMN LAST_NAME FORMAT A20 HEADING 'EMPLOYEE|NAME'
    > COLUMN REMARKS FORMAT A20 WRAP
    > COLUMN REMARKS FORMAT A20 WORD_WRAP
    > COLUMN REMARKS FORMAT A20 TRUNCATE


    Formating a column as $$[edit]

    >COLUMN SALARY FORMAT $9,999,990.99

    Headings[edit]

    > COLUMN column_name HEADING column_heading
    > COLUMN SALARY HEADING 'MONTHLY|SALARY'
    > SET UNDERLINE =
    > SET UNDERLINE '-'

    Set display rows[edit]

    > SET PAGESIZE 66;

    To turn pause on[edit]

    > SET PAUSE ON;

    chosing line width[edit]

    > set lines 200

    chosing number of lines per page[edit]

    ?? what are these two exactly??

    > set pages 200
    > set pagesize 6


    working with Schemas / tables[edit]

    List Schemas[edit]

    > SELECT username FROM all_users ORDER BY username;


    List tablespaces[edit]

    > SELECT tablespace_name FROM dba_tablespaces;


    List tables current user has access to[edit]

    > SELECT table_name FROM all_tables;


    List tables in a Schema[edit]

    > SELECT Table_Name FROM All_Tables where Owner = 'TRANSACTIONS';


    List all tables in current schema[edit]

    > SELECT table_name FROM user_tables;


    Read field constraints[edit]

    Current schema[edit]

    > SELECT constraint_name,search_condition FROM user_constraints WHERE table_name='tablename';

    AnOther schema[edit]

    > SELECT constraint_name,search_condition FROM all_constraints WHERE table_name='tablename' AND owner='schemma_name' ;


    Working with Tables[edit]

    Describe table[edit]

    List a tables coloms and of what data type they are[edit]

    > DESC tablename;


    List content of a table[edit]

    all FROM a local table[edit]

    > SELECT * FROM tablename;

    all FROM a table in schema[edit]

    > SELECT * FROM schema.tablename;

    specific FROM a table in schema[edit]

    > SELECT name,value FROM schema.tablename;
    Cookies help us deliver our services. By using our services, you agree to our use of cookies.
    Cookies help us deliver our services. By using our services, you agree to our use of cookies.