Oracle Administration

From Bitbull Wiki
Jump to navigation Jump to search





1 get DB status info

SQL> SELECT * FROM v$instance ;
SQL> SELECT * FROM v$locks ;
SQL> SELECT * FROM v$status ;
SQL> SELECT * FROM v$database ;
SQL> SELECT * FROM v$datafile ;
SQL> SELECT * FROM v$datafile_header ;
SQL> SELECT * FROM v$logfile ;
SQL> SELECT * FROM v$controlfile ;
SQL> SELECT * FROM v$option ;
SQL> SELECT * FROM v$parameter ;
SQL> SELECT * FROM v$session ;
SQL> SELECT * FROM v$sga ;
SQL> SELECT * FROM v$version ;
SQL> alter database backup controlfile to trace as '/tmp/db-info.trc';

2 recreate DB console

$ emca -deconfig dbcontrol db -repos drop
$ emca -config dbcontrol db -repos create

3 recreate DB console repo

$ emca -repos drop
$ emca -repos create

4 enable/disable encryption in EM

$ emctl secure dbconsole
$ emctl unsecure dbconsole

5 change DB user password

SQL> alter user <username> identified by <s3cret>;


6 reconfigure db control (Oracle Enterprise Manager)

  • ensure listener is running
  • have an entry in tnsnames.ora for the instance

test with: tnsping <alias>

  • have the following information available:
    • the sid name
    • the listener port
    • SYS password
    • DBSNMP password
    • SYSMAN password
  • run emca -repos create
  • run emca -config dbcontrol db

7 change db control to administrate second instance

lsnrctl status
emctl stop dbconsole
export ORACLE_SID=DB-NAME2
emctl start dbconsole

8 some useful comands

To start a session as sysdba: sqlplus sys@tnsname as sysdba;
To start a sysdba session under Windows (9iAS): sqlplus "/as sysdba"
To list all tables in current schema: SELECT TABLE_NAME, NUM_ROWS FROM USER_TABLES;
or, all tables current user has access to: SELECT OWNER, TABLE_NAME, NUM_ROWS FROM USER_TABLES ORDER BY OWNER;
To list all schemas: SELECT username FROM all_users ORDER BY username;
To turn pause on: SET PAUSE ON;
To list top n rows of a table in order: SELECT * FROM (SELECT * FROM t ORDER BY c) WHERE ROWNUM <= n;
Show current database: SELECT * FROM global_name;
Use database: CONNECT schema/password@tnsname;
Show who I am: SHOW USER;
Describe table: DESC tablename;
Set display rows: SET PAGESIZE 66;
Read field constraints: SELECT constraint_name,search_condition FROM user_constraints WHERE table_name='tablename';
Copy table from foreign host to here: COPY FROM user@tnsname CREATE tablename USING SELECT * FROM tablename;
Start SQLPLUS without login: SQLPLUS /NOLOG
Change a user's password: ALTER USER user IDENTIFIED BY password;
Unlock an account: ALTER USER user ACCOUNT UNLOCK;
find the tablespace of a table: SELECT tablespace_name FROM all_tables WHERE table_name = 'YOURTABLENAME';
find total number of records in a table SELECT COUNT(*) FROM tablename;
create structure (no data) of a table from another table CREATE TABLE NEWTABLENAME AS SELECT * FROM OLDTABLENAME WHERE 1=2;
increase the size of a tablespace ALTER DATABASE DATAFILE 'filename' RESIZE nn M;
add data file to tablespace ALTER TABLESPACE tablespacename ADD DATAFILE 'filename' SIZE nn M;
delete archive logs RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'TRUNC(SYSDATE-1)';