You've missed one place to get an overview of Oracle: the Concepts Guide. It covers all the major topics (including backup and recovery, which is quite important and doesn't appear in the list of links you've posted).
Whats the next step? Create the Schema or Tablespace?
Both! They're orthogonal. Users are logical entities that access your database. Tablespaces are a storage concept. A user can have access to multiple tablespaces, and a tablespace can store data from multiple schemas. You need both, and you need to grant access to the appropriate tablespace to the users you create. (See e.g. here for the difference between user and schema.)
Tablespace datafile(s) is where actual data from tables is stored?
Yes, all your database's data and indexes are stored in tablespaces. The main storage structures are:
- Ordinary tablespaces store normal, persistent data. That's going to be the largest part of your database, space-usage wise.
- Temporary tablespaces store non-persistent data - global temporary tables that get purged at the end of sessions or transactions, temporary storage for things like on-disk sorts, etc.
- Undo tablespace(s) and redo log files: that's what Oracle uses to provide ACID guarantees.
- Control files: they describe your database (name, files, log sequence and checkpoint information, even some backup info).
(The system tablespace is an ordinary tablespace, except that you shouldn't store anything in it - consider it as Oracle internal and off-limits for ordinary use.)
In addition, your should take great care of your redo log files, the "most crucial structure for database recovery". They are "hot" (lots of writes) and should be on their own disks/luns.
How many [tablespaces/datafiles] are needed?
As much as you need. There's no general rule here. The number of datafiles will depend on how much data you need to store, operating system limits, Oracle datafile size limits, your storage (hard disks/volumes) constraints, backup/recovery considerations (e.g. having only one humongous Bigfile datafile might not be the best idea), ...
How you structure your tablespaces is up to you too. Having a tablespace per "application" in your tablespace can be good approach to get started. You can always create more tablespaces later if needed (but keep in mind that moving an object from one tablespace to another can be time-consuming, and might require either downtime or pretty complex operations).
Default or Temporary?
Both! You need space to store your data persistently, and you also need some amount of temporary storage for your database's operation.
How much space will I need for it?
Anywhere between a few megabytes and several terabytes – only you can know here. To estimate the space you need for a table, create a table with the same structure, fill it up with some sample data (should be more or less statistically representative of what you'll be storing in it) and measure the space usage. Then extrapolate. Don't forget to include the space required indexes (and materialized views)!
Autoextend?
I'd say yes, use autoextend features, but set limits. You probably shouldn't let Oracle try to autoextend past the actual available space on your filesystems. And monitor space usage. (Keep in mind that datafile extension is relatively costly. Don't set the autoextend size too small.)
For ZFS specifically, Oracle has a whitepaper you might be interested in: Configuring ZFS for an Oracle Database (270k PDF).
If you don't have any backups. Well... You're just flat down.
Nothing to be done.
You might as well drop the entire database and recreate.
Reasons follow:
1.- Corrupt blocks can only be repaired by getting the original block from a full backup (at least of the datafile that has the corrupted blocks) and applying the archivelogs after the original block has been restored to the original location.
2.- No backups, No archivelogs and no exports means you lost your data. There is no way to get it back as you cannot find out the actual storage order of each row once it got corrupted.
3.- If the undo tablespace got corupted as well, you can't even recover a datafile/block because the RDBMS won't find the undo data needed to make it consistent. In the best of cases you could apply forward changes on the redo logs, but you couln't rollback unfinished transactions before the crash.
Next time, as soon as you have a running database, get it to work in archivelog mode, make a full back every once in a while and, to be on the safe side, export the most important data you have there..
Best Answer
In your example, you have Autoextend on with 4M as the next extension, but you also have MAXSIZE set to 8G, which means that Oracle is being told that once a tablespace reaches 8G, it will no longer automatically add space.
This is for safety purposes, as sometimes administrators want to know when a datafile is growing very large very quickly.
In your case, you could issue
alter tablespace indx2 autoextend on maxsize <some number>G;, which would allow this tablespace to automatically extend to that maximum size, depending on how much space you have on your server! If you run out of actual physical disk, nothing in the Oracle database will protect you from this.Additionally, you'll still want to periodically monitor the size of this tablespace, as you will (theoretically) still eventually reach the maximum size of the autoextend. Even if you set the maxsize to unlimited, there are still factors that determine the maximum size of a tablespace that are out-of-scope for your particular question. Just know that there is always a maximum upper limit to a tablespace, and someone will have to monitor it to take appropriate action.