New Apple Store In New York City

Apple just opened a new store in New York City on 5th Avenue. It just joined the line of store along with Tiffany and Saks Fifth Avenue. One of my favorite hanging grounds when I was in New York. The entrance to the store is made of glass. The actual store is underground.

I so miss living in New York. :(

Lead User Developement Process

I've been reading up on new developement process and one that caught my eye is "Lead User Development Process" developed at MIT.

Lead users have two characteristics:
1. they expect relatively high benefits
2. their needs are at “the leading edge of the market”

Lead users present strong needs that will become general in the marketplace in the future and hence serve as a need forecasting laboratory for marketing research.

But what's really innovative about this new concept is that lead users can provide new product concept and design data as well. Designing for extreme characters is a technique that takes the opposite approach. Instead of designing for characters that are emotionally shallow, the developement process designs for characters that have exaggerated emotional attitudes.

For example, if you perform a normal requirement analysis to develop a new query tool for Oracle, then you will end up with a query tool that works like any other query tool out there. But if you use a Lead User for requirement analysis, like some admin who experienced fixing the database at 3AM in the morning, then you will end up with an innovative query tool.

Snippet://Oracle/DUAL

Oracle Table DUAL
All Oracle accounts have access to a table called dual. You can query against this table to get the current account, system date/time, and excecute mathematical functions. For example
select user from dual;
select sysdate from dual;
select power(4,3) from dual;

Other Useful Commands
Use "Describe" To Get Table Definition
Use "Column" To Set Column Output Length
Use "Show All" and "Set" To View/Set SQL Editor Internal Variables and Their Values
Use "Truncate" To Delete All Rows of a Table
Use "Alter Table" To Change Table Definition
Adding Constraints to Table Definition
Specifying Primary Key in Table Definition

You can use the describe command on the following views to obtain information on your account.
View Name Description
DICT table names and table description
DICT_COLUMN column names of table names and column description
CAT names of all user's tables, views, synonyms, and sequences
OBJ information on all objects in your account
TABS table information on all user's tables
COLS column information on all user's columns
USER_VIEWS view information on all user's views
SYN synonyms information on all user's synonyms
SEQ sequence information on all user's sequences
USER_CONSTRAINTS constraint information on user's constraints
USER_CONS_COLUMNS column information on user's constraints
IND index information on all user's indices
USER_IND_COLUMNS column information on user's indices

Snippet://Oracle/RecycleBin

Delete With Purge
DROP TABLE table_name PURGE;
DROP INDEX index_name PURGE;
DROP TABLESPACE tablespace_name PURGE;

NOTE: You can’t roll back a PURGE statement

Lookup Recycle Bin
SELECT * FROM USER_RECYCLEBIN;

Recycle Bin Naming Convention
BIN$unique_id$version
unique_id is an unique 26 character across all Oracle
version is a random number set by set by the database

Recover Recycle Bin
FLASHBACK TABLE "BIN$04LhcpnoanfgMAAAAAANPw==$0" TO BEFORE DROP RENAME TO new_table_name;
FLASHBACK TABLE table_name TO BEFORE DROP RENAME TO new_table_name1;
FLASHBACK TABLE table_name TO BEFORE DROP RENAME TO new_table_name2;

which will reinstate the first version of the table to new_table_name1 and the second versions to new_table_name2. The values of the column COL1 in new_table_name1 and new_table_name2 will be 1 and 2 respectively.

Purge Recycle Bin
PURGE RECYCLEBIN; -- Purge Current User Recycle Bin
PURGE USER_RECYCLEBIN; -- Purge Current User Recycle Bin
PURGE DBA_RECYCLEBIN; -- As DBA, purge all

Be Warned...

The un-drop feature brings the table back to its original name, but not the associated objects like indexes and triggers, which are left with the recycled names. Sources such as views and procedures defined on the table are not recompiled and remain in the invalid state. These old names must be retrieved manually and then applied to the flashed-back table.

The information is kept in the view named USER_RECYCLEBIN. Before flashing-back the table, use the following query to retrieve the old names.

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'RECYCLETEST')
AND ORIGINAL_NAME != 'RECYCLETEST';

OBJECT_NAME ORIGINAL_N TYPE
------------------------------ ---------- --------
BIN$04LhcpnianfgMAAAAAANPw==$0 IN_RT_01 INDEX
BIN$04LhcpnganfgMAAAAAANPw==$0 TR_RT TRIGGER

After the table is flashed-back, the indexes and triggers on the table RECYCLETEST will be named as shown in the OBJECT_NAME column. From the above query, you can use the original name to rename the objects as follows:

ALTER INDEX "BIN$04LhcpnianfgMAAAAAANPw==$0" RENAME TO IN_RT_01;
ALTER TRIGGER "BIN$04LhcpnganfgMAAAAAANPw==$0" RENAME TO TR_RT;

One notable exception is the bitmap indexes. When they are dropped, they are not placed in the recycle bin--hence they are not retrievable. The constraint names are also not retrievable from the view. They have to be renamed from other sources.

Snippet://Oracle/Table

Lookup All Table
SELECT * FROM TAB;

Create Table
CREATE TABLE table_name
(
id NUMBER(6,0),
colname1 VARCHAR2(20 BYTE) CONSTRAINT constname_nn NOT NULL ENABLE,
colname2 NUMBER(8,2),
CONSTRAINT t_id_PK PRIMARY KEY ("id") ENABLE,
CONSTRAINT t_col1_UK UNIQUE ("colname1") ENABLE,
CONSTRAINT t_col2_MIN CHECK (colname2>0) ENABLE,
CONSTRAINT t_col_FK FOREIGN KEY ("colname")
REFERENCES other_table_name ("primarycol") ENABLE
);
COMMENT ON COLUMN table_name.colname IS 'something';
CREATE INDEX index_name ON table_name ("colname")

Use "Truncate" To Delete All Rows of a Table
The truncate command will permanently delete all rows from a table. No need for commit.
truncate table table_name;

Use "Alter Table" To Change Table Definition
Use the Alter Table command to change the definition of a table. You can either add another column or change the definition of an existing column.
alter table table_name add (colname varchar2(24));
alter table table_name modify (colname varchar2(32));

Constraints to Table Definition
There are four kinds of constraints
1. not null
2. check value
3. unique
4. foreign key.

To disable a constraint, use the alter table command. To enable a disabled constraint, again use the alter table command. The following examples disables and then re-enables the salary check condition

ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;

Delete Table
DROP TABLE table_name;
DROP TABLE table_name PURGE; -- To delete and purge from the recycle bin

Create Sequence
CREATE SEQUENCE name_seq INCREMENT BY 1 START WITH 1;

How To Use Sequence
INSERT INTO table_name (id,...) VALUES (name_seq.NextVal,...);
INSERT INTO table_name VALUES (name_seq.NextVal,...);

NOTE: This is actually the best way to to use auto increment for a primary key. The other way:

INSERT INTO table_name (id, ...)
(SELECT CASE WHEN MAX(id) IS NULL THEN 1 ELSE MAX(id)+1 END, ... FROM table_name);

won't work because if first session INSERTS something, then another session won't be able to insert anything until session 1 commits transaction. After that when session 2 tries to INSERT, a unique contraints voilation will occur.

Snippet://Oracle/User

Create Tablespace
CREATE TABLESPACE ts_username
DATAFILE '/path/to/oracle/oradata/SID/ts_username.dbf'
SIZE 10M -- Initial Size
AUTOEXTEND ON NEXT 10M -- Increment Size
MAXSIZE 100M; -- Maximum Quota

Create New User
CREATE USER username
IDENTIFIED BY password
DEFAULT TABLESPACE ts_username -- Defaults to SYS if not specified
TEMPORARY TABLESPACE ts_temp
ACCOUNT LOCK -- Account Locked
PASSWORD EXPIRE; -- Force user to change password at next login

Lock/Unlock User Account:
ALTER USER username ACCOUNT LOCK;
ALTER USER username ACCOUNT UNLOCK;

Change User Password
ALTER USER username IDENTIFIED BY newpassword;

Grant User DDL/DML Rights
GRANT CONNECT TO username;
GRANT RESOURCE TO username;
GRANT DBA TO username; -- Make user a DB Administrator
GRANT SELECT ON tablename TO username;
GRANT CREATE TABLE TO username;
GRANT CREATE VIEW TO username;
GRANT CREATE SEQUENCE TO username;

Delete User Including Related Items Excluding Tables/Views/Sequence
DROP USER username CASCADE;

COMMIT/ROLLBACK
COMMIT; -- Commit Changes
ROLLBACK; -- Roll back changes since last commit

Who Am I
SELECT user, uid FROM dual

Find Active Sessions
SELECT sid, serial#, username, TO_CHAR(logon_time,'Month dd hh24:mi:ss')
FROM sys.v_$session;

Kill Session
ALTER SYSTEM KILL SESSION '12,33' -- 12,33 is the sid and serial#

TNSNAMES
local_SID =
(DESCRIPTION =
(ADDRESS = (PROTOCOL= TCP)(Host= hostname.network)(Port= 1521))
(CONNECT_DATA = (SID = remote_SID))
)