query

walkerdba
walkerdba used Ask the Experts™
on
SQL> create tablespace my_tb datafile '/u01/oracle/oradata/repdb/my_tbs.dbf' size 100m; Tablespace created.


 SQL> create table my_tab tablespace my_tbs as select * from dba_objects;

why we are using the part " select * from dba_objects" in the query.....


??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
armchair_scouseDeveloper

Commented:
Presumably to adopt the structure/columns of dba_objects as the basis of the structure of your table my_tbs, and possibly to import any records from dba_objects if there are any present in the table.

Happy New Year!!
David VanZandtOracle Database Administrator III

Commented:
The sys.dba_objects view shows the data dictionary (every database object).  It is a commonly cited set of "random" data.  As your example shows, it's a quick way to populate a test table.  The other advantage is that it gives you a consistent test bed if you need to drop and repopulate the table.
Oracle dba
Top Expert 2009
Commented:
you have 2 different names of tablespace in the 2 commands.

tablespace created: MY_TB
table destination tablespace: MY_TBS

was the intention not the same tablespace  ?
or just a typo ... :)
Mark GeerlingsDatabase Administrator

Commented:
Or, are you asking: "Why create a table that is a copy of data that is already available from a view?"  One reason for doing this is for performance.  If you need to retrieve this data multiple times, repeated queries of this table will likely be much faster (and cause many fewer logical block reads) than a query from the view.

Author

Commented:
solved

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial