Tuesday, July 15, 2008

How, then, do you increase the size of your log files ?

The simple answer is - you can't - you have to create new ones and drop the old ones, with an approach something like the following:


-- See what you have at present
select group#, member from v$logfile;

3 C:\ORACLE\ORADATA\O8I\REDO02.LOG
4 C:\ORACLE\ORADATA\O8I\REDO01.LOG

-- add two new files
alter database add logfile 'c:\oracle\oradata\o8i\redo03.log' size 4M ;

Database altered.

alter database add logfile 'c:\oracle\oradata\o8i\redo04.log' size 4M ;

Database altered.

-- Make sure that the current log file is one of the new ones
alter system switch logfile;

System altered.

alter system switch logfile;

System altered.

-- Now drop the old log files
alter database drop logfile 'c:\oracle\oradata\o8i\redo02.log';

Database altered.

alter database drop logfile 'c:\oracle\oradata\o8i\redo01.log';

Database altered.

There are a couple of variations on dropping log files. The example above works if you are not using Oracle's log file duplexing to keep two copies of each log; however if you are using duplexing you have to use slightly different commands; there are a couple of possibilites listed here:

-- See what you have at present
select group#, member from v$logfile;

3 C:\ORACLE\ORADATA\O8I\REDO02A.LOG
3 D:\ORACLE\ORADATA\O8I\REDO02B.LOG
4 C:\ORACLE\ORADATA\O8I\REDO01A.LOG
4 D:\ORACLE\ORADATA\O8I\REDO01B.LOG
-- add two new files

alter database add logfile (
'c:\oracle\oradata\o8i\redo03a.log',
'd:\oracle\oradata\o8i\redo03b.log'
) size 4M reuse
;


Database altered.

alter database add logfile (
'c:\oracle\oradata\o8i\redo04a.log',
'd:\oracle\oradata\o8i\redo04b.log'
) size 4M reuse
;


Database altered.

-- Make sure that the current log file is one of the new ones
alter system switch logfile;

System altered.

alter system switch logfile;

System altered.

-- Now drop the old log files
-- We get rid of the first group in one show (2a and 2b)
-- then one file (member) from the next group
-- then the second group by specifying the name of the one remaining file.
alter database drop logfile group 3;

Database altered.

alter database drop logfile member 'c:\oracle\oradata\o8i\redo01a.log';

Database altered.
alter database drop logfile 'd:\oracle\oradata\o8i\redo01b.log';

Database altered.

Thursday, July 10, 2008

Basic HTTP authentication in Tomcat

you just need to insert in the <Engine...></Engine> tag of your server configuration file (conf/server.xml) this tag:

  <Realm className="org.apache.catalina.realm.MemoryRealm" />

Once you have done this, you should edit (or create) the conf/tomcat-users.xml file where you will place the name of all the users you wanna give access to, and their "role". A simple example could be this one:
<?xml version='1.0' encoding='utf-8'?>
<tomcat-users>
<role rolename="test"/>
<user username="user" password="pass" roles="test"/>

</tomcat-users>

This has created a role called "test", and a "user" that belongs to that role.

The second step is the standard servlet way to set the authentication, siply adding to your web application descriptor (web.xml) some imformation like, for example:
...
<security-constraint>
<web-resource-collection>
<web-resource-name>
Protected Site
</web-resource-name>

