thanhtt
15-04-07, 07:52 PM
Oracle General:
1. What are the key environment variables?
Among many others, your home and instance:
export ORACLE_HOME=oracle_home_dir
export ORACLE_SID=instance_name
2. How do you shut down or start up an Oracle instance?
Must log on as a sys user:
sqlplus sys/***@instance as sysdba
Then:
shutdown
or
startup
3. How do you start and stop a listener?
lsnrctl status
lsnrctl start
lsnrctl stop
http://www.psoug.org/reference/listener.html
4. What are the key Oracle files?
They are in network/admin folder.
tnsnames.ora: list of database connection information (client/server)
sqlnet.ora: communication parameters setup
listener.ora: list of databases to listen for on this machine
5. How do you connect to the database to execute queries?
sqlplus user/password@server_instance
On a default system, sometimes you can use scott/tiger.
6. How do you see the errors from your recently created view/procedure?
show errors;
SQL and PL/SQL:
7. How do you output a line from PL/SQL?
DBMS_OUTPUT.PUT_LINE('Hello.');
8. How do you get the current date?
SELECT sysdate FROM dual;
SELECT systimestamp FROM dual;
9. What are some other syntax considerations?
Commands must end with a semi-colon;
Strings must be single-quoted
SQLPlus:
10. How do you show the structure of a table?
desc table
11. How do I re-execute the most recent query/command?
/
12. How do I see my most recent query?
l (for "list")
13. How do I see the PL/SQL procedure output?
SET SERVEROUTPUT ON;
14. How do I execute a SQL file?
@filename.sql
More Advanced:
15. How do I see who is currently connected?
SELECT username, program FROM v$session WHERE username IS NOT NULL;
Or, courtesy of a colleague of mine, something like this:
SELECT p.SPID "OS_PID", to_char(s.SID,'999') SID, s.serial#,
SUBSTR(p.USERNAME,1,10) "OS_USER", SUBSTR(s.USERNAME,1,16) "ORACLE_USER",
SUBSTR(TO_CHAR(s.logon_time, 'DD Month YY "at" HH:MI:SS'),1,30) "LOGON TIME", s.program, s.machine
FROM v$process p, v$session s
WHERE s.PADDR=p.ADDR
AND s.username IS NOT NULL
ORDER BY s.logon_time;
16. How do I find all invalid objects?
Query dba_objects for status = 'INVALID', something like this:
SELECT owner, decode(object_type,'PACKAGE BODY','PACKAGE',object_type) OBJECT_TYPE, count(object_name)
FROM dba_objects WHERE status = 'INVALID' GROUP BY owner, object_type;
17. How do I recompile invalid objects?
Must be logged in with privileges, and use this:
@?/rdbms/admin/utlrp.sql
18. How do I compute table and index statistics for a schema?
Answer from Steve Ensslen:
10g: Don't, it can be set up to analyze itself.
Pre-10g: Must be logged in with privileges:
execute DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
19. How do I analyze the performance of a query/procedure (query plans, index choice, etc)?
Many ways, one way is SQL Trace with TKPROF, which I have explained here:
http://thinkoracle.blogspot.com/2005/09/analyzing-query-performance.html
20. How do I tell which database am I in?
select name from v$database;
or
select instance_name, host_name from v$instance;
or
SELECT SYS_CONTEXT(‘USERENV’,’DB_NAME’) FROM DUAL;
http://thinkoracle.blogspot.com/2005/07/which-instance-am-i-in.html
Bonus:
21. How do I set up an Oracle client?
http://thinkoracle.blogspot.com/2005/06/oracle-client.html
22. How do I get data into and out of Oracle?
http://thinkoracle.blogspot.com/2005/08/import-export.html
(Nguồn: thinkoracle)
1. What are the key environment variables?
Among many others, your home and instance:
export ORACLE_HOME=oracle_home_dir
export ORACLE_SID=instance_name
2. How do you shut down or start up an Oracle instance?
Must log on as a sys user:
sqlplus sys/***@instance as sysdba
Then:
shutdown
or
startup
3. How do you start and stop a listener?
lsnrctl status
lsnrctl start
lsnrctl stop
http://www.psoug.org/reference/listener.html
4. What are the key Oracle files?
They are in network/admin folder.
tnsnames.ora: list of database connection information (client/server)
sqlnet.ora: communication parameters setup
listener.ora: list of databases to listen for on this machine
5. How do you connect to the database to execute queries?
sqlplus user/password@server_instance
On a default system, sometimes you can use scott/tiger.
6. How do you see the errors from your recently created view/procedure?
show errors;
SQL and PL/SQL:
7. How do you output a line from PL/SQL?
DBMS_OUTPUT.PUT_LINE('Hello.');
8. How do you get the current date?
SELECT sysdate FROM dual;
SELECT systimestamp FROM dual;
9. What are some other syntax considerations?
Commands must end with a semi-colon;
Strings must be single-quoted
SQLPlus:
10. How do you show the structure of a table?
desc table
11. How do I re-execute the most recent query/command?
/
12. How do I see my most recent query?
l (for "list")
13. How do I see the PL/SQL procedure output?
SET SERVEROUTPUT ON;
14. How do I execute a SQL file?
@filename.sql
More Advanced:
15. How do I see who is currently connected?
SELECT username, program FROM v$session WHERE username IS NOT NULL;
Or, courtesy of a colleague of mine, something like this:
SELECT p.SPID "OS_PID", to_char(s.SID,'999') SID, s.serial#,
SUBSTR(p.USERNAME,1,10) "OS_USER", SUBSTR(s.USERNAME,1,16) "ORACLE_USER",
SUBSTR(TO_CHAR(s.logon_time, 'DD Month YY "at" HH:MI:SS'),1,30) "LOGON TIME", s.program, s.machine
FROM v$process p, v$session s
WHERE s.PADDR=p.ADDR
AND s.username IS NOT NULL
ORDER BY s.logon_time;
16. How do I find all invalid objects?
Query dba_objects for status = 'INVALID', something like this:
SELECT owner, decode(object_type,'PACKAGE BODY','PACKAGE',object_type) OBJECT_TYPE, count(object_name)
FROM dba_objects WHERE status = 'INVALID' GROUP BY owner, object_type;
17. How do I recompile invalid objects?
Must be logged in with privileges, and use this:
@?/rdbms/admin/utlrp.sql
18. How do I compute table and index statistics for a schema?
Answer from Steve Ensslen:
10g: Don't, it can be set up to analyze itself.
Pre-10g: Must be logged in with privileges:
execute DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
19. How do I analyze the performance of a query/procedure (query plans, index choice, etc)?
Many ways, one way is SQL Trace with TKPROF, which I have explained here:
http://thinkoracle.blogspot.com/2005/09/analyzing-query-performance.html
20. How do I tell which database am I in?
select name from v$database;
or
select instance_name, host_name from v$instance;
or
SELECT SYS_CONTEXT(‘USERENV’,’DB_NAME’) FROM DUAL;
http://thinkoracle.blogspot.com/2005/07/which-instance-am-i-in.html
Bonus:
21. How do I set up an Oracle client?
http://thinkoracle.blogspot.com/2005/06/oracle-client.html
22. How do I get data into and out of Oracle?
http://thinkoracle.blogspot.com/2005/08/import-export.html
(Nguồn: thinkoracle)