Oracle Administration
Jump to navigation
Jump to search
Contents
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)'; |