<!-- This would protect the entire site -->
<url-pattern> /* </url-pattern>
<!-- If you list http methods,
only those methods are protected -->

<http-method> DELETE </http-method>

<http-method> GET </http-method>
<http-method> POST </http-method>
<http-method> PUT </http-method>

</web-resource-collection>
<auth-constraint>
<!-- Roles that have access -->
<role-name> test </role-name>

</auth-constraint>

</security-constraint>

<!-- BASIC authentication -->
<login-config>
<auth-method> BASIC </auth-method>
<realm-name> Example Basic Authentication </realm-name>

</login-config>

<!-- Define security roles -->
<security-role>
<description> Test role </description>
<role-name> test </role-name>

</security-role>
..
That should be enough to start...

Tuesday, July 8, 2008

Administration - Session

  • Show all connected users
  • Time since last user activity
  • Sessions sorted by logon time
  • Show user info including os pid
  • Show a users current sql
  • Session status associated with the specified os process id
  • All active sql
  • Display any long operations
  • List open cursors per user


  • Show all connected users
    set lines 100 pages 999
    col ID format a15
    select username
    , sid || ',' || serial# "ID"
    , status
    , last_call_et "Last Activity"
    from v$session
    where username is not null
    order by status desc
    , last_call_et desc
    /

    Time since last user activity
    set lines 100 pages 999
    select username
    , floor(last_call_et / 60) "Minutes"
    , status
    from v$session
    where username is not null
    order by last_call_et
    /
    Sessions sorted by logon time
    set lines 100 pages 999
    col ID format a15
    col osuser format a15
    col login_time format a14
    select username
    , osuser
    , sid || ',' || serial# "ID"
    , status
    , to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
    , last_call_et
    from v$session
    where username is not null
    order by login_time
    /
    Show user info including os pid
    col "SID/SERIAL" format a10
    col username format a15
    col osuser format a15
    col program format a40
    select s.sid || ',' || s.serial# "SID/SERIAL"
    , s.username
    , s.osuser
    , p.spid "OS PID"
    , s.program
    from v$session s
    , v$process p
    Where s.paddr = p.addr
    order by to_number(p.spid)
    /
    Show a users current sql
    Select sql_text
    from v$sqlarea
    where (address, hash_value) in
    (select sql_address, sql_hash_value
    from v$session
    where username like '&username')
    /
    Session status associated with the specified os process id
    select s.username
    , s.sid
    , s.serial#
    , p.spid
    , last_call_et
    , status
    from V$SESSION s
    , V$PROCESS p
    where s.PADDR = p.ADDR
    and p.spid='&pid'
    /
    All active sql
    set feedback off
    set serveroutput on size 9999
    column username format a20
    column sql_text format a55 word_wrapped
    begin
    for x in
    (select username||'('||sid||','||serial#||') ospid = '|| process ||
    ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') current_time,
    sql_address,
    sql_hash_value
    from v$session
    where status = 'ACTIVE'
    and rawtohex(sql_address) <> '00'
    and username is not null ) loop
    for y in (select sql_text
    from v$sqlarea
    where address = x.sql_address ) loop
    if ( y.sql_text not like '%listener.get_cmd%' and
    y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
    end if;
    end loop;
    end loop;
    end;
    /
    Display any long operations
    set lines 100 pages 999
    col username format a15
    col message format a40
    col remaining format 9999
    select username
    , to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
    , time_remaining remaining
    , message
    from v$session_longops
    where time_remaining = 0
    order by time_remaining desc
    /
    List open cursors per user
    set pages 999
    select sess.username
    , sess.sid
    , sess.serial#
    , stat.value cursors
    from v$sesstat stat
    , v$statname sn
    , v$session sess
    where sess.username is not null
    and sess.sid = stat.sid
    and stat.statistic# = sn.statistic#
    and sn.name = 'opened cursors current'
    order by value
    /
    Or alternatively...

    set lines 100 pages 999
    select count(hash_value) cursors
    , sid
    , user_name
    from v$open_cursor
    group by
    sid
    , user_name
    order by
    cursors
    /

    Monday, July 7, 2008

    Perl Connecting to the database ORACLE

    Connecting to different databases requires different techniques. For exhaustive information, be sure to read the documentation that comes with your DBD. This example will cover connecting to Oracle.
    use strict;
    use DBI;

    my $dbh = DBI->connect( 'dbi:Oracle:orcl',
    'jeffrey',
    'jeffspassword',
    );

    The connect string above takes three arguments: a data source name, a username, and a password. The DSN is in the form dbi:DriverName:instance. But how do we know if the connect succeeded or not? First, connect will return a true value on success, untrue otherwise. Second, DBI will place an error message in the package variable $DBI::errstr.

    use strict;
    use DBI;

    my $dbh = DBI->connect( 'dbi:Oracle:orcl',
    'jeffrey',
    'jeffspassword',
    ) || die "Database connection not made: $DBI::errstr";
    $dbh->disconnect();

    Using the disconnect() method will avoid the error "Database handle destroyed without explicit disconnect".

    Options

    The connect() method can take a hash of options. Often-used options include: AutoCommit, which when true will automatically commit database transactions; RaiseError, which tells DBI to croak $DBI::errstr upon errors; and PrintError, which tells DBI to warn $DBI::errstr.

    In this program, we will want to use transactions, so we will turn AutoCommit off, RaiseError on, and leave PrintError at its default of on.

    use strict;
    use DBI;

    my $dbh = DBI->connect( 'dbi:Oracle:orcl',
    'jeffrey',
    'jeffspassword',
    {
    RaiseError => 1,
    AutoCommit => 0
    }
    ) || die "Database connection not made: $DBI::errstr";
    $dbh->disconnect();

    Note that setting AutoCommit off with a database that doesn't support transactions will result in a fatal error.

    Issuing SQL

    Now we are ready to do something useful with our database. There are two ways to get an SQL statement to your database. For queries which return rows, such as SELECT, we will use the prepare method. For other queries, such as CREATE and DELETE, we will use the do method. Let's stick to the latter for now and move on to the former later.

    This program will create an employee table in the database.

    use strict;
    use DBI;

    my $dbh = DBI->connect( 'dbi:Oracle:orcl',
    'jeffrey',
    'jeffspassword',
    {
    RaiseError => 1,
    AutoCommit => 0
    }
    ) || die "Database connection not made: $DBI::errstr";

    my $sql = qq{ CREATE TABLE employees ( id INTEGER NOT NULL,
    name VARCHAR2(128),
    title VARCHAR2(128),
    phone CHAR(8)
    ) };
    $dbh->do( $sql );

    $dbh->disconnect();

    Intermediate

    We have seen how to connect to the database, detect errors, and issue simple SQL statements. Now let's move on to some more useful code.

    SELECT Statements

    The SELECT statement is probably the most often used statement in SQL. To use SELECT, we will first prepare the statement and then execute it. In the following code, the $sth is the statement handle, which we will use to access the result of the SELECT.

    use strict;
    use DBI;

    my $dbh = DBI->connect( 'dbi:Oracle:orcl',
    'jeffrey',
    'jeffspassword',
    {
    RaiseError => 1,
    AutoCommit => 0
    }
    ) || die "Database connection not made: $DBI::errstr";

    my $sql = qq{ SELECT * FROM employees };
    my $sth = $dbh->prepare( $sql );
    $sth->execute();

    $dbh->disconnect();

    The listing above will cause the database to make an execution plan for the statement, then execute that statement. It doesn't actually do anything with the rows returned. In the next listing, we use bind_columns to get the records out of the database. bind_columns binds each column to a scalar reference. When fetch is called, those scalars are filled with the values from the database.

    use strict;
    use DBI;

    my $dbh = DBI->connect( 'dbi:Oracle:orcl',
    'jeffrey',
    'jeffspassword',
    {
    RaiseError => 1,
    AutoCommit => 0
    }
    ) || die "Database connection not made: $DBI::errstr";

    my $sql = qq{ SELECT id, name, title, phone FROM employees };
    my $sth = $dbh->prepare( $sql );
    $sth->execute();

    my( $id, $name, $title, $phone );
    $sth->bind_columns( undef, \$id, \$name, \$title, \$phone );

    while( $sth->fetch() ) {
    print "$name, $title, $phone\n";
    }

    $sth->finish();
    $dbh->disconnect();

    That's a nice program for printing out a company phone book, but how about a WHERE clause? We will use bind_param to prepare an SQL statement one time, and execute it several times very quickly.

    use strict;
    use DBI qw(:sql_types);

    my $dbh = DBI->connect( 'dbi:Oracle:orcl',
    'jeffrey',
    'jeffspassword',
    {
    RaiseError => 1,
    AutoCommit => 0
    }
    ) || die "Database connection not made: $DBI::errstr";

    my @names = ( "Larry%", "Tim%", "Randal%", "Doug%" );

    my $sql = qq{ SELECT id, name, title, phone FROM employees WHERE name LIKE ? };
    my $sth = $dbh->prepare( $sql );

    for( @names ) {
    $sth->bind_param( 1, $_, SQL_VARCHAR );
    $sth->execute();

    my( $id, $name, $title, $phone );
    $sth->bind_columns( undef, \$id, \$name, \$title, \$phone );

    while( $sth->fetch() ) {
    print "$name, $title, $phone\n";
    }
    }

    $sth->finish();
    $dbh->disconnect();

    Advanced

    Transactions

    So far, we haven't done anything that would require transactions, but if we need to issue UPDATE or DELETE statements, we will want to use them. The best way to implement robust transactions with DBI, according to the DBI documentation, is to use eval{...} blocks to trap errors, then use commit or rollback to finish the transaction. That is what we will do in the following listings.

    This program loads four records into our database.

    use strict;
    use DBI qw(:sql_types);

    my $dbh = DBI->connect( 'dbi:Oracle:orcl',
    'jeffrey',
    'jeffspassword',
    {
    RaiseError => 1,
    AutoCommit => 0
    }
    ) || die "Database connection not made: $DBI::errstr";

    my @records = (
    [ 0, "Larry Wall", "Perl Author", "555-0101" ],
    [ 1, "Tim Bunce", "DBI Author", "555-0202" ],
    [ 2, "Randal Schwartz", "Guy at Large", "555-0303" ],
    [ 3, "Doug MacEachern", "Apache Man", "555-0404" ]
    );

    my $sql = qq{ INSERT INTO employees VALUES ( ?, ?, ?, ? ) };
    my $sth = $dbh->prepare( $sql );


    for( @records ) {
    eval {
    $sth->bind_param( 1, @$_->[0], SQL_INTEGER );
    $sth->bind_param( 2, @$_->[1], SQL_VARCHAR );
    $sth->bind_param( 3, @$_->[2], SQL_VARCHAR );
    $sth->bind_param( 4, @$_->[3], SQL_VARCHAR );
    $sth->execute();
    $dbh->commit();
    };

    if( $@ ) {
    warn "Database error: $DBI::errstr\n";
    $dbh->rollback(); #just die if rollback is failing
    }
    }

    $sth->finish();
    $dbh->disconnect();

    Calling Oracle stored procedures

    One question I am frequently asked, in person and via the DBI users' mailing list, is how to call stored procedures using DBD::Oracle. Here I will give examples of varying complexity.

    The program calls a stored procedure with one in parameter and no return value. We assume that the procedure does not call commit. Note the use of positional placeholders in this program. Also note the use of the eval block: if your Oracle procedure raises an exception, it wil be translated into die in your Perl program, and the error message will be placed in $@ and $DBI::errstr.

    use strict;
    use DBI;

    my $dbh = DBI->connect(
    'dbi:Oracle:orcl',
    'jeffrey',
    'jeffspassword',
    {
    RaiseError => 1,
    AutoCommit => 0
    }
    ) || die "Database connection not made: $DBI::errstr";

    eval {
    my $func = $dbh->prepare(q{
    BEGIN
    jwb_function(
    parameter1_in => :parameter1
    );
    END;
    });

    $func->bind_param(":parameter1", 'Bunce'); #positional placeholders are handy!
    $func->execute;

    $dbh->commit;
    };

    if( $@ ) {
    warn "Execution of stored procedure failed: $DBI::errstr\n";
    $dbh->rollback;
    }

    $dbh->disconnect;

    The next program calls a stored function with a return value. To return a value from a function, we bind the placeholder using bind_param_inout. When using this method, we must tell DBD::Oracle how many bytes to allocate for the return value.

    use strict;
    use DBI;

    my $dbh = DBI->connect(
    'dbi:Oracle:orcl',
    'jeffrey',
    'jeffspassword',
    {
    RaiseError => 1,
    AutoCommit => 0
    }
    ) || die "Database connection not made: $DBI::errstr";

    my $rv; #holds the return value from Oracle stored procedure
    eval {
    my $func = $dbh->prepare(q{
    BEGIN
    :rv := jwb_function(
    parameter1_in => :parameter1
    );
    END;
    });

    $func->bind_param(":parameter1", 'Bunce');
    $func->bind_param_inout(":rv", \$rv, 6);
    $func->execute;

    $dbh->commit;
    };

    if( $@ ) {
    warn "Execution of stored procedure failed: $DBI::errstr\n";
    $dbh->rollback;
    }

    print "Execution of stored procedure returned $rv\n";

    $dbh->disconnect;

    Thursday, July 3, 2008

    substr [Oracle SQL]

    substr(string, position)
    substr(string, position, length)
    The position parameter indicates where the substring starts. It's not zero indexed, that is, in order to start with the fourth character, the parameter has to be 4:
    select substr('1234567890',4) from dual;
    4567890
    However, if the position parameter is 0, it is treated like being 1:
    select substr('1234567890',0, 3) from dual;
    123
    select substr('1234567890',1, 3) from dual;
    123
    The parameter can also be negative, in which case it is counted from the right side:
    select substr('1234567890',-4,3) from dual;
    789

    Function Oracle convert

    Description Oracle MS SQL Server
    Smallest integer >= n CEIL CEILING
    Modulus MOD %
    Truncate number TRUNC
    Max or min number or string in list GREATEST,
    LEAST

    Translate NULL to n NVL ISNULL
    Return NULL if two values are equal DECODE NULLIF
    String concatenation CONCAT(str1,str2) str1 + str2
    Convert ASCII to char CHR CHAR
    Capitalize first letters of words INITCAP
    Find string in string INSTR CHARINDEX
    Find pattern in string INSTR PATINDEX
    String length LENGTH DATALENGTH
    Pad string with blanks LPAD,
    RPAD

    Trim leading or trailing chars other than blanks LTRIM(str,chars),
    RTRIM(str,chars)

    Replace chars in string REPLACE STUFF
    Convert number to string TO_CHAR STR, CAST
    Convert string to number TO_NUMBER CAST
    Get substring from string SUBSTR SUBSTRING
    Char for char translation in string TRANSLATE
    Date addition ADD_MONTH or + DATEADD
    Date subtraction MONTHS_BETWEEN or - DATEDIFF
    Last day of month LAST_DAY
    Time zone conversion NEW_TIME
    Next specified weekday after date NEXT_DAY
    Convert date to string TO_CHAR DATENAME, CONVERT
    Convert string to date TO_DATE CAST
    Convert date to number TO_NUMBER(TO_CHAR(d)) DATEPART
    Date round ROUND CONVERT
    Date truncate TRUNC CONVERT
    Current date SYSDATE GETDATE
    Convert hex to binary HEXTORAW CAST
    Convert binary to hex RAWTOHEX CONVERT
    If statement in an expression DECODE CASE ... WHEN
    or COALESCE
    User's login id number or name UID, USER SUSER_ID, SUSER_NAME
    User's database id number or name UID, USER USER_ID, USR_NAME
    Current user USER USER

    Wednesday, July 2, 2008

    Xóa quảng cáo yahoo 8.1

    @ECHO OFF
    TITLE Remove ads from Yahoo Messenger 8

    > %TEMP%.\noYMads.reg ECHO REGEDIT4
    >>%TEMP%.\noYMads.reg ECHO.
    >>%TEMP%.\noYMads.reg ECHO [HKEY_CURRENT_USER\Software\Yahoo\Pager\YUrl]
    >>%TEMP%.\noYMads.reg ECHO "Messenger Ad"="*"
    >>%TEMP%.\noYMads.reg ECHO "Webcam Upload Ad"="*"
    >>%TEMP%.\noYMads.reg ECHO "Webcam Viewer Ad"="*"
    >>%TEMP%.\noYMads.reg ECHO "Webcam Viewer Ad Big"="*"
    >>%TEMP%.\noYMads.reg ECHO "Webcam Viewer Ad Medium"="*"
    >>%TEMP%.\noYMads.reg ECHO "Change Room Banner"="*"
    >>%TEMP%.\noYMads.reg ECHO "Conf Adurl"="*"
    >>%TEMP%.\noYMads.reg ECHO "Chat Adurl"="*"
    >>%TEMP%.\noYMads.reg ECHO "Y Content"="*"
    REGEDIT /S %TEMP%.\noYMads.reg
    DEL %TEMP%.\noYMads.reg

    ATTRIB -R "%PROGRAMFILES%\Yahoo!\Messenger\Cache\urls.xml"
    ECHO "" >"%PROGRAMFILES%\Yahoo!\Messenger\Cache\urls.xml"
    ATTRIB +R "%PROGRAMFILES%\Yahoo!\Messenger\Cache\urls.xml"

    Tuesday, July 1, 2008

    Creating Oracle (Password) Authenticated User


    Dependencies
    user$

    dba_users all_users user_users
    dba_ts_quotas all_ts_quotas user_ts_quotas

    proxy_users - users who can assume the identity of other users

    resource_cost - lists the cost for each resource.

    user_resource_limits - resource limits for the current user

    user_password_limits - password parameters assigned by profile

    v_$session - user session information

    v_$sesstat - user session statistics

    v_$statname - decoded statistic names for v_$sesstat

    System Privileges

    create user

    alter user drop user

    become user - allows grantee to act as any other user
    Default users and passwords col user_name format a30
    col pwd_verifier format a20

    SELECT *
    FROM default_pwd$
    ORDER BY 1;

    Changing DBSNMP Password
    1.0 Stop the standalone dbconsole
    on UNIX/Linux
    $ emctl stop dbconsole
    on Windows stop the OracleDBConsole service or open a DOS Command Window and set the ORACLE_HOME and ORACLE_SID environment variables. Then:
    C:\> emctl stop dbconsole

    2.0 Verify the standalone dbconsole and the emagent are stopped
    on Unix
    $ emctl status dbconsole
    $ emctl status agent
    on Windows
    C:\> emctl status dbconsole
    C:\> emctl status agent

    3.0 Connect to the database as a user with DBA privilege with SQL*Plus
    and execute
    SQL> alter user dbsnmp identified by ;

    4.0 Verify the new password is valid
    SQL> connect dbsnmp/[@database_alias]

    5.0 Go to $ORACLE_HOME/host_sid/sysman/emd
    5.1 Save the file targets.xml to targets.xml.orig
    5.2 Open the file targets.xml and search for the line:
    " ENCRYPTED="TRUE"/>
    Replace the encrypted value by the new password value
    Replace TRUE by FALSE

    6.0 Restart the standalone dbconsole
    on Unix
    $ emctl start dbconsole
    on Windows
    Start the Windows Service OracleDBConsole or
    open a DOS Command Window and type:
    C:\> set ORACLE_SID=
    C:\> set ORACLE_HOME=
    C:\> cd %ORACLE_HOME%/bin
    C:\> emctl start dbconsole

    7.0 Check that the password has been encrypted
    Open the file targets.xml and search for the line:
    " ENCRYPTED="TRUE"/>
    Check that the password VALUE is encrypted
    Check that the value of ENCRYPTED is TRUE

    Changing SYSMAN Password
    1.0 Stop the standalone dbconsole
    on Unix
    $ emctl stop dbconsole
    on Windows
    Stop the Windows Service OracleDBConsole or open a DOS Command Window and type:
    C:\> emctl stop dbconsole

    2.0 Check that the standalone dbconsole is stopped
    on Unix
    $ emctl status dbconsole
    on Windows check the status of the Windows Service OracleDBConsole
    or open a DOS Command Window and type:
    C:\> emctl status dbconsole

    3.0 Connect to the database as a user with DBA privilege with SQL*Plus
    and execute
    SQL> alter user sysman identified by ;

    4.0 Check the new password
    SQL> connect sysman/[@database_alias]

    5.0 Go to $ORACLE_HOME/host_sid/sysman/config
    5.1 Save the file emoms.properties to emoms.properties.orig
    5.2 Edit the file emoms.properties
    a. Search for the line beginning with:
    oracle.sysman.eml.mntr.emdRepPwd=
    Replace the encrypted value by the new password value
    b. Search for the line:
    oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE
    Replace TRUE by FALSE

    6.0 Restart the standalone dbconsole
    on Unix
    $ emctl start dbconsole
    on Windows
    Start the Windows Service OracleDBConsole or open a DOS Command Window and type:
    C:\> emctl start dbconsole

    7.0 Check that the password has been encrypted
    Edit the file emoms.properties
    7.1 Search for the line beginning with:
    oracle.sysman.eml.mntr.emdRepPwd=
    Check that the password is encrypted
    7.2 Search for the line beginning with:
    oracle.sysman.eml.mntr.emdRepPwdEncrypted=
    Check that the value is TRUE

    Create User Authenticated by Password
    Simple Password CREATE USER
    IDENTIFIED BY
    CREATE USER oracle1
    IDENTIFIED BY oracle1;

    SELECT username, password, created, password_versions
    FROM dba_users
    ORDER BY 1;
    Create User with Complex Password CREATE USER
    IDENTIFIED BY ""
    CREATE USER oracle2
    IDENTIFIED BY "N0t!4N0W"

    Include Access To A Default Tablespace

    Thanks Teresa Robinson for the correction

    CREATE USER
    IDENTIFIED BY
    DEFAULT TABLESPACE ;
    SELECT tablespace_name
    FROM dba_tablespaces
    WHERE contents NOT IN ('TEMPORARY', 'UNDO')
    AND tablespace_name NOT IN (
    SELECT tablespace_name
    FROM dba_rollback_segs)
    AND tablespace_name NOT LIKE 'SYS%';

    CREATE USER oracle3
    IDENTIFIED BY oracle3
    DEFAULT TABLESPACE uwdata;

    SELECT username, default_tablespace
    FROM dba_users
    ORDER BY 1;

    Include Access To A Temporary Tablespace
    CREATE USER
    IDENTIFIED BY
    TEMPORARY TABLESPACE ;
    SELECT tablespace_name
    FROM dba_tablespaces
    WHERE contents = 'TEMPORARY';

    CREATE USER oracle4
    IDENTIFIED BY oracle4
    DEFAULT TABLE uwdata
    TEMPORARY TABLESPACE temp;

    SELECT username, default_tablespace, temporary_tablespace
    FROM dba_users
    ORDER BY 1;

    Include Quota On Tablespaces
    CREATE USER
    IDENTIFIED BY
    DEFAULT TABLESPACE
    TEMPORARY TABLESPACE
    QUOTA ON
    QUOTA ON
    QUOTA ON ;
    CREATE USER oracle5
    IDENTIFIED BY oracle5
    DEFAULT TABLESPACE uwdata
    TEMPORARY TABLESPACE temp
    QUOTA 0 ON SYSTEM
    QUOTA 0 ON SYSAUX
    QUOTA UNLIMITED ON uwdata
    QUOTA 10M ON data_med;

    SELECT username, tablespace_name, max_bytes, max_blocks
    FROM dba_ts_quotas
    ORDER BY 1;

    Include Profile

    Follow PROFILE link at page bottom for more information

    CREATE USER
    IDENTIFIED BY
    DEFAULT TABLESPACE
    TEMPORARY TABLESPACE
    QUOTA ON
    PROFILE ;
    SELECT DISTINCT profile
    FROM dba_profiles;

    CREATE USER oracle6
    IDENTIFIED BY "N0Way!"
    DEFAULT TABLESPACE uwdata
    TEMPORARY TABLESPACE temp
    QUOTA 0 ON SYSTEM
    QUOTA 0 ON SYSAUX
    QUOTA UNLIMITED ON uwdata
    PROFILE monitoring_profile;

    SELECT username, profile
    FROM dba_users
    ORDER BY 1;

    Expire the password on creation
    CREATE USER
    IDENTIFIED BY
    DEFAULT TABLESPACE
    TEMPORARY TABLESPACE
    QUOTA ON
    PROFILE
    PASSWORD EXPIRE;
    CREATE USER oracle7
    IDENTIFIED BY oracle7
    DEFAULT TABLESPACE uwdata
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON uwdata
    PASSWORD EXPIRE;

    SELECT username, expiry_date, account_status
    FROM dba_users;

    Lock or unlock the account on creation
    CREATE USER
    IDENTIFIED BY
    DEFAULT TABLESPACE
    TEMPORARY TABLESPACE
    QUOTA ON
    PROFILE
    ACCOUNT ;
    CREATE USER oracle8
    IDENTIFIED BY oracle8
    DEFAULT TABLESPACE uwdata
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON uwdata
    ACCOUNT LOCK;

    SELECT username, lock_date, account_status
    FROM dba_users;

    Other user creation defaults
    set linesize 121

    SELECT username, initial_rsrc_consumer_group, editions_enabled
    FROM dba_users
    ORDER BY 1;

    conn uwclass/uwclass

    col service_name format a20

    SELECT schemaname, service_name
    FROM gv$session
    ORDER BY 1;

    Creating Operating System Authenticated User

    Changes to make for external authentication
    1. Set the initSID.ora parameters:

    remote_os_authent=TRUE
    os_authent_prefix = "OPS$"

    2. Generate a new spfile

    CREATE spfile FROM pfile='initorabase.ora';

    3. Add the following to the sqlnet.ora

    sqlnet.authentication_services = (NTS)

    The syntax for CREATE USER where authentication is performed by the operating system on the server
    CREATE USER IDENTIFIED EXTERNALLY;
    Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:

    CREATE USER ops$oracle IDENTIFIED EXTERNALLY;

    SELECT username, password, external_name
    FROM dba_users
    ORDER BY 1;

    GRANT create session TO ops$oracle;

    Step 2: Create a user in the operating system named oracle if one does not already exist.

    Step 3: Go to command line (terminal window in UNIX, cmd in Windows. Type 'sqlplus' (without the single quotes). You should be connected to the database without having to enter username/password.

    The syntax for CREATE USER where authentication is performed by the operating system on the client
    CREATE USER IDENTIFIED EXTERNALLY;
    Step 1: Connect as system/manager in SQL*Plus and create the Oracle user:

    CREATE USER "PC100\USER" IDENTIFIED EXTERNALLY;

    where PC100 is the name of the client computer. Then

    GRANT CREATE SESSION TO "PC100\USER";

    2 - Create a user in Windows named USER.

    3 - Log on Windows as USER and go to the C:\> command line.

    Type 'sqlplus' (without the single quotes). You should be connected to your database without having to enter any username/password.
    Note: Automatic logins by PC, Apple MacIntosh, and OS/2 users are not secure. Anyone can edit the Oracle configuration file and change their user ID. For security reasons, if users of these systems are logging in over the network, Oracle Corporation strongly recommends you disable the ops$ logins in the listener.ora.
    Group membership in UNIX Operating system accounts that are members of the operating system's DBA group are not required to provide a userid and password when logging in.
    DBA
    Group membership in Windows Operating system accounts that are members of the operating system's ORA_DBA group are not required to provide a userid and password when logging in.
    ORA_DBA

    Alter User
    Change The Password ALTER USER
    IDENTIFIED BY ;
    ALTER USER SYS
    IDENTIFIED BY "N0t!A!Chance";
    View Password Hashes Current Password:

    SELECT name, password
    FROM user$;
    Previous Passwords (requires Profile verify function is active):

    SELECT u.name, h.password, h.password_date
    FROM user$ u, user_history$ h
    WHERE u.user# = h.user#;
    Grant Access To A Tablespace ALTER USER
    QUOTA ON ;
    ALTER USER uwclass
    QUOTA 100K ON XDB;
    Revoke Access From A Tablespace ALTER USER
    QUOTA 0 ON ;
    ALTER USER uwclass
    QUOTA 0 ON XDB;
    Lock An Account ALTER USER ACCOUNT LOCK;
    ALTER USER uwclass ACCOUNT LOCK;
    Unlock An Account ALTER USER ACCOUNT UNLOCK;
    ALTER USER uwclass ACCOUNT UNLOCK;

    Change Password Based on Hash
    ALTER USER ACCOUNT IDENTIFIED BY VALUES ';
    SELECT password
    FROM user$
    WHERE name = 'SCOTT';

    ALTER USER scott IDENTIFIED BY XYZ;

    SELECT password
    FROM user$
    WHERE name = 'SCOTT';

    ALTER USER scott IDENTIFIED BY VALUES 'F894844C34402B67';

    conn scott/tiger

    Proxy Clause
    Grant Proxy with Password ALTER USER GRANT CONNECT THROUGH
    AUTHENTICATED USING PASSWORD;
    ALTER USER app_user GRANT CONNECT THROUGH uwweb
    AUTHENTICATED USING PASSWORD;
    Grant Proxy with Distinguished Name ALTER USER GRANT CONNECT THROUGH
    AUTHENTICATED USING DISTINGUISHED NAME;
    ALTER USER app_user GRANT CONNECT THROUGH uwweb
    AUTHENTICATED USING DISTINGUISHED NAME;
    Grant Proxy with Role ALTER USER GRANT CONNECT THROUGH
    WITH ROLE ;
    ALTER USER app_user GRANT CONNECT THROUGH uwweb
    WITH ROLE CONNECT;

    or

    ALTER USER app_user GRANT CONNECT THROUGH uwweb
    WITH ROLE ALL EXCEPT payroll;
    Grant Proxy based on Authenticating Certificate ALTER USER GRANT CONNECT THROUGH
    AUTHENTICATED USING CERTIFICATE TYPE VERSION ;
    ALTER USER appuser GRANT CONNECT THROUGH uwweb
    AUTHENTICATED USING CERTIFICATE TYPE 'X.509' VERSION '3';
    Drop Proxy User DROP USER REVOKE CONNECT THROUGH ;
    ALTER USER app_user REVOKE CONNECT THROUGH uwweb;

    Drop User
    Drop User Without Objects DROP USER ;
    DROP USER uwclass;
    Drop User With Objects DROP USER CASCADE;
    DROP USER uwclass CASCADE;

    User Related Queries
    View Memory Use for Each User Session SELECT username, value || 'bytes' "Current UGA memory"
    FROM v_$session sess, v_$sesstat sstat, v_$statname sname
    WHERE sess.sid = sstat.sid
    AND sstat.statistic# = sname.statistic#
    AND sname.name = 'session uga memory';
    Active Connected Users SELECT COUNT(*) "ACTIVE USERS"
    FROM v_$session
    WHERE username IS NOT NULL;

    Currently Connected Users
    SELECT SUBSTR(v_$session.USERNAME,1,15) USERNAME,
    SUBSTR(s.status,1,8) STATUS,
    SUBSTR(s.server,1,10) SERVER,
    SUBSTR(s.type,1,10) TYPE,
    SUBSTR(s.event,1,20) "WAIT EVENT",
    DECODE(s.command,
    1,'Create Table',
    2,'Insert',
    3,'Select',
    6,'Update',
    7,'Delete',
    8,'Drop',
    9,'Create Index',
    10,'Drop Index',
    12,'Drop Table',
    17,'Grant',
    26,'Lock Table',
    42,'Alter Session',
    43,'Alter User',
    44,'Commit',
    45,'Rollback',
    s.command) COMMAND
    FROM v_$session s, v_$session_wait w
    WHERE (s.sid = w.sid)
    AND s.username != 'SYS'
    ORDER BY s.username;

    User Information
    set linesize 121
    col username format a10
    col profile format a10
    col "tmp tbs" format a10

    SELECT u.username, u.default_tablespace, u.temporary_tablespace "TMP TBS", u.profile, r.granted_role,
    r.admin_option, r.default_role
    FROM sys.dba_users u, sys.dba_role_privs r
    WHERE u.username = r.grantee (+)
    GROUP BY u.username, u.default_tablespace,
    u.temporary_tablespace, u.profile, r.granted_role,
    r.admin_option, r.default_role;

    Identify Current Session
    SELECT user, osuser
    FROM gv_$session
    WHERE sid = (
    SELECT sid
    FROM gv$mystat
    WHERE rownum = 1);

    Idle Time
    col SID format 999
    col IDLE format a20
    col PROGRAM format a20
    col USERNAME format a20

    SELECT sid, osuser, username, status,
    TO_CHAR(logon_time, 'DAY HH24:MI:SS') LOGON_TIME,
    FLOOR(last_call_et/3600)||':'||
    FLOOR(MOD(last_call_et,3600)/60)||':'||
    MOD(MOD(last_call_et,3600),60) IDLE, program
    FROM v_$session
    WHERE username IS NOT NULL
    ORDER BY last_call_et;

    User Information
    col program format a17

    SELECT sid, serial#, SUBSTR(username,1,10) NAME, SUBSTR(machine,1,10) COMPUTER, command, status, SUBSTR(osuser,1,8) OSUSER, process, program
    FROM v_$session
    ORDER BY name;
    Connection Information set linesize 121

    SELECT sid, authentication_type, osuser, network_service_banner
    FROM gv_$session_connect_info;

    Privileged Users
    SELECT * FROM gv$pwfile_users;

    /* However, for that to be meaningful, you must be using a password file
    to authenticate privileged users. You could instead choose to use O/S
    authentication, in which case it's membership of the relevant O/S group
    that confers 'super user' status on a person (and I've no idea how to code a procedure that would query group memberships for ORA_DBA group (Windows) or dba group (Unix)), if it were actually possible in the first place.

    What's more, the check of gv$pwdfile_users is only relevant if you're
    using an exclusive password file (ie, remote_login_passwordfile in the
    init.ora is set to EXCLUSIVE). If it is instead set to SHARED, then SYS
    is, and can be, the only privileged user. You then typically let the
    relevant people know what SYS's password is... and there really isn't a
    stored procedure which can determine what you happen to have mentioned to assorted members of the DBA team.
    */