New Name !
The first is a new name this EA it's named sqlcl for sql command line. However, the binary to start it up is simply sql. Nothing is easier when you need to run some sql than typing 'sql' and hitting enter.
#./sql klrice/klrice@//localhost/orcl SQLcl: Release 4.1.0 Beta on Fri Jan 30 12:53:05 2015 Copyright (c) 1982, 2015, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
There's never too much Information
The next is a new information command has a lot more in it now. Hit me up on twitter if you can think of more things it should show some of the things here are directly from folks doing exactly that. Sometimes functional specs can be done in 140 chars.
KLRICE@ORCL>
KLRICE@ORCL>info hr.employees
TABLE: EMPLOYEES
LAST ANALYZED:2015-01-17 12:06:26.0
ROWS :107
SAMPLE SIZE :107
INMEMORY :DISABLED
COMMENTS :employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.
Columns
NAME DATA TYPE NULL DEFAULT COMMENTS
*EMPLOYEE_ID NUMBER(6,0) No Primary key of employees table.
FIRST_NAME VARCHAR2(20 BYTE) Yes First name of the employee. A not null column.
LAST_NAME VARCHAR2(25 BYTE) No Last name of the employee. A not null column.
EMAIL VARCHAR2(25 BYTE) No Email id of the employee
PHONE_NUMBER VARCHAR2(20 BYTE) Yes Phone number of the employee; includes country code and area code
HIRE_DATE DATE No Date when the employee started on this job. A not null column.
JOB_ID VARCHAR2(10 BYTE) No Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.
SALARY NUMBER(8,2) Yes Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)
COMMISSION_PCT NUMBER(2,2) Yes Commission percentage of the employee; Only employees in sales
department elgible for commission percentage
MANAGER_ID NUMBER(6,0) Yes Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)
DEPARTMENT_ID NUMBER(4,0) Yes Department id where employee works; foreign key to department_id
column of the departments table
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS COLUMN_EXPRESSION
HR.EMP_JOB_IX NONUNIQUE VALID JOB_ID
HR.EMP_NAME_IX NONUNIQUE VALID LAST_NAME, FIRST_NAME
HR.EMP_EMAIL_UK UNIQUE VALID EMAIL
HR.EMP_EMP_ID_PK UNIQUE VALID EMPLOYEE_ID
HR.EMP_MANAGER_IX NONUNIQUE VALID MANAGER_ID
HR.EMP_DEPARTMENT_IX NONUNIQUE VALID DEPARTMENT_ID
References
TABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE VALIDATED GENERATED
DEPARTMENTS DEPT_MGR_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
EMPLOYEES EMP_MANAGER_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
JOB_HISTORY JHIST_EMP_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
KLRICE@ORCL>
Info doesn't end there, you can do info+ and get different data and I'm in the process of changing the indexes to show some stats also like the main table.
KLRICE@ORCL>info+ hr.employees
TABLE: EMPLOYEES
LAST ANALYZED:2015-01-17 12:06:26.0
ROWS :107
SAMPLE SIZE :107
INMEMORY :DISABLED
COMMENTS :employees table. Contains 107 rows. References with departments,
jobs, job_history tables. Contains a self reference.
Columns
NAME DATA TYPE NULL DEFAULT LOW_VALUE HIGH_VALUE NUM_DISTINCT HISTOGRAM
*EMPLOYEE_ID NUMBER(6,0) No 100 206 107 NONE
FIRST_NAME VARCHAR2(20 BYTE) Yes Adam Winston 91 NONE
LAST_NAME VARCHAR2(25 BYTE) No Abel Zlotkey 102 NONE
EMAIL VARCHAR2(25 BYTE) No ABANDA WTAYLOR 107 NONE
PHONE_NUMBER VARCHAR2(20 BYTE) Yes 011.44.1343.329268 650.509.4876 107 NONE
HIRE_DATE DATE No 1987.06.17.00.00.00 2000.04.21.00.00.00 98 NONE
JOB_ID VARCHAR2(10 BYTE) No AC_ACCOUNT ST_MAN 19 NONE
SALARY NUMBER(8,2) Yes 2100 24000 57 NONE
COMMISSION_PCT NUMBER(2,2) Yes .1 .4 7 NONE
MANAGER_ID NUMBER(6,0) Yes 100 205 18 NONE
DEPARTMENT_ID NUMBER(4,0) Yes 10 110 11 NONE
Indexes
INDEX_NAME UNIQUENESS STATUS FUNCIDX_STATUS COLUMNS COLUMN_EXPRESSION
HR.EMP_JOB_IX NONUNIQUE VALID JOB_ID
HR.EMP_NAME_IX NONUNIQUE VALID LAST_NAME, FIRST_NAME
HR.EMP_EMAIL_UK UNIQUE VALID EMAIL
HR.EMP_EMP_ID_PK UNIQUE VALID EMPLOYEE_ID
HR.EMP_MANAGER_IX NONUNIQUE VALID MANAGER_ID
HR.EMP_DEPARTMENT_IX NONUNIQUE VALID DEPARTMENT_ID
References
TABLE_NAME CONSTRAINT_NAME DELETE_RULE STATUS DEFERRABLE VALIDATED GENERATED
DEPARTMENTS DEPT_MGR_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
EMPLOYEES EMP_MANAGER_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
JOB_HISTORY JHIST_EMP_FK NO ACTION ENABLED NOT DEFERRABLE VALIDATED USER NAME
KLRICE@ORCL>
Those who don't know history are doomed to repeat.
The last thing to be sure to take a look at is the history. History by itself is great however we wanted to show how often the history is uses so there's now "history usage" which will show how many times that history item has been run. The other thing new here is that instead of timing statements only when user request it, we now time and record that time for everything. You can access is very simply with "history time" that will show the timings of how long the things in the history took. The format of the time is HH:MI:SS.fff and we shorten the leading numbers when zero to be able to read and format it easier.
KLRICE@ORCL>history time .... 20 info s 21 (00.050) select table_name from user_tables 22 info+ s 23 info hr.dept 24 into scott.emp 25 info scott.emp 26 info+ scott.emp 27 info all_tables 28 info all_tab_comments 29 (04.531) select * from all_tables t,all_tab_comments tc where t.owner = tc.owner and t.table_name = tc.table_name 30 (00.420) select * from all_tables t,all_tab_comments tc where t.owner = tc.owner (+) and t.table_name = tc.table_name(+)
While this is 3 really quick things, there's a lot more in there with this new EA. We will blog the other new things more in the coming days.